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
passing vsql string and date variables from bash shell to sql file
Hi!
Script: show_table.sql
Script: show_table.sql
--Run script from command line:
-- show table
--
SELECT * FROM v_catalog.all_tables WHERE table_name = :TABLE;
$> vsql -v TABLE="'Auction'" -f ./show_table.sqlVerify output (1) from vsql:
schema_name | table_id | table_name | table_type | remarks
-------------+-------------------+------------+------------+---------
public | 45035996274842314 | Auction | TABLE |
(1 row)
daniel=> \set TABLE '''Auction'''Verify output (2) from vsql:
daniel=> \i /tmp/show_table.sql
schema_name | table_id | table_name | table_type | remarks
-------------+-------------------+------------+------------+---------
public | 45035996274842314 | Auction | TABLE |
(1 row)
daniel=> SELECT * FROM v_catalog.all_tables WHERE table_name = 'Auction';Regards, idiot.
schema_name | table_id | table_name | table_type | remarks
-------------+-------------------+------------+------------+---------
public | 45035996274842314 | Auction | TABLE |
(1 row)
Nice nickname !! kkkk . Also great expl.
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
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
you need to wrap the parameter value in single quotes and wrap that with double quotesNot 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:
--As you can see - no quotes around of MINDATE variable.
-- 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)
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.
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.
No, Im not.
But its not important, what important - did you solved your problem? If yes, who cares?
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)
when using parameter passing from the command lineDoesn'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)
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)
[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)
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?
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
