Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nMt8f-0005Rf-S6 for pgsql-interfaces@arkaria.postgresql.org; Wed, 23 Feb 2022 15:01:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nMt8e-0003rz-O9 for pgsql-interfaces@arkaria.postgresql.org; Wed, 23 Feb 2022 15:01:08 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nMt8e-0003rq-Gr for pgsql-interfaces@lists.postgresql.org; Wed, 23 Feb 2022 15:01:08 +0000 Received: from mail2.fastcrypt.com ([192.99.154.132]) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nMt8Z-0007rS-J1 for pgsql-interfaces@lists.postgresql.org; Wed, 23 Feb 2022 15:01:08 +0000 Received: from mail-ej1-f44.google.com (mail-ej1-f44.google.com [209.85.218.44]) by mail2.fastcrypt.com (Postfix) with ESMTPSA id 518B4232D38 for ; Wed, 23 Feb 2022 10:01:00 -0500 (EST) Received: by mail-ej1-f44.google.com with SMTP id a23so52804335eju.3 for ; Wed, 23 Feb 2022 07:01:00 -0800 (PST) X-Gm-Message-State: AOAM530i9batXH+CQJx8AMRcia9wSTW5KaTgnkFKku8QU5Mp/RNfq6Cj 0V2iHAB1hLMVdS7vYiseeO/6qmu53K59Wg+nWRY= X-Google-Smtp-Source: ABdhPJztqzC6xHdprZ0f+9wSUrP812ZpT7GQM0JI6NvZ/A07+CBt0HrXvmWAV/Ai+yVCNQSJy+A4oRSvw01ip2J4Iy4= X-Received: by 2002:a17:907:7618:b0:6cf:5756:26c4 with SMTP id jx24-20020a170907761800b006cf575626c4mr107465ejc.492.1645628459141; Wed, 23 Feb 2022 07:00:59 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Dave Cramer Date: Wed, 23 Feb 2022 10:00:43 -0500 X-Gmail-Original-Message-ID: Message-ID: Subject: Re: Use java interface "setArray" to insert the array of user-defined type to table To: Charles Kong Cc: pgsql-interfaces@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000bd531305d8b0bbfb" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000bd531305d8b0bbfb Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, 23 Feb 2022 at 09:58, Charles Kong wrote: > Hi all, > I use a user-defined type in =E2=80=9Cschema.sql=E2=80=9D > CREATE TYPE infoEntity AS ( > "entity_id" bigint, > "seq" integer, > "message" varchar(255) > ) > > in java code: > java_infoEntity[] infoEntityArray; //java_infoEntity is the class with th= e > same member variables. > infoEntityArray =3D new java_infoEntity[3]; > infoEntityArray[0] =3D new java_infoEntity(); > .... > > //info_Entity's type is user-defined type "infoEntity". > sql =3D "insert into \"T_abc\" (info_Entity) values(?);"; > statement =3D conn.prepareStatement( sql ); > Array array =3D 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?* > > 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 --000000000000bd531305d8b0bbfb Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

=


On Wed, 23 Feb 2022 at 09:58, Charles Kong <kdy77k@gmail.com> wrote:
Hi all,
I use a user= -defined type in =E2=80=9Cschema.sql=E2=80=9D
CREATE TYPE infoEnt= ity AS (
=C2=A0 =C2=A0 "entity_id" bigint,
=C2=A0 =C2=A0 &q= uot;seq" integer,
=C2=A0 =C2=A0 "message" varchar(255))

in java code:
java_infoEntity[] i= nfoEntityArray; //java_infoEntity is the class with the same member variabl= es.
infoEntityArray=C2=A0=3D new java_infoEntity[3];
infoEntityArray[0] =3D new java_infoEntity();
....

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

How to cope with it?


The= JDBC driver does not know how to deal with user defined types. You will ha= ve to write code to serialize the type yourself.

R= egards,
Dave=C2=A0
--000000000000bd531305d8b0bbfb--