isql out in shell script

Shell Programming and Scripting
1

Hi
I wrote a script for connecting isql(Sybase database) and stored the SQL output in Unix box.

my script:

isql -U${USER} -P${PASS} -S${SERVER} <<EOF > $WORK_DIR/out1.log
go	
select convert(varchar(10),sdcurrent,101) from pr_sysdate
go
exit
EOF

Unfortunately, the output file not having the sql query out put.

Please advise on this

2

Have you tested what you get using
isql -U${USER} -P${PASS} -S${SERVER}
and give those inputlines from stdin ?
Maybe ; needed in every sqlline - usually sql syntax need ;

3

Yes... the isql has been connecting to Database... but not executing next commands.

Code:
USER="xyz"
PASS="abc"
SERVER="SCCS"
DB="blue"
WORK_DIR="/usr/home/genesys/Sayantan/ramakrishna"
set -x
	isql -U${USER} -P${PASS} -S${SERVER}<<EOF
	go
	sp_help NBEventLog	
	go
	exit
	EOF


Out put: 

+ isql -Uxyz -Pabc -SSCCS
+ 0< /var/tmp/sh14880.1
Msg 2409, Level 11, State 2:
Cannot find the requested character set in Syscharsets:  name = 'roman8'.
No conversions will be done.

I hope the isql not able recognize the EOF(stdin) in script.Please help me.
The Cannot find message are expecting in my isql.

4

I've never used Sybase, but any SQL engine that I've used had a limited set of commands that were recognized. I don't think it can intrepret the EOF logic. In the past I have created a temp file in the stream of the script and then executed the isql (or similar) with a directive to use a file for command input and use the temp file there. This may be a good option for you.

5

Thanks... :wink:

6

If you do a search on sybase and scripts, you will find many threads about it. Many of them use the EOF with no issues - the one's I've notice do NOT use an exit - that maybe what is messing with your script.

See sybase connection through shell script as one example

and I'm not sure, but don't you need -o in your isql statement to specify the output file?

isql -U${USER} -P${PASS} -S${SERVER} <<EOF > -o $WORK_DIR/out1.log