Thread: 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
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 <rene@lab.applinet.nl>
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 <rene@lab.applinet.nl> > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
There are two related issues here. The first is that the database server does not yet support error codes. Therefore the JDBC driver also doesn't support them. This is on the todo list for the server but didn't get done in 7.2. You can still get the error message and look at it, but this isn't a good alternative to proper error code support. The second issue is that with postgres you may need to rethink your strategy here. Once postgres encounters an error it aborts the transaction. This means you generally can't trap an error and continue processing. When you trap an error you will need to rollback the current transaction, start a new transaction and then continue processing. thanks, --Barry User One wrote: > 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 majordomo@postgresql.org > >
Henry, Here is the format I use: given table foo with two columns (bar, baz) with bar the primary key: insert into foo (bar, baz) select ?,? where not exists (select 1 from foo where bar = ?) Then I test the number of rows inserted (if 0, then need to do an update as the row already exists). thanks, --Barry Henry D. wrote: > 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 <rene@lab.applinet.nl> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > >