Subject: Use java interface "setArray" to insert the array of user-defined type to table

From: Charles Kong <[email-masked]>
Date: Wed, 23 Feb 2022 14:58:04 +0000

Hi all,
I use a user-defined type in “schema.sql”
CREATE TYPE infoEntity AS (
    "entity_id" bigint,
    "seq" integer,
    "message" varchar(255)
)

in java code:
java_infoEntity[] infoEntityArray; //java_infoEntity is the class with the
same member variables.
infoEntityArray = new java_infoEntity[3];
infoEntityArray[0] = new java_infoEntity();
....

//info_Entity's type is user-defined type "infoEntity".
sql = "insert into \"T_abc\" (info_Entity) values(?);";
statement = conn.prepareStatement( sql );
Array array = conn.createArrayOf("infoEntity",  infoEntityArray);
statement.setArray(1,array);
statement.addBatch();
statement.executeBatch();
....
....
Sql throw exception:
sqlException:  SQLState: 22P02 Error Code: 0 Error Message: Batch entry 0
insert into "T_abc" (info_Entity)
values('{"com.xxx.java_infoEntity@597d57aa"}')
was aborted: ERROR: malformed record literal: "
com.xxx.java_infoEntity@597d57aa"

*How to cope with it?*

---

From: Dave Cramer <[email-masked]>
Date: Wed, 23 Feb 2022 10:00:43 -0500

On Wed, 23 Feb 2022 at 09:58, Charles Kong <[email-masked]> wrote:

> values('{"com.xxx.java_infoEntity@597d57aa"}') was aborted: ERROR:
> malformed record literal: "com.xxx.java_infoEntity@597d57aa"
>
> *How to cope with it?*
>
>
The JDBC driver does not know how to deal with user defined types. You will
have to write code to serialize the type yourself.

Regards,
Dave