Vsql: parameter passing

gsolet

Hi,

My problem is simple. I try to run a SQL script with parameter passing.

 

vsql -U supp_app -w bidon -h vertorix  -v pbstrt=PARAM1  -f test_param.sql -t 

 

Script SQL test_param.sql  :

\echo :pbstrt
select 'gogo '  || c.commentaire
from supp_app.comment_base c
where instance = :pbstrt;

And  I have this error :

PARAM1
vsql:test_param.sql:5: ERROR 2624:  Column "PARAM1" does not exist

 

Can anyone help me?

Gilbert

DerrickR

Hi Gilbert!

 

You've set :pbstrt to PARAM1.

 

When you use :pbstrt in your SQL statement, vsql replaces it with PARAM1. So you've written:

 

select 'gogo '  || c.commentaire
from supp_app.comment_base c
where instance = PARAM1;

 

As you can see, this will generate the error you are seeing if PARAM1 isn't a valid identifier for that query.

 

What you probably wanted was the following. Note the single quotes.

 

select 'gogo '  || c.commentaire
from supp_app.comment_base c
where instance = 'PARAM1';

 

To get this, you'll have to set :pbstrt to 'PARAM1'. Like this

 

vsql -v "pbstrt='PARAM1'"

 

Notice I used double quotes around the entire argument. Otherwise my shell (bash) would interpret the single quotes rather than see them as literal single quotes.

 

 

 

gsolet

It works perfectly.

Thanks a lot for your rapid answer.

Gilbert