I think I've found an issue in the MS JDBC driver similar but slightly different to that discussed previously in relation to jRDC2 here.
This time I came across a problem when querying an MS SQL Server database DateTime2 column directly with an SQL object (via jConnectionPool).
I think I've solved the problem (based on the help from @OliverA in the jRDC2 post linked above), but I would appreciate it if someone with access to SQL Server would check that it's not just me making mistakes or going mad.
Thanks in advance!
I'm in the UK and our clocks moved forward on Sunday 27/03/2022 from GMT to BST.
The problem I noted was when trying to retrieve a datetime (yyyy-MM-dd HH:mm:ss) from a datetime2 column.
Querying in Server Management Studio gives:
But the code below logs 2022-03-27 02:00:00
What is different to the jRDC2 issue post, and what really puzzles me is that if I change the time in the database to 2022-03-27 03:00:00, so it's still after the BST change, both SSMS & the code above correctly return 2022-03-27 03:00:00.
It seems to be only times between 2022-03-27 01:00:00 & 2022-03-27 01:59:59 that are affected.
Can anyone verify this, &/or explain it?
Thanks again in advance!
This time I came across a problem when querying an MS SQL Server database DateTime2 column directly with an SQL object (via jConnectionPool).
I think I've solved the problem (based on the help from @OliverA in the jRDC2 post linked above), but I would appreciate it if someone with access to SQL Server would check that it's not just me making mistakes or going mad.
Thanks in advance!
I'm in the UK and our clocks moved forward on Sunday 27/03/2022 from GMT to BST.
The problem I noted was when trying to retrieve a datetime (yyyy-MM-dd HH:mm:ss) from a datetime2 column.
Querying in Server Management Studio gives:
But the code below logs 2022-03-27 02:00:00
B4X:
#AdditionalJar: mssql-jdbc-9.2.1.jre11 'Ive tried mssql-jdbc-10.2.0.jre11 as well, same result.
DateTime.SetTimeZone(0)
Private Sub btnTest_Click
pool.GetConnectionAsync("Con")
Wait For Con_ConnectionReady (success As Boolean, sql As SQL)
If success Then
Dim query As String = $"SELECT reset_period_start FROM loggers WHERE logger_unique_id=?"$
Dim SenderFilter As Object = sql.ExecQueryAsync("SQL", query, Array(1))
Wait For (SenderFilter) SQL_QueryComplete (success As Boolean, rs As ResultSet)
If success Then
Do While rs.NextRow
If rs.GetString("reset_period_start")<>Null Then
Log(rs.GetString("reset_period_start")) '2022-03-27 02:00:00
End If
Loop
rs.Close
End If
sql.Close
End If
End SubWhat is different to the jRDC2 issue post, and what really puzzles me is that if I change the time in the database to 2022-03-27 03:00:00, so it's still after the BST change, both SSMS & the code above correctly return 2022-03-27 03:00:00.
It seems to be only times between 2022-03-27 01:00:00 & 2022-03-27 01:59:59 that are affected.
Can anyone verify this, &/or explain it?
Thanks again in advance!
B4X:
DateTime.SetTimeZone(0)
Private Sub btnTest_Click
pool.GetConnectionAsync("Con")
Wait For Con_ConnectionReady (success As Boolean, sql As SQL)
If success Then
Dim query As String = $"SELECT reset_period_start FROM loggers WHERE logger_unique_id=?"$
Dim SenderFilter As Object = sql.ExecQueryAsync("SQL", query, Array(1))
Wait For (SenderFilter) SQL_QueryComplete (success As Boolean, rs As ResultSet)
If success Then
Dim jrs As JavaObject = rs
Do While rs.NextRow
If rs.GetString("reset_period_start")<>Null Then
Dim strPerStart As String = DBHelper.GetUTCDateTimeColumn(jrs, 1) 'the number here is the column index (1 based) of the results
Log(strPerStart)
End If
Loop
rs.Close
End If
sql.Close
End If
End Sub
Public Sub GetUTCDateTimeColumn (jrs As JavaObject, colNum As Int) As String
Dim tz As JavaObject
tz.InitializeStatic("java.util.TimeZone")
Dim cal As JavaObject
cal.InitializeStatic("java.util.Calendar")
Dim tzUTC As JavaObject = tz.RunMethod("getTimeZone", Array ("UTC"))
Dim SQLTime As JavaObject = jrs.RunMethodJO("getTimestamp", Array(colNum, cal.RunMethod("getInstance", Array(tzUTC))))
If SQLTime.IsInitialized Then
Dim longLastBlockDT As Long=SQLTime.RunMethod("getTime", Null)
Log("getTime=" & longLastBlockDT)
DateTime.DateFormat="yyyy-MM-dd HH:mm:ss"
Return DateTime.Date(longLastBlockDT)
Else
Return Null
End If
End Sub