Shell Programming and Scripting

I can do this on the command line:

Code:

sqsh -S 192.168.x.x -o tmp -U user -P fakepass -D horizon -C "\
select second_id
from borrower
where btype like '%wsd%'
"

I can also just leave the SQL at the end intact on one line ....
... However, when I throw this in a script like:

Code:

$SQSH -o SQSHout -S $SERVER -U $USER -P $PASS -D $DB -C '"SELECT second_id FROM borrower WHERE btype like "%wsd%""'

When I run the script I get the SQSH help page. So my question is whether
this is an oddity of bash quoting or a weird sqsh thing.

You can do stuff like this with isql by piping the string or using here
docs. sqsh uses the -C option to allow the SQL at the end. sqsh is much better for the most part, but I can't get the string part to work. I've tried numerous quoting combos. I've already looked at the sqsh man page more
than I care to mention. Is this my bash ignorance or a limitation of sqsh?

I gotta go do something else before I throw myself under a train.

Thanks for reading ~

Bub

With

Code:

$SQSH -o SQSHout -S $SERVER -U $USER -P $PASS -D $DB -C '"SELECT second_id FROM borrower WHERE btype like "%wsd%""'

you passed the following query to sqsh:

Code:

"SELECT second_id FROM borrower WHERE btype like "%wsd%""

**** UPDATE ~ binlib points out an easier ( more correct ) way to quote the expression in the post following this one. ******

How do I hide quotes from sqsh and pass the SQL?

---------- Post updated at 06:58 PM ---------- Previous update was at 06:02 PM ----------

Solution ....finally.

Problem:
Protect quoted expression "%wsd%" from bashes prying eyes. sqsh must see it in quotes.

Answer:

Code:

SQL="SELECT second_id FROM borrower WHERE btype like '"'%wsd%'"'"

Victory is sweet. Now if I could just get out of this damn valentines banquet I get
conned into every year .....[SIGH]

Thanks ~

Bub

PS: sqsh rocks if you need to deal with Sybase or MS SQL server.

---------- Post updated at 07:14 PM ---------- Previous update was at 06:58 PM ----------

I've seen other SQSH users in this mess while Googling, so here's a tip for
passing queries to SQSH without resorting to external SQL scripts.

Define your query:

Code:

SQL="SELECT second_id FROM borrower WHERE btype like '"'%wsd%'"'"

Note carefully the quoting in the expression %wsd%. The expression is surrounded by single quoted double quotes: eg., '"'
while the whole query is surrounded by single quotes ...making
for a quote mess on the end ...eg., '"'" ( thats: single - double - single - double ).

***** EDIT ***** Please see the following two posts ...esp. from Binlib. You can quote the last expression with a single set of single quotes.
Thanks binlib! Tested and true.

Next, run that filthy query like so:

Code:

$SQSH="/usr/bin/sqsh"
$SERVER="server.whatever.org"
$USER="username"
$PASS="secrets"
$DB="nameOfDatabase"

$SQSH -S "$SERVER" -U "$USER" -P "$PASS" -D "$DB" -C "$SQL" > $OUT

You don't necessarily need all the variables, but it's a bit easier on
the eyes of those stuck reading it later. I usually define the sqsh
binary for cron, otherwise you'd likely have to define it for the
cron environment. Could be mistaken ...I always try to cron-proof
all but the most common commands. Easier than fixing every cron
environment path variables.

Later ...

Bub

***** UPDATED **** Please see following two posts for simpler quoting option.

Code:

"SELECT second_id FROM borrower WHERE btype like '"'%wsd%'"'"

is one argument made of three parts:

Code:

SELECT second_id FROM borrower WHERE btype like '

quoted by double quotes, followed by

Code:

%wsd%

quoted by single quote, followed by

Code:

'

quoted by double quotes.
Since the %wsd% part doesn't need to be single quoted, it can be simply embedded with the other parts. As in

Code:

"SELECT second_id FROM borrower WHERE btype like '%wsd%'"

which is simpler. In fact, it even works if you want the wsd part to be a variable. On the other hand, if the wsd part has dollar signs, then your method of single quoting it is better.

I had declared sqsh like this at the top:

Code:

declare -rx SQSH=/usr/bin/sqsh

When I removed the 'declare' part and just said:

Code:

SQSH="/usr/bin/sqsh"

...it worked. This coincided with the last quoting concoction
I had tried ...thus I mistakenly concluded that it was the
quoting that fixed it.

This issue was strange because sqsh was running but complaining about
syntax errors in the options. So basically I was chasing the proverbial
'red herring'.

I thought that the 'declare -rx' syntax was perfectly legal. I have used this
form before with no issues. I sync with rsync this way. I wonder what the deal is with sqsh?

Thanks for pointing out my mistake!!

Bub