Thread: java.sql.BatchUpdateException with user-defined type when upgrading JDBC driver to version 9.3
java.sql.BatchUpdateException with user-defined type when upgrading JDBC driver to version 9.3
From
Lisa Woodring
Date:
We have a user-defined database type (that is essentially an enum). When using any version of the JDBC driver, we can insert into that database field with our normal operational code (which only does single inserts).
However, when using DBUnit with our test code, it performs a batch insert into our test database for initialization of a test. In order to get this to initially work with our user-defined type (back a couple of years ago), we added a class that extends "org.dbunit.ext.postgresql.PostgresqlDataTypeFactory" and overrides the method "isEnumType". This was all that was needed to handle the user-defined type.
But now with upgrading versions of the postgres JDBC driver, it does not work. With changing only the JDBC driver, it breaks when moving from version 9.2 to 9.3.
These versions work: 9.2-1004-jdbc4, 9.2-1004-jdbc41
These versions do NOT work: 9.3-1100-jdbc4, 9.3-1100-jdbc41, 9.4-1201-jdbc41
Any ideas on how to resolve this? Stack trace:
[junit] Caused by: org.dbunit.DatabaseUnitException: Exception processing table name='FOO'
[junit] at org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOperation.java:231)
[junit] at org.dbunit.operation.CompositeOperation.execute(CompositeOperation.java:79)
[junit] at org.unitils.dbunit.datasetloadstrategy.impl.CleanInsertLoadStrategy.doExecute(CleanInsertLoadStrategy.java:45)
[junit] at org.unitils.dbunit.datasetloadstrategy.impl.BaseDataSetLoadStrategy.execute(BaseDataSetLoadStrategy.java:44)
[junit] ... 51 more
[junit] Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into "public"."FOO" ("fooid", "name", "desc", "parentfooid", "gtype") values (999999, 'Virtual', 'Virtual Machines', 0, 'virtual') was aborted. Call getNextException to see the cause.
[junit] at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2762)
[junit] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1999)
[junit] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:421)
[junit] at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2929)
[junit] at org.dbunit.database.statement.PreparedBatchStatement.executeBatch(PreparedBatchStatement.java:86)
[junit] at org.dbunit.database.statement.AutomaticPreparedBatchStatement.executeBatch(AutomaticPreparedBatchStatement.java:83)
[junit] at org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOperation.java:224)
[junit] ... 54 more
The offending column is "gtype" (and the batch insert works if I remove setting this column). "gtype" is defined as follows:
Column | Type | Modifiers
---------+---------+--------------------------
gtype | vtype | default 'regular'::vtype
Schema | Name | Internal name | Size | Elements | Access privileges | Description
--------+-------+---------------+------+-------------+-------------------+-------------
public | vtype | vtype | 4 | regular +| |
| | | | location +| |
| | | | restriction+| |
| | | | virtual | |
Re: java.sql.BatchUpdateException with user-defined type when upgrading JDBC driver to version 9.3
From
Dave Cramer
Date:
Lisa,
Now I realize that you probably aren't going to change your schema, but ... just saying..
can you post the server logs from this error ?
Dave
On 13 March 2015 at 11:13, Lisa Woodring <lisa.woodring@iglass.net> wrote:
We have a user-defined database type (that is essentially an enum). When using any version of the JDBC driver, we can insert into that database field with our normal operational code (which only does single inserts).However, when using DBUnit with our test code, it performs a batch insert into our test database for initialization of a test. In order to get this to initially work with our user-defined type (back a couple of years ago), we added a class that extends "org.dbunit.ext.postgresql.PostgresqlDataTypeFactory" and overrides the method "isEnumType". This was all that was needed to handle the user-defined type.But now with upgrading versions of the postgres JDBC driver, it does not work. With changing only the JDBC driver, it breaks when moving from version 9.2 to 9.3.These versions work: 9.2-1004-jdbc4, 9.2-1004-jdbc41These versions do NOT work: 9.3-1100-jdbc4, 9.3-1100-jdbc41, 9.4-1201-jdbc41Any ideas on how to resolve this? Stack trace:[junit] Caused by: org.dbunit.DatabaseUnitException: Exception processing table name='FOO'[junit] at org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOperation.java:231)[junit] at org.dbunit.operation.CompositeOperation.execute(CompositeOperation.java:79)[junit] at org.unitils.dbunit.datasetloadstrategy.impl.CleanInsertLoadStrategy.doExecute(CleanInsertLoadStrategy.java:45)[junit] at org.unitils.dbunit.datasetloadstrategy.impl.BaseDataSetLoadStrategy.execute(BaseDataSetLoadStrategy.java:44)[junit] ... 51 more[junit] Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into "public"."FOO" ("fooid", "name", "desc", "parentfooid", "gtype") values (999999, 'Virtual', 'Virtual Machines', 0, 'virtual') was aborted. Call getNextException to see the cause.[junit] at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2762)[junit] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1999)[junit] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:421)[junit] at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2929)[junit] at org.dbunit.database.statement.PreparedBatchStatement.executeBatch(PreparedBatchStatement.java:86)[junit] at org.dbunit.database.statement.AutomaticPreparedBatchStatement.executeBatch(AutomaticPreparedBatchStatement.java:83)[junit] at org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOperation.java:224)[junit] ... 54 moreThe offending column is "gtype" (and the batch insert works if I remove setting this column). "gtype" is defined as follows:Column | Type | Modifiers---------+---------+--------------------------gtype | vtype | default 'regular'::vtypeSchema | Name | Internal name | Size | Elements | Access privileges | Description--------+-------+---------------+------+-------------+-------------------+-------------public | vtype | vtype | 4 | regular +| || | | | location +| || | | | restriction+| || | | | virtual | |
Re: java.sql.BatchUpdateException with user-defined type when upgrading JDBC driver to version 9.3
From
George Woodring
Date:
Here is the error from the server log.
Mar 13 09:39:04 lbeam postgres[12235]: [7-1] ERROR: column "gtype" is of type vtype but expression is of type character varying at character 133
Mar 13 09:39:04 lbeam postgres[12235]: [7-2] HINT: You will need to rewrite or cast the expression.
Mar 13 09:39:04 lbeam postgres[12235]: [7-3] STATEMENT: insert into "public"."machgroups" ("machgroupsid", "name", "mgdesc", "parentgrpid", "virtual", "gtype") values
($1, $2, $3, $4, $5, $6)
iGLASS Networks
www.iglass.net
www.iglass.net
On Fri, Mar 13, 2015 at 1:52 PM, Dave Cramer <pg@fastcrypt.com> wrote:
Lisa,FWIW, enum types are somewhat of a PITA, better to use text and check constraints.Now I realize that you probably aren't going to change your schema, but ... just saying..can you post the server logs from this error ?DaveOn 13 March 2015 at 11:13, Lisa Woodring <lisa.woodring@iglass.net> wrote:We have a user-defined database type (that is essentially an enum). When using any version of the JDBC driver, we can insert into that database field with our normal operational code (which only does single inserts).However, when using DBUnit with our test code, it performs a batch insert into our test database for initialization of a test. In order to get this to initially work with our user-defined type (back a couple of years ago), we added a class that extends "org.dbunit.ext.postgresql.PostgresqlDataTypeFactory" and overrides the method "isEnumType". This was all that was needed to handle the user-defined type.But now with upgrading versions of the postgres JDBC driver, it does not work. With changing only the JDBC driver, it breaks when moving from version 9.2 to 9.3.These versions work: 9.2-1004-jdbc4, 9.2-1004-jdbc41These versions do NOT work: 9.3-1100-jdbc4, 9.3-1100-jdbc41, 9.4-1201-jdbc41Any ideas on how to resolve this? Stack trace:[junit] Caused by: org.dbunit.DatabaseUnitException: Exception processing table name='FOO'[junit] at org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOperation.java:231)[junit] at org.dbunit.operation.CompositeOperation.execute(CompositeOperation.java:79)[junit] at org.unitils.dbunit.datasetloadstrategy.impl.CleanInsertLoadStrategy.doExecute(CleanInsertLoadStrategy.java:45)[junit] at org.unitils.dbunit.datasetloadstrategy.impl.BaseDataSetLoadStrategy.execute(BaseDataSetLoadStrategy.java:44)[junit] ... 51 more[junit] Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into "public"."FOO" ("fooid", "name", "desc", "parentfooid", "gtype") values (999999, 'Virtual', 'Virtual Machines', 0, 'virtual') was aborted. Call getNextException to see the cause.[junit] at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2762)[junit] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1999)[junit] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:421)[junit] at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2929)[junit] at org.dbunit.database.statement.PreparedBatchStatement.executeBatch(PreparedBatchStatement.java:86)[junit] at org.dbunit.database.statement.AutomaticPreparedBatchStatement.executeBatch(AutomaticPreparedBatchStatement.java:83)[junit] at org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOperation.java:224)[junit] ... 54 moreThe offending column is "gtype" (and the batch insert works if I remove setting this column). "gtype" is defined as follows:Column | Type | Modifiers---------+---------+--------------------------gtype | vtype | default 'regular'::vtypeSchema | Name | Internal name | Size | Elements | Access privileges | Description--------+-------+---------------+------+-------------+-------------------+-------------public | vtype | vtype | 4 | regular +| || | | | location +| || | | | restriction+| || | | | virtual | |
Re: java.sql.BatchUpdateException with user-defined type when upgrading JDBC driver to version 9.3
From
Dave Cramer
Date:
This http://stackoverflow.com/questions/851758/java-enums-jpa-and-postgres-enums-how-do-i-make-them-work-together should give you a hint as to why.
You can also do use the connection parameter stringtype=unspecified https://jdbc.postgresql.org/documentation/94/connect.html#connection-parameters
To get around this. But I would strongly suggest you change your schema to text and use check constraints. Your life will be easier.
Dave
On 13 March 2015 at 14:51, George Woodring <george.woodring@iglass.net> wrote:
Here is the error from the server log.Mar 13 09:39:04 lbeam postgres[12235]: [7-1] ERROR: column "gtype" is of type vtype but expression is of type character varying at character 133Mar 13 09:39:04 lbeam postgres[12235]: [7-2] HINT: You will need to rewrite or cast the expression.Mar 13 09:39:04 lbeam postgres[12235]: [7-3] STATEMENT: insert into "public"."machgroups" ("machgroupsid", "name", "mgdesc", "parentgrpid", "virtual", "gtype") values($1, $2, $3, $4, $5, $6)iGLASS Networks
www.iglass.netOn Fri, Mar 13, 2015 at 1:52 PM, Dave Cramer <pg@fastcrypt.com> wrote:Lisa,FWIW, enum types are somewhat of a PITA, better to use text and check constraints.Now I realize that you probably aren't going to change your schema, but ... just saying..can you post the server logs from this error ?DaveOn 13 March 2015 at 11:13, Lisa Woodring <lisa.woodring@iglass.net> wrote:We have a user-defined database type (that is essentially an enum). When using any version of the JDBC driver, we can insert into that database field with our normal operational code (which only does single inserts).However, when using DBUnit with our test code, it performs a batch insert into our test database for initialization of a test. In order to get this to initially work with our user-defined type (back a couple of years ago), we added a class that extends "org.dbunit.ext.postgresql.PostgresqlDataTypeFactory" and overrides the method "isEnumType". This was all that was needed to handle the user-defined type.But now with upgrading versions of the postgres JDBC driver, it does not work. With changing only the JDBC driver, it breaks when moving from version 9.2 to 9.3.These versions work: 9.2-1004-jdbc4, 9.2-1004-jdbc41These versions do NOT work: 9.3-1100-jdbc4, 9.3-1100-jdbc41, 9.4-1201-jdbc41Any ideas on how to resolve this? Stack trace:[junit] Caused by: org.dbunit.DatabaseUnitException: Exception processing table name='FOO'[junit] at org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOperation.java:231)[junit] at org.dbunit.operation.CompositeOperation.execute(CompositeOperation.java:79)[junit] at org.unitils.dbunit.datasetloadstrategy.impl.CleanInsertLoadStrategy.doExecute(CleanInsertLoadStrategy.java:45)[junit] at org.unitils.dbunit.datasetloadstrategy.impl.BaseDataSetLoadStrategy.execute(BaseDataSetLoadStrategy.java:44)[junit] ... 51 more[junit] Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into "public"."FOO" ("fooid", "name", "desc", "parentfooid", "gtype") values (999999, 'Virtual', 'Virtual Machines', 0, 'virtual') was aborted. Call getNextException to see the cause.[junit] at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2762)[junit] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1999)[junit] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:421)[junit] at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2929)[junit] at org.dbunit.database.statement.PreparedBatchStatement.executeBatch(PreparedBatchStatement.java:86)[junit] at org.dbunit.database.statement.AutomaticPreparedBatchStatement.executeBatch(AutomaticPreparedBatchStatement.java:83)[junit] at org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOperation.java:224)[junit] ... 54 moreThe offending column is "gtype" (and the batch insert works if I remove setting this column). "gtype" is defined as follows:Column | Type | Modifiers---------+---------+--------------------------gtype | vtype | default 'regular'::vtypeSchema | Name | Internal name | Size | Elements | Access privileges | Description--------+-------+---------------+------+-------------+-------------------+-------------public | vtype | vtype | 4 | regular +| || | | | location +| || | | | restriction+| || | | | virtual | |
Re: java.sql.BatchUpdateException with user-defined type when upgrading JDBC driver to version 9.3
From
Lisa Woodring
Date:
Yes, this is the only place where we use enums.
From postgres log:
Mar 16 09:17:27 lbeam postgres[4209]: [5-1] ERROR: column "gtype" is of type vtype but expression is of type character varying at character 114
Mar 16 09:17:27 lbeam postgres[4209]: [5-2] HINT: You will need to rewrite or cast the expression.
Mar 16 09:17:27 lbeam postgres[4209]: [5-3] STATEMENT: insert into "public"."foo" ("fooid", "name", "desc", "gtype") values ($1, $2, $3, $4)
On Fri, Mar 13, 2015 at 1:52 PM, Dave Cramer <pg@fastcrypt.com> wrote:
Lisa,FWIW, enum types are somewhat of a PITA, better to use text and check constraints.Now I realize that you probably aren't going to change your schema, but ... just saying..can you post the server logs from this error ?DaveOn 13 March 2015 at 11:13, Lisa Woodring <lisa.woodring@iglass.net> wrote:We have a user-defined database type (that is essentially an enum). When using any version of the JDBC driver, we can insert into that database field with our normal operational code (which only does single inserts).However, when using DBUnit with our test code, it performs a batch insert into our test database for initialization of a test. In order to get this to initially work with our user-defined type (back a couple of years ago), we added a class that extends "org.dbunit.ext.postgresql.PostgresqlDataTypeFactory" and overrides the method "isEnumType". This was all that was needed to handle the user-defined type.But now with upgrading versions of the postgres JDBC driver, it does not work. With changing only the JDBC driver, it breaks when moving from version 9.2 to 9.3.These versions work: 9.2-1004-jdbc4, 9.2-1004-jdbc41These versions do NOT work: 9.3-1100-jdbc4, 9.3-1100-jdbc41, 9.4-1201-jdbc41Any ideas on how to resolve this? Stack trace:[junit] Caused by: org.dbunit.DatabaseUnitException: Exception processing table name='FOO'[junit] at org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOperation.java:231)[junit] at org.dbunit.operation.CompositeOperation.execute(CompositeOperation.java:79)[junit] at org.unitils.dbunit.datasetloadstrategy.impl.CleanInsertLoadStrategy.doExecute(CleanInsertLoadStrategy.java:45)[junit] at org.unitils.dbunit.datasetloadstrategy.impl.BaseDataSetLoadStrategy.execute(BaseDataSetLoadStrategy.java:44)[junit] ... 51 more[junit] Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into "public"."FOO" ("fooid", "name", "desc", "parentfooid", "gtype") values (999999, 'Virtual', 'Virtual Machines', 0, 'virtual') was aborted. Call getNextException to see the cause.[junit] at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2762)[junit] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1999)[junit] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:421)[junit] at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2929)[junit] at org.dbunit.database.statement.PreparedBatchStatement.executeBatch(PreparedBatchStatement.java:86)[junit] at org.dbunit.database.statement.AutomaticPreparedBatchStatement.executeBatch(AutomaticPreparedBatchStatement.java:83)[junit] at org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOperation.java:224)[junit] ... 54 moreThe offending column is "gtype" (and the batch insert works if I remove setting this column). "gtype" is defined as follows:Column | Type | Modifiers---------+---------+--------------------------gtype | vtype | default 'regular'::vtypeSchema | Name | Internal name | Size | Elements | Access privileges | Description--------+-------+---------------+------+-------------+-------------------+-------------public | vtype | vtype | 4 | regular +| || | | | location +| || | | | restriction+| || | | | virtual | |
Re: java.sql.BatchUpdateException with user-defined type when upgrading JDBC driver to version 9.3
From
Lisa Woodring
Date:
Nevermind, the last message. I had not seen that George replied.
Thank you. The "stringtype" method worked to easily fix this for our test code.
-- Lisa
On Mon, Mar 16, 2015 at 9:20 AM, Lisa Woodring <lisa.woodring@iglass.net> wrote:
Yes, this is the only place where we use enums.From postgres log:Mar 16 09:17:27 lbeam postgres[4209]: [5-1] ERROR: column "gtype" is of type vtype but expression is of type character varying at character 114Mar 16 09:17:27 lbeam postgres[4209]: [5-2] HINT: You will need to rewrite or cast the expression.Mar 16 09:17:27 lbeam postgres[4209]: [5-3] STATEMENT: insert into "public"."foo" ("fooid", "name", "desc", "gtype") values ($1, $2, $3, $4)On Fri, Mar 13, 2015 at 1:52 PM, Dave Cramer <pg@fastcrypt.com> wrote:Lisa,FWIW, enum types are somewhat of a PITA, better to use text and check constraints.Now I realize that you probably aren't going to change your schema, but ... just saying..can you post the server logs from this error ?DaveOn 13 March 2015 at 11:13, Lisa Woodring <lisa.woodring@iglass.net> wrote:We have a user-defined database type (that is essentially an enum). When using any version of the JDBC driver, we can insert into that database field with our normal operational code (which only does single inserts).However, when using DBUnit with our test code, it performs a batch insert into our test database for initialization of a test. In order to get this to initially work with our user-defined type (back a couple of years ago), we added a class that extends "org.dbunit.ext.postgresql.PostgresqlDataTypeFactory" and overrides the method "isEnumType". This was all that was needed to handle the user-defined type.But now with upgrading versions of the postgres JDBC driver, it does not work. With changing only the JDBC driver, it breaks when moving from version 9.2 to 9.3.These versions work: 9.2-1004-jdbc4, 9.2-1004-jdbc41These versions do NOT work: 9.3-1100-jdbc4, 9.3-1100-jdbc41, 9.4-1201-jdbc41Any ideas on how to resolve this? Stack trace:[junit] Caused by: org.dbunit.DatabaseUnitException: Exception processing table name='FOO'[junit] at org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOperation.java:231)[junit] at org.dbunit.operation.CompositeOperation.execute(CompositeOperation.java:79)[junit] at org.unitils.dbunit.datasetloadstrategy.impl.CleanInsertLoadStrategy.doExecute(CleanInsertLoadStrategy.java:45)[junit] at org.unitils.dbunit.datasetloadstrategy.impl.BaseDataSetLoadStrategy.execute(BaseDataSetLoadStrategy.java:44)[junit] ... 51 more[junit] Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into "public"."FOO" ("fooid", "name", "desc", "parentfooid", "gtype") values (999999, 'Virtual', 'Virtual Machines', 0, 'virtual') was aborted. Call getNextException to see the cause.[junit] at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2762)[junit] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1999)[junit] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:421)[junit] at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2929)[junit] at org.dbunit.database.statement.PreparedBatchStatement.executeBatch(PreparedBatchStatement.java:86)[junit] at org.dbunit.database.statement.AutomaticPreparedBatchStatement.executeBatch(AutomaticPreparedBatchStatement.java:83)[junit] at org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOperation.java:224)[junit] ... 54 moreThe offending column is "gtype" (and the batch insert works if I remove setting this column). "gtype" is defined as follows:Column | Type | Modifiers---------+---------+--------------------------gtype | vtype | default 'regular'::vtypeSchema | Name | Internal name | Size | Elements | Access privileges | Description--------+-------+---------------+------+-------------+-------------------+-------------public | vtype | vtype | 4 | regular +| || | | | location +| || | | | restriction+| || | | | virtual | |