B4J Question [SOLVED]ExecNonQuery2 Error ?

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

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

No.

Can you post the full error message from the logs?

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.

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

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

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

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):


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

OliverA said:

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):

B4X:

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)

Click to expand...

No avail, I am using Firebird Database.

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

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.

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

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

Have tried that & it was a success.

Seem that ExecnonQuery2 unable to handle date field.

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.

incendio said:

Seem that ExecnonQuery2 unable to handle date field.

Click to expand...

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.

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.

incendio said:

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

Click to expand...

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