I need to save, time triggered, the value of an OPC UA Variable in a MSSQL Table, I want to use the query object made available by the editor. In query window i put this SQL Statement:
INSERT INTO [dbo].[TestTab] (Intero) VALUES ({@Campetella.ns=2;s=Active_Production\OK_Parts})
I use in this case the Placeholder. for OPC UA variable.
It does not work, i receive this error in out.log
ERROR 2024-02-18 15:18:38.135 UTC de.indisystems.quickhmi.server.c.a.b.a.a:242 - Error converting parameter value for database query. Try as string.
com.microsoft.sqlserver.jdbc.SQLServerException: L'indice 1 non rientra nell'intervallo consentito.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) ~[?:?]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setterGetParam(SQLServerPreparedStatement.java:1126) ~[?:?]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setValue(SQLServerPreparedStatement.java:1140) ~[?:?]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setInt(SQLServerPreparedStatement.java:1422) ~[?:?]
at de.indisystems.quickhmi.server.c.a.b.a.a.c(SourceFile:191) [qhmi_server.jar:10.6.3]
at de.indisystems.quickhmi.server.c.a.b.a.a$a.run(SourceFile:374) [qhmi_server.jar:10.6.3]
at java.lang.Thread.run(Thread.java:835) [?:?]
ERROR 2024-02-18 15:18:38.140 UTC de.indisystems.quickhmi.server.c.a.b.a.a:343 - Index 1 is out of range.
com.microsoft.sqlserver.jdbc.SQLServerException: Index 1 is out of range.
Can you help me tanks in advance.
It looks like we have a small error in our documentation. Placeholders in database queries should not be enclosed in curly brackets.
Could you please try the statement as following and see if it works?
INSERT INTO [dbo].[TestTab] (Intero) VALUES (@Campetella.ns=2;s=Active_Production\OK_Parts)
I Tried to use SQL Statement without curly brakets but with no success. I use this SQL Statement:
INSERT INTO [dbo].[TestTab] (Intero) VALUES (@Campetella.ns=2;s=Active_Production\OK_Parts)
and in log.out was written this error:
ERROR 2024-03-01 17:07:45.008 UTC de.indisystems.quickhmi.server.c.a.b.a.a:343 - L'indice 1 non rientra nell'intervallo consentito.
com.microsoft.sqlserver.jdbc.SQLServerException: L'indice 1 non rientra nell'intervallo consentito.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) ~[?:?]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setterGetParam(SQLServerPreparedStatement.java:1126) ~[?:?]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setValue(SQLServerPreparedStatement.java:1140) ~[?:?]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setString(SQLServerPreparedStatement.java:1685) ~[?:?]
at de.indisystems.quickhmi.server.c.a.b.a.a.c(SourceFile:243) [qhmi_server.jar:10.6.3]
at de.indisystems.quickhmi.server.c.a.b.a.a$a.run(SourceFile:374) [qhmi_server.jar:10.6.3]
at java.lang.Thread.run(Thread.java:835) [?:?]
Only in this situation, with a constant, I succeeded in inserting row in the MSSQL Table.
INSERT INTO [dbo].[TestTab] (Intero) VALUES (22)
It seems that the backslash in your variable name is causing problems.
When replacing variable names in SQL with SQL placeholders (?), the backslash in the name is treated as a escape character. We will be able to solve the problem with one of the next updates.
Until then, the workaround is to remove the backslashes from the variable names.
