Skip site navigation (1) Skip section navigation (2)

Re: DatabaseMetaData and Transactions

From: Carl Olivier <carl(at)zero-one(dot)co(dot)za>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: DatabaseMetaData and Transactions
Date: 2005-06-07 10:54:27
Message-ID: 473185057AB4D7118B6C00508B624DDA1C96BB@ALPHENPDC (view raw or flat)
Thread:
Lists: pgsql-jdbc
 Hi Dave.

Thanks - should have thought of looking in those logs!  So much for our
exception handling/reporting at that point.

Anyway - now resolved, with my thanks.

I do however have an additional query!

In 7.3 (using the 7.3 JDBC driver) we were able to do the following:

=== SNIP ===

if (value == null)
{
	stmt.setString( index, null );
}

=== END ===

Some context here.  We have a method that constructs a PreparedStatement
taking the sql (eg):

UPDATE table SET cola = ?, colb = ?, colc = ?

Or

INSERT INTO table (cola, colb, colc) VALUES (?, ?, ?)

And an Object[] of values (in the correct order) for the sql being used in
the PreparedStatement.

Now - the reason we do a setString(index, null) and not a setNull(index,
Types.SOME_TYPE) is because this method is generic and does not always KNOW
the datatypes for the columns.

Now, using PostgreSQL 8 (and the new JDBC driver for 8) we ARE still able to
do that when using a 7..3 db server, but get the following error when using
a version 8 PGSQL server:

ERROR: column "created_by" is of type integer but expression is of type
character varying

Where created_by is being passed a NULL value (using the setString(index,
null) method).

Does anyone have any comments/suggestions/etc?  Is there any way to get
around this?  Or will we need to update our side to always pass in the
column meta data etc for use?

Thanks in advance,

Carl



-----Original Message-----
From: Dave Cramer [mailto:pg(at)fastcrypt(dot)com] 
Sent: Monday, June 06, 2005 4:23 PM
To: Carl Olivier
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] DatabaseMetaData and Transactions

Carl,

One of the queries is failing, have a look at the logs in postgres and
report back to us what is failing.

Dave
On 6-Jun-05, at 9:38 AM, Carl Olivier wrote:

> Greetings.
>
> Having a problem with regards DatabaseMetaData retrievals within a 
> Transaction.  I have a large transaction running - within which 
> database alterations need to occur.  However, during this process I 
> need to retrieve
> Table metadata (via the DatabaseMetaData.getTables() method).   
> However, this
> does not seem possible in POSTGRES?  I receive the following error:
>
> ERROR: current transaction is aborted, queries ignored until end of 
> transaction block.
>
> I trace that directly to a call to the getTables method of the 
> DatabaseMetaData implementation.
>
> Can anyone provide me with any advice here?  Is this correct 
> behaviour, and is there any way (other than caching table metadata 
> prior to, and maintaining said cache during the transaction) to allow 
> for meta data retrieval during a transaction?
>
> Thanks in advance!
>
> Regards,
>
> Carl
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>
>

Responses

pgsql-jdbc by date

Next:From: Gilles DubochetDate: 2005-06-07 16:11:16
Subject: Binary precision of a number type
Previous:From: Sylvain BoilyDate: 2005-06-07 09:34:27
Subject: Probleme with jdbc driver an openxhange.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group