B4J Question JRDC2 Insert datetime2 to SQL Server as ticks

[Chris2]

Hello everyone.

Based on Erel's reply (post #2) from https://www.b4x.com/android/forum/threads/datetime.119969/post-750103:

If you are using jRDC2 then you should send the ticks value (Long).

I am triyng to insert a record into a MS SQL Server database table via JRDC2. The table has four columns:
id (tinyint) channel(tinyint) period_start (datetime2(0)) count(smallint) value(float).

I'm sending a ticks value for the period_start column.

The insertion code is:

Public Sub InsertLoggedData  As ResumableSub
    Dim lstCommands As List
    lstCommands.Initialize

    Dim comInsert As String = "insert_loggeddata"
    Dim cmdInsert As DBCommand = CreateCommand(comInsert, Array(1, 1, DateTime.now, 10, 1))
    lstCommands.Add(cmdInsert)
    
    Dim j As HttpJob = CreateRequest.ExecuteBatch(lstCommands, Null)
    Wait For(j) JobDone(j As HttpJob)
    If j.Success Then
        Log("InsertInto LoggedData successful")
    Else
        Log("InsertInto LoggedData failed: " & LastException)
    End If
    j.Release
    Return j.Success

This insert fails with:

ResponseError. Reason: com.microsoft.sqlserver.jdbc.SQLServerException: Operand type clash: bigint is incompatible with datetime2, Response: .....

Inserting with the datetime as a string ("yyyy-MM-dd HH:mm:ss") works. But querying datetime2(0) columns returns ticks, so I'd like to keep it as ticks all the way through.

Do I have to insert the datetime2(0) as a string, or can it be done as ticks?

Thanks.

 

[Erel]

Why not store the ticks value as is, in a number column?

 

[Chris2]

No particular reason I guess. I just assumed that datetimes should be stored in a datetime type column .

I'm not sure what issues I might run into down the line when querying the data, but I can think that through...

 

[OliverA]

Inserting with the datetime as a string ("yyyy-MM-dd HH:mm:ss") works. But querying datetime2(0) columns returns ticks, so I'd like to keep it as ticks all the way through.

When inserting, you'll have to go by MS SQL conventions for datetime2, which means you have to use a string value. As to the returned tick value, that is done by jRDC2's code. The code sees the query column as a date/time type and converts the requested result to a date/time tick value.
The relevant code in jRDC2 for returning ticks is

            Else If DateTimeMethods.ContainsKey(ct) Then
                Dim SQLTime As JavaObject = jrs.RunMethodJO(DateTimeMethods.Get(ct), Array(i + 1))
                If SQLTime.IsInitialized Then
                    row(i) = SQLTime.RunMethod("getTime", Null)
                Else
                    row(i) = Null
                End If

where ct contains an integer value representing the type of column returned. If the value is 91, 92 or 93, then the column contains date, time, or date/time information.

 

[Erel]

Note that the date string is ambiguous as it doesn't contain timezone offset. There is no such problem with ticks.

 

[Chris2]

Thanks both for the explanations.

The DateTime stored will always be UTC so although the string would be ambiguous as you say Erel, it would be known and consistant.
I guess my choice is either to store as ticks in a number column, or convert to string (making sure to keep it as UTC) before inserting into a Datetime2 column.