Re: [XMLPipeDB-developer] PreparedStatement query parameters
Brought to you by:
kdahlquist,
zugzugglug
From: John D. N. D. <do...@lm...> - 2011-08-25 02:44:22
|
Hi Rich, Your second sentence/paragraph is right --- for every selected species, there must be: (a) a corresponding "id = ?" clause, and (b) a corresponding setString call, setting that parameter to the taxon ID. Implied, but not explicitly stated, is that *after* all of these clauses, you have one more clause, for "type = ?". The parameter to set for that is systemTable.getKey(). And, yes, the code for this must be based solely on the current content of the selectedSpeciesProfiles collection. So, given what you are setting out to do (one clause per selected species profile, with corresponding parameter, followed by specification of the system type), here is what you have: - You have the collection of selected species profiles. ***For each element in this collection, you must have an "id = ?" clause, and you must set the parameter of that clause with the taxon ID of that element.*** - You have the systemTable object from which you will grab the parameter for "type = ?". You have the (a) part down (that is, the concatenation of the correct number of "id = ?" clauses to the query). It looks like this: for (int i = 0; i < selectedSpeciesProfiles.size(); i++) { basePrepareStatement .append((i == 0) ? " and (" : " or ") .append("id = ?"); } Now, when the PreparedStatement has been created, ***you need to call setString once for each "id = ?" clause that you appended in the code block above.*** Finally, for the *last* parameter in the query (self-test question: what is the index of that last parameter?), you must set that to systemTable.getKey(). The translation of the above paragraph into Java is essentially what you are setting out to do. John David N. Dionisio, PhD Associate Professor, Computer Science Loyola Marymount University On Aug 24, 2011, at 7:26 PM, Richard Brous wrote: > OK, spent some time digging through it with an eye to that 3rd '?' > > I believe I simply need to add as many ps.setString(?, Integer.toString(selected.getTaxon())); as there are species selected. > > Exports for single species work fine but if I want to export 2 species I need the following (example is hardcoded): > > > ps = ConnectionManager.getRelationalDBConnection().prepareStatement( basePrepareStatement.toString() ); > > for > > ( SpeciesProfile selected : selectedSpeciesProfiles ) { > > ps.setString(1, Integer.toString(selected.getTaxon())); > > ps.setString(2, Integer.toString(selected.getTaxon())); > > ps.setString(3, systemTable.getKey()); > > result = ps.executeQuery(); > > etc... > > > The solve is to programmatically build the ps.set... going forward. > > Richard > > > > On Tue, Aug 23, 2011 at 8:53 PM, John David N. Dionisio <do...@lm...> wrote: > > Hi Rich, > > Sure, I don't think it will do any harm to give the method another once-over. What you have to see solidly are: > > - What information does this method extract from the relational database? > - What information does this method send to the Gene Database? > > As to the code --- no, I did not change anything. I only added comments. Not sure about the successful export then. What I thought was that you did execute the changes I described, and that is how you successfully exported the .gdb. > > John David N. Dionisio, PhD > Associate Professor, Computer Science > Loyola Marymount University > > > > > On Aug 23, 2011, at 8:41 PM, Richard Brous wrote: > > > Yes your comments make sense but I feel as though my understanding of this method is the weak link... I'll work through it again tomorrow. > > > > Also, in thinking back over our discussions regarding getSystemTableManager(), I read through the code again and saw your comments. > > > > Then I realized I may have assumed you corrected the method when you may only in fact had commented... Was this the case? > > > > But then how did I get a successful two species export yesterday? > > > > OK, my brain is starting to hurt... > > > > I'm going to head to bed and start fresh tomorrow. > > > > Richard > > > > On Tue, Aug 23, 2011 at 8:18 PM, John David N. Dionisio <do...@lm...> wrote: > > Hi Rich, > > > > The key is this message: > > > > org.postgresql.util.PSQLException: No value specified for parameter 3. > > > > That means the third question mark in the query (which turns out to be type = ?) was never given a value. Check that method and make sure that the code invokes as many set's as there are question marks in the query. And for this particular query, the last set is for the gene database type. > > > > John David N. Dionisio, PhD > > Associate Professor, Computer Science > > Loyola Marymount University > > > > > > On Aug 23, 2011, at 7:59 PM, Richard Brous <rbr...@gm...> wrote: > > > >> org.postgresql.util.PSQLException: No value specified for parameter 3. > > > > ------------------------------------------------------------------------------ > > EMC VNX: the world's simplest storage, starting under $10K > > The only unified storage solution that offers unified management > > Up to 160% more powerful than alternatives and 25% more efficient. > > Guaranteed. http://p.sf.net/sfu/emc-vnx-dev2dev > > _______________________________________________ > > xmlpipedb-developer mailing list > > xml...@li... > > https://lists.sourceforge.net/lists/listinfo/xmlpipedb-developer > > > > > > > <ATT00001..txt><ATT00002..txt> > > > ------------------------------------------------------------------------------ > EMC VNX: the world's simplest storage, starting under $10K > The only unified storage solution that offers unified management > Up to 160% more powerful than alternatives and 25% more efficient. > Guaranteed. http://p.sf.net/sfu/emc-vnx-dev2dev > _______________________________________________ > xmlpipedb-developer mailing list > xml...@li... > https://lists.sourceforge.net/lists/listinfo/xmlpipedb-developer > > > > <ATT00001..txt><ATT00002..txt> |