B4J Question JRDC2 errors when querying SQL Server datetimeoffset column

Chris2 · Mar 31, 2021

When querying a SQL Server database datetimeoffset(0) column I'm getting the following error:

B4X:

ResponseError. Reason: java.lang.RuntimeException: Cannot serialize object: 2021-03-28 02:00:00 +00:00, Response: <html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8"/>
<title>Error 500 java.lang.RuntimeException: Cannot serialize object: 2021-03-28 02:00:00 +00:00</title>
</head>
<body><h2>HTTP ERROR 500</h2>
<p>Problem accessing /rdc. Reason:
<pre>    java.lang.RuntimeException: Cannot serialize object: 2021-03-28 02:00:00 +00:00</pre></p><hr><a href="http://eclipse.org/jetty">Powered by Jetty:// 9.4.z-SNAPSHOT</a><hr/>
</body>
</html>
ERROR: <html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8"/>
<title>Error 500 java.lang.RuntimeException: Cannot serialize object: 2021-03-28 02:00:00 +00:00</title>
</head>
<body><h2>HTTP ERROR 500</h2>
<p>Problem accessing /rdc. Reason:
<pre>    java.lang.RuntimeException: Cannot serialize object: 2021-03-28 02:00:00 +00:00</pre></p><hr><a href="http://eclipse.org/jetty">Powered by Jetty:// 9.4.z-SNAPSHOT</a><hr/>
</body>
</html>

Is datetimeoffset incompatible with jRDC2?

jahswant · Mar 31, 2021

Check this. https://www.b4x.com/android/forum/threads/jrdc2-cannot-serialize-object.91593/

Chris2 · Mar 31, 2021

Thanks @jahswant, but I'm using jRDC2 ver 2.2 which I think includes the fixes for those issues.

Interestingly though, adding the log that @Erel suggested in post #2 of your link gives me
ct: -155: row(i): 2021-03-28 02:00:00 +00:00.

So, a column type -155 that is not included in the DateTimeMethods map in jRDC2 so far as I can see.

Maybe that will point someone smarter than me in the right direction

.

Chris2 · Mar 31, 2021

but I'm using jRDC2 ver 2.2...

2.22.

OliverA · Apr 1, 2021

Try this additional code in jRDC2

B4X:

            Else ct = -155 Then
                Dim SQLTime As JavaObject = jrs.RunMethodJO("getObject", Array(i + 1)) 'Should return a  microsoft.sql.DateTimeOffset object
                If SQLTime.IsInitialized Then
                    'https://docs.microsoft.com/en-us/sql/connect/jdbc/reference/datetimeoffset-members?view=sql-server-ver15
                    row(i) = SQLTime.RunMethod("getTimestamp", Null)
                Else
                    row(i) = Null
                End If

Note: Totally untested

Chris2 · Apr 1, 2021

Thanks @OliverA !
I won't be able to try it until tomorrow, but will report back when I can.

Chris2 · Apr 1, 2021

Adding in @OliverA's suggestion gives the same error unfortunately.
It seems that the DateTimeOffset object is being returned OK, but I still get the 'Cannot serialize object' error.
I added some extra logging in:

B4X:

Else If ct = -155 Then
                Log("ct=-155, " & jrs.RunMethod("getObject", Array(i + 1))) ' Add this log statement to see what is being returned. Should call the object's toString method
                Dim SQLTime As JavaObject = jrs.RunMethodJO("getObject", Array(i + 1)) 'Should return a  microsoft.sql.DateTimeOffset object
                Log("SQLTime=" & SQLTime)
                If SQLTime.IsInitialized Then
                    'https://docs.microsoft.com/en-us/sql/connect/jdbc/reference/datetimeoffset-members?view=sql-server-ver15
                    row(i) = SQLTime.RunMethod("getTimestamp", Null)
                    Log(row(i)) ' Added log of end result for datetime ticks
                    Log(SQLTime.RunMethod("getMinutesOffset", Null))
                Else
                    row(i) = Null
                End If
            Else If DateTimeMethods.ContainsKey(ct) Then

The jRDC2 logs give;

B4X:

ct=-155, 2021-03-28 02:00:00 +00:00
SQLTime=(DateTimeOffset) 2021-03-28 02:00:00 +00:00
2021-03-28 03:00:00.0
0
(RuntimeException) java.lang.RuntimeException: Cannot serialize object: 2021-03-28 03:00:00.0
 Command: , took: 669ms, client=127.0.0.1

It also looks like I'm having my UTC time issue here too - https://www.b4x.com/android/forum/threads/jrdc2-retrieve-sql-server-datetime2-as-utc.129255/

OliverA · Apr 1, 2021

Chris2 said:
I still get the 'Cannot serialize object' error.

My fault. Try this

B4X:

            Else If ct = -155 Then
                Log("ct=-155, " & jrs.RunMethod("getObject", Array(i + 1))) ' Add this log statement to see what is being returned. Should call the object's toString method
                'Retrieve a  microsoft.sql.DateTimeOffset object and from it retrieve a java.sql.Timestamp
                'https://docs.microsoft.com/en-us/sql/connect/jdbc/reference/datetimeoffset-members?view=sql-server-ver15
                Dim SQLTime As JavaObject = jrs.RunMethodJO("getObject", Array(i + 1)).RunMethodJO("getTimestamp", Null)
                Log("SQLTime=" & SQLTime)
                If SQLTime.IsInitialized Then                    
                    row(i) = SQLTime.RunMethod("getTime", Null)
                    Log(row(i)) ' Added log of end result for datetime ticks
                    'Log(SQLTime.RunMethod("getMinutesOffset", Null))
                Else
                    row(i) = Null
                End If

Chris2 · Apr 2, 2021

No more errors! Many thanks.
The jRDC2 logs now show:

B4X:

ct=-155, 2021-03-28 02:00:00 +00:00
SQLTime=(Timestamp) 2021-03-28 03:00:00.0
1616896800000
 Command: query: select_lastblockDT_loggeruid, took: 5ms, client=127.0.0.1

'Retrieve a microsoft.sql.DateTimeOffset object and from it retrieve a java.sql.Timestamp
'https://docs.microsoft.com/en-us/sq.../datetimeoffset-members?view=sql-server-ver15

Thanks for including your comments and the link too. I understand a little better now where this is coming from, and why post #5 didn't work ?.

B4J Question JRDC2 errors when querying SQL Server datetimeoffset column

Chris2

Active Member

jahswant

Well-Known Member

Chris2

Active Member

Chris2

Active Member

OliverA

Expert

Chris2

Active Member

Chris2

Active Member

OliverA

Expert

Chris2

Active Member

Similar Threads