On Fri, Oct 25, 2002 at 11:22:09AM -0400, Hale Pringle wrote:
> Since this came up, I thought I'd stick in another case sensitivity issue I
> found "interesting" recently. I was dealing with the issue for inserting a
> row into a table that has a serial ID number and having a gelid() method
> that returns the ID that was just generated. You have been discussing this
> relative to the JDBC3 spec, but, as you guys know, it can be done now with a
> little extra coding.
> I ended up with a sequence "Orders_order_id_seq" for a table orders. (Note
> the capital "O" in the sequence and the lower case in the table name. I'm
> not sure how this happened and it doesn't really matter. I dropped/created
> the table several times during development. Anyway the interesting thing
> was that when I issued the commands:
> orderId = 0;
> rsOrder = statementOrder.executeQuery("Select
> nextval('Orders_order_id_seq')");
> while ( rsOrder.next() ) {
> orderId = rsOrder.getInt( "nextval" );
> }
> query = "Insert into orders ("+fields+ ",order_id) values
> ("+values+","+orderId+")";
> statementOrder.executeUpdate( query );
>
> I would get "Sequence "orders_order_id_seq" does not exist." Yet when I did
> a table update that relied on order_id being set to its default value (which
> called a nextval("'Orders_order_id_seq'")::text) it would work. (no -
Hmm, I bet it's actually nextval('"Orders_order_id_seq"')::text)
Right? Note that nextval() takes a string as it's first argument: that string
happens to represent the name of a sequence object. In this case, the string
is doublequoted, so it goes through the parser without being lower()ed.
If you can convince Java to put a doublequote in your rsOrder string, I bet
you'd see the same results.
Ross (catching up on the lists)
--
Ross Reedstrom, Ph.D. reedstrm@rice.edu
Executive Director phone: 713-348-6166
Gulf Coast Consortium for Bioinformatics fax: 713-348-6182
Rice University MS-39
Houston, TX 77005