RE: Problem using SQL where library contains a period

Specifying "SELECT * FROM \"TRK1.5B\".T2USERP" as the SQL string worked fine.
Thanks for your help.
Lee.

-----Original Message-----
From: Richard Dettinger [mailto:cujo@us.ibm.com]
Sent: Friday, January 12, 2001 3:32 PM
To: JAVA400-L@midrange.com
Subject: Re: Problem using SQL where library contains a period

You need to put quotes around the library name.  It works (at least
basically) just like iSQL.

iSQL Create example:
     create collection TRK1.5B
     Numeric constant .5B not valid.
     create collection "TRK1.5B"
     Collection "TRK1.5B" created.

Here is what you query line should be:

            ResultSet rs = s.executeQuery("SELECT * FROM
\"TRK1.5B\".T2USERP");

With the quotes around the name to signify that this is the collection, you
program will work (does for me anyway).

Good question though.

Regards,

Richard D. Dettinger
AS/400 Java Data Access Team

"TRUE! nervous, very, very dreadfully nervous I had been and am; but why
WILL you say that I am mad?
The disease had sharpened my senses, not destroyed, not dulled them. "

- Edgar Allan Poe
"The Tell-Tale Heart"

Lee Bircham <lee.bircham@socl.com>@midrange.com on 01/12/2001 06:26:57 AM

Please respond to JAVA400-L@midrange.com

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

To:   "'JAVA400-L@midrange.com'" <JAVA400-L@midrange.com>
cc:
Subject:  Problem using SQL where library contains a period

Hi,

I have a problem when using JDBC and SQL to access my AS400 database. The
problem happens when there is a period in the library name. Eg Library
TRK1.5B

An extract of my java code is as follows:
// Register Native JDBC Driver and connect to local DB.
Class.forName("com.ibm.db2.jdbc.app.DB2Driver");
Connection c = DriverManager.getConnection("jdbc:db2:*local");
// Run an SQL query on the table.
Statement s = c.createStatement();
ResultSet rs = s.executeQuery("SELECT * FROM TRK1.5B.T2USERP ORDER BY
SRUSER");

When this is run I get the following error message:
java.sql.SQLException: Numeric constant .5B not valid.?

This is because there is a period in the library name and SQL doesn't like
TRK1.5B.T2USERP
If I change the library name to another that has no period eg TRKTEST the
java program works fine.
Any ideas apart from changing the library name on getting round this?

Thanks,
Lee.

This E-Mail and any attachments maybe confidential and be the subject of
legal professional privilege.
Any disclosure , use, storage or copying of this E-Mail without the consent
of the sender is strictly prohibited.
Please notify the sender immediately if you are not the intended recipient
and then delete the E-Mail from
your inbox and do not disclose the contents to another person ,use, copy or
store the information in any medium.

+---
| 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 E-Mail and any attachments maybe confidential and be the subject of legal professional privilege.
Any disclosure , use, storage or copying of this E-Mail without the consent of the sender is strictly prohibited.
Please notify the sender immediately if you are not the intended recipient and then delete the E-Mail from
your inbox and do not disclose the contents to another person ,use, copy or store the information in any medium.