OFBiz › OFBiz - Dev

RE: [OFBiz] Dev - Updated PostgreSQL Driver

‹ Previous Topic Next Topic ›
 
classic Classic list List threaded Threaded
♦
♦
 3 messages Options
Kasubaski, Matt
Reply | Threaded
Open this post in threaded view
♦
♦
|

RE: [OFBiz] Dev - Updated PostgreSQL Driver

Kasubaski, Matt
I think this may be caused by a mismatch between how the DB was built
and how OfBiz is configured.  The error looks like the database has the
wrong column type than what is expected.

Entitymodel_shipment.xml defines "labelimage" as:
<entity entity-name="ShipmentPackageRouteSeg" ...>
 <field name="labelImage" type="blob"></field>


Fieldtypepostgres.xml has "blob" defined as:
<field-type-def type="blob" sql-type="OID"
java-type="java.lang.Object"></field-type-def>

Fieldtypepostgres72.xml has "blob" defined as:
<field-type-def type="blob" sql-type="BYTEA"
java-type="java.lang.Object"></field-type-def>

It would appear that the database was built using the
fieldtypepostgres.xml, but is now using fieldtypepostgres72.xml.  The
older JDBC may not have made a distinction between the two types, but
the new one does.

My suggestion would be to change the column type to "BYTEA", or recreate
the database using the new driver.  (Use export/import XML if you need
to save data.)

Matt

-----Original Message-----
From: [hidden email] [mailto:[hidden email]]
On Behalf Of J. Eckard
Sent: Tuesday, July 12, 2005 5:12 PM
To: OFBiz Project Development Discussion
Subject: [OFBiz] Dev - Updated PostgreSQL Driver

After updating my local PostgreSQL jdbc driver to the latest version in

SVN, things seemed ok, but when I tried to quickship an order, I got  
the following error:

