Passing shell variable to Informix sql

[mstrong]

Hi,

I'm trying to write a k shell script that will prompt the user for a location, and then pass that value to a stand alone sql that the script will call. I've done this with Oracle before, but the same method isn't working. This is the way I've approached it so far:

echo " Enter location "
read LOC
export LOC
/opt/informix/bin/dbaccess dbname /invctrl/freeze_cnt.sql
the sql is:

SELECT
count(*) from itemloc

where
location='&LOC' and
freeze_fl='Y'
I've tried different variations on the location, and as it is now dbaccess is taking it as a literal, not a variable. Any help is appreciated.

 

[RobJordan]

Does it work if you do each step of the script
from the command line including the export?

Robert Robert G. Jordan

Robert@JORDAN2000.com

http://www.JORDAN2000.com/scripts

Unix Sys Admin
Chicago, Illinois U.S.A.

 

[olded]

mstrong:

First, your question probably should have been over in the "informix Online Server" or "informix solutions" forum, but I can handle it here.

Unfortunately, Informix doesn't handle embedding shell variables the same way Oracle does. In order for Informix to function, the way you're trying to write it. You'd have to embed the value of LOC into the SQL script.

One method is to use a "unix here" document; you can do something like this:

echo " Enter location "
read LOC

dbaccess -e dbname <<MSG
SELECT count(*) from itemloc
where location= &quot;$LOC&quot; and freeze_fl='Y'
EDS

If you want to send the count to a file:

dbaccess -e dbname <<MSG
output to count.txt without headings
SELECT count(*) from itemloc
where location= &quot;$LOC&quot; and freeze_fl='Y'
EDS


Let me know if you have any other questions.

Ed
Schaefer
olded@ix.netcom.com

 

[mstrong]

That did the trick. Thanks for the help guys.

 

[Diogo]

Hello.

It stows searching a doubt mine and it found my reply here.

It happens that when I place statment &quot;where&quot; it gives error to me.

Somebody knows to say me reason?

Thanks any way.


DR

 

[PHV]

Diogo, which error ?
If you want some help, please post your code and the error message you get.

 

[Diogo]

The code is:
dbaccess b4c4 <<MSG
unload to /usr/bin/gls/teste/teste.txt
select * from ttriem010800 where 1=1
EDS

And the error is:
Database selected.
201: A syntax error has occurred.
Database closed.
teste.sh[11]: Error: not found.

DR

 

[PHV]

Try this:

dbaccess b4c4 - <<MSG
unload to '/usr/bin/gls/teste/teste.txt'
select * from ttriem010800 where 1=1
MSG

Or you can try this:

echo "
unload to '/usr/bin/gls/teste/teste.txt'
select * from ttriem010800 where 1=1
" | dbaccess b4c4 -

Note: don't understand the where clause.

Hope This Help
PH.

 

[Diogo]

Excuse to place this condition, but already was in desperation.
I want to place another condition (t_idre=&quot;123&quot.
To happen that if this condition(1=1) not to function, does not function none, certain?

If I to remove &quot;where 1=1&quot;, functions well.


Thanks any way.

DR

 

[PHV]

And what is the problem with the condition on the t_idre column ?

 

[Diogo]

It gives error to me. The same that I wrote behind.
Then to know if the error he was of the field t_idre, I tried to place 1=1, and there he had to function without any problem. I find.

DR

 

[Diogo]

It stows making tests and it left to give error to me.

The final result is:
dbaccess b4c4 <<MSG
unload to '/usr/bin/gls/teste/result.txt'
select * from ttriem010800 where t_idre = &quot;123&quot;;

Thanks any way.

DR

 

[PHV]

A tip if you raise syntax error in informix:
Open a interactive dbaccess session and submit your faulty sql code. You will have the cursor positionned near the location of the error when you choose the Modify option.

Hope This Help
PH.