Start a new topic

Variables not working with Vertica in 92.2327

[This topic is migrated from our old forums. The original author name has been removed]

We have the following script to add new users in Vertica using the DBVis variables. This script worked fine in previous versions but is generating errors now. Note that we don't have a "$1" in the inputs we are attempting to inject into the statements. ----- begin script ----- '-- create user create user ${username}$ identified by '${password}$'; '-- grant access to the ad hoc query pool grant usage on resource pool adhoc to ${username}$; '-- set default user pool alter user ${username}$ resource pool adhoc; '-- grant access to read only role grant reportuser_read to ${username}$; '-- set default role alter user ${username}$ default role reportuser_read; ----- begin error output ----- 08:48:06 [-- - 0 row(s), 0.000 secs] [Error Code: 4856, SQL State: 42601] [Vertica][VJDBC](4856) ERROR: Syntax error at or near "$1" 08:48:06 [-- - 0 row(s), 0.000 secs] [Error Code: 4856, SQL State: 42601] [Vertica][VJDBC](4856) ERROR: Syntax error at or near "$1" 08:48:06 [-- - 0 row(s), 0.000 secs] [Error Code: 4856, SQL State: 42601] [Vertica][VJDBC](4856) ERROR: Syntax error at or near "$1" 08:48:06 [-- - 0 row(s), 0.000 secs] [Error Code: 4856, SQL State: 42601] [Vertica][VJDBC](4856) ERROR: Syntax error at or near "$1" 08:48:06 [-- - 0 row(s), 0.000 secs] [Error Code: 4856, SQL State: 42601] [Vertica][VJDBC](4856) ERROR: Syntax error at or near "$1" ----- Upon further inspection it looks like the variable injection for Vertica in general is broken. The following 1 line statement with a fully described variable fails as well: select * from tables where table_schema = '${schema_name||public||string}$'; 08:52:00 [SELECT - 0 row(s), 0.000 secs] Error setting value for column 1: java.sql.SQLException: [Vertica][JDBC](10940) Invalid parameter index: 1. Thanks, dave

[This reply is migrated from our old forums.]

Re: Variables not working with Vertica in 92.2327
David, Thanks for your post. In the latest beta the support for variables has been enhanced (maybe too much based on your response) and that is the new Type column next to each variable in the variable window. It is automatically set based on the input you enter for each variable. The drawback for backwards compatibility is that whenever a type is set (other than Unspecified) the final SQL is prepared with the database meaning that all variables are replaced with place holders (?). Each value is then bound with the corresponding question mark. The automatic type detection should probably be an option which is disabled for backwards compatibility. The current workaround for your first set of SQLs is to make sure the Type = Unspecified before you click "Continue" in the variable window. For the last sample in your post you specifically define the variable as being "string". The type is then indicated as being a string in the variable window and the preparation steps as explained above are executed. This is no different than previous versions. The only thing you need to do is to remove the quotes around the variable. In the latest beta you also have the option to switch from the String type to Unspecified and in this case leave the quotes as-is. We will look into a fix for the upcoming beta so that this by default works as in previous versions. Regards Roger
[This reply is migrated from our old forums.]

Re: Variables not working with Vertica in 92.2327
David, Just to inform you that this is fixed in the latest 92.2328 beta version. http://www.dbvis.com/download/beta/ Regards Roger