This Question have no Text answers yet!

Java, PostgreSQL, and Hibernate: Select statement with nested strings

By : Tinclon
Source: Stackoverflow.com
Question!

I'm trying to unload data from RedShift to S3 using Java + Hibernate, but cannot figure out how to get it to accept the string-within-a-string in the SQL.

No matter the escape sequences I try to use to get the quote characters correctly passed to RedShift, I just get exception after exception.

Any ideas would be welcome. Thanks.

Here's the query I'm trying to run on RedShift (and if I type this directly into RedShift, it works):

unload ('select raw_line from stl_load_errors where starttime > \'2013-01-01\' ')
  to 's3://myBucket/myTable/'
  credentials 'aws_access_key_id=*;aws_secret_access_key=*' gzip;

And here are the various commands I've tried in Java, with the errors I've encountered:

Single backslash

session.createSQLQuery("unload ('select raw_line from stl_load_errors where starttime > \'2013-01-01\' ')"
  + " to 's3://myBucket/myTable/'"
  + " credentials 'aws_access_key_id=" + key + ";aws_secret_access_key=" + secret + "'  gzip")
  .executeUpdate();

RESULTS
WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 0, SQLState: 42601
ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - ERROR: syntax error at or near "2013" Position: 66
ERROR org.hibernate.exception.SQLGrammarException: could not execute statement

Double backslash

session.createSQLQuery("unload ('select raw_line from stl_load_errors where starttime > \\'2013-01-01\\' ')"
  + " to 's3://myBucket/myTable/'"
  + " credentials 'aws_access_key_id=" + key + ";aws_secret_access_key=" + secret + "'  gzip")
  .executeUpdate();

RESULTS
ERROR org.hibernate.QueryException: Space is not allowed after parameter prefix ':' [unload ('select raw_line from stl_load_errors where starttime > \'2013-01-01\' ') to 's3://myBucket/myTable/' credentials 'aws_access_key_id=*;aws_secret_access_key=*' gzip]
at org.hibernate.engine.query.spi.ParameterParser.parse(ParameterParser.java:95)

Triple backslash

session.createSQLQuery("unload ('select raw_line from stl_load_errors where starttime > \\\'2013-01-01\\\' ')"
  + " to 's3://myBucket/myTable/'"
  + " credentials 'aws_access_key_id=" + key + ";aws_secret_access_key=" + secret + "'  gzip")
  .executeUpdate();

RESULTS
2013-06-19 01:39:59,233 ERROR org.hibernate.QueryException: Space is not allowed after parameter prefix ':' [unload ('select raw_line from stl_load_errors where starttime > \'2013-01-01\' ') to 's3://myBucket/myTable/' credentials 'aws_access_key_id=*;aws_secret_access_key=*' gzip]
at org.hibernate.engine.query.spi.ParameterParser.parse(ParameterParser.java:95)

Quadruple backslash

session.createSQLQuery("unload ('select raw_line from stl_load_errors where starttime > \\\\'2013-01-01\\\\' ')"
  + " to 's3://myBucket/myTable/'"
  + " credentials 'aws_access_key_id=" + key + ";aws_secret_access_key=" + secret + "'  gzip")
  .executeUpdate();

RESULTS
WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 0, SQLState: 42601
ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - ERROR: syntax error at or near "2013" Position: 68
ERROR org.hibernate.exception.SQLGrammarException: could not execute statement

Named parameter

session.createSQLQuery("unload (:query)"
  + " to 's3://myBucket/myTable/'"
  + " credentials 'aws_access_key_id=" + key + ";aws_secret_access_key=" + secret + "'  gzip")
  .setString("query", "select raw_line from stl_load_errors where starttime > '2013-01-01'")
  .executeUpdate();

RESULTS:
WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 0, SQLState: 42601
ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - ERROR: syntax error at or near "$1" Position: 9
ERROR org.hibernate.exception.SQLGrammarException: could not execute statement

By : Tinclon


Video about Java, PostgreSQL, and Hibernate: Select statement with nested strings