Re: java.sql.BatchUpdateException with user-defined type when upgrading JDBC driver to version 9.3

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(dot)woodring(at)iglass(dot)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 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(at)fastcrypt(dot)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 ?
>>
>> Dave
>>
>> Dave Cramer
>>
>> dave.cramer(at)credativ(dot)ca
>> http://www.credativ.ca
>>
>> On 13 March 2015 at 11:13, Lisa Woodring <lisa(dot)woodring(at)iglass(dot)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-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 |
>>> |
>>>
>>>
>>>
>>
>