Re: Case Sensitivity

>Eric,

>The whole case sensitivity thing is a problem.
<snip>

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 -
adding the "::text" to my code didn't seem to change anything.)

In orther words the sequence was case sensitive for my code, but not for the
default value generating code.

The solution was to drop the sequence and create a new one with a lower case
"o", but it would be nice if nextval() worked the same way outside the
default value generator as it does inside.