1. *** New version of B4J is available ***
    B4J v7.8
    Dismiss Notice

Android Question [RESOLVED] SQL Cursor Col Names not availale after query

Discussion in 'Android Questions' started by Jmu5667, Apr 8, 2019.

  1. Jmu5667

    Jmu5667 Well-Known Member Licensed User

    EDITL: rs.postion was not set :oops:

    Hello

    I have a function that access's a table which has 3 cols in it.

    This is the function:
    Code:
    Public Sub is_number_whitelisted(pNumber As StringAs Boolean

       
    Dim res As Boolean = False
       
    Dim rs As Cursor
       
    Dim partialLength As Int = 9
       
    Dim sNumber As String
       
    Dim sf As StringFunctions
     
     
       sf.Initialize
     
       
    If pNumber.Length < partialLength Then
           partialLength = pNumber.Length
       
    End If
     
       sNumber = sf.Right(pNumber,partialLength)
     
       
    Dim sSql As String = "SELECT *  FROM whitelist " & _
                               
    "WHERE identifier LIKE '%" & sNumber & "%'"

       
    ' // search the whitelist
       writelog("mod_functions() is_number_whitelisted, SQL = " & sSql)
       rs = mod_core_consts.SQLDB.ExecQuery(sSql)
       
    If rs.RowCount > 0 Then
           writelog(
    "mod_functions() is_number_whitelisted rs.RowCount    = " & rs.RowCount)
           writelog(
    "mod_functions() is_number_whitelisted rs.ColumnCount = " & rs.ColumnCount)
       
           
    Dim x As Int
       
           
    For x = 0 To rs.ColumnCount-1
               writelog(
    "mod_functions() is_number_whitelisted Column Name = " & rs.GetColumnName(x))
           
    Next
       
           writelog(
    "mod_functions() is_number_whitelisted NAME = " & rs.Getstring("alias")) <- ERROR
       
           
    'writelog("mod_functions() is_number_whitelisted NUM  = " & rs.Getstring("identifier"))
           'writelog("mod_functions() is_number_whitelisted TYPE = " & rs.GetInt("type"))
           res= True
       
    End If
     
       
    ' // search the address book
       If Not(res) Then
           sSql = 
    "SELECT account_name, contact_number FROM address_book " & _
                               
    "WHERE contact_number LIKE '%" & sNumber & "%'"
           rs = mod_core_consts.SQLDB.ExecQuery(sSql)
           
    If rs.RowCount > 0 Then
               writelog(
    "mod_functions() is_number_whitelisted rs.RowCount    = " & rs.RowCount)
               writelog(
    "mod_functions() is_number_whitelisted rs.ColumnCount = " & rs.ColumnCount)
               
    'writelog("mod_functions() is_number_whitelisted NAME = " & rs.Getstring("account_name"))
               'writelog("mod_functions() is_number_whitelisted NUM  = " & rs.Getstring("contact_number"))
               res= True
           
    End If
       
    End If


       
    Return res

    End Sub
    This is my log data :

    Code:
    04/08/2019 14:30:44.567 - Atlas HELLO - svc_wsh_messaging(), wsh_PingResponse(), Server Response PING_OK
    04/08/2019 14:30:46.194 - Atlas HELLO - mod_functions() is_number_whitelisted, SQL = SELECT *  FROM whitelist WHERE identifier LIKE '%8xxxxxxx6%' <- Its my mobile phone number
    04/08/2019 14:30:46.201 - Atlas HELLO - mod_functions() is_number_whitelisted rs.RowCount    = 1
    04/08/2019 14:30:46.207 - Atlas HELLO - mod_functions() is_number_whitelisted rs.ColumnCount = 3
    04/08/2019 14:30:46.213 - Atlas HELLO - mod_functions() is_number_whitelisted Column ANme = alias
    04/08/2019 14:30:46.219 - Atlas HELLO - mod_functions() is_number_whitelisted Column ANme = identifier
    04/08/2019 14:30:46.225 - Atlas HELLO - mod_functions() is_number_whitelisted Column ANme = type
    mod_functions_is_number_whitelisted (java line: 
    325)
    android.database.CursorIndexOutOfBoundsException: Index -
    1 requested, with a size of 1
       at android.database.AbstractCursor.checkPosition(AbstractCursor.java:
    460)
       at android.database.AbstractWindowedCursor.checkPosition(AbstractWindowedCursor.java:
    136)
       at android.database.AbstractWindowedCursor.getString(AbstractWindowedCursor.java:
    50)
       at anywheresoftware.b4a.sql.SQL$CursorWrapper.GetString(
    SQL.java:377)
       at com.islesystems.hello.mod_functions._is_number_whitelisted(mod_functions.java:
    325)
       at com.islesystems.hello.main._lbluserphone_click(main.java:
    1509)
       at java.lang.reflect.Method.invoke(Native Method)
       at anywheresoftware.b4a.BA.raiseEvent2(BA.java:
    196)
       at anywheresoftware.b4a.BA.raiseEvent2(BA.java:
    180)
       at anywheresoftware.b4a.BA.raiseEvent(BA.java:
    176)
       at anywheresoftware.b4a.objects.ViewWrapper$
    1.onClick(ViewWrapper.java:80)
       at android.view.View.performClick(
    View.java:6291)
       at android.view.View$PerformClick.run(
    View.java:24931)
       at android.os.Handler.handleCallback(Handler.java:
    808)
       at android.os.Handler.dispatchMessage(Handler.java:
    101)
       at android.os.Looper.loop(Looper.java:
    166)
       at android.app.ActivityThread.main(ActivityThread.java:
    7529)
       at java.lang.reflect.Method.invoke(Native Method)
       at com.android.internal.os.Zygote$MethodAndArgsCaller.run(Zygote.java:
    245)
       at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:
    921)
    android.database.CursorIndexOutOfBoundsException: Index -
    1 requested, with a size of 1
    It appears that the col names are not available. I have not exerienced this before and this is the first time I have need to use the 'LIKE' expression in a SQL statement.

    Any Ideas ?

    Regards

    John.
     
    Last edited: Apr 8, 2019
  2. OliverA

    OliverA Expert Licensed User

    FYI, if you switch to ResultSet instead of Cursor, then instead of
    Code:
    If rs.RowCount > 0 Then
    you use
    Code:
    If rs.NextRow Then
    NextRow will properly set the underlying SQL cursor to the proper record (if one exits. If no records are returned, NextRow will be False) and all your ResultSet methods should return the proper values (without having to set the position first).
     
    DonManfred, Erel and Jmu5667 like this.
  3. Jmu5667

    Jmu5667 Well-Known Member Licensed User

    Your right of course :) too much work on at the moment, good complaint though :)
     
  4. Erel

    Erel Administrator Staff Member Licensed User

    Tip: never use Cursor. Only ResultSet.
     
  5. Jmu5667

    Jmu5667 Well-Known Member Licensed User

    OK :)
     
  6. Jmu5667

    Jmu5667 Well-Known Member Licensed User

    if resultset more efficient ?
     
  7. Erel

    Erel Administrator Staff Member Licensed User

    No. Performance is exactly the same. It is a bit simpler to use and the main benefit is that it is cross platform.
     
  8. Jmu5667

    Jmu5667 Well-Known Member Licensed User

    Thanks Erel.
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice