Re: Problems with CLOB in postgresql

[Vladimir Lahoda <vl...@gmail.com>]

Hello Rainer,

thanks for your instant advice. It does the trick, indeed! 
I feel a bit ashamed that I did not realize myself that the driver can be subclassed…

Thanks,
Vladimir

On 11. 2. 2012, at 23:31, Rainer Döbele wrote:

> Hello Vladimir,
> 
> thanks for letting us know.
> 
> Indeed the TEXT data type for DDL generation got lost on the way, when we removed redundant code.
> This will be easy to fix and I will do that in the next few days.
> 
> For you other problem, I suggest the following:
> Please create your own driver class that extends DBDatabaseDriverPostgreSQL and pass an instance to this class to the open method of your DBDatabase object.
> Now override the method getResultValue(ResultSet rset, int columnIndex, DataType dataType) 
> and provide you own logic for obtaining the clob value e.g. like this:
> 
>    if (dataType == DataType.CLOB)
>    {
>        return rset.getString(columnIndex);
>     }
>    else return super.getResultValue(...)
> 
> If I understood you correctly this should do the trick.
> If you confirm this, then we will modify the Postgre driver code accordingly.
> 
> Looking forward to hearing from you
> 
> Regards
> Rainer
> 
> 
>> from: Vladimir Lahoda [mailto:vlahoda@gmail.com]
>> to: empire-db-dev@incubator.apache.org
>> re: Problems with CLOB in postgresql
>> 
>> Hi,
>> 
>> I've switched my empire-db (2.2.0) project from Oracle to Postgresql
>> (version 8.4) and encountered two problems with CLOB columns.
>> 
>> First, trying to read the CLOB value from the result set using
>> DBReader.getValue() fails with the following error:
>> 
>> org.postgresql.util.PSQLException: Bad value for type long : some
>> string value
>> 	at
>> org.postgresql.jdbc2.AbstractJdbc2ResultSet.toLong(AbstractJdbc2ResultS
>> et.java:2796)
>> 	at
>> org.postgresql.jdbc2.AbstractJdbc2ResultSet.getLong(AbstractJdbc2Result
>> Set.java:2019)
>> 	at
>> org.postgresql.jdbc4.Jdbc4ResultSet.getClob(Jdbc4ResultSet.java:43)
>> 	at
>> org.apache.empire.db.DBDatabaseDriver.getResultValue(DBDatabaseDriver.j
>> ava:490)
>> 	at org.apache.empire.db.DBReader.getValue(DBReader.java:374)
>> 
>> The problem is that DbReader eventually calls JDBC method getClob()
>> which IMHO is not implemented correctly in the postgresql driver. It is
>> recommended to use getString() method on the CLOBs in Postgresql
>> instead. But calling DBReader.getString() does not help either, because
>> it ends up calling getValue().toString(), with the same result as
>> above. The same situation holds for BLOBs as well. Any hints or
>> workarounds, please?
>> 
>> 
>> The second problem is with the DDL generator, which defines the CLOB
>> column type as CLOB, but this is unsupported by Postgresql and should
>> be TEXT instead. Some googling suggests that this was already fixed for
>> previous versions of empire-db (at least in 2.0.7), but probably the
>> fix got lost in refactorings for version 2.2.0.
>> 
>> No other complaints so far ;-) Thanks a lot for this great project and
>> congratulations for promotion to TLP!
>> 
>> 
>> Regards,
>> 
>> Vladimir

re: Problems with CLOB in postgresql

[Rainer Döbele <do...@esteam.de>]

Hi Vladimir,

no need to feel ashamed. Its natural that you don't know the code as well as I do ;-)
We tried to design Empire-db in a way that provides many interception points - primarily by subclassing.

But we need to get this change in the trunk.
I have just created a JIRA issue for this.
Please give us some time for the changes as currently we're all very busy.

Regards
Rainer



> from: Vladimir Lahoda [mailto:vlahoda@gmail.com]
> to: dev@empire-db.apache.org
> re: Re: Problems with CLOB in postgresql
> 
> Hello Rainer,
> 
> thanks for your instant advice. It does the trick, indeed!
> I feel a bit ashamed that I did not realize myself that the driver can
> be subclassed...
> 
> Thanks,
> Vladimir
> 
> On 11. 2. 2012, at 23:31, Rainer Döbele wrote:
> 
> > Hello Vladimir,
> >
> > thanks for letting us know.
> >
> > Indeed the TEXT data type for DDL generation got lost on the way,
> when we removed redundant code.
> > This will be easy to fix and I will do that in the next few days.
> >
> > For you other problem, I suggest the following:
> > Please create your own driver class that extends
> DBDatabaseDriverPostgreSQL and pass an instance to this class to the
> open method of your DBDatabase object.
> > Now override the method getResultValue(ResultSet rset, int
> columnIndex, DataType dataType)
> > and provide you own logic for obtaining the clob value e.g. like
> this:
> >
> >    if (dataType == DataType.CLOB)
> >    {
> >        return rset.getString(columnIndex);
> >     }
> >    else return super.getResultValue(...)
> >
> > If I understood you correctly this should do the trick.
> > If you confirm this, then we will modify the Postgre driver code
> accordingly.
> >
> > Looking forward to hearing from you
> >
> > Regards
> > Rainer
> >
> >
> >> from: Vladimir Lahoda [mailto:vlahoda@gmail.com]
> >> to: empire-db-dev@incubator.apache.org
> >> re: Problems with CLOB in postgresql
> >>
> >> Hi,
> >>
> >> I've switched my empire-db (2.2.0) project from Oracle to Postgresql
> >> (version 8.4) and encountered two problems with CLOB columns.
> >>
> >> First, trying to read the CLOB value from the result set using
> >> DBReader.getValue() fails with the following error:
> >>
> >> org.postgresql.util.PSQLException: Bad value for type long : some
> >> string value
> >> 	at
> >>
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.toLong(AbstractJdbc2ResultS
> >> et.java:2796)
> >> 	at
> >>
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.getLong(AbstractJdbc2Result
> >> Set.java:2019)
> >> 	at
> >> org.postgresql.jdbc4.Jdbc4ResultSet.getClob(Jdbc4ResultSet.java:43)
> >> 	at
> >>
> org.apache.empire.db.DBDatabaseDriver.getResultValue(DBDatabaseDriver.j
> >> ava:490)
> >> 	at org.apache.empire.db.DBReader.getValue(DBReader.java:374)
> >>
> >> The problem is that DbReader eventually calls JDBC method getClob()
> >> which IMHO is not implemented correctly in the postgresql driver. It
> is
> >> recommended to use getString() method on the CLOBs in Postgresql
> >> instead. But calling DBReader.getString() does not help either,
> because
> >> it ends up calling getValue().toString(), with the same result as
> >> above. The same situation holds for BLOBs as well. Any hints or
> >> workarounds, please?
> >>
> >>
> >> The second problem is with the DDL generator, which defines the CLOB
> >> column type as CLOB, but this is unsupported by Postgresql and
> should
> >> be TEXT instead. Some googling suggests that this was already fixed
> for
> >> previous versions of empire-db (at least in 2.0.7), but probably the
> >> fix got lost in refactorings for version 2.2.0.
> >>
> >> No other complaints so far ;-) Thanks a lot for this great project
> and
> >> congratulations for promotion to TLP!
> >>
> >>
> >> Regards,
> >>
> >> Vladimir