Android Question RDC Time Data type unmatched Exception

[Bernard Harris]

When using RDC I am able to get the Date to update properly to our enterprise database but the Time will not update from the parameter passed.
Locally I am saving the date and time in separate string fields, because of the format difference between Android and DB2. The format on the enterprise database for date is yyyy-mm-dd and for time is 00.00.00. I have them saving locally okay but when I try and pass this into RDC as parameters it won't update the time and gives me a SQLException: Data type mismatch.: 09.00.00. Again I can do it successfully if I have a fixed variable in the built SQL statement but when it uses a parameter it will fail.

Here are my code statements:
RDC - sql.UpdateDeliveryTime=UPDATE SNEFIL.SHPHSTT SET DLVTIME = ? WHERE TKT# = ? AND STATUS = 'S'
Client -
cmd.Name = "UpdateDeliveryTime"
cmd.Parameters = Array As Object("09.00.00", 6008931724)
reqManager.ExecuteCommand(cmd, Null)

Do you have any suggestions on how to correct this? My only other alternative is to use the current time but that would be after the fact.

 

[Erel]

You should start a new thread in B4J forum for further questions related to jRDC2.

 

[Bernard Harris]

Okay, will do. Thanks!

 

[OliverA]

I can use a SQL statement and SET DLVTIME = '09.00.00' and it works fine. But when I try using the parameterized version it fails (SET DLVTIME = ?).

When you use an SQL statement such as

SET DLVTIME = '09.00.00'

, the SQL processor processes everything as a string and goes from there. When you parameterized expressions, then the underlying JDBC driver expects the proper object. So for a time column, the JDBC driver expects a time variable. Now if you would hand code the parameterized expression, you would use the setTime method (https://docs.oracle.com/javase/7/do...redStatement.html#setTime(int, java.sql.Time)) and pass it a variable declared as java.sql.Time. B4J's implementation of prepared statement uses the generic setObject method (https://docs.oracle.com/javase/7/do...atement.html#setObject(int, java.lang.Object)). The setObject method determines the type by the type of the parameter passed. If you pass an int, setObject calls setInt, for a float, setFloat, and for a string, setString. These set methods have to match the underlying table's column definition or you get a data type mismatch error message. For some column data types, the JDBC driver may accept string formats. These data types usually include the DATE and TIME type. Now 1) this is really up to the JDBC driver implementation and 2) it is up to that JDBC implementation on what to accept as a reasonable input format. This format does not have to comply with what the database would expect if you would just build the SQL statement completely out. Even though you must format the time as '09.00.00' in you SQL query, the JDBC implementation for DB2 expects 09:00:00 when passed as a parameter in string format.

 

[Bernard Harris]

Thanks OliverA, that makes sense. So how do I implement the setTime within B4A then, so I pass the proper type in?

 

[OliverA]

You should not have to, that's the kicker. The source of the DB2 JDBC driver indicates that it would properly handle a string time as long as it is formatted hh:mm:ss. Can you post the code that includes any variable declarations and the actual call to the prepared statement method? Which version of the JDBC driver for DB2 are you using?

 

[Bernard Harris]

Here is the code (copied from a previous post):
RDC config.properties setting - sql.UpdateDeliveryTime=UPDATE SNEFIL.SHPHSTT SET DLVTIME = ? WHERE TKT# = ? AND STATUS = 'S'
B4A test code -
cmd.Name = "UpdateDeliveryTime"
cmd.Parameters = Array As Object("09.00.00", 6008931724)
reqManager.ExecuteCommand(cmd, Null)

I tried changing it to "09:00:00" and got a different error, which is also stated in a previous post, I think.

 

[OliverA]

On a technicality, you should be using ExecuteBatch for any non-select SQL statements. The ExecuteCommand client method is translated on the server side to to the ExecQuery2 method of the SQL class. This in turn uses the ExecuteQuery method of JDBC's PreparedStatement class. Even though your not getting an exception, ExecuteBatch would be the proper method to use (see https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html).

 

[Bernard Harris]

Thanks for the tip! But that probably won't fix my time issue, right?

 

[OliverA]

Try it with using hi:mm:ss for the time format and post complete error messages, both client and server.

 

[OliverA]

Post version of Jdbc driver used and db2 version.

 

[Bernard Harris]

My jdbc driver is at version 9.4, latest, and the db2 version is and IBM i server at 7.3.

The error on the client is:
java.sql.SQLException: Internal driver error. : Unrecognized valueType 1
Error: Server Error

The error on the server is:
Dec 15, 2017 12:38:52 PM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
INFO: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 20000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 2sa4lq9spdme93haq010|3f0ee7cb, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.ibm.jtopenlite.database.jdbc.JDBCDriver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 2sa4lq9spdme93haq010|3f0ee7cb, idleConnectionTestPeriod -> 600, initialPoolSize -> 3, jdbcUrl -> jdbc:jtopenlite://10.110.25.146, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 1800, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 150, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
Dec 15, 2017 12:38:53 PM com.mchange.v2.c3p0.stmt.GooGooStatementCache checkinStatement
INFO: Problem with checked-in Statement, discarding.
com.ibm.jtopenlite.database.jdbc.NotImplementedException: Not implemented
at com.ibm.jtopenlite.database.jdbc.JDBCStatement.clearBatch(JDBCStatement.java:160)
at com.mchange.v2.c3p0.stmt.GooGooStatementCache.refreshStatement(GooGooStatementCache.java:627)
at com.mchange.v2.c3p0.stmt.GooGooStatementCache.checkinStatement(GooGooStatementCache.java:272)
at com.mchange.v2.c3p0.stmt.GooGooStatementCache.checkinAll(GooGooStatementCache.java:324)
at com.mchange.v2.c3p0.impl.NewPooledConnection.checkinAllCachedStatements(NewPooledConnection.java:759)
at com.mchange.v2.c3p0.impl.NewPooledConnection.markClosedProxyConnection(NewPooledConnection.java:389)
at com.mchange.v2.c3p0.impl.NewProxyConnection.close(NewProxyConnection.java:1225)
at anywheresoftware.b4a.remotedatabase.Servlet.doGet(Servlet.java:86)
at anywheresoftware.b4a.remotedatabase.Servlet.doPost(Servlet.java:52)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:538)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:478)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:937)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:406)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:871)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:117)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:110)
at org.eclipse.jetty.server.Server.handle(Server.java:346)
at org.eclipse.jetty.server.HttpConnection.handleRequest(HttpConnection.java:589)
at org.eclipse.jetty.server.HttpConnection$RequestHandler.content(HttpConnection.java:1065)
at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:823)
at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:220)
at org.eclipse.jetty.server.HttpConnection.handle(HttpConnection.java:411)
at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEndPoint.java:535)
at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEndPoint.java:40)
at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:529)
at java.lang.Thread.run(Thread.java:748)

