Re: Fwd: PreparedStatement.setObject(...) fails for OffsetDateTime when used in a merge statement
"Fred Toussi" <[email protected]> Tue, 07 Mar 2023 18:37:31 +0000
| Newsgroups | gmane.comp.java.hsqldb.user |
|---|---|
| Message-ID | <[email protected]> |
--===============2047269058057901550==
Content-Type: multipart/alternative;
boundary=6ff778baf1db44bda1379c469f2dcfab
--6ff778baf1db44bda1379c469f2dcfab
Content-Type: text/plain
I am aware that you don't need a cast for INSERT or UPDATE and that 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
>
--6ff778baf1db44bda1379c469f2dcfab
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 and that you do need one for MERGE. The reason is it is not genera=
lly possible to determine the type of the stand-alone ? variable in a ME=
RGE 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, w=
hich avoids internal conversion to VARCHAR and back to the required type=
.<br></div><div style=3D"font-family:Arial;"><br></div><div>On Tue, Mar =
7, 2023, at 16:02, Lance Java via Hsqldb-user wrote:<br></div><blockquot=
e type=3D"cite" id=3D"qt" style=3D""><div dir=3D"ltr"><div>Please see th=
e failng test case at <a href=3D"https://gist.github.com/uklance/14fd8c2=
7e34ffddbe504109009e4dcc3">https://gist.github.com/uklance/14fd8c27e34ff=
ddbe504109009e4dcc3</a> which shows the column type is TIMESTAMP WI=
TH 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", "tes=
t");<br></div><div> connection.createStatemen=
t().execute(<br></div><div> &nb=
sp; "CREATE TABLE SAMPLE (\n" +<br></div><div>  =
; " ID NUMERIC(12,0) PRIMARY K=
EY,\n" +<br></div><div> =
" CODE VARCHAR2(50)," +<br></div><div> =
" LAST_UPDATED TIMESTAMP WITH=
TIME ZONE,\n" +<br></div><div> =
")"<br></div><div> );<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 parameter</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 INSER=
T 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 Mar 2023 at 15:51, Fred Toussi via Hs=
qldb-user <<a href=3D"mailto:[email protected]">hsqld=
[email protected]</a>> wrote:<br></div><blockquote class=3D=
"qt-gmail_quote" style=3D"margin-top:0px;margin-right:0px;margin-bottom:=
0px;margin-left:0.8ex;border-left-color:rgb(204, 204, 204);border-left-s=
tyle:solid;border-left-width:1px;padding-left:1ex;"><div class=3D"qt-msg=
773025684330080279"><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 parame=
ter. Assuming you want TIMESTAMP WITH TIME ZONE as the type:<br></div><d=
iv style=3D"font-family:Arial;"><br></div><blockquote type=3D"cite" id=3D=
"qt-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_UPDATED FROM DUAL) va=
l " +<br></div></div></div></div></div></blockquote><div style=3D"font-f=
amily:Arial;"><br></div><div>On Tue, Mar 7, 2023, at 15:11, Lance Java v=
ia Hsqldb-user wrote:<br></div><blockquote type=3D"cite" id=3D"qt-m_7730=
25684330080279qt"><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>This=
merge logic will fail<br></div><div><br></div><div><div> p=
rivate void merge2Sample(long id, String code, OffsetDateTime createdDat=
e) throws SQLException {<br></div><div> =
String sql =3D<br></div><div> =
"MERGE INTO SAMPLE t " +<br></div><div>  =
; "USING (SELECT ? AS ID, ? AS CODE, =
? AS LAST_UPDATED FROM DUAL) val " +<br></div><div> =
"ON (t.ID =3D val.ID) " +<br></div><d=
iv> "WHEN MATCHED=
THEN UPDATE SET t.CODE =3D val.CODE, t.LAST_UPDATED =3D val.LAST_UPDATE=
D " +<br></div><div> &nb=
sp; "WHEN NOT MATCHED THEN INSERT (ID, CODE, LAST_UPDATED) VALUES (val.I=
D, val.CODE, val.LAST_UPDATED)";<br></div><div> &nbs=
p; try (PreparedStatement ps =3D connection.prepareStatement(sql)) {<br>=
</div><div> ps.setLong(1, id);<=
br></div><div> ps.setString(2, =
code);<br></div><div> ps.setObj=
ect(3, createdDate);<br></div><div> &n=
bsp; assertThat(ps.executeUpdate()).isEqualTo(1);<br></div><div> &=
nbsp; }<br></div><div> }<br></div></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<br></div><div><br></div><div><div> private void merge1Sa=
mple(long id, String code, OffsetDateTime createdDate) throws SQLExcepti=
on {<br></div><div> DateTimeFormatter d=
ateFormatter =3D DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSSxxx=
xx");<br></div><div> String sql =3D<br></div>=
<div> "MERGE INTO=
SAMPLE t " +<br></div><div> &n=
bsp; "USING (SELECT ? AS ID, ? AS CODE, ? AS LAST_UPDATED FROM DU=
AL) val " +<br></div><div> &nbs=
p; "ON (t.ID =3D val.ID) " +<br></div><div> &=
nbsp; "WHEN MATCHED THEN UPDATE SET t.CODE =3D=
val.CODE, 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_UPDAT=
ED)";<br></div><div> try (PreparedStatement p=
s =3D connection.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>Please see the failing test case at <a href=3D"https://gi=
st.github.com/uklance/14fd8c27e34ffddbe504109009e4dcc3" style=3D"font-fa=
mily:Verdana, "sans-serif";" target=3D"_blank">https://gist.gi=
thub.com/uklance/14fd8c27e34ffddbe504109009e4dcc3</a> which throws =
the following exception<br></div><div><div><br></div><div><p><span 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><span class=3D"font" style=3D"font-family:V=
erdana, "sans-serif";"> &nb=
sp; at org.hsqldb.error.Error.error(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.error.Error.error(Unknown Source)<u></u><u></u><=
/span><br></p><p><span class=3D"font" style=3D"font-family:Verdana, &quo=
t;sans-serif";"> &nb=
sp; at org.hsqldb.types.DateTimeType.convertToDatetimeSpecial(Unknown So=
urce)<u></u><u></u></span><br></p><p><span class=3D"font" style=3D"font-=
family:Verdana, "sans-serif";"> &=
nbsp; at org.hsqldb.types.DateTimeType.convertToType(U=
nknown Source)<u></u><u></u></span><br></p><p><span class=3D"font" style=
=3D"font-family:Verdana, "sans-serif";"> &nbs=
p; at org.hsqldb.ExpressionOp.getValue(Unk=
nown Source)<u></u><u></u></span><br></p><p><span class=3D"font" style=3D=
"font-family:Verdana, "sans-serif";"> &=
nbsp; at org.hsqldb.StatementDML.getInsertData(U=
nknown Source)<u></u><u></u></span><br></p><p><span class=3D"font" style=
=3D"font-family:Verdana, "sans-serif";"> &nbs=
p; at org.hsqldb.StatementDML.executeMerge=
Statement(Unknown Source)<u></u><u></u></span><br></p><p><span class=3D"=
font" style=3D"font-family:Verdana, "sans-serif";">  =
; at org.hsqldb.StatementDML.g=
etResult(Unknown Source)<u></u><u></u></span><br></p><p><span class=3D"f=
ont" style=3D"font-family:Verdana, "sans-serif";"> =
at org.hsqldb.StatementDMQL.e=
xecute(Unknown Source)<u></u><u></u></span><br></p><p><span class=3D"fon=
t" style=3D"font-family:Verdana, "sans-serif";"> &n=
bsp; at org.hsqldb.Session.executeCo=
mpiledStatement(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.Session.=
execute(Unknown Source)<u></u><u></u></span><br></p><p><span class=3D"fo=
nt" style=3D"font-family:Verdana, "sans-serif";"> &=
nbsp; ... 71 more</span><br></p></di=
v></div></div></div></div><div><br></div><div>__________________________=
_____________________<br></div><div>Hsqldb-user mailing list<br></div><d=
iv><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">htt=
ps://lists.sourceforge.net/lists/listinfo/hsqldb-user</a><br></div><div>=
<br></div></blockquote><div style=3D"font-family:Arial;"><br></div></div=
><div>_______________________________________________<br></div><div>Hsql=
db-user mailing list<br></div><div><a href=3D"mailto:[email protected]=
ourceforge.net" target=3D"_blank">[email protected]</a><=
br></div><div><a href=3D"https://lists.sourceforge.net/lists/listinfo/hs=
qldb-user" rel=3D"noreferrer" target=3D"_blank">https://lists.sourceforg=
e.net/lists/listinfo/hsqldb-user</a><br></div></div></blockquote></div><=
div><br></div><div>_______________________________________________<br></=
div><div>Hsqldb-user mailing list<br></div><div><a href=3D"mailto:Hsqldb=
[email protected]">[email protected]</a><br></=
div><div><a href=3D"https://lists.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>
--6ff778baf1db44bda1379c469f2dcfab--
--===============2047269058057901550==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
--===============2047269058057901550==
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
--===============2047269058057901550==--