B4J Question JRDC2 Insert datetime2 to SQL Server as ticks

Chris2

Member
Licensed User
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:
InsertData:
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:
Log:
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.
 

Chris2

Member
Licensed User
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

Expert
Licensed User
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
B4X:
            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.
 

Chris2

Member
Licensed User
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.
 
Top