java.sql.SQLException: Internal driver error. : Unrecognized valueType 1
at com.ibm.jtopenlite.database.jdbc.JDBCError.getSQLException(JDBCError.java:126)
at com.ibm.jtopenlite.database.jdbc.Column.getValueTimeAsString(Column.java:588)
at com.ibm.jtopenlite.database.jdbc.Column.convertToBytes(Column.java:1894)
at com.ibm.jtopenlite.database.jdbc.JDBCPreparedStatement.getExtendedParameterMarkerData(JDBCPreparedStatement.java:325)
at com.ibm.jtopenlite.database.jdbc.JDBCPreparedStatement.executeUpdate(JDBCPreparedStatement.java:364)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:147)
at anywheresoftware.b4a.remotedatabase.Servlet.executeBatch(Servlet.java:112)
at anywheresoftware.b4a.remotedatabase.Servlet.doGet(Servlet.java:80)
at anywheresoftware.b4a.remotedatabase.Servlet.doPost(Servlet.java:52)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:538)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:478)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:937)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:406)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:871)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:117)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:110)
at org.eclipse.jetty.server.Server.handle(Server.java:346)
at org.eclipse.jetty.server.HttpConnection.handleRequest(HttpConnection.java:589)
at org.eclipse.jetty.server.HttpConnection$RequestHandler.content(HttpConnection.java:1065)
at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:823)
at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:220)
at org.eclipse.jetty.server.HttpConnection.handle(HttpConnection.java:411)
at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEndPoint.java:535)
at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEndPoint.java:40)
at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:529)
at java.lang.Thread.run(Thread.java:748)

 

[OliverA]

Are you using db2jcc.jar or db2jcc4.jar for the jdbc driver?

 

[Bernard Harris]

Sorry, I am actually using jtopenlite.jar. It's IBMs open source android driver.

 

[OliverA]

Sorry, I am actually using jtopenlite.jar. It's IBMs open source android driver.

Yeah, noticed it in the error messages.

cmd.Name = "UpdateDeliveryTime"
cmd.Parameters = Array As Object("09.00.00", 6008931724)
reqManager.ExecuteCommand(cmd, Null)

Try

dim someDate as String = "09:00:00"
cmd.Name = "UpdateDeliveryTime"
cmd.Parameters = Array As Object(someDate, 6008931724)
reqManager.ExecuteCommand(cmd, Null)

 

[Bernard Harris]

Still the same error.

 

[OliverA]

Still the same error.

I found a flaw in the source of the JDBC driver! Someone forgot a break statement and therefore the Switch portion of the string to date conversion runs right into the default of the Switch statement which throws the "Unrecognized valueType" error (see
https://github.com/piguangming/jt40...ibm/jtopenlite/database/jdbc/Column.java#L731). I have no clue on how to report the error to the driver maintainers.

 

[Bernard Harris]

Wow! You the man! Do you think it would be better then to use the jt400.jar instead? I realized that since this is on the server I don't need the cut down version and can use the full driver. Unless that driver has the same issue.

 

[OliverA]

Wow! You the man! Do you think it would be better then to use the jt400.jar instead? I realized that since this is on the server I don't need the cut down version and can use the full driver. Unless that driver has the same issue.

All you can do is try and let us know how it went.

 

[Bernard Harris]

Okay, I will test that later and see. Thanks for your help and giving me time out of your busy day.

 

[Bernard Harris]

The full driver worked but I now had to reformat my date. It required the date string as "12/15/2017" and the time string as "09:00:00". Thanks again for the help! More of a driver issue than anything.
Now I just need to find out how to get jRDC2 working, but that's another post...