Redshift Unload(Select Statement) with named arguments is causing UnableToExecuteStatementException.

[Viswadeep Veguru]

Hi,

I am seeing an exception with named arguments and Unload.

What I understand from here, Select Statement is 'TICKED' so the parser is considering as constant, so its not replacing the named arguments.

please let me know, what is the best way to solve this problem.

Thanks

Viswadeep

The following is the exception

Caused by: org.skife.jdbi.v2.exceptions.CallbackFailedException: org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: java.sql.SQLException: [Amazon](500310) Invalid operation: syntax error at or near ":"; [statement:"UNLOAD ('select  CreditCardCity  from PaymentMethod paymentmethod where paymentmethod.UPDATEDDATE < :p0') to 's3://location/test/'  WITH credentials AS 'aws_access_key_id=XXXX;aws_secret_access_key=XXXX' manifest gzip ESCAPE ALLOWOVERWRITE parallel off ", located:"UNLOAD ('select  CreditCardCity  from PaymentMethod paymentmethod where paymentmethod.UPDATEDDATE < :p0') to 's3://location/test/'  WITH credentials AS 'aws_access_key_id=XXXX;aws_secret_access_key=XXXX' manifest gzip ESCAPE ALLOWOVERWRITE parallel off ", rewritten:"UNLOAD ('select  CreditCardCity  from PaymentMethod paymentmethod where paymentmethod.UPDATEDDATE < :p0') to 's3://location/test/'  WITH credentials AS 'aws_access_key_id=XXXX;aws_secret_access_key=XXXX' manifest gzip ESCAPE ALLOWOVERWRITE parallel off ", arguments:{ positional:{}, named:{p0:2015-12-24 15:29:15.0}, finder:[]}]

        at org.skife.jdbi.v2.DBI.withHandle(DBI.java:284)

        ... 20 more

Caused by: org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: java.sql.SQLException: [Amazon](500310) Invalid operation: syntax error at or near ":"; [statement:"UNLOAD ('select  CreditCardCity  from PaymentMethod paymentmethod where paymentmethod.UPDATEDDATE < :p0') to 's3://location/test/'  WITH credentials AS 'aws_access_key_id=XXXX;aws_secret_access_key=XXXX' manifest gzip ESCAPE ALLOWOVERWRITE parallel off ", located:"UNLOAD ('select  CreditCardCity  from PaymentMethod paymentmethod where paymentmethod.UPDATEDDATE < :p0') to 's3://location/test/'  WITH credentials AS 'aws_access_key_id=XXXX;aws_secret_access_key=XXXX' manifest gzip ESCAPE ALLOWOVERWRITE parallel off ", rewritten:"UNLOAD ('select  CreditCardCity  from PaymentMethod paymentmethod where paymentmethod.UPDATEDDATE < :p0') to 's3://location/test/'  WITH credentials AS 'aws_access_key_id=XXXX;aws_secret_access_key=XXXX' manifest gzip ESCAPE ALLOWOVERWRITE parallel off ", arguments:{ positional:{}, named:{p0:2015-12-24 15:29:15.0}, finder:[]}]

        at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1334)

        at org.skife.jdbi.v2.Query.fold(Query.java:173)

        at org.skife.jdbi.v2.Query.list(Query.java:82)

        at org.skife.jdbi.v2.Query.list(Query.java:75)

[Steven Schlansker]

Hi Viswadeep,

I do not understand Redshift at all, so please help me understand --
I believe this behavior is correct. jdbi will not substitute parameters
inside of a string value; that would cause severe issues with almost
any other database. For example imagine if you tried to run the statement

SELECT * FROM table WHERE value1=':active' AND value2=:bind

You would only expect to bind one parameter, "bind", but jdbi
would translate the first WHERE clause to value1='?' which makes no
sense at all.

I can't see a way to make this work like you want. I did some brief searching around
and was unable to find any examples at all of people binding parameters
with UNLOAD statements -- are you sure this is possible? You might have better
luck asking this sort of question in a Redshift specific forum, if you get
an answer then we would happily help you with any jdbi specifics.

If you really end up wanting it to work this way, and establish that Redshift will
handle bound parameters the way you intend, you could try implementing a
custom StatementRewriter.

Please let me know if I've misunderstood.

Best,
Steven

> --
> You received this message because you are subscribed to the Google Groups "jDBI" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Brian McCallister]

If you need to interpolate into string literals you are going to have to use templating. I am not aware of any JDBC driver that will interpolate params into a string lteral, though maybe the redshift one will.

You could use the included in StringTemplate (1) based templating mechanism to treat the statement as a template, but if you do this you should probably carefully validate your template values to not accidentally break the sql.

-Brian

1) https://github.com/jdbi/jdbi/blob/master/src/main/java/org/skife/jdbi/v2/sqlobject/stringtemplate/StringTemplate3StatementLocator.java

[Viswadeep]

After working on this further, i have discovered this "Unload('select...')..."  can not be bounded,  The reason seems 'Select..' is considered as String literal.

Viswadeep Veguru.

 

You received this message because you are subscribed to a topic in the Google Groups "jDBI" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jdbi/MSNk8Km7pho/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jdbi+uns...@googlegroups.com.