Re: JDBC and columns with quote characters in their names

["Richard Dettinger" <cujo@xxxxxxxxxx>]

Make my life easy by e-mailing me a recreation program and I will ensure
that it works for the Native JDBC driver and get it fixed in the Toolbox as
well.

Please make the recreation program create a table so that its pretty much
stand-alone.

Yeah, I know its not hard to do, but if you already have something, then I
don't have to recreate it - and it buys me an hour or two to finish what I
am working on before I look at it. 8-)

Thanks.

Regards,

Richard D. Dettinger
AS/400 Java Data Access Team

"Biologists have a special word for stability -- dead"

                Larry Wall
                Open Source Developers Journal
                Issue 1, Jan  2000


"McCallion, Martin" <MccalliM@Midas-Kapiti.com>@midrange.com on 04/03/2001
11:31:17 AM

Please respond to JAVA400-L@midrange.com

Sent by:  owner-java400-l@midrange.com


To:   JAVA400-L@midrange.com
cc:
Subject:  JDBC and columns with quote characters in their names



I'm using JDBC to work with a DB2/400 database.  However, it so happens
that the database's column names have double quotes in them, so they
appear like "OrderID".  Note that the double-quote characters are
actually part of the name.  This happens when you create the database by
exporting from another system (in this case MS Access), but you can put
similar values into a CREATE TABLE statement in interactive SQL on the
400.

The trouble is that SQL is meant to be case insensitive.  If, in
interactive SQL, you create a table with a column name of "ORDERID", it
will be created as ORDERID.  But if you create it as "OrderID", it will
be created as "OrderID".  Again, I stress that the quote characters are
_part of the name_.

Now, this causes a problem when connecting to the the database using
JDBC: the driver strips off the quotes and passes the naked string to
the database, which converts it to upper case and says "Column ORDERID
not found".

I can get round this in some statements by using the database's quote
character, like this:

     dbMetaQuoteStr = conn.getMetaData().getIdentifierQuoteString();
     sqlString.append(dbMetaQuoteStr + keyFields[i] +
dbMetaQuoteStr);

This works, for example, in a SELECT statement.  However, the problem I
am having is when I come to write a record to the database.  The code:

     rs.insertRow();

(where rs is a ResultSet) throws an SQLException: [SQL0206] Column
ORDERID not in specified tables.  Presumably the same thing will happen
with updateRow(), too.

It seems to me that I somehow have to get the database's quote character
into the column names that are held by the ResultSet object, but I don't
see how that is possible.

It's clearly a bug if you can create a legal column name one way, but
can't use it another way; and since I'm using the JTOpen version of the
Toolbox, I can look at the source and see where this happens, so I
suppose I _could_ try to fix it and offer my fix to the community; but
I'm not sure I want to open that can of worms right now.  Plus, when our
product goes out to clients, there's no guarantee that they would have
the latest version of the toolbox, or indeed, be using the toolbox at
all.

Has anyone come across this?  Is there a workaround?

TIA,

Martin.

--
Martin McCallion
Midas-Kapiti International
Work:  mccallim@midas-kapiti.com
Home: martin.mccallion@ukonline.co.uk

Apologies for the length of this sig, but company policy says:
This email message is intended for the named recipient only.  It may be
privileged and/or confidential.  If you are not the intended named
recipient of this email then you should not copy it or use it for any
purpose, nor disclose its contents to any other person.  You should
contact Midas-Kapiti International as shown below so that we can take
appropriate action at no cost to yourself.

Midas-Kapiti International Ltd, 1 St George's Road, Wimbledon, London,
SW19 4DR, UK
Email: Postmaster@midas-kapiti.com Tel: +44 (0)208 879 1188 Fax: +44
(0)208 947 3373
Midas-Kapiti International Ltd is registered in England and Wales under
company no. 971479
+---
| This is the JAVA/400 Mailing List!
| To submit a new message, send your mail to JAVA400-L@midrange.com.
| To subscribe to this list send email to JAVA400-L-SUB@midrange.com.
| To unsubscribe from this list send email to JAVA400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: joe@zappie.net
+---



+---
| This is the JAVA/400 Mailing List!
| To submit a new message, send your mail to JAVA400-L@midrange.com.
| To subscribe to this list send email to JAVA400-L-SUB@midrange.com.
| To unsubscribe from this list send email to JAVA400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: joe@zappie.net
+---