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

[Jmu5667]

EDITL: rs.postion was not set

Hello

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

This is the function:

Public Sub is_number_whitelisted(pNumber As String) As 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 :

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.

 

[OliverA]

FYI, if you switch to ResultSet instead of Cursor, then instead of

If rs.RowCount > 0 Then

you use

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).

 

[Jmu5667]

FYI, if you switch to ResultSet instead of Cursor, then instead of

If rs.RowCount > 0 Then

you use

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).

Your right of course too much work on at the moment, good complaint though

 

[Erel]

Tip: never use Cursor. Only ResultSet.

 

[Jmu5667]

Tip: never use Cursor. Only ResultSet.

OK

 

[Jmu5667]

Tip: never use Cursor. Only ResultSet.

if resultset more efficient ?

 

[Erel]

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

 

[Jmu5667]

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

Thanks Erel.