Android Question jRDC2 Client

[Domingo Garcia]

Hi, I'm trying to extract the response of a query but I'm unable to get a valid value.

    Dim cnt As Int = 0
    Dim req As DBRequestManager = CreateRequest
    Dim cmd As DBCommand
    cmd = CreateCommand("sel_chk_id", Array(gtrndate, gdriver))
    Wait For (req.ExecuteQuery(cmd, 0, Null)) JobDone(h As HttpJob)
    If h.Success Then
        req.HandleJobAsync(h, "req")
        Wait For (req) req_Result(res As DBResult)
        cnt = res.Rows.Size
        req.PrintTable(res)
        If cnt > 0 Then
            Dim Value As Int = res.Columns.Get("CheckOutId")  ' This value is 1 in the DB
            ckid = Value                                                           ' but it returns value zero
        Else
            addNewChkOut
        End If
        Log("ckid1:" & ckid)
    Else
        Log("ERROR: " & h.ErrorMessage)
    End If
    h.Release

 

[Erel]

Make it easier for us to help you. What is the output of res.PrintTable?

 

[Domingo Garcia]

Tag: null, Columns: 1, Rows: 1
CheckOutId
1

 

[OliverA]

I finally see what is going on (I'm thinking this is the same issue you had here: https://www.b4x.com/android/forum/threads/jrdc2-response-not-processed.89098/#post-565122.)

res.Columns.Get

This does not return the value contained in the row of information returned. This returns the column position in the returned query of the column you are trying to access. That is the first step to get the value that you wanted. You need something like this:

DIM colIdx as Int = res.Columns.Get("CheckOutId") ' Get column position of needed data
DIM rowIdx as Int = 0 ' Were going to access the first row
DIM row() as Object = res.Rows.Get(rowIdx) ' Let's get the first row of the returned query
DIM Value as Int = row(colIdx) ' Let's fetch the data were looking for

For personal use I've created a little sub for that

'Column map keys are case sensitive. Use this to allow for case insensitive match.
Public Sub GetColumnObject(res As DBResult, rowIdx As Int, colName As String) As Object
'   If res = Null Or res.IsInitialized = False Then Return Null
'   If rowIdx < 0 Or res.Rows.Size < rowIdx Then Return Null
   Dim colIdx As Int = -1
   Dim columns As Map = res.Columns
   For Each key As String In columns.Keys
       If key.EqualsIgnoreCase(colName) Then
           colIdx = columns.Get(key)
           Exit
       End If
   Next
   If colIdx = -1 Then Return Null
   Dim row() As Object = res.Rows.Get(rowIdx)
   Return row(colIdx)
End Sub

And use it

Dim Value As Int = GetColumnObject(res, 0, "checkoutid")

 

[Erel]

This does not return the value contained in the row of information returned. This returns the column position in the returned query of the column you are trying to access.

That's true.

I wouldn't use this code:

  For Each key As String In columns.Keys
       If key.EqualsIgnoreCase(colName) Then
           colIdx = columns.Get(key)
           Exit
       End If
   Next

The whole idea of using a map is that you can quickly get the value based on the key.

In this specific case there is a single column so you can get it with:

If cnt > 0 Then
  Dim row() As Object = res.Rows.Get(0)
  Dim value As Int = row(0)
End If

The generic way to go over the results:

For Each row() As Object In res.Rows
 'get value for Column1
 Dim ColumnIndex As Int = res.Columns.Get("Column1")
 Dim value As Object = row(ColumnIndex)
 'Or in a single line
 Dim value As Object = row(res.Columns.Get("Column1"))
Next

 

[Domingo Garcia]

Thank you,
That solved the problem.

 

[OliverA]

The whole idea of using a map is that you can quickly get the value based on the key.

I understand. But when you come from an SQL world you may be used to case insensitivity, and this sub was addressing this (https://www.b4x.com/android/forum/threads/why-does-cursor-getint-colname-have-to-be-lowercase.67073/). Looking over the routine after your comments made me realize though that I was using the sledgehammer approach, since this sub always does a case insensitive search, even though the 80/20 rule may apply, where 80% of the time the correct column casing will be provided. I also don't like it that the sub returns "null" when it cannot find the column name. I should have the sub bomb out. On top of that, I probably should not have posted the sub at all, since the poster was just looking for a quick (and correct) answer. At least I got some feedback out of the posting.

 

[Erel]

I understand your concern. In retrospect it might have been better to lower case the keys and provide a method that gets the value based on the key. That method should have also lowered case the key.

 

[OliverA]

In retrospect it might have been better to lower case the keys and provide a method that gets the value based on the key. That method should have also lowered case the key.

Yeah, but that would just end up biting you, in case the user wants to get the column names for display and had them formatted for such (in upper/lowercase formatting). Sometimes you can't have it all. So we use code to adapt.