Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

output parameter into unix variable

Posted on 2002-05-06
9
Medium Priority
?
5,445 Views
Last Modified: 2012-08-14
Hi all,

Been a while since I've been here. Howeverm I've got a new question.
I'm trying to call a stored procedure from a unix shell script. So far, no problem. But in the stored procedure there is an outputparameter which I would like to put in a unix variable. This is giving me problems... I've tries a few things but so far, nothing. The sp works fine exept my unix-variable stays empty.

Any experience in this?


Code:

echo $1
isql $DB_CONNECTSTRING  > log.txt <<aho
declare @iets varchar(10)
exec aho_test @p_in = $1 , @p_out = @iets output
go
aho
echo $iets
cat log.txt

executed as: test 6
result:
6 <---- this is 'echo $1'
  <---- this is where the 'echo' of $iets should be.
(return status = 0)

Return parameters:

 @p_out
 ----------
 groter

(1 row affected)

when I try it like this:

echo $1
isql $DB_CONNECTSTRING  > log.txt <<aho
exec aho_test @p_in = $1 , @p_out = $iets output
go
aho
echo $iets
cat log.txt

then excute: test 6
this is the result:
6 <---- this is 'echo $1'
  <---- this is where the 'echo' of $iets should be.

(return status = 0)

Any one??
0
Comment
Question by:ahoor
9 Comments
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6991771
all i can think of is :

echo $1
isql $DB_CONNECTSTRING  > log.txt <<aho
exec aho_test @p_in = $1 , @p_out = @iets output
select @p_out
go
aho
cat log.txt

0
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6991785
oh i found somethign ...

var=`isql -U... << Sqlbatch
set nocount on
exec procname @var1 output
 :
 :
go
select @var1
go
Sqlbatch
`
Those are back quotes, BTW (the ones underneath the tilde)

0
 
LVL 3

Author Comment

by:ahoor
ID: 6995844
Sorry, no succes so far... what shell should this be executed in? ksh, sh?
Still the variable is not being filled...

This should be possible...
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 51

Expert Comment

by:ahoffmann
ID: 6996739
# if your shell is sh syntax:
iets="`cat log.txt`"
echo $iets

# if your shell is csh syntax
set iets = "`cat log.txt`"
echo $iets
0
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6996787
ahoor.. please take a close look at this example which seems to work for me.

var=`isql -U.. <<Sqlbatch
set nocount on
declare @b char(2)
exec xb @b output
go
select @b
Sqlbatch
`
echo $var

I am running this script in tcsh shell and it works fine for me.

If this does not work, could you please post the shell script that you are trying to execute.

Thanks.
0
 
LVL 3

Author Comment

by:ahoor
ID: 6996882
Alright, this works is I add one statement:

echo $1
var=`isql $DB_CONNECTSTRING <<aho
set nocount on
declare @iets varchar(10)
exec aho_test @p_in = $1 , @p_out = @iets output
go
aho
`
echo $var
echo $var | read a b c d e f g h iets
echo $iets

The output becomes:

6
(return status = 0) Return parameters: @p_out ---------- groter
groter

where 6 = $1 = input in shell
(return status etc = $var = output of sql statement
grotesr = $iets is 9th column of output which is the value of the output parameter.

I will leave this question open til friday for further remarks, maybe there's an easier way which you or someone will think off seeing this new input.
Thanks so far...
0
 
LVL 5

Accepted Solution

by:
amitpagarwal earned 480 total points
ID: 6996896
a slight change to get exactly what you want.

var=`isql -U.. <<Sqlbatch
set nocount on
declare @b char(2)
exec xb @b output
go
select @b
Sqlbatch
`
echo $var | awk '{print $2}'
0
 
LVL 7

Expert Comment

by:waynezhu
ID: 6997450
While ago, I wrote a similar script for Oracle  which did same thing to assign the SQL output to a shell variable. It appears you already have the answer. What I am going to do is to modify your script to make it little bit compact:

echo $1
iets=`isql $DB_CONNECTSTRING <<aho | awk '{print $9}'
set nocount on
declare @iets varchar(10)
exec aho_test @p_in = $1 , @p_out = @iets output
go
aho
`
echo $iets

In this way, the script will work for sh, ksh, bash, zsh.

I did not use the read command, because it does not work
for sh, bash, and even Linux's ksh (if RedHad 7.1 and up
you can use zsh instead)


For reference, I also pasted my script below:

#
TFBN=`
sqlplus -s $user/$passwd <<!  | head -1
set pages 0
select
decode(substr(p.value,1,1),'?','\$ORACLE_HOME'||substr(p.value,2),p.value)||'/ora_'||i.instance_name||'_'||pr.spid
from v\\$parameter p, v\\$instance i, v\\$process pr
where p.name='user_dump_dest'
and pr.addr=(select paddr from v\\$session where audsid = userenv('SESSIONID'));
!
`
echo $TFBN
#


Good luck.

0
 
LVL 3

Author Comment

by:ahoor
ID: 7005586
Right, I will accept Amitpagarwal's solution because this is the only one that also works for multiple output parameters which I may need in the near future.

Thanks all who participated.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my humble opinion (IMHO), TouchDown from Symantec is the best in class for this type of application, but Symantec has end-of-lifed it and although one can keep using it, it will no longer be supported or upgraded.  Time to look for alternatives t…
This blog will spread awareness about Dropbox. We have given the statements based upon our experience. Along with this, there is a section of some new plans that should be added in Dropbox this year. This will make the storage service enhanced from …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses
Course of the Month11 days, 12 hours left to enroll

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question