How to get text that is printed into console using sybase procedure into shell script

Question

I have a sybase procedure which prints data like: print 'Hello World'.

How can I get that text into shell script. In other words how can a shell script read from the console of the database?

Answer 1

You can do this a couple of different ways. If you have a script file that you are running, do the following to specify an output file:

isql -U username -P password -S servername -i Inputscript.name -o outputfile.name

It will run the script, and output the results into the file specified by the -o

To run isql 'interactively' in your script you can do the following within your shell script:

isql -U username -P password -S servername -b << ENDSQL >> outputfile.name
set nocount on    \*stops displaying rows affected count*\
go
select some, data from table
go
ENDSQL

You can also use the example above to set the results of a query to a variable:

myvar = `isql -U username -P password -S servername << ENDSQL
sp_my_procedure
go
ENDSQL`

A couple of notes. If you are executing SQL statements, and are going to use the results in other processing (e.g. not just trying to print them), you will likely want to set nocount on within your SQL to prevent affected rowcount from printing, and use the -b flag in your isql connection string to prevent headers from being printed.