B4J Question cursor getstring2 error if datetime filed is '0000-00-00 00:00:00' (mySQL)

[pmt]

I use B4J 5.9 , jSQ 1.50 and database is MySQL

I have a table name table1 with 3 fields
-id is char(5)
-check_date is datetime
-check_str is varchar(255)

Data test 1 record:
-id = 1
-check_date = 0000-00-00 00:00:00
-check_str = 0000-00-00 00:00:00


I try 3 steps
1.

cmd = "select id, check_date , check_str from table1 where id = ?"
id = 1
res= DBUtils.ExecuteMap(con,cmd,Array As String(id))

I get error "java.lang.RuntimeException: java.lang.NumberFormatException: empty String".

I check log in DBUtils.ExecuteMap ,error occur at "check_date" field.

------------------------------------------------------------------------------------------------
2.

cmd = "select  id , check_str from table1 where id = ?"
id = 1
res= DBUtils.ExecuteMap(con,cmd,Array As String(id))

No error , but I get id = 1 ,check_str is empty string

------------------------------------------------------------------------------------------------
3.

cmd = "select id, DATE_FORMAT(check_date,'%d-%m-%Y') as check_date1 , check_str from table1 where id = ?"
id = 1
res= DBUtils.ExecuteMap(con,cmd,Array As String(id))

No error , but I get id = 1 , check_date1 = 00-00-0000 ,check_str is empty string

------------------------------------------------------------------------------------------------

This is bug ?

Regards,
PMT

 

[Erel]

DBUtils is intended for working with SQLite databases.

Can you post the full error message from the logs?

 

[pmt]

DBUtils is intended for working with SQLite databases.

Can you post the full error message from the logs?

Yes , Here error log

ExecuteMap: SELECT id, check_date,check_str FROM table1 Where id = ?
DBUtils.ExecuteMap > cur.GetColumnName(i) >id
DBUtils.ExecuteMap > cur.GetColumnName(i) >check_date
(MySQLNonTransientConnectionException) com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Can't call rollback when autocommit=true
Command: AddPoint, took: 44ms, client=192.168.1.102
java.lang.RuntimeException: java.lang.NumberFormatException: empty String
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:119)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:77)
    at anywheresoftware.b4j.object.JServlet$Handle.run(JServlet.java:130)
    at anywheresoftware.b4j.object.JServlet.Handle(JServlet.java:110)
    at anywheresoftware.b4j.object.JServlet.doPost(JServlet.java:80)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
    at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:841)
    at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:535)
    at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:188)
    at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1595)
    at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:188)
    at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1253)
    at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:168)
    at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:473)
    at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1564)
    at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:166)
    at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1155)
    at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
    at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:126)
    at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)
    at org.eclipse.jetty.server.Server.handle(Server.java:564)
    at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:317)
    at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:251)
    at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:279)
    at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:110)
    at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:124)
    at org.eclipse.jetty.util.thread.Invocable.invokePreferred(Invocable.java:128)
    at org.eclipse.jetty.util.thread.Invocable$InvocableExecutor.invoke(Invocable.java:222)
    at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:294)
    at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.produce(EatWhatYouKill.java:126)
    at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:673)
    at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:591)
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.NumberFormatException: empty String
    at sun.misc.FloatingDecimal.readJavaFormatString(FloatingDecimal.java:1842)
    at sun.misc.FloatingDecimal.parseDouble(FloatingDecimal.java:110)
    at java.lang.Double.parseDouble(Double.java:538)
    at b4j.example.addpoint._handle(addpoint.java:390)
    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)
    ... 33 more

Regards,
pmt

 

[keirS]

This not a bug. Java cant represent a DATETIME of "0000-00-00 00:00:00" so it returns empty. You should add "zeroDateTimeBehavior=convertToNull" in your JDBC URL
then empty dates will automatically be converted to Nulls.

 

[pmt]

This not a bug. Java cant represent a DATETIME of "0000-00-00 00:00:00" so it returns empty. You should add "zeroDateTimeBehavior=convertToNull" in your JDBC URL
then empty dates will automatically be converted to Nulls.

Thank you I will try

 

[pmt]

This not a bug. Java cant represent a DATETIME of "0000-00-00 00:00:00" so it returns empty. You should add "zeroDateTimeBehavior=convertToNull" in your JDBC URL
then empty dates will automatically be converted to Nulls.

OK , Works.