How to execute an SQL statement stored in a shell script variable using isql

Question

So, I am trying to execute an SQL statement stored in a shell script variable using isql

Generic code attached.

SQL="select * from Student"

EMPLOYEES=`isql -U $USERNAME -P $PASSWORD -D $DATABASE -S $SERVER<<EOF
$SQL
go

However on executing this, I get an error such as: Incorrect syntax near the keyword 'Select' and Incorrect suntax near '='

Anyone knows how to execute the SQL query directly stored in a variable from isql?

Your "here document" is not closed (no single line with "EOF") and also your backticks are not closed. Is this a copy/paste error on your part? — Christian.K, Mar 07 '12 at 08:56

score 6 · Accepted Answer · answered Mar 07 '12 at 08:53

6

Try in this way:

EMPLOYEES=`echo "$SQL" | isql -U $USERNAME -P $PASSWORD -D $DATABASE -S $SERVER`

answered Mar 07 '12 at 08:53

Ivan Kinash

Thanks, turns out I was using in double quotes from the file where these SQL statements were being picked up. Taking them off and adding the EOF worked. – Rishabh Mar 07 '12 at 12:11

score 4 · Answer 2 · answered Mar 07 '12 at 09:04

According to this page, the following should work:

   SQL="select * from Student"

   EMPLOYEES=`isql -U $USERNAME -P $PASSWORD -D $DATABASE -S $SERVER<<EOF
   $SQL
   go
   EOF
   `

Also see my comment to your question, because in the code you posted you were lacking the closing "EOF" and backtick (each on a separate line).

You could also try to do it as @user1254184 has suggested in his answer:

   EMPLOYEES=`echo "$SQL" | isql -U $USERNAME -P $PASSWORD -D $DATABASE -S $SERVER`

Finally, to work around potential issues with quotes, etc. you could use a temporary file:

   echo "$SQL" > $TMP/sql.$$
   EMPLOYEES=`isql -U $USERNAME -P $PASSWORD -D $DATABASE -S $SERVER -i $TMP/sql.$$`
   rm $TMP/sql.$$

You may want to choose a better name for the temporary file.

Neat, this is an interesting solution – Rishabh Mar 07 '12 at 12:12 — Rishabh, Mar 07 '12 at 12:12

2 Answers2