Re: Fwd: PreparedStatement.setObject(...) fails for OffsetDateTime when used in a merge statement
Fred Toussi via Hsqldb-user <[email protected]> Tue, 07 Mar 2023 15:36:54 +0000
| Newsgroups | gmane.comp.java.hsqldb.user |
|---|---|
| Message-ID | <[email protected]> |
--===============4946822765296022926==
Content-Type: multipart/alternative;
boundary=7c2ece141aff4dfc86904d6f2eda2db5
--7c2ece141aff4dfc86904d6f2eda2db5
Content-Type: text/plain
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
>
--7c2ece141aff4dfc86904d6f2eda2db5
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE html><html><head><title></title><style type=3D"text/css">p.Mso=
Normal,p.MsoNoSpacing{margin:0}</style></head><body><div style=3D"font-f=
amily:Arial;">You can also use a CAST to the intended SQL type for the p=
arameter. Assuming you want TIMESTAMP WITH TIME ZONE as the type:<br></d=
iv><div style=3D"font-family:Arial;"><br></div><blockquote type=3D"cite"=
id=3D"qt" style=3D""><div dir=3D"ltr"><div class=3D"qt-gmail_quote"><di=
v dir=3D"ltr"><div><div>"MERGE INTO SAMPLE t " +<br></div><div>"USING (S=
ELECT ? 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"ci=
te" id=3D"qt" style=3D""><div dir=3D"ltr"><div class=3D"qt-gmail_quote">=
<div dir=3D"ltr" class=3D"qt-gmail_attr">HSQLDB seems to fail for Prepar=
edStatement.setObject(...) for OffsetDateTime when used in a merge state=
ment.<br></div><div dir=3D"ltr"><div><br></div><div>This merge logic wil=
l fail<br></div><div><br></div><div><div> private void merg=
e2Sample(long id, String code, OffsetDateTime createdDate) throws SQLExc=
eption {<br></div><div> String sql =3D<=
br></div><div> "M=
ERGE INTO SAMPLE t " +<br></div><div> =
"USING (SELECT ? AS ID, ? AS CODE, ? AS LAST_UPDATE=
D FROM DUAL) val " +<br></div><div> &n=
bsp; "ON (t.ID =3D val.ID) " +<br></div><div> =
"WHEN MATCHED THEN UPDATE SET=
t.CODE =3D val.CODE, t.LAST_UPDATED =3D val.LAST_UPDATED " +<br></div><=
div> "WHEN NOT MA=
TCHED THEN INSERT (ID, CODE, LAST_UPDATED) VALUES (val.ID, val.CODE, val=
.LAST_UPDATED)";<br></div><div> try (Prepared=
Statement ps =3D connection.prepareStatement(sql)) {<br></div><div> =
; ps.setLong(1, id);<br></div><div>&n=
bsp; ps.setString(2, code);<br></div>=
<div> ps.setObject(3, createdDa=
te);<br></div><div> assertThat(=
ps.executeUpdate()).isEqualTo(1);<br></div><div> &nb=
sp; }<br></div><div> }<br></div></div><div><br></div><div>A=
s a workaround I can call PreparedStatement.setString(...) with a string=
formatted using a DateTimeFormatter but I'd prefer not to<br></div><div=
><br></div><div><div> private void merge1Sample(long id, St=
ring code, OffsetDateTime createdDate) throws SQLException {<br></=
div><div> DateTimeFormatter dateFormatter =3D=
DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSSxxxxx");<br></div><=
div> String sql =3D<br></div><div> &nbs=
p; "MERGE INTO SAMPLE t " +<br=
></div><div> "USI=
NG (SELECT ? AS ID, ? AS CODE, ? AS LAST_UPDATED FROM DUAL) val " +<br><=
/div><div> "ON (t=
.ID =3D val.ID) " +<br></div><div> &nb=
sp; "WHEN MATCHED THEN UPDATE SET t.CODE =3D val.CODE, t.L=
AST_UPDATED =3D val.LAST_UPDATED " +<br></div><div> =
"WHEN NOT MATCHED THEN INSERT (ID, CO=
DE, LAST_UPDATED) VALUES (val.ID, val.CODE, val.LAST_UPDATED)";<br></div=
><div> try (PreparedStatement ps =3D connecti=
on.prepareStatement(sql)) {<br></div><div> &n=
bsp; ps.setLong(1, id);<br></div><div> =
ps.setString(2, code);<br></div><div> =
ps.setString(3, dateFormatter.format(createdDate))=
;<br></div><div> assertThat(ps.=
executeUpdate()).isEqualTo(1);<br></div><div> =
}<br></div><div> }<br></div></div><div><br></div><div>Plea=
se see the failing test case at <a href=3D"https://gist.github.com/=
uklance/14fd8c27e34ffddbe504109009e4dcc3" style=3D"font-family:Verdana, =
"sans-serif";" target=3D"_blank">https://gist.github.com/uklan=
ce/14fd8c27e34ffddbe504109009e4dcc3</a> which throws the following =
exception<br></div><div><div><br></div><div><p class=3D"qt-MsoNormal"><s=
pan class=3D"font" style=3D"font-family:Verdana, "sans-serif";=
">Caused by: org.hsqldb.HsqlException: data exception: invalid datetime =
format<u></u><u></u></span><br></p><p class=3D"qt-MsoNormal"><span class=
=3D"font" style=3D"font-family:Verdana, "sans-serif";"> &=
nbsp; at org.hsqldb.error.Erro=
r.error(Unknown Source)<u></u><u></u></span><br></p><p class=3D"qt-MsoNo=
rmal"><span class=3D"font" style=3D"font-family:Verdana, "sans-seri=
f";"> at org.=
hsqldb.error.Error.error(Unknown Source)<u></u><u></u></span><br></p><p =
class=3D"qt-MsoNormal"><span class=3D"font" style=3D"font-family:Verdana=
, "sans-serif";"> &nb=
sp; at org.hsqldb.types.DateTimeType.convertToDatetimeSpecial(Unkn=
own Source)<u></u><u></u></span><br></p><p class=3D"qt-MsoNormal"><span =
class=3D"font" style=3D"font-family:Verdana, "sans-serif";">&n=
bsp; at org.hsqldb.types=
.DateTimeType.convertToType(Unknown Source)<u></u><u></u></span><br></p>=
<p class=3D"qt-MsoNormal"><span class=3D"font" style=3D"font-family:Verd=
ana, "sans-serif";"> =
at org.hsqldb.ExpressionOp.getValue(Unknown Source)<u></u><=
u></u></span><br></p><p class=3D"qt-MsoNormal"><span class=3D"font" styl=
e=3D"font-family:Verdana, "sans-serif";"> &nb=
sp; at org.hsqldb.StatementDML.getInsertDa=
ta(Unknown Source)<u></u><u></u></span><br></p><p class=3D"qt-MsoNormal"=
><span class=3D"font" style=3D"font-family:Verdana, "sans-serif&quo=
t;;"> at org.hsqld=
b.StatementDML.executeMergeStatement(Unknown Source)<u></u><u></u></span=
><br></p><p class=3D"qt-MsoNormal"><span class=3D"font" style=3D"font-fa=
mily:Verdana, "sans-serif";"> &nb=
sp; at org.hsqldb.StatementDML.getResult(Unknown Sourc=
e)<u></u><u></u></span><br></p><p class=3D"qt-MsoNormal"><span class=3D"=
font" style=3D"font-family:Verdana, "sans-serif";">  =
; at org.hsqldb.StatementDMQL.=
execute(Unknown Source)<u></u><u></u></span><br></p><p class=3D"qt-MsoNo=
rmal"><span class=3D"font" style=3D"font-family:Verdana, "sans-seri=
f";"> at org.=
hsqldb.Session.executeCompiledStatement(Unknown Source)<u></u><u></u></s=
pan><br></p><p class=3D"qt-MsoNormal"><span class=3D"font" style=3D"font=
-family:Verdana, "sans-serif";"> =
at org.hsqldb.Session.execute(Unknown Source)<u=
></u><u></u></span><br></p><p class=3D"qt-MsoNormal"><span class=3D"font=
" style=3D"font-family:Verdana, "sans-serif";"> &nb=
sp; ... 71 more</span><br></p></div>=
</div></div></div></div><div><br></div><div>____________________________=
___________________<br></div><div>Hsqldb-user mailing list<br></div><div=
><a href=3D"mailto:[email protected]">Hsqldb-user@lists.=
sourceforge.net</a><br></div><div><a href=3D"https://lists.sourceforge.n=
et/lists/listinfo/hsqldb-user">https://lists.sourceforge.net/lists/listi=
nfo/hsqldb-user</a><br></div><div><br></div></blockquote><div style=3D"f=
ont-family:Arial;"><br></div></body></html>
--7c2ece141aff4dfc86904d6f2eda2db5--
--===============4946822765296022926==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
--===============4946822765296022926==
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
--===============4946822765296022926==--