Re: [XMLPipeDB-developer] PreparedStatement query parameters

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

I was thinking switch in this way:

*

for* ( SpeciesProfile selected : selectedSpeciesProfiles ) {

switch (numOfSpecies)

case 1 :        ps.setString(1, Integer.*toString*(selected.getTaxon()));

 ps.setString(2, systemTable.getKey());

break;

case 2 :        ps.setString(1, Integer.*toString*(selected.getTaxon()));

 ps.setString(2, Integer.*toString*(selected.getTaxon()));

          ps.setString(3, systemTable.getKey());

          break;

case 3 :

case 4 :

case 5:

result = ps.executeQuery();

 I hadn't implemented it yet so never found out if it would work or not.

----------------------------------------------------------------------------------------------------------------------------------

In thinking about how to do this programatically I started getting hung up
on how to do it for multiple lines.

I understand how to do it for a submit now which needs multiple arguments
(and not a string like I tried previously)... but its still only creating
the arguments for a single line.

Essentially I want to trigger based off selectedSpeciesProfiles.getSize() to
provide the number of ps.setString(?,
Integer.toString()(selected.getTaxon())) lines that are needed. Then cap it
off with ps.setString(?, systemTable.getkey() )

Maybe I'm overthinking it by trying to create multiple lines simultaneously
instead of one by one....

Going to sleep on this since its midnight and pick it up in the morning.

Thanks as always!

Richard

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

>  Hi Rich,
>
> Actually, I don't see how switch would help at all.  It's...hm, I really
> don't know how much would be right to say pedagogically.  I daresay you
> might be overthinking this, or else misunderstanding some part of this whole
> process.
>
> You have a collection of species profiles.  You have to form a query that
> is based on that collection.  Really, there is no additional information
> (nor programming construct) to include.
>
> The ability to convert data structures into appropriate, corresponding
> queries is a key element of database programming, I'd say, so I think I
> prefer to let you mull over this a bit more. :)
>
>
> John David N. Dionisio, PhD
> Associate Professor, Computer Science
> Loyola Marymount University
>
>
> On Aug 24, 2011, at 8:26 PM, Richard Brous <rbr...@gm...> wrote:
>
>   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
>>
>
>
> ------------------------------------------------------------------------------
> 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
>
>
>
> ------------------------------------------------------------------------------
> 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
>
>