Re: [XMLPipeDB-developer] PreparedStatement query parameters

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

Understood, we are on the same page.

I have been contemplating how to do this (knowing that there will be a limit
to the number of compared species) between using switch statement or using a
more programmatic way.

I'm leaning towards the switch at the moment because I know I can get it
done that way although certainly not as elegant.

Richard

On Wed, Aug 24, 2011 at 7:44 PM, John David N. Dionisio <do...@lm...>wrote:

> 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>
>
>
>
> ------------------------------------------------------------------------------
> 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
>