B4J Question [SOLVED]ExecNonQuery2 Error ?

[incendio]

Hi guys,

I have this code

    InsQry.ExecNonQuery2("insert into t_in_ot1 (id,id_trs_typ,id_cst_sppl,trs_dt,dscp,inp_by) values(?,?,?,?,?,?)", _
                Array As Object(IdInOt1,42,459,"11/30/2017","this is a test","abc"))

Raised an error "illegal Argument exception"

Change the codes to this

Private Str As StringBuilder
Str.Initialize

IdCnt = 459
Dt = "11/30/2017"
InpBy = "abc"
Str.Append("insert into t_in_ot1 (id,id_trs_typ,id_cst_sppl,trs_dt,dscp,inp_by) ")
Str.Append("values(" & IdInOt1 & ",42," & IdCnt & ",'" & Dt & "','This is a test','" & InpBy & "')" )
Log(Str)
   
InsQry.ExecNonQuery(Str)

Runs OK.

Where did I go wrong?

 

[PCastagnetti]

I think you have to pass an array of strings instead of object

Array As String(....

 

[incendio]

No, it should be Array of Object, not all parameters are string.

 

[incendio]

No.

 

[Erel]

Can you post the full error message from the logs?

 

[incendio]

That was the full err mesage, only 1 line.

It was running from a jar file.

I will try to run via B4J IDE, to find out if there are any others err message.

 

[Erel]

You can also run the jar from the command line with java -jar <your jar> to see the logs.

 

[incendio]

Yes, i have run the jar via command line, and that is the only error message appeared.

 

[Erel]

Are you catching unhandled exceptions? Can you post a screenshot of the logs?

 

[OliverA]

What database backend are you using?
Just for kicks, try this (i'm just adding single quotes around your date. I'm just guessing here since I don't know the database backend):

InsQry.ExecNonQuery2("insert into t_in_ot1 (id,id_trs_typ,id_cst_sppl,trs_dt,dscp,inp_by) values(?,?,?,?,?,?)", _
                Array As Object(IdInOt1,42,459,"'11/30/2017'","this is a test","abc"))

Update: Reason for my guess (https://dev.mysql.com/doc/refman/5.7/en/date-and-time-literals.html)

 

[incendio]

What database backend are you using?
Just for kicks, try this (i'm just adding single quotes around your date. I'm just guessing here since I don't know the database backend):

InsQry.ExecNonQuery2("insert into t_in_ot1 (id,id_trs_typ,id_cst_sppl,trs_dt,dscp,inp_by) values(?,?,?,?,?,?)", _
                Array As Object(IdInOt1,42,459,"'11/30/2017'","this is a test","abc"))

Update: Reason for my guess (https://dev.mysql.com/doc/refman/5.7/en/date-and-time-literals.html)

No avail, I am using Firebird Database.

 

[incendio]

Here are the complete error log

java.sql.SQLException: An SQLException was provoked by the following failure: java.lang.IllegalArgumentException
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:77)
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:74)
at com.mchange.v2.c3p0.impl.NewPooledConnection.handleThrowable(NewPooledConnection.java:505)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setObject(NewProxyPreparedStatement.java:892)
at anywheresoftware.b4j.objects.SQL.ExecNonQuery2(SQL.java:198)
at b4j.example.main._button1_action(main.java:74)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:90)
at anywheresoftware.b4a.BA$1.run(BA.java:215)
at com.sun.javafx.application.PlatformImpl.lambda$null$173(PlatformImpl.java:295)
at java.security.AccessController.doPrivileged(Native Method)
at com.sun.javafx.application.PlatformImpl.lambda$runLater$174(PlatformImpl.java:294)
at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at com.sun.glass.ui.win.WinApplication.lambda$null$148(WinApplication.java:191)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.IllegalArgumentException
at java.sql.Date.valueOf(Date.java:143)
at org.firebirdsql.jdbc.field.FBDateField.setString(FBDateField.java:72)
at org.firebirdsql.jdbc.field.FBField.setObject(FBField.java:661)
at org.firebirdsql.jdbc.AbstractPreparedStatement.setObject(AbstractPreparedStatement.java:393)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setObject(NewProxyPreparedStatement.java:878)
... 15 more

 

[Daestrum]

Have you tried entering the date as 2017/11/30 in your example code as there's lots of discussions re java.sql.Date.valueOf not accepting dates
in format other than yyyy/mm/dd.

 

[incendio]

2017/11/30 format will be rejected by Firebird Database.

 

[OliverA]

Just for testing purposes, remove the date column from your insert statement.

 

[incendio]

Have tried that & it was a success.

Seem that ExecnonQuery2 unable to handle date field.

 

[incendio]

Tried change date format to : 2017-11-30 and it was a success.

Seem that ExecnonQuery2 for date field need sqlite date format & luckily it was also accepted by Firebird database.

 

[OliverA]

Seem that ExecnonQuery2 unable to handle date field.

Just for clarification, ExecNonQuery2 just uses JDBC's setObject method to pass values to the prepared statement (see https://github.com/AnywhereSoftware...rc/anywheresoftware/b4j/objects/SQL.java#L198) instead of the finer grained set methods that include the type in the name (https://docs.oracle.com/javase/8/docs/api/java/sql/PreparedStatement.html). So in ExecnonQuery2, the type of variable used (int, long, string, etc.) determines what finer grained method the setObject method uses. In your case, since you are passing a string, setObject uses setString instead of setDate. It is then up to the JDBC driver implementation on how to handle this in case of dates. Each JDBC may implement this a little different (so what works here for Firebird may or may not work for another JDBC driver). So, some experimenting will be necessary (as you did in your case).

PS: Even if the SQL implementation of B4A/J would expose the setDate method, it would really not help much unless the implementation also provides for a Date object and a Calendar object (see https://docs.oracle.com/javase/8/do...setDate-int-java.sql.Date-java.util.Calendar-) and any appropriate methods to set/get them.

 

[incendio]

On ExecnonQuery, I think the value "11/30/17" pass as String to JDBC driver and app runs OK.

But on ExecnonQuery2, not sure pass as what type, I think with ExecnonQuery2, B4J tried to converts it as a date which will fail.

 

[Erel]

B4J tried to converts it as a date which will fail.

The jSQL library just passes the parameters to the underlying JDBC driver.