Re: Fwd: PreparedStatement.setObject(...) fails for OffsetDateTime when used in a merge statement
Fred Toussi via Hsqldb-user <[email protected]> Tue, 07 Mar 2023 18:40:35 +0000
| Newsgroups | gmane.comp.java.hsqldb.user |
|---|---|
| Message-ID | <[email protected]> |
--===============7109787489871821934==
Content-Type: multipart/alternative;
boundary=443cb71c2bdc4b268451445e5bec45b7
--443cb71c2bdc4b268451445e5bec45b7
Content-Type: text/plain
I am aware that you don't need a cast for INSERT or UPDATE. You do need one for MERGE. The reason is it is not generally possible to determine the type of the stand-alone ? variable in a MERGE statement. The CAST gives it the intended type. This issue currently affects the data time types and will be fixed in the next release. Even after the fix, it is more efficient to use CASTs in MERGE statements, which avoids internal conversion to VARCHAR and back to the required type.
On Tue, Mar 7, 2023, at 16:02, Lance Java via Hsqldb-user wrote:
> 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
>
> _______________________________________________
> Hsqldb-user mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>
--443cb71c2bdc4b268451445e5bec45b7
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}
p.MsoNormal,p.MsoNoSpacing{margin:0}</style></head><body><div style=3D"f=
ont-family:Arial;">I am aware that you don't need a cast for INSERT or U=
PDATE. You do need one for MERGE. The reason is it is not generally poss=
ible to determine the type of the stand-alone ? variable in a MERGE stat=
ement. The CAST gives it the intended type. This issue currently affects=
the data time types and will be fixed in the next release. Even after t=
he fix, it is more efficient to use CASTs in MERGE statements, which avo=
ids internal conversion to VARCHAR and back to the required type.<br></d=
iv><div style=3D"font-family:Arial;"><br></div><div style=3D"font-family=
:Arial;"><br></div><div>On Tue, Mar 7, 2023, at 16:02, Lance Java via Hs=
qldb-user wrote:<br></div><blockquote type=3D"cite" id=3D"qt" style=3D""=
><div dir=3D"ltr"><div>Please see the failng test case at <a href=3D"htt=
ps://gist.github.com/uklance/14fd8c27e34ffddbe504109009e4dcc3">https://g=
ist.github.com/uklance/14fd8c27e34ffddbe504109009e4dcc3</a> which shows =
the column type is TIMESTAMP WITH TIME ZONE<br></div><div><br></div=
><div><div> @BeforeEach<br></div><div> public =
void beforeEach() throws Exception {<br></div><div> =
connection =3D DriverManager.getConnection("jdbc:hsqldb:mem:test;=
sql.syntax_ora=3Dtrue", "test", "test");<br></div><div> &nb=
sp; connection.createStatement().execute(<br></div><div> &n=
bsp; "CREATE TABLE SAMPLE (\n"=
+<br></div><div> =
" ID NUMERIC(12,0) PRIMARY KEY,\n" +<br></div><div> =
" CODE VARCHAR2(50)," =
+<br></div><div> =
" LAST_UPDATED TIMESTAMP WITH TIME ZONE,\n" +<br></div><div> =
; ")"<br></div><div>&nb=
sp; );<br></div><div> }<br></div></div=
><div><br></div><div>> <span class=3D"font" style=3D"font-family=
:Arial;">You can also use a CAST to the intended SQL type for the parame=
ter</span><br></div><div><span class=3D"font" style=3D"font-family:Arial=
;">The failing test case shows that PreparedStatement.setObject(...=
) works for OffsetDateTime for INSERT and UPDATE statements. Why is this=
only failing for MERGE?</span><br></div></div><div><br></div><div class=
=3D"qt-gmail_quote"><div dir=3D"ltr" class=3D"qt-gmail_attr">On Tue, 7 M=
ar 2023 at 15:51, Fred Toussi via Hsqldb-user <<a href=3D"mailto:hsql=
[email protected]">[email protected]</a>>=
wrote:<br></div><blockquote class=3D"qt-gmail_quote" style=3D"margin-to=
p:0px;margin-right:0px;margin-bottom:0px;margin-left:0.8ex;border-left-c=
olor:rgb(204, 204, 204);border-left-style:solid;border-left-width:1px;pa=
dding-left:1ex;"><div class=3D"qt-msg773025684330080279"><div><u></u><br=
></div><div><div style=3D"font-family:Arial;">You can also use a CAST to=
the intended SQL type for the parameter. Assuming you want TIMESTAMP WI=
TH TIME ZONE as the type:<br></div><div style=3D"font-family:Arial;"><br=
></div><blockquote type=3D"cite" id=3D"qt-m_773025684330080279qt"><div d=
ir=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 TI=
ME ZONE) AS LAST_UPDATED FROM DUAL) val " +<br></div></div></div></div><=
/div></blockquote><div style=3D"font-family:Arial;"><br></div><div>On Tu=
e, Mar 7, 2023, at 15:11, Lance Java via Hsqldb-user wrote:<br></div><bl=
ockquote type=3D"cite" id=3D"qt-m_773025684330080279qt"><div dir=3D"ltr"=
><div><div dir=3D"ltr">HSQLDB seems to fail for PreparedStatement.setObj=
ect(...) for OffsetDateTime when used in a merge statement.<br></div><di=
v dir=3D"ltr"><div><br></div><div>This merge logic will fail<br></div><d=
iv><br></div><div><div> private void merge2Sample(long id, =
String code, OffsetDateTime createdDate) throws SQLException {<br>=
</div><div> String sql =3D<br></div><div>&nbs=
p; "MERGE INTO SAMPLE t=
" +<br></div><div> &nbs=
p; "USING (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.CO=
DE, t.LAST_UPDATED =3D val.LAST_UPDATED " +<br></div><div> =
"WHEN NOT MATCHED THEN INSERT =
(ID, CODE, LAST_UPDATED) VALUES (val.ID, val.CODE, val.LAST_UPDATED)";<b=
r></div><div> try (PreparedStatement ps =3D c=
onnection.prepareStatement(sql)) {<br></div><div> &n=
bsp; ps.setLong(1, id);<br></div><div> =
ps.setString(2, code);<br></div><div> =
ps.setObject(3, createdDate);<br></div><div=
> assertThat(ps.executeUpdate()=
).isEqualTo(1);<br></div><div> }<br></div><di=
v> }<br></div></div><div><br></div><div>As a workaround I c=
an call PreparedStatement.setString(...) with a string formatted using a=
DateTimeFormatter but I'd prefer not to<br></div><div><br></div><div><d=
iv> private void merge1Sample(long id, String code, OffsetD=
ateTime createdDate) throws SQLException {<br></div><div> &n=
bsp; DateTimeFormatter dateFormatter =3D DateTimeFormatter=
.ofPattern("yyyy-MM-dd HH:mm:ss.SSSxxxxx");<br></div><div> =
String sql =3D<br></div><div> &=
nbsp; "MERGE INTO SAMPLE t " +<br></div><div> =
"USING (SELECT ? AS ID=
, ? AS CODE, ? AS LAST_UPDATED FROM DUAL) val " +<br></div><div> &=
nbsp; "ON (t.ID =3D val.ID) " =
+<br></div><div> =
"WHEN MATCHED THEN UPDATE SET t.CODE =3D val.CODE, t.LAST_UPDATED =3D va=
l.LAST_UPDATED " +<br></div><div> &nbs=
p; "WHEN NOT MATCHED THEN INSERT (ID, CODE, LAST_UPDATED) =
VALUES (val.ID, val.CODE, val.LAST_UPDATED)";<br></div><div>  =
; try (PreparedStatement ps =3D connection.prepareStatemen=
t(sql)) {<br></div><div> ps.set=
Long(1, id);<br></div><div> ps.=
setString(2, code);<br></div><div> &nb=
sp; ps.setString(3, dateFormatter.format(createdDate));<br></div><div>&n=
bsp; assertThat(ps.executeUpdate()).i=
sEqualTo(1);<br></div><div> }<br></div><div>&=
nbsp; }<br></div></div><div><br></div><div>Please see the failing=
test case at <a href=3D"https://gist.github.com/uklance/14fd8c27e3=
4ffddbe504109009e4dcc3" style=3D"font-family:Verdana, "sans-serif&q=
uot;;" target=3D"_blank">https://gist.github.com/uklance/14fd8c27e34ffdd=
be504109009e4dcc3</a> which throws the following exception<br></div=
><div><div><br></div><div><p><span class=3D"font" style=3D"font-family:V=
erdana, "sans-serif";">Caused by: org.hsqldb.HsqlException: da=
ta exception: invalid datetime format<u></u><u></u></span><br></p><p><sp=
an class=3D"font" style=3D"font-family:Verdana, "sans-serif";"=
> at org.hsqldb.er=
ror.Error.error(Unknown Source)<u></u><u></u></span><br></p><p><span cla=
ss=3D"font" style=3D"font-family:Verdana, "sans-serif";"> =
; at org.hsqldb.error.Er=
ror.error(Unknown Source)<u></u><u></u></span><br></p><p><span class=3D"=
font" style=3D"font-family:Verdana, "sans-serif";">  =
; at org.hsqldb.types.DateTime=
Type.convertToDatetimeSpecial(Unknown Source)<u></u><u></u></span><br></=
p><p><span class=3D"font" style=3D"font-family:Verdana, "sans-serif=
";"> at org.h=
sqldb.types.DateTimeType.convertToType(Unknown Source)<u></u><u></u></sp=
an><br></p><p><span class=3D"font" style=3D"font-family:Verdana, "s=
ans-serif";"> =
at org.hsqldb.ExpressionOp.getValue(Unknown Source)<u></u><u></u></span=
><br></p><p><span class=3D"font" style=3D"font-family:Verdana, "san=
s-serif";"> a=
t org.hsqldb.StatementDML.getInsertData(Unknown Source)<u></u><u></u></s=
pan><br></p><p><span class=3D"font" style=3D"font-family:Verdana, "=
sans-serif";">  =
; at org.hsqldb.StatementDML.executeMergeStatement(Unknown Source)<u></u=
><u></u></span><br></p><p><span class=3D"font" style=3D"font-family:Verd=
ana, "sans-serif";"> =
at org.hsqldb.StatementDML.getResult(Unknown Source)<u></u>=
<u></u></span><br></p><p><span class=3D"font" style=3D"font-family:Verda=
na, "sans-serif";"> &=
nbsp; at org.hsqldb.StatementDMQL.execute(Unknown Source)<u></u><u=
></u></span><br></p><p><span class=3D"font" style=3D"font-family:Verdana=
, "sans-serif";"> &nb=
sp; at org.hsqldb.Session.executeCompiledStatement(Unknown Source)=
<u></u><u></u></span><br></p><p><span class=3D"font" style=3D"font-famil=
y:Verdana, "sans-serif";"> =
at org.hsqldb.Session.execute(Unknown Source)<u></u><=
u></u></span><br></p><p><span class=3D"font" style=3D"font-family:Verdan=
a, "sans-serif";"> &n=
bsp; ... 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:Hsqldb-use=
[email protected]" target=3D"_blank">[email protected]=
.net</a><br></div><div><a href=3D"https://lists.sourceforge.net/lists/li=
stinfo/hsqldb-user" target=3D"_blank">https://lists.sourceforge.net/list=
s/listinfo/hsqldb-user</a><br></div><div><br></div></blockquote><div sty=
le=3D"font-family:Arial;"><br></div></div><div>_________________________=
______________________<br></div><div>Hsqldb-user mailing list<br></div><=
div><a href=3D"mailto:[email protected]" target=3D"_blan=
k">[email protected]</a><br></div><div><a href=3D"https:=
//lists.sourceforge.net/lists/listinfo/hsqldb-user" rel=3D"noreferrer" t=
arget=3D"_blank">https://lists.sourceforge.net/lists/listinfo/hsqldb-use=
r</a><br></div></div></blockquote></div><div><br></div><div>____________=
___________________________________<br></div><div>Hsqldb-user mailing li=
st<br></div><div><a href=3D"mailto:[email protected]">Hs=
[email protected]</a><br></div><div><a href=3D"https://lis=
ts.sourceforge.net/lists/listinfo/hsqldb-user">https://lists.sourceforge=
.net/lists/listinfo/hsqldb-user</a><br></div><div><br></div></blockquote=
><div style=3D"font-family:Arial;"><br></div></body></html>
--443cb71c2bdc4b268451445e5bec45b7--
--===============7109787489871821934==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
--===============7109787489871821934==
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
--===============7109787489871821934==--