Thread: JDBC syntax error at end of input
Hello,
I am trying to build a query using StringBuilder based off a variable size ArrayList of inputs. When testing a simplest use case of one input ID, I get an syntax error at end of input. However, if I copy the generated SQL and run it in pgAdmin3, it is able to execute successfully. Below is the error with loglevel2 enabled. Any advice would be much appreciated.
SELECT seq_chromats.species_code_4, seq_chromats.fasta_header_line, seq_chromats.fasta_sequence, seq_chromats.flag_two_reads_exist, seq_amplicons.amplicon_name, seq_primers.primer_name FROM seq_amplicons INNER JOIN seq_primers ON seq_primers.amplicon_name = seq_amplicons.amplicon_name INNER JOIN seq_chromats ON seq_chromats.primer_id = seq_primers.primer_id WHERE seq_amplicons.amplicon_name IN (('0_8156_01'))
14:40:53.272 (1) PostgreSQL 9.1 JDBC4 (build 901)
14:40:53.277 (1) Trying to establish a protocol version 3 connection to localhost:5432
14:40:53.282 (1) FE=> StartupPacket(user=sswap_agent, database=treegenes_development, client_encoding=UTF8, DateStyle=ISO, extra_float_digits=2)
14:40:53.286 (1) <=BE AuthenticationReqMD5(salt=3ce4592b)
14:40:53.287 (1) FE=> Password(md5digest=md5b4b2bdfb96e02dea70d85a4890a23c4e)
14:40:53.290 (1) <=BE AuthenticationOk
14:40:53.303 (1) <=BE ParameterStatus(application_name = )
14:40:53.303 (1) <=BE ParameterStatus(client_encoding = UTF8)
14:40:53.303 (1) <=BE ParameterStatus(DateStyle = ISO, MDY)
14:40:53.303 (1) <=BE ParameterStatus(integer_datetimes = on)
14:40:53.303 (1) <=BE ParameterStatus(IntervalStyle = postgres)
14:40:53.303 (1) <=BE ParameterStatus(is_superuser = off)
14:40:53.303 (1) <=BE ParameterStatus(server_encoding = SQL_ASCII)
14:40:53.303 (1) <=BE ParameterStatus(server_version = 9.0.7)
14:40:53.303 (1) <=BE ParameterStatus(session_authorization = sswap_agent)
14:40:53.303 (1) <=BE ParameterStatus(standard_conforming_strings = off)
14:40:53.303 (1) <=BE ParameterStatus(TimeZone = US/Pacific)
14:40:53.303 (1) <=BE BackendKeyData(pid=30534,ckey=1141196332)
14:40:53.303 (1) <=BE ReadyForQuery(I)
14:40:53.305 (1) simple execute, handler=org.postgresql.core.SetupQueryRunner$SimpleResultHandler@4b0d78ec, maxRows=0, fetchSize=0, flags=23
14:40:53.305 (1) FE=> Parse(stmt=null,query="SET extra_float_digits = 3",oids={})
14:40:53.307 (1) FE=> Bind(stmt=null,portal=null)
14:40:53.307 (1) FE=> Execute(portal=null,limit=1)
14:40:53.307 (1) FE=> Sync
14:40:53.309 (1) <=BE ParseComplete [null]
14:40:53.309 (1) <=BE BindComplete [null]
14:40:53.309 (1) <=BE CommandStatus(SET)
14:40:53.309 (1) <=BE ReadyForQuery(I)
14:40:53.310 (1) compatible = 9.1
14:40:53.310 (1) loglevel = 2
14:40:53.310 (1) prepare threshold = 5
getConnection returning driver[className=org.postgresql.Driver,org.postgresql.Driver@4669b7fe]
14:40:53.338 (1) simple execute, handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@31b27882, maxRows=0, fetchSize=0, flags=17
14:40:53.338 (1) FE=> Parse(stmt=null,query="SELECT seq_chromats.species_code_4, seq_chromats.fasta_header_line, seq_chromats.fasta_sequence, seq_chromats.flag_two_reads_exist, seq_amplicons.amplicon_name, seq_primers.primer_name FROM seq_amplicons INNER JOIN seq_primers ON seq_primers.amplicon_name = seq_amplicons.amplicon_name INNER JOIN seq_chromats ON seq_chromats.primer_id = seq_primers.primer_id WHERE seq_amplicons.amplicon_name IN (",oids={})
14:40:53.339 (1) FE=> Bind(stmt=null,portal=null)
14:40:53.339 (1) FE=> Describe(portal=null)
14:40:53.339 (1) FE=> Execute(portal=null,limit=0)
14:40:53.339 (1) FE=> Sync
14:40:53.346 (1) <=BE ErrorMessage(ERROR: syntax error at end of input
Position: 400)
org.postgresql.util.PSQLException: ERROR: syntax error at end of input
Position: 400
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
at edu.dendrome.sswap.QueryWithConfig.queryChromatSeqsByAmplicon(QueryWithConfig.java:380)
at edu.dendrome.sswap.AmpliconMultiFastaService.initializeRequest(AmpliconMultiFastaService.java:88)
at info.sswap.api.servlet.SimpleSSWAPServlet.handleRequest(SimpleSSWAPServlet.java:125)
at info.sswap.api.servlet.AbstractSSWAPServlet.doPost(AbstractSSWAPServlet.java:658)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Thread.java:680)
SQLException: SQLState(42601)
14:40:53.350 (1) <=BE ReadyForQuery(I)
ERROR: syntax error at end of input
Position: 400
Best,
-Hans
On Thu, May 17, 2012 at 2:49 PM, Hans Vasquez-Gross <havasquezgross@ucdavis.edu> wrote: > I am trying to build a query using StringBuilder based off a variable > size ArrayList of inputs. When testing a simplest use case of one input ID, > I get an syntax error at end of input. However, if I copy the generated SQL > and run it in pgAdmin3, it is able to execute successfully. Below is the > error with loglevel2 enabled. Any advice would be much appreciated. What is your Java code?
Below you can find the method which is called.
Thank you,
-Hans
public boolean queryChromatSeqsByAmplicon(ArrayList<String> ids) throws SQLException {
Connection dbConnection = null;
ResultSet resultSet = null;
StringBuilder sb = new StringBuilder();
String selectTableSQL =
"SELECT seq_chromats.species_code_4, "
+"seq_chromats.fasta_header_line, "
+"seq_chromats.fasta_sequence, "
+"seq_chromats.flag_two_reads_exist, "
+"seq_amplicons.amplicon_name, "
+"seq_primers.primer_name "
+"FROM seq_amplicons "
+"INNER JOIN seq_primers ON seq_primers.amplicon_name = seq_amplicons.amplicon_name "
+"INNER JOIN seq_chromats ON seq_chromats.primer_id = seq_primers.primer_id "
+"WHERE seq_amplicons.amplicon_name IN (";
sb.append(selectTableSQL);
for(String ampliconId : ids) {
sb.append("('" + ampliconId + "'),");
}
sb.deleteCharAt(sb.length()-1); //fixes trailing comma
sb.append(")");
System.out.println(sb.toString());
try {
dbConnection = getDBConnection();
Statement stmt = dbConnection.createStatement();
resultSet = stmt.executeQuery(selectTableSQL);
crs = new CachedRowSetImpl();
crs.populate(resultSet);
dbConnection.close();
return true;
} catch (SQLException se) {
System.out.println(se.getMessage());
return false;
} catch (Exception e) {
return false;
}
}
On Thu, May 17, 2012 at 10:25 PM, Maciek Sakrejda <m.sakrejda@gmail.com> wrote:
On Thu, May 17, 2012 at 2:49 PM, Hans Vasquez-GrossWhat is your Java code?
<havasquezgross@ucdavis.edu> wrote:
> I am trying to build a query using StringBuilder based off a variable
> size ArrayList of inputs. When testing a simplest use case of one input ID,
> I get an syntax error at end of input. However, if I copy the generated SQL
> and run it in pgAdmin3, it is able to execute successfully. Below is the
> error with loglevel2 enabled. Any advice would be much appreciated.
----------
Hans Vasquez-Gross
iPlant Developer
Neale Lab - Robbins Hall 262
Department of Plant Science
University of California at Davis
Email: havasquezgross@ucdavis.edu
Phone: (530) 752-0609
Skype: hansvg.ucd
Skype: hansvg.ucd
Hans Vasquez-Gross wrote: > resultSet = stmt.executeQuery(selectTableSQL); Shouldn't that be?: resultSet = stmt.executeQuery(sb.toString()); -Kevin