Solved

output parameter into unix variable

Posted on 2002-05-06
9
5,181 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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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 120 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

733 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