Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Vsql: parameter passing

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

Comments

  • 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.

     

     

     

  • It works perfectly.

    Thanks a lot for your rapid answer.

    Gilbert

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.