Sybase Variables and local Shell variables 
Author Message
 Sybase Variables and local Shell variables

I am trying to make ISQL statment calls in my shell script.

Following is part of the code I have in my script.

ISQL -U$LOGIN <<EOF
$PASSWORD
use $DATABASE
go

-- This query would only result in one row
go
EOF


In other words I want to store the value of colm_1 in a shell variable say
$shell_colm


Thanks in advance

-neeru



Tue, 16 Nov 2004 22:35:20 GMT
 Sybase Variables and local Shell variables

I've used a somewhat crude method of doing this in the past that involved
sending the output of the isql statement to a temp file that is then parsed
until only the specific field desired is obtained. There may be more
efficient methods of accomplishing this and I would be interested in seeing
how other solutions have been implemented, but here's what I've used:

TEMPFILE=temp_isql.out
cp /dev/null $TEMPFILE

#
# RUN YOUR QUERY
#
isql -U$LOGIN -P$PWD -S$SERVER <<EOF > ${TEMPFILE}
use $DATABASE
go

go
EOF

# The following logic will edit out the first two lines and the
# last two lines of the output. They contain the column names and
# the number of rows selected, respectively.

NUM_LINES=`wc -l $TEMPFILE | cut -c1-9`
LAST_LINE=`expr $NUM_LINES - 2`
FIRST_LINE=`expr $LAST_LINE - 2`
shell_colm=`tail -$LAST_LINE $TEMPFILE | head -$FIRST_LINE | sed "s/
file://g"`
rm -f $TEMPFILE

$shell_colm should then contain your data.

NOTE: the sed command at the end of the line above should read sed
"s/<SPACE><FWD SLASH><FWD SLASH>g"` It appears as though Outlook Express has
manipulated the way it's displayed. Hopefully you'll be able to make sense
of this.

CB


Quote:
> I am trying to make ISQL statment calls in my shell script.

> Following is part of the code I have in my script.

> ISQL -U$LOGIN <<EOF
> $PASSWORD
> use $DATABASE
> go

> -- This query would only result in one row
> go
> EOF


> In other words I want to store the value of colm_1 in a shell variable say
> $shell_colm


> Thanks in advance

> -neeru



Wed, 17 Nov 2004 04:09:49 GMT
 Sybase Variables and local Shell variables
How about piping the output of isql to another process/loop, e.g.:

isql -b -w$LINELEN -U$LOGIN -P$PASSWORD -S$SERVER -D$DATABASE<<EOF |
set nocount on
select colm_1 from table1 group by colm_1
go
EOF
while read shell_com
do
echo $shell_com  # or whatever
done

Quote:

> I am trying to make ISQL statment calls in my shell script.

> Following is part of the code I have in my script.

> ISQL -U$LOGIN <<EOF
> $PASSWORD
> use $DATABASE
> go

> -- This query would only result in one row
> go
> EOF


> In other words I want to store the value of colm_1 in a shell variable say
> $shell_colm


> Thanks in advance

> -neeru

--

Phil Houstoun

-----------------------------------------------------------------------



Wed, 17 Nov 2004 23:58:11 GMT
 Sybase Variables and local Shell variables
Another simpler solution is to back-tick the whole isql command, e.g.:

$shell_com=`isql -b -w$LINELEN -U$LOGIN -P$PWORD -S$SERVER -D$DB<<EOF
set nocount on
select colm_1 from table1 group by colm_1
go
EOF`

However, the above example only works when returning a single row.  For
multiple rows, you'd still have to pipe to some sort of sub-shell.  Better to
trade in your #!/bin/sh for #!/bin/perl :^)

Quote:
> > I am trying to make ISQL statment calls in my shell script.

> > Following is part of the code I have in my script.

> > ISQL -U$LOGIN <<EOF
> > $PASSWORD
> > use $DATABASE
> > go

> > -- This query would only result in one row
> > go
> > EOF


> > In other words I want to store the value of colm_1 in a shell variable say
> > $shell_colm


> > Thanks in advance

> > -neeru

> --

> Phil Houstoun

> -----------------------------------------------------------------------

--

Phil Houstoun

-----------------------------------------------------------------------



Thu, 18 Nov 2004 01:56:28 GMT
 Sybase Variables and local Shell variables

Quote:

> Another simpler solution is to back-tick the whole isql command, e.g.:

> $shell_com=`isql -b -w$LINELEN -U$LOGIN -P$PWORD -S$SERVER -D$DB<<EOF
> set nocount on
> select colm_1 from table1 group by colm_1
> go
> EOF`

> However, the above example only works when returning a single row.  For
> multiple rows, you'd still have to pipe to some sort of sub-shell.  Better to
> trade in your #!/bin/sh for #!/bin/perl :^)

> > > I am trying to make ISQL statment calls in my shell script.

> > > Following is part of the code I have in my script.

> > > ISQL -U$LOGIN <<EOF
> > > $PASSWORD
> > > use $DATABASE
> > > go

> > > -- This query would only result in one row
> > > go
> > > EOF


> > > In other words I want to store the value of colm_1 in a shell variable say
> > > $shell_colm


> > > Thanks in advance

> > > -neeru

> > --

> > Phil Houstoun

> > -----------------------------------------------------------------------

Thanks for your quick responses though if I try the while loop, the
script seems to hang. Any thoughts?

Again i appreciate all your responses. I knew I could do it by
exporting the variables output to a file and greping or parsing the
file to my desired result. I just dreaded to write a huge script to
parse the file..
Thank you
-Neeru



Fri, 19 Nov 2004 22:58:57 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. shell variable into bind variable

2. How to assign a value into local variable from a query in string variable

3. Using variable withing shell scripts for calling Oracle DB procedures

4. SQL Loader + korn shell variables

5. V$ views in CRON SQL*Plus script - shell thinks $xxx is a variable

6. Problem getting shell variable under NT

7. Returning Oracle Variable to the Shell

8. unix shell script to isql variable sharing

9. How to pass a shell variable to ISQL?

10. Need to access shell environment variable from stored procedures

11. R-4GL, acess to shell enviroment variables

12. Export data in order to use it as a variable in shell


 
Powered by phpBB® Forum Software