Re: Case Sensitivity

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