passing vsql string and date variables from bash shell to sql file

David_Uchimoto

How can I pass variables that require wrapping in single quotes (i.e. strings and dates) to a sql file using vsql from a bash command?  vsql parameter substitution works fine for types that don't require single quote wrapping (int, numeric) in the sql, but I haven't found any examples that demonstrate usage with a type requiring quotes.

var_test.sql:
select table_name from tables where table_name = :table_name;

command:
$ vsql -d my_db -h localhost -w my_pwd -f ./var_test.sql -v table_name=my_table_name -e

The example above passes the :table_name variable in from the command line, but the sql throws an error because the table name is not wrapped in single quotes ("column "my_table_name" does not exist").  If I wrap the :table_name variable in single quotes, the parameter substitution breaks.

Thanks,
Dave Uchimoto

id10t

Hi!

Script: show_table.sql

--
-- show table
-- 
SELECT * FROM v_catalog.all_tables WHERE table_name = :TABLE;

Run script from command line:

$> vsql -v TABLE="'Auction'" -f ./show_table.sql 
 schema_name |     table_id      | table_name | table_type | remarks 
-------------+-------------------+------------+------------+---------
 public      | 45035996274842314 | Auction    | TABLE      | 
(1 row)

Verify output (1) from vsql:

daniel=> \set TABLE '''Auction'''
daniel=> \i /tmp/show_table.sql 
 schema_name |     table_id      | table_name | table_type | remarks 
-------------+-------------------+------------+------------+---------
 public      | 45035996274842314 | Auction    | TABLE      | 
(1 row)

Verify output (2) from vsql:

daniel=> SELECT * FROM v_catalog.all_tables WHERE table_name = 'Auction';
 schema_name |     table_id      | table_name | table_type | remarks 
-------------+-------------------+------------+------------+---------
 public      | 45035996274842314 | Auction    | TABLE      | 
(1 row)

Regards, idiot.

Adrian_Oprea

Nice nickname !! kkkk . Also great expl.

id10t

Thanks, Im big and fat troll

David_Uchimoto

Thanks.  

To be clear to anyone reading, when using parameter passing from the command line (-v, --set) you need to wrap the parameter value in single quotes and wrap that with double quotes.  Single quotes with escaped single quotes doesn't work (it's hard to tell with the font type on this page, which is which).

WORKS:
$ vsql -d my_db -h localhost -w my_pwd -f ./var_test.sql -v table_name="'my_table_name'" -e

DOES NOT WORK:
$ vsql -d my_db -h localhost -w my_pwd -f ./var_test.sql -v table_name='''my_table_name''' -e

id10t

you need to wrap the parameter value in single quotes and wrap that with double quotes

Not agree, you don't need to do it. Its a correct syntax, but you can avoid quotes with some kind of hack. Actually quotes needed for pointing if is it KEYWORD or STRING or any other DATA TYPE.

Wrong conclusion, read docs carefully with attention.
I agree that this subject is not clear in docs - for that Vertica has community.

Example(for no quotes)

SCRIPT - dates.sql:

--
-- an example how to use in 'vsql' named params
--

-----------------------------------------------
-- here is hack: add quotes in script

\set MINDATE '':MINDATE''::DATE

------------------------------------------------
SELECT
    'TODAY'::DATE - :MINDATE AS 'DATES DIFF',
    'TODAY'::DATE            AS 'TODAY',
    :MINDATE                 AS 'MIN DATE'
FROM
    v_catalog.dual;



USAGE
$ vsql -v MINDATE=2014-01-01 -f dates.sql  DATES DIFF |   TODAY    |  MIN DATE   ------------+------------+------------         336 | 2014-12-03 | 2014-01-01 (1 row)

As you can see - no quotes around of MINDATE variable.
Looks better: http://pastebin.com/zuuznSAU

PS
GetSatisfaction - die finally.
Who is responsible for such idiotic decision to take non-free framework like GetSatisfaction with many bugs and not StackOverflow?

SO - free and well supported engine. UI is high configurable, just take a look on other SO forums like bikes, code review and so on. A lot of features like mobile support, source code highlighting and so on. SO engine exactly fits this community requirements + has embedded WIKI engine.

Who is my friend and an idiot too? Who is throwing money of HP Vertica to garbage - on buggy programs? You are head-paint of company and this community.

Best, idiot.

David_Uchimoto

You've taken my statement out of context

when using parameter passing from the command line (-v, --set) you need to wrap the parameter value in single quotes and wrap that with double quotes.

You are correct that single quotes with escaped single quotes works from the vsql prompt, but when you are executing a command or sql file from the console shell by calling the vsql executable with the -v or --set options, you need to do as I've laid out with my examples.

id10t

No, Im not.

when using parameter passing from the command line

Doesn't changes a thing. We didn't talk about passing params from Google or any other app, but from command line only, so this sentence doesn't adds/changes a thing. Take a look - I send a parameter from command line without quotes.

But its not important, what important - did you solved your problem? If yes, who cares?

you need to do as I've laid out with my examples.

Really? Ok, teach me.

$ cat /tmp/script.sql 
select :DAVE as 'DAVID MONG'

$ vsql -v DAVE=''

YES'' -f /tmp/script.sql
DAVID MONG
------------
YES
(1 row)

And one more time - wrong conclusions.

Parser removes chars, here is a full example: http://pastebin.com/ZjDdUHP1

PS
No I don't wanna confrontation with you, but you are MONG!
(Yes, and Im idiot, I know)

David_Uchimoto

I'm no BASH expert and this may be an idiosyncrasy of RHEL, but your example fails on my cluster.

[dbadmin@p3549571 ~]$ cat script.sql
select :DAVE as 'DAVID MONG'
[dbadmin@p3549571 ~]$ vsql -f ./script.sql --set DAVE=''YES''
vsql:./script.sql:1: ERROR 2624:  Column "YES" does not exist

Double or triple single quotes just doesn't work with the BASH invocation.  This, however, works.

[dbadmin@p3549571 ~]$ vsql -f ./script.sql --set DAVE="'YES'"
 DAVID MONG
------------
 YES
(1 row)

Sashidhar_Arcot

What I have:

cat export_table_ddl.sql
\timing

/* Exporting the table ddl */
Select count(*) from :SCHEMA_NAME.:TABLE_NAME;
Select export_objects('',':SCHEMA_NAME.:TABLE_NAME');

\timing

 

When I do:

$ vsql -v SCHEMA_NAME="POC_WS" -v TABLE_NAME="Channel_Forecast" -f export_table_ddl.sql
Timing is on.
count
-------
1100
(1 row)

Time: First fetch (1 row): 67.512 ms. All rows formatted: 67.620 ms
vsql:export_table_ddl.sql:5: ERROR 2569: Catalog object :SCHEMA_NAME.:TABLE_NAME does not exist
Timing is off.

 

 

I get the above error. I want to parameterize this script to have DDL exported only for the table I want and not for all the tables. How would I do it?