Re: JDBC syntax error at end of input

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(dot)sakrejda(at)gmail(dot)com>wrote:

> On Thu, May 17, 2012 at 2:49 PM, Hans Vasquez-Gross
> <havasquezgross(at)ucdavis(dot)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?
>

--
----------
Hans Vasquez-Gross
iPlant Developer
Neale Lab - Robbins Hall 262
Department of Plant Science
University of California at Davis
Email: havasquezgross(at)ucdavis(dot)edu
Phone: (530) 752-0609
Skype: hansvg.ucd