[JDBC] JDBC SQLCodes or Error Numbers - Or how to handle errors
I'm new to postgresql and it's JDBC driver, although an old hand a relation databases. I'm trying to rely on past practices in developing some code using Java and postgresql. I'm trying to do a INSERT into a table with the full knowledge that I might be trying to insert a duplicate row (which won't be allowed by the Primary Key). Since 80% of the inserts will succeed (because I don't expect many duplicates) I didn't want to waste programming time or runtime by doing a select to first see if the data existed. I'm used to just doing the insert and catching the SQLCODE -803 on DB2 to know that the row I'm trying to insert already exists. Is there an easy way to do this using Postgresql and JDBC? Postgresql version is 7.1.3 JDBC driver is the one that I compiled from the 7.1.3 installation I'm using the Java SDK 1.3.1_01 -- So is my only hope trying to match the error text and determine what when wrong? Thanks, Henry ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [JDBC] DatabaseMetaData.getTables()
On Sat, 27 Oct 2001 19:25:49 -0500, you wrote: There seems to be a problem with DatabaseMetaData.getTables() [...] It throws a NullPointerException: This may have been fixed by now: http://fts.postgresql.org/db/mw/msg.html?mid=1021572 Regards, René Pijlman [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [JDBC] JDBC SQLCodes or Error Numbers - Or how to handle errors
On Sun, 28 Oct 2001 00:33:07 -0700, you wrote: I'm used to just doing the insert and catching the SQLCODE -803 on DB2 to know that the row I'm trying to insert already exists. Is there an easy way to do this using Postgresql and JDBC? I'm afraid not. SQLException.getErrorCode() always returns 0. The PostgreSQL backend currently doesn't provide error codes. This issue is on the TODO list (Admin: 'Allow elog() to return error codes'). Once it is fixed in the backend and the FE/BE protocol, it can be fixed in the JDBC driver as well. http://lab.applinet.nl/postgresql-jdbc/#SQLException Regards, René Pijlman [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [JDBC] DatabaseMetaData.getTables()
I forgot to mention I'm using the current CVS. On Sun, Oct 28, 2001 at 10:07:06AM +0100, Rene Pijlman wrote: On Sat, 27 Oct 2001 19:25:49 -0500, you wrote: There seems to be a problem with DatabaseMetaData.getTables() [...] It throws a NullPointerException: This may have been fixed by now: http://fts.postgresql.org/db/mw/msg.html?mid=1021572 Regards, René Pijlman [EMAIL PROTECTED] -- Jason Davies [EMAIL PROTECTED] PGP signature
Re: [JDBC] JDBC SQLCodes or Error Numbers - Or how to handle errors
Is there a better way to test for duplicate rows than by first do a select to see if the key already exists? Henry D. Rene Pijlman wrote: On Sun, 28 Oct 2001 00:33:07 -0700, you wrote: I'm used to just doing the insert and catching the SQLCODE -803 on DB2 to know that the row I'm trying to insert already exists. Is there an easy way to do this using Postgresql and JDBC? I'm afraid not. SQLException.getErrorCode() always returns 0. The PostgreSQL backend currently doesn't provide error codes. This issue is on the TODO list (Admin: 'Allow elog() to return error codes'). Once it is fixed in the backend and the FE/BE protocol, it can be fixed in the JDBC driver as well. http://lab.applinet.nl/postgresql-jdbc/#SQLException Regards, René Pijlman [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [JDBC] DatabaseMetaData.getTables()
It appears the getBytes was previously being used to return a byte array of any arbitrary column. Fixes for blobs seem to have broken this. The question is as Jason pointed out which do we fix. It doesn't seem unreasonable to be able to return a byte array for any arbitray column. On the other hand is this the intended use? Dave -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Jason Davies Sent: October 27, 2001 8:26 PM To: [EMAIL PROTECTED] Subject: [JDBC] DatabaseMetaData.getTables() Hi, There seems to be a problem with DatabaseMetaData.getTables() when I do the following: ResultSet R=conn.getMetaData().getTables(null, null, %, null); It throws a NullPointerException: java.lang.NullPointerException at org.postgresql.jdbc2.DatabaseMetaData.getTables(DatabaseMetaData.java:17 32) at Test.main(Test.java:66) Looking at the source, ResultSet.getBytes() is called and it returns null, causing this exception to be thrown. However I can use ResultSet.getString() without a problem. I'm using 7.1.3 at the moment. Does ResultSet.getBytes() need to be fixed or should getTables() be modified? I'd be grateful for any insights. Or you can just tell me to use the latest cvs version of PostgreSQL :) What is the consensus on supporting older versions, will you phase out old code when 7.2 comes out? -- Jason Davies [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [JDBC] DatabaseMetaData.getTables()
On Sun, Oct 28, 2001 at 07:14:42AM -0500, Dave Cramer wrote: It appears the getBytes was previously being used to return a byte array of any arbitrary column. Fixes for blobs seem to have broken this. The question is as Jason pointed out which do we fix. It doesn't seem unreasonable to be able to return a byte array for any arbitray column. On the other hand is this the intended use? This is what the documentation says: public byte[] getBytes(int columnIndex) throws SQLException Retrieves the value of the designated column in the current row of this ResultSet object as a byte array in the Java programming language. The bytes represent the raw values returned by the driver. It seems to imply that it _should_ return a byte array for any arbitary column. But as usual, it's up to us to decide. I think it's reasonable, since we are working with byte arrays in the code anyway. -- Jason Davies [EMAIL PROTECTED] PGP signature
Re: [JDBC] DatabaseMetaData.getTables()
Well I think we can restore the orignal functionality of getBytes so that it returns a byte array for other objects As long as we preserve the functionality for bytea types, and LargeObjects Dave -Original Message- From: Jason Davies [mailto:[EMAIL PROTECTED]] Sent: October 28, 2001 8:34 AM To: Dave Cramer Cc: [EMAIL PROTECTED] Subject: Re: [JDBC] DatabaseMetaData.getTables() On Sun, Oct 28, 2001 at 07:14:42AM -0500, Dave Cramer wrote: It appears the getBytes was previously being used to return a byte array of any arbitrary column. Fixes for blobs seem to have broken this. The question is as Jason pointed out which do we fix. It doesn't seem unreasonable to be able to return a byte array for any arbitray column. On the other hand is this the intended use? This is what the documentation says: public byte[] getBytes(int columnIndex) throws SQLException Retrieves the value of the designated column in the current row of this ResultSet object as a byte array in the Java programming language. The bytes represent the raw values returned by the driver. It seems to imply that it _should_ return a byte array for any arbitary column. But as usual, it's up to us to decide. I think it's reasonable, since we are working with byte arrays in the code anyway. -- Jason Davies [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster