We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Vsql: parameter passing — Vertica Forum

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