---- exception report  
----------------------------------------------------------
Exception: org.ofbiz.entity.GenericEntityException
Message: Exception while inserting the following entity:  
[GenericEntity:ShipmentPackageRouteSeg][createdTxStamp,2005-07-12  
17:56:
18.412(java.sql.Timestamp)][shipmentRouteSegmentId,00001(java.lang.Strin

g)][shipmentId,70026(java.lang.String)][lastUpdatedTxStamp,2005-07-12  
17:56:
18.412(java.sql.Timestamp)][shipmentPackageSeqId,00001(java.lang.String)

][createdStamp,2005-07-12  
17:56:18.412(java.sql.Timestamp)][lastUpdatedStamp,2005-07-12  
17:56:18.412(java.sql.Timestamp)] (while inserting:  
[GenericEntity:ShipmentPackageRouteSeg][createdTxStamp,2005-07-12  
17:56:
18.412(java.sql.Timestamp)][shipmentRouteSegmentId,00001(java.lang.Strin

g)][shipmentId,70026(java.lang.String)][lastUpdatedTxStamp,2005-07-12  
17:56:
18.412(java.sql.Timestamp)][shipmentPackageSeqId,00001(java.lang.String)

][createdStamp,2005-07-12  
17:56:18.412(java.sql.Timestamp)][lastUpdatedStamp,2005-07-12  
17:56:18.412(java.sql.Timestamp)] (SQL Exception while executing the  
following:INSERT INTO public.SHIPMENT_PACKAGE_ROUTE_SEG (SHIPMENT_ID,  
SHIPMENT_PACKAGE_SEQ_ID, SHIPMENT_ROUTE_SEGMENT_ID, TRACKING_CODE,  
BOX_NUMBER, LABEL_IMAGE, LABEL_INTL_SIGN_IMAGE, LABEL_HTML,  
PACKAGE_TRANSPORT_COST, PACKAGE_SERVICE_COST, PACKAGE_OTHER_COST,  
COD_AMOUNT, INSURED_AMOUNT, CURRENCY_UOM_ID, LAST_UPDATED_STAMP,  
LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP) VALUES (?, ?,  
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (ERROR: column  
"label_image" is of type bytea but expression is of type oid)))

This isn't an isolated error, it happens for any order. I have not  
updated the PostgreSQL server itself, it is still at 7.4.6.

Any suggestions for a fix would be greatly appreciated...

-Joe

 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev
 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev
J. Eckard
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: [OFBiz] Dev - Updated PostgreSQL Driver

J. Eckard
Finally found an answer on this one... when you attempt to store a null  
in a BYTEA column with a current postgresql JDBC driver, you'll get an  
exception. From the following message to the pgsql-jdbc list, it looks  
like we may need to update the fieldtype defs for postgres to use OID  
instead of BYTEA for blobs.

(from http://archives.postgresql.org/pgsql-jdbc/2005-08/msg00003.php )

On Mon, 1 Aug 2005, Andrus Adamchik wrote:

 > Just tested it with build 312 and the original problem went away. Now
 > I am seeing a new problem with null BLOBs only:
 >
 > java.sql.SQLException: ERROR: column "blob_col" is of type bytea but
 > expression is of type oid

bytea is not the correct type to use when storing Blobs.  You need to  
use
oid as the underlying type, or if you want to use bytea you need to use
setBytes or setNull(x, Types.BINARY).

Kris Jurka

On Jul 21, 2005, at 6:26 PM, Kasubaski, Matt wrote:

> I think this may be caused by a mismatch between how the DB was built
> and how OfBiz is configured.  The error looks like the database has the
> wrong column type than what is expected.
>
> Entitymodel_shipment.xml defines "labelimage" as:
> <entity entity-name="ShipmentPackageRouteSeg" ...>
>  <field name="labelImage" type="blob"></field>
>
>
> Fieldtypepostgres.xml has "blob" defined as:
> <field-type-def type="blob" sql-type="OID"
> java-type="java.lang.Object"></field-type-def>
>
> Fieldtypepostgres72.xml has "blob" defined as:
> <field-type-def type="blob" sql-type="BYTEA"
> java-type="java.lang.Object"></field-type-def>
>
> It would appear that the database was built using the
> fieldtypepostgres.xml, but is now using fieldtypepostgres72.xml.  The
> older JDBC may not have made a distinction between the two types, but
> the new one does.
>
> My suggestion would be to change the column type to "BYTEA", or  
> recreate
> the database using the new driver.  (Use export/import XML if you need
> to save data.)
>
> Matt
>
> -----Original Message-----
> From: [hidden email] [mailto:[hidden email]]
> On Behalf Of J. Eckard
> Sent: Tuesday, July 12, 2005 5:12 PM
> To: OFBiz Project Development Discussion
> Subject: [OFBiz] Dev - Updated PostgreSQL Driver
>
> After updating my local PostgreSQL jdbc driver to the latest version in
>
> SVN, things seemed ok, but when I tried to quickship an order, I got
> the following error:
>
> ---- exception report
> ----------------------------------------------------------
> Exception: org.ofbiz.entity.GenericEntityException
> Message: Exception while inserting the following entity:
> [GenericEntity:ShipmentPackageRouteSeg][createdTxStamp,2005-07-12
> 17:56:
> 18.412(java.sql.Timestamp)][shipmentRouteSegmentId,00001(java.lang.Stri
> n
>
> g)][shipmentId,70026(java.lang.String)][lastUpdatedTxStamp,2005-07-12
> 17:56:
> 18.412(java.sql.Timestamp)][shipmentPackageSeqId,00001(java.lang.String
> )
>
> ][createdStamp,2005-07-12
> 17:56:18.412(java.sql.Timestamp)][lastUpdatedStamp,2005-07-12
> 17:56:18.412(java.sql.Timestamp)] (while inserting:
> [GenericEntity:ShipmentPackageRouteSeg][createdTxStamp,2005-07-12
> 17:56:
> 18.412(java.sql.Timestamp)][shipmentRouteSegmentId,00001(java.lang.Stri
> n
>
> g)][shipmentId,70026(java.lang.String)][lastUpdatedTxStamp,2005-07-12
> 17:56:
> 18.412(java.sql.Timestamp)][shipmentPackageSeqId,00001(java.lang.String
> )
>
> ][createdStamp,2005-07-12
> 17:56:18.412(java.sql.Timestamp)][lastUpdatedStamp,2005-07-12
> 17:56:18.412(java.sql.Timestamp)] (SQL Exception while executing the
> following:INSERT INTO public.SHIPMENT_PACKAGE_ROUTE_SEG (SHIPMENT_ID,
> SHIPMENT_PACKAGE_SEQ_ID, SHIPMENT_ROUTE_SEGMENT_ID, TRACKING_CODE,
> BOX_NUMBER, LABEL_IMAGE, LABEL_INTL_SIGN_IMAGE, LABEL_HTML,
> PACKAGE_TRANSPORT_COST, PACKAGE_SERVICE_COST, PACKAGE_OTHER_COST,
> COD_AMOUNT, INSURED_AMOUNT, CURRENCY_UOM_ID, LAST_UPDATED_STAMP,
> LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP) VALUES (?, ?,
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (ERROR: column
> "label_image" is of type bytea but expression is of type oid)))
>
> This isn't an isolated error, it happens for any order. I have not
> updated the PostgreSQL server itself, it is still at 7.4.6.
>
> Any suggestions for a fix would be greatly appreciated...
>
> -Joe
>
>
> _______________________________________________
> Dev mailing list
> [hidden email]
> http://lists.ofbiz.org/mailman/listinfo/dev

 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev
David E. Jones
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: [OFBiz] Dev - Updated PostgreSQL Driver

David E. Jones

I have changed the default from BYTEA to OID in the  
fieldtypepostgres.xml file.

Thanks for the update.

-David


On Aug 1, 2005, at 3:01 PM, J. Eckard wrote:

> Finally found an answer on this one... when you attempt to store a  
> null in a BYTEA column with a current postgresql JDBC driver,  
> you'll get an exception. From the following message to the pgsql-
> jdbc list, it looks like we may need to update the fieldtype defs  
> for postgres to use OID instead of BYTEA for blobs.
>
> (from http://archives.postgresql.org/pgsql-jdbc/2005-08/msg00003.php )
>
> On Mon, 1 Aug 2005, Andrus Adamchik wrote:
>
> > Just tested it with build 312 and the original problem went away.  
> Now
> > I am seeing a new problem with null BLOBs only:
> >
> > java.sql.SQLException: ERROR: column "blob_col" is of type bytea but
> > expression is of type oid
>
> bytea is not the correct type to use when storing Blobs.  You need  
> to use
> oid as the underlying type, or if you want to use bytea you need to  
> use
> setBytes or setNull(x, Types.BINARY).
>
> Kris Jurka
>
> On Jul 21, 2005, at 6:26 PM, Kasubaski, Matt wrote:
>
>
>> I think this may be caused by a mismatch between how the DB was built
>> and how OfBiz is configured.  The error looks like the database  
>> has the
>> wrong column type than what is expected.
>>
>> Entitymodel_shipment.xml defines "labelimage" as:
>> <entity entity-name="ShipmentPackageRouteSeg" ...>
>>  <field name="labelImage" type="blob"></field>
>>
>>
>> Fieldtypepostgres.xml has "blob" defined as:
>> <field-type-def type="blob" sql-type="OID"
>> java-type="java.lang.Object"></field-type-def>
>>
>> Fieldtypepostgres72.xml has "blob" defined as:
>> <field-type-def type="blob" sql-type="BYTEA"
>> java-type="java.lang.Object"></field-type-def>
>>
>> It would appear that the database was built using the
>> fieldtypepostgres.xml, but is now using fieldtypepostgres72.xml.  The
>> older JDBC may not have made a distinction between the two types, but
>> the new one does.
>>
>> My suggestion would be to change the column type to "BYTEA", or  
>> recreate
>> the database using the new driver.  (Use export/import XML if you  
>> need
>> to save data.)
>>
>> Matt
>>
>> -----Original Message-----
>> From: [hidden email] [mailto:dev-
>> [hidden email]]
>> On Behalf Of J. Eckard
>> Sent: Tuesday, July 12, 2005 5:12 PM
>> To: OFBiz Project Development Discussion
>> Subject: [OFBiz] Dev - Updated PostgreSQL Driver
>>
>> After updating my local PostgreSQL jdbc driver to the latest  
>> version in
>>
>> SVN, things seemed ok, but when I tried to quickship an order, I got
>> the following error:
>>
>> ---- exception report
>> ----------------------------------------------------------
>> Exception: org.ofbiz.entity.GenericEntityException
>> Message: Exception while inserting the following entity:
>> [GenericEntity:ShipmentPackageRouteSeg][createdTxStamp,2005-07-12
>> 17:56:
>> 18.412(java.sql.Timestamp)][shipmentRouteSegmentId,00001
>> (java.lang.Strin
>>
>> g)][shipmentId,70026(java.lang.String)][lastUpdatedTxStamp,2005-07-12
>> 17:56:
>> 18.412(java.sql.Timestamp)][shipmentPackageSeqId,00001
>> (java.lang.String)
>>
>> ][createdStamp,2005-07-12
>> 17:56:18.412(java.sql.Timestamp)][lastUpdatedStamp,2005-07-12
>> 17:56:18.412(java.sql.Timestamp)] (while inserting:
>> [GenericEntity:ShipmentPackageRouteSeg][createdTxStamp,2005-07-12
>> 17:56:
>> 18.412(java.sql.Timestamp)][shipmentRouteSegmentId,00001
>> (java.lang.Strin
>>
>> g)][shipmentId,70026(java.lang.String)][lastUpdatedTxStamp,2005-07-12
>> 17:56:
>> 18.412(java.sql.Timestamp)][shipmentPackageSeqId,00001
>> (java.lang.String)
>>
>> ][createdStamp,2005-07-12
>> 17:56:18.412(java.sql.Timestamp)][lastUpdatedStamp,2005-07-12
>> 17:56:18.412(java.sql.Timestamp)] (SQL Exception while executing the
>> following:INSERT INTO public.SHIPMENT_PACKAGE_ROUTE_SEG (SHIPMENT_ID,
>> SHIPMENT_PACKAGE_SEQ_ID, SHIPMENT_ROUTE_SEGMENT_ID, TRACKING_CODE,
>> BOX_NUMBER, LABEL_IMAGE, LABEL_INTL_SIGN_IMAGE, LABEL_HTML,
>> PACKAGE_TRANSPORT_COST, PACKAGE_SERVICE_COST, PACKAGE_OTHER_COST,
>> COD_AMOUNT, INSURED_AMOUNT, CURRENCY_UOM_ID, LAST_UPDATED_STAMP,
>> LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP) VALUES (?, ?,
>> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (ERROR: column
>> "label_image" is of type bytea but expression is of type oid)))
>>
>> This isn't an isolated error, it happens for any order. I have not
>> updated the PostgreSQL server itself, it is still at 7.4.6.
>>
>> Any suggestions for a fix would be greatly appreciated...
>>
>> -Joe
>>
>>
>> _______________________________________________
>> Dev mailing list
>> [hidden email]
>> http://lists.ofbiz.org/mailman/listinfo/dev
>>
>
> _______________________________________________
> Dev mailing list
> [hidden email]
> http://lists.ofbiz.org/mailman/listinfo/dev
>

 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev
Free forum by Nabble Edit this page