B4J Question JRDC2 Insert datetime2 to SQL Server as ticks

C

Chris2

Active Member
Licensed User
Longtime 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).
Click to expand...

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.
 
Sort by date Sort by votes
C

Chris2

Active Member
Licensed User
Longtime 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...
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Chris2 said:
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.
Click to expand...
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.
 
Upvote 0
C

Chris2

Active Member
Licensed User
Longtime 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.
 
Upvote 0
You must log in or register to reply here.
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…