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]

It is a mistake to use RDC (v1). You should switch to jRDC2.

SQLException: Data type mismatch.: 09.00.00

Is this the full error message?

 

[Bernard Harris]

Since this is new I can switch to jRDC2. Do I just build the jRDC2 app on the server and run that then?

Also, the full error showing in the log is this:
java.sql.SQLException: Data type mismatch. : 09.00.00
Error: Server Error

The error on the web server is:

java.sql.SQLException: Data type mismatch. : 09.00.00
at com.ibm.jtopenlite.database.jdbc.JDBCError.getSQLException(JDBCError.java:126)
at com.ibm.jtopenlite.database.jdbc.Column.getValueTimeAsString(Column.java:605)
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)
Caused by: java.lang.IllegalArgumentException
at java.sql.Time.valueOf(Time.java:110)
at com.ibm.jtopenlite.database.jdbc.Column.getValueTimeAsString(Column.java:583)
... 26 more

 

[Bernard Harris]

So when using jRDC2 where do I install the jdbc driver for the B4J project to use?

 

[Erel]

Do I just build the jRDC2 app on the server and run that then?

See jRDC2 tutorial.

What is the type of DLVTIME? What is the meaning of 09:00:00? 9am today?

 

[Bernard Harris]

This column in the enterprise database table is type of TIME. And yes, the value of 09.00.00 is 9 am. It actually has a period between hh.mm.ss and not the colon. When the value displays it will display as colon but the data is saved as a period between each.

 

[Erel]

Try it with colons instead of dots. It will probably work.

 

[Bernard Harris]

I did try that, using colons, and then I tried it using the ticks but both failed. I am trying to get jRDC2 up and running now to see if that helps.

 

[Erel]

jRDC2 will not solve this issue.

However I checked java.sql.Time.valueOf source code and it expects colons. Did you get the same error message?

 

[Bernard Harris]

I did get a different error but I can't remember what that was. Trying now to use jRDC2 but having issues connecting. I can't see exactly where the jdbc driver should go from the tutorial.

 

[Erel]

I can't see exactly where the jdbc driver should go from the tutorial.

Should be copied to the additional libraries folder and added with #AdditionalJar.

Make sure to update the link. It should end with /rdc.

 

[Bernard Harris]

Well I've tried jRDC2, got my jdbc driver in the Additional folder, and I'm getting a Timeout error when I test the process. I'm not sure what I've done wrong but it doesn't appear to allow connection using jRDC2.

So I've reverted back to RDC because at least I could get this to work, barring the TIME data type.

The error I get when trying the 09:00:00 is this:
java.sql.SQLException: Internal driver error. : Unrecognized valueType 1

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.j

 

[Bernard Harris]

Erel - First - THANKS for your help so far! Second - how does jRDC run the web server itself? Is this installed along with jRDC or does it use something other than RDC used? Just trying to figure out why the timeout issue. I also update the config.properties in the jRDC sourcecode\Files directory so hopefully that was correct.

 

[OliverA]

java.sql.SQLException: Internal driver error. : Unrecognized valueType 1

This makes no sense. Looking at the JDBC source code (https://sourceforge.net/p/jt400/svn.../ibm/jtopenlite/database/jdbc/Column.java#l96), valueType 1 is a String. You are passing a string and so this message makes no sense. The previous one (for 09.00.00) makes sense since, as @Erel pointed out above, you need to pass "JDBC standard time" encoding which is hh:mm:ss.

how does jRDC run the web server itself?

The server used is jetty (https://www.eclipse.org/jetty/) and is embedded when you include the jServer library.

 

[incendio]

It is a mistake to use RDC (v1). You should switch to jRDC2.

Is there something I should aware since there are my projects that still use RDC.

 

[incendio]

I don't know what database you are using on the server.

But from my experience, date format should be in sqlite format, so perhaps, time also need in sqlite format which is HH:MM:SS.

 

[Bernard Harris]

This makes no sense. Looking at the JDBC source code (https://sourceforge.net/p/jt400/svn.../ibm/jtopenlite/database/jdbc/Column.java#l96), valueType 1 is a String. You are passing a string and so this message makes no sense. The previous one (for 09.00.00) makes sense since, as @Erel pointed out above, you need to pass "JDBC standard time" encoding which is hh:mm:ss.


The server used is jetty (https://www.eclipse.org/jetty/) and is embedded when you include the jServer library.

Thank you. I see that it's within the jRDC project.

Concerning the error trying to update using the time, I think it's a difference between the two that is causing the issue. 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 = ?).

So my question with RDC is this - Can I pass the variable into the command process and instead of using the parameterized query I can use a dynamically built query?

 

[Bernard Harris]

I don't know what database you are using on the server.

But from my experience, date format should be in sqlite format, so perhaps, time also need in sqlite format which is HH:MM:SS.

The database is DB2 for IBM i. A time field is stored as hh.mm.ss but displays with the colons. As I stated above I can update the column using just the SET DLVTIME = '09.00.00' but the parameterized version doesn't work, either "." or ":".

 

[Erel]

So my question with RDC is this - Can I pass the variable into the command process and instead of using the parameterized query I can use a dynamically built query?

You can do whatever you like. jRDC2 code is quite simple. You can modify it and handle the parameter in a different way in this specific case.

The relevant code is in RDCHandler.ExecuteQuery2.

 

[Bernard Harris]

Thanks Erel. Now if I could just get jRDC2 up and running...