?
Solved

output parameter into unix variable

Posted on 2002-05-06
9
Medium Priority
?
5,314 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Check out the latest tech news, community articles, and expert highlights in August's newsletter.
Active Directory can easily get cluttered with unused service, user and computer accounts. In this article, I will show you the way I like to implement ADCleanup..
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

777 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