Re: Fwd: PreparedStatement.setObject(...) fails for OffsetDateTime when used in a merge statement
Lance Java via Hsqldb-user <[email protected]> Tue, 7 Mar 2023 16:02:00 +0000
| Newsgroups | gmane.comp.java.hsqldb.user |
|---|---|
| Message-ID | <CAN7Z0vSkBQWVqfbyKLYpKKkSUqvroun5xr75QEiDAYBxua6MBA@mail.gmail.com> |
--===============4582045902313740525==
Content-Type: multipart/alternative; boundary="000000000000db5ee105f6518828"
--000000000000db5ee105f6518828
Content-Type: text/plain; charset="UTF-8"
Please see the failng test case at
https://gist.github.com/uklance/14fd8c27e34ffddbe504109009e4dcc3 which
shows the column type is TIMESTAMP WITH TIME ZONE
@BeforeEach
public void beforeEach() throws Exception {
connection =
DriverManager.getConnection("jdbc:hsqldb:mem:test;sql.syntax_ora=true",
"test", "test");
connection.createStatement().execute(
"CREATE TABLE SAMPLE (\n" +
" ID NUMERIC(12,0) PRIMARY KEY,\n" +
" CODE VARCHAR2(50)," +
" LAST_UPDATED TIMESTAMP WITH TIME ZONE,\n" +
")"
);
}
> You can also use a CAST to the intended SQL type for the parameter
The failing test case shows that PreparedStatement.setObject(...) works for
OffsetDateTime for INSERT and UPDATE statements. Why is this only failing
for MERGE?
On Tue, 7 Mar 2023 at 15:51, Fred Toussi via Hsqldb-user <
[email protected]> wrote:
> You can also use a CAST to the intended SQL type for the parameter.
> Assuming you want TIMESTAMP WITH TIME ZONE as the type:
>
> "MERGE INTO SAMPLE t " +
> "USING (SELECT ? AS ID, ? AS CODE, CAST(? AS TIMESTAMP WITH TIME ZONE) AS
> LAST_UPDATED FROM DUAL) val " +
>
>
> On Tue, Mar 7, 2023, at 15:11, Lance Java via Hsqldb-user wrote:
>
> 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
>
> _______________________________________________
> Hsqldb-user mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>
>
> _______________________________________________
> Hsqldb-user mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>
--000000000000db5ee105f6518828
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
<div dir=3D"ltr">Please see the failng test case at <a href=3D"https://gist=
.github.com/uklance/14fd8c27e34ffddbe504109009e4dcc3">https://gist.github.c=
om/uklance/14fd8c27e34ffddbe504109009e4dcc3</a> which shows the column type=
is=C2=A0TIMESTAMP WITH TIME ZONE<div><br></div><div>=C2=A0 =C2=A0 @BeforeE=
ach<br>=C2=A0 =C2=A0 public void beforeEach() throws Exception {<br>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 connection =3D DriverManager.getConnection("jdbc:=
hsqldb:mem:test;sql.syntax_ora=3Dtrue", "test", "test&q=
uot;);<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 connection.createStatement().execute(=
<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 "CREATE TA=
BLE SAMPLE (\n" +<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 " =C2=A0 ID NUMERIC(12,0) PRIMARY KEY,\n" +<br>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 " =C2=A0 CODE VARCHAR2(5=
0)," +<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 &quo=
t; =C2=A0 LAST_UPDATED TIMESTAMP WITH TIME ZONE,\n" +<br>=C2=A0 =C2=A0=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ")"<br>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 );<br>=C2=A0 =C2=A0 }<br></div><div><br></div><div>>=C2=A0=
<span style=3D"font-family:Arial">You can also use a CAST to the intended S=
QL type for the parameter</span></div><div><span style=3D"font-family:Arial=
">The failing=C2=A0test case shows that PreparedStatement.setObject(...) wo=
rks for OffsetDateTime for INSERT and UPDATE statements. Why is this only f=
ailing for MERGE?</span></div></div><br><div class=3D"gmail_quote"><div dir=
=3D"ltr" class=3D"gmail_attr">On Tue, 7 Mar 2023 at 15:51, Fred Toussi via =
Hsqldb-user <<a href=3D"mailto:[email protected]">hsqldb=
[email protected]</a>> wrote:<br></div><blockquote class=3D"gm=
ail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,=
204,204);padding-left:1ex"><div class=3D"msg773025684330080279"><u></u><div=
><div style=3D"font-family:Arial">You can also use a CAST to the intended S=
QL type for the parameter. Assuming you want TIMESTAMP WITH TIME ZONE as th=
e type:<br></div><div style=3D"font-family:Arial"><br></div><blockquote typ=
e=3D"cite" id=3D"m_773025684330080279qt"><div dir=3D"ltr"><div><div dir=3D"=
ltr"><div><div>"MERGE INTO SAMPLE t " +<br></div><div>"USING=
(SELECT ? AS ID, ? AS CODE, CAST(? AS TIMESTAMP WITH TIME ZONE) AS LAST_UP=
DATED FROM DUAL) val " +<br></div></div></div></div></div></blockquote=
><div style=3D"font-family:Arial"><br></div><div>On Tue, Mar 7, 2023, at 15=
:11, Lance Java via Hsqldb-user wrote:<br></div><blockquote type=3D"cite" i=
d=3D"m_773025684330080279qt"><div dir=3D"ltr"><div><div dir=3D"ltr">HSQLDB =
seems to fail for PreparedStatement.setObject(...) for OffsetDateTime when =
used in a merge statement.<br></div><div dir=3D"ltr"><div><br></div><div>Th=
is merge logic will fail<br></div><div><br></div><div><div>=C2=A0 =C2=A0 pr=
ivate void merge2Sample(long id, String code, OffsetDateTime createdDate) t=
hrows SQLException =C2=A0{<br></div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 String=
sql =3D<br></div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 "MERGE INTO SAMPLE t " +<br></div><div>=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></div><div>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 "ON (t.ID =3D val.ID) " +<=
br></div><div>=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.L=
AST_UPDATED " +<br></div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 "WHEN NOT MATCHED THEN INSERT (ID, CODE, LAST_UPDATE=
D) VALUES (val.ID, val.CODE, val.LAST_UPDATED)";<br></div><div>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 try (PreparedStatement ps =3D connection.prepareStatem=
ent(sql)) {<br></div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ps.setL=
ong(1, id);<br></div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ps.setS=
tring(2, code);<br></div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ps.=
setObject(3, createdDate);<br></div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
=C2=A0 assertThat(ps.executeUpdate()).isEqualTo(1);<br></div><div>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 }<br></div><div>=C2=A0 =C2=A0 }<br></div></div><div><b=
r></div><div>As a workaround I can call PreparedStatement.setString(...) wi=
th a string formatted using a DateTimeFormatter but I'd prefer not to<b=
r></div><div><br></div><div><div>=C2=A0 =C2=A0 private void merge1Sample(lo=
ng id, String code, OffsetDateTime createdDate) throws SQLException =C2=A0{=
<br></div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 DateTimeFormatter dateFormatter =
=3D DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSSxxxxx");<=
br></div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 String sql =3D<br></div><div>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 "MERGE INTO SAMPL=
E t " +<br></div><div>=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></div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 "ON (t.ID =3D val.ID) " +<br></div><div>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 "WHEN MATCHED THEN UPDATE SE=
T t.CODE =3D val.CODE, t.LAST_UPDATED =3D val.LAST_UPDATED " +<br></di=
v><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 "WHEN N=
OT MATCHED THEN INSERT (ID, CODE, LAST_UPDATED) VALUES (val.ID, val.CODE, v=
al.LAST_UPDATED)";<br></div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 try (Prep=
aredStatement ps =3D connection.prepareStatement(sql)) {<br></div><div>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ps.setLong(1, id);<br></div><div>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ps.setString(2, code);<br></div><div=
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ps.setString(3, dateFormatter.fo=
rmat(createdDate));<br></div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
assertThat(ps.executeUpdate()).isEqualTo(1);<br></div><div>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 }<br></div><div>=C2=A0 =C2=A0 }<br></div></div><div><br></div=
><div>Please see the failing test case at=C2=A0<a href=3D"https://gist.gith=
ub.com/uklance/14fd8c27e34ffddbe504109009e4dcc3" style=3D"font-family:Verda=
na,"sans-serif"" target=3D"_blank">https://gist.github.com/uklanc=
e/14fd8c27e34ffddbe504109009e4dcc3</a>=C2=A0which throws the following exce=
ption<br></div><div><div><br></div><div><p><span style=3D"font-family:Verda=
na,"sans-serif"">Caused by: org.hsqldb.HsqlException: data except=
ion: invalid datetime format<u></u><u></u></span><br></p><p><span 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.error.Error.error(Unknown Source)<u>=
</u><u></u></span><br></p><p><span 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.error.Error.error(Unknown Source)<u></u><u></u></span><br></p><p><sp=
an 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.types.DateTimeType.conve=
rtToDatetimeSpecial(Unknown Source)<u></u><u></u></span><br></p><p><span st=
yle=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.types.DateTimeType.convertToTy=
pe(Unknown Source)<u></u><u></u></span><br></p><p><span 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.ExpressionOp.getValue(Unknown Source)<u></u><=
u></u></span><br></p><p><span 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.StatementDML.getInsertData(Unknown Source)<u></u><u></u></span><br></p><p=
><span 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.executeM=
ergeStatement(Unknown Source)<u></u><u></u></span><br></p><p><span 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 Sourc=
e)<u></u><u></u></span><br></p><p><span 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.StatementDMQL.execute(Unknown Source)<u></u><u></u></span><br><=
/p><p><span 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.execute=
CompiledStatement(Unknown Source)<u></u><u></u></span><br></p><p><span styl=
e=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.execute(Unknown Source=
)<u></u><u></u></span><br></p><p><span style=3D"font-family:Verdana,"s=
ans-serif"">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ...=
71 more</span><br></p></div></div></div></div></div><div><br></div><div>__=
_____________________________________________<br></div><div>Hsqldb-user mai=
ling list<br></div><div><a href=3D"mailto:[email protected]=
" target=3D"_blank">[email protected]</a><br></div><div><a =
href=3D"https://lists.sourceforge.net/lists/listinfo/hsqldb-user" target=3D=
"_blank">https://lists.sourceforge.net/lists/listinfo/hsqldb-user</a><br></=
div><div><br></div></blockquote><div style=3D"font-family:Arial"><br></div>=
</div>_______________________________________________<br>
Hsqldb-user mailing list<br>
<a href=3D"mailto:[email protected]" target=3D"_blank">Hsql=
[email protected]</a><br>
<a href=3D"https://lists.sourceforge.net/lists/listinfo/hsqldb-user" rel=3D=
"noreferrer" target=3D"_blank">https://lists.sourceforge.net/lists/listinfo=
/hsqldb-user</a><br>
</div></blockquote></div>
--000000000000db5ee105f6518828--
--===============4582045902313740525==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
--===============4582045902313740525==
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
--===============4582045902313740525==--