Thread: UPDATE_RULE

I have seen some talk on some older threads about some patches for the
getImported getExported Keys functions in the DatabaseMetaData class
that fix:

1) Case sensitivity in the table name (right now I have to cast down to
lower case to get results)
2) Return  the proper values for the UPDATE_RULE, and DELETE_RULE  columns

I am currently using the latest developer 7.3 jdbc driver with java 1.4.
Is there someplace I can get a fix to this?

Eric

Eric,

The whole case sensitivity thing is a problem. It is likely that the
tables are actually in lower case in the databaase, no? Postgres
actually is a case sensitive database so you have to ask for the
information in the correct case. There is one caveat: if you do create
table FOO ... it willl create a table foo. If you do create table
"FOO"... you will get a table FOO. We have some case inconsistencies in
the driver because we are waffling back and forth wrt supporting the
underlying ability to have case sensitive names or not.

As far as the update rule, and delete rule go, no there isn't patch for
it. What are you looking for exactly?

Dave
On Wed, 2002-10-23 at 15:59, Eric Hallander wrote:
> I have seen some talk on some older threads about some patches for the
> getImported getExported Keys functions in the DatabaseMetaData class
> that fix:
>
> 1) Case sensitivity in the table name (right now I have to cast down to
> lower case to get results)
> 2) Return  the proper values for the UPDATE_RULE, and DELETE_RULE  columns
>
> I am currently using the latest developer 7.3 jdbc driver with java 1.4.
> Is there someplace I can get a fix to this?
>
> Eric
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>

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

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