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-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.
--
----------
Hans Vasquez-Gross
iPlant Developer
Neale Lab - Robbins Hall 262
Department of Plant Science
University of California at Davis
Phone: (530) 752-0609
Skype: hansvg.ucd