Fwd: PreparedStatement.setObject(...) fails for OffsetDateTime when used in a merge statement
Lance Java via Hsqldb-user <[email protected]> Tue, 7 Mar 2023 15:11:45 +0000
| Newsgroups | gmane.comp.java.hsqldb.user |
|---|---|
| Message-ID | <CAN7Z0vT755pSMXiNN=UZ4pakSy60TdE9kqx6_+qEeWcm7=0ngA@mail.gmail.com> |
--===============7967504149207147769==
Content-Type: multipart/alternative; boundary="00000000000024e52205f650d5d4"
--00000000000024e52205f650d5d4
Content-Type: text/plain; charset="UTF-8"
HSQLDB seems to fail for PreparedStatement.setObject(...) for
OffsetDateTime when used in a merge statement.
This merge logic will fail
private void merge2Sample(long id, String code, OffsetDateTime
createdDate) throws SQLException {
String sql =
"MERGE INTO SAMPLE t " +
"USING (SELECT ? AS ID, ? AS CODE, ? AS LAST_UPDATED FROM
DUAL) val " +
"ON (t.ID = val.ID) " +
"WHEN MATCHED THEN UPDATE SET t.CODE = val.CODE,
t.LAST_UPDATED = val.LAST_UPDATED " +
"WHEN NOT MATCHED THEN INSERT (ID, CODE, LAST_UPDATED)
VALUES (val.ID, val.CODE, val.LAST_UPDATED)";
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setLong(1, id);
ps.setString(2, code);
ps.setObject(3, createdDate);
assertThat(ps.executeUpdate()).isEqualTo(1);
}
}
As a workaround I can call PreparedStatement.setString(...) with a string
formatted using a DateTimeFormatter but I'd prefer not to
private void merge1Sample(long id, String code, OffsetDateTime
createdDate) throws SQLException {
DateTimeFormatter dateFormatter =
DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSSxxxxx");
String sql =
"MERGE INTO SAMPLE t " +
"USING (SELECT ? AS ID, ? AS CODE, ? AS LAST_UPDATED FROM
DUAL) val " +
"ON (t.ID = val.ID) " +
"WHEN MATCHED THEN UPDATE SET t.CODE = val.CODE,
t.LAST_UPDATED = val.LAST_UPDATED " +
"WHEN NOT MATCHED THEN INSERT (ID, CODE, LAST_UPDATED)
VALUES (val.ID, val.CODE, val.LAST_UPDATED)";
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setLong(1, id);
ps.setString(2, code);
ps.setString(3, dateFormatter.format(createdDate));
assertThat(ps.executeUpdate()).isEqualTo(1);
}
}
Please see the failing test case at
https://gist.github.com/uklance/14fd8c27e34ffddbe504109009e4dcc3 which
throws the following exception
Caused by: org.hsqldb.HsqlException: data exception: invalid datetime format
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.types.DateTimeType.convertToDatetimeSpecial(Unknown
Source)
at org.hsqldb.types.DateTimeType.convertToType(Unknown Source)
at org.hsqldb.ExpressionOp.getValue(Unknown Source)
at org.hsqldb.StatementDML.getInsertData(Unknown Source)
at org.hsqldb.StatementDML.executeMergeStatement(Unknown Source)
at org.hsqldb.StatementDML.getResult(Unknown Source)
at org.hsqldb.StatementDMQL.execute(Unknown Source)
at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 71 more
--00000000000024e52205f650d5d4
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
<div dir=3D"ltr"><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail=
_attr">HSQLDB seems to fail for PreparedStatement.setObject(...) for Offset=
DateTime when used in a merge statement.<br></div><div dir=3D"ltr"><div><br=
></div><div>This merge logic will fail</div><div><br></div><div>=C2=A0 =C2=
=A0 private void merge2Sample(long id, String code, OffsetDateTime createdD=
ate) throws SQLException =C2=A0{<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 String sql =
=3D<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 "MERGE =
INTO SAMPLE t " +<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 "USING (SELECT ? AS ID, ? AS CODE, ? AS LAST_UPDATED FROM DUAL)=
val " +<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 &q=
uot;ON (t.ID =3D val.ID) " +<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 "WHEN MATCHED THEN UPDATE SET t.CODE =3D val.CODE, t=
.LAST_UPDATED =3D val.LAST_UPDATED " +<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 "WHEN NOT MATCHED THEN INSERT (ID, CODE, L=
AST_UPDATED) VALUES (val.ID, val.CODE, val.LAST_UPDATED)";<br>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 try (PreparedStatement ps =3D connection.prepareStatem=
ent(sql)) {<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ps.setLong(1, id);=
<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ps.setString(2, code);<br>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ps.setObject(3, createdDate);<br>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 assertThat(ps.executeUpdate()).isEqu=
alTo(1);<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 }<br>=C2=A0 =C2=A0 }<br></div><div>=
<br></div><div>As a workaround I can call PreparedStatement.setString(...) =
with a string formatted using a DateTimeFormatter but I'd prefer not to=
</div><div><br></div><div>=C2=A0 =C2=A0 private void merge1Sample(long id, =
String code, OffsetDateTime createdDate) throws SQLException =C2=A0{<br>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 DateTimeFormatter dateFormatter =3D DateTimeFormat=
ter.ofPattern("yyyy-MM-dd HH:mm:ss.SSSxxxxx");<br>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 String sql =3D<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 "MERGE INTO SAMPLE t " +<br>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 "USING (SELECT ? AS ID, ? AS CODE, =
? AS LAST_UPDATED FROM DUAL) val " +<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 "ON (t.ID =3D val.ID) " +<br>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 "WHEN MATCHED THEN UP=
DATE SET t.CODE =3D val.CODE, t.LAST_UPDATED =3D val.LAST_UPDATED " +<=
br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 "WHEN NOT M=
ATCHED THEN INSERT (ID, CODE, LAST_UPDATED) VALUES (val.ID, val.CODE, val.L=
AST_UPDATED)";<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 try (PreparedStatement p=
s =3D connection.prepareStatement(sql)) {<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 ps.setLong(1, id);<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 ps.setString(2, code);<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ps.=
setString(3, dateFormatter.format(createdDate));<br>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 assertThat(ps.executeUpdate()).isEqualTo(1);<br>=C2=A0=
=C2=A0 =C2=A0 =C2=A0 }<br>=C2=A0 =C2=A0 }<br></div><div><br></div><div>Ple=
ase see the failing test case at=C2=A0<a href=3D"https://gist.github.com/uk=
lance/14fd8c27e34ffddbe504109009e4dcc3" style=3D"font-family:Verdana,"=
sans-serif"" target=3D"_blank">https://gist.github.com/uklance/14fd8c2=
7e34ffddbe504109009e4dcc3</a>=C2=A0which throws the following exception<br>=
</div><div><div><br></div><div><p class=3D"MsoNormal"><span lang=3D"DE" sty=
le=3D"font-family:Verdana,"sans-serif"">Caused by: org.hsqldb.Hsq=
lException: data exception: invalid datetime format<u></u><u></u></span></p=
><p class=3D"MsoNormal"><span lang=3D"DE" style=3D"font-family:Verdana,&quo=
t;sans-serif"">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =
at org.hsqldb.error.Error.error(Unknown Source)<u></u><u></u></span></p><p =
class=3D"MsoNormal"><span lang=3D"DE" style=3D"font-family:Verdana,"sa=
ns-serif"">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 at o=
rg.hsqldb.error.Error.error(Unknown Source)<u></u><u></u></span></p><p clas=
s=3D"MsoNormal"><span lang=3D"DE" style=3D"font-family:Verdana,"sans-s=
erif"">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 at org.h=
sqldb.types.DateTimeType.convertToDatetimeSpecial(Unknown Source)<u></u><u>=
</u></span></p><p class=3D"MsoNormal"><span lang=3D"DE" style=3D"font-famil=
y:Verdana,"sans-serif"">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0 at org.hsqldb.types.DateTimeType.convertToType(Unknown Sour=
ce)<u></u><u></u></span></p><p class=3D"MsoNormal"><span lang=3D"DE" style=
=3D"font-family:Verdana,"sans-serif"">=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0 at org.hsqldb.ExpressionOp.getValue(Unknown Sou=
rce)<u></u><u></u></span></p><p class=3D"MsoNormal"><span lang=3D"DE" style=
=3D"font-family:Verdana,"sans-serif"">=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0 at org.hsqldb.StatementDML.getInsertData(Unknow=
n Source)<u></u><u></u></span></p><p class=3D"MsoNormal"><span lang=3D"DE" =
style=3D"font-family:Verdana,"sans-serif"">=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 at org.hsqldb.StatementDML.executeMergeSt=
atement(Unknown Source)<u></u><u></u></span></p><p class=3D"MsoNormal"><spa=
n lang=3D"DE" style=3D"font-family:Verdana,"sans-serif"">=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 at org.hsqldb.StatementDML=
.getResult(Unknown Source)<u></u><u></u></span></p><p class=3D"MsoNormal"><=
span lang=3D"DE" style=3D"font-family:Verdana,"sans-serif"">=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 at org.hsqldb.Statement=
DMQL.execute(Unknown Source)<u></u><u></u></span></p><p class=3D"MsoNormal"=
><span lang=3D"DE" style=3D"font-family:Verdana,"sans-serif"">=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 at org.hsqldb.Session.e=
xecuteCompiledStatement(Unknown Source)<u></u><u></u></span></p><p class=3D=
"MsoNormal"><span lang=3D"DE" style=3D"font-family:Verdana,"sans-serif=
"">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 at org.hsqld=
b.Session.execute(Unknown Source)<u></u><u></u></span></p><p class=3D"MsoNo=
rmal"><span lang=3D"DE" style=3D"font-family:Verdana,"sans-serif"=
">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ... 71 more</span>=
</p></div></div></div>
</div></div>
--00000000000024e52205f650d5d4--
--===============7967504149207147769==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
--===============7967504149207147769==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
_______________________________________________
Hsqldb-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
--===============7967504149207147769==--