Android Question SQLITE unfiltered logs

[Rusty]

I have been using:

Dim AnsTxt As String
      AnswerCursor.Position = 0
      AnsTxt = AnswerCursor.GetString("Answers.AnswerText")

to retrieve a column from an (open) database/table.
I was reviewing the unfiltered logs and see:

requesting column name with table name -- Answers.AnswerText
java.lang.Exception
at net.sqlcipher.database.SQLiteCursor.getColumnIndex(SQLiteCursor.java:315)
at net.sqlcipher.AbstractCursor.getColumnIndexOrThrow(AbstractCursor.java:284)
at android.database.CursorWrapper.getColumnIndexOrThrow(CursorWrapper.java:78)
at anywheresoftware.b4a.sql.SQL$CursorWrapper.GetString(SQL.java:355)
at talkingsurvey.b4a.survey.main._qdisplay(main.java:8706)
at talkingsurvey.b4a.survey.main._displayquestions(main.java:16803)
at talkingsurvey.b4a.survey.main._vv_complete(main.java:18769)
at talkingsurvey.b4a.survey.main._btnvvstop_click(main.java:18595)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:708)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:340)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:247)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:134)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:157)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:153)
at anywheresoftware.b4a.objects.ViewWrapper$1.onClick(ViewWrapper.java:78)
at android.view.View.performClick(View.java:4487)
at android.view.View$PerformClick.run(View.java:18746)
at android.os.Handler.handleCallback(Handler.java:733)
at android.os.Handler.dispatchMessage(Handler.java:95)
at android.os.Looper.loop(Looper.java:149)
at android.app.ActivityThread.main(ActivityThread.java:5257)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:794)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:610)
at dalvik.system.NativeStart.main(Native Method)

for every column retrieved with .getstring("columnname") instead of .getstring2(columnindex)
Is this an error or something that is generated every time you access a named column instead of an indexed column in a cursor?
Rusty

 

[Erel]

It should be:

AnswerCursor.GetString("AnswerText")

 

[Rusty]

What do I do if there are duplicate column names in joined tables?
i.e.
[survey].surveyid
and
[question].surveyid

(I've tried using the brackets with the same results.)
BTW, in all cases the data IS correctly retrieved, but there are logs (like in post number 1) showing an exception on each column...
Rusty

 

[Mahares]

Suppose you have 2 tables created as such:

Dim MyQuery As String    ="CREATE TABLE survey (surveyid INTEGER,  location TEXT, county TEXT, lat REAL, lon REAL)"
SQL1.ExecNonQuery(MyQuery)
Dim MyQuery As String    ="CREATE TABLE question  (surveyid INTEGER,  accuracy TEXT, marker TEXT)"
SQL1.ExecNonQuery(MyQuery)

You can join the 2 tables with the common column and display data from both like this:

MyQuery="SELECT S.surveyid, S.location , S.county, Q.accuracy FROM survey S " _
    & " INNER JOIN question Q ON S.surveyid = Q.surveyid ORDER BY Q.accuracy"
    Cursor1=SQL1.ExecQuery(MyQuery)
    For i=0 To Cursor1.RowCount-1
        Cursor1.Position=i
        Log (Cursor1.GetString("surveyid") & "    " & Cursor1.GetString("location") _
        & "  " & Cursor1.GetString("county") & "  " & Cursor1.GetString("accuracy"))
    Next

You can also give the columns names alias names. especially if the tables have 2 columns with the same name, but different data for both, if you want to show both columns. Example: S.location AS loc

 

[Rusty]

Thanks Mahares,
If the two fields are not part of the joined index and you need to see both values, what do you do then?
s.surveyid and q.surveyid?
wouldn't that be the same as my original mistake?
survey.surveyid and question.surveyid?
... I guess I'm a bit confused.
MSSQL, we use [survey]. surveyid, and [question].surveyid
or you can give an alias like you did and use
.surveyid and [q].surveyid

a better example might be "comments" where we have
.comments and [q].comments (obviously not join key elements, but duplicate names within joined tables)

Apparently, this works in B4a/Android, but ALWAYS causes and exception that can be trapped, but ... still, they are exceptions...
any further ideas?
Rusty

 

[Mahares]

You do this using the 2 comments, each with its own alias. I do not think you will get the exception:

 MyQuery="SELECT S.surveyid, S.comment AS surveycom , S.county, Q.comment AS questioncom FROM survey S " _
    & " INNER JOIN question Q ON S.surveyid = Q.surveyid"
    Cursor1=SQL1.ExecQuery(MyQuery)
    For i=0 To Cursor1.RowCount-1
        Cursor1.Position=i
        Log (Cursor1.GetString("surveyid") & "    " & Cursor1.GetString("questioncom") _
        & "  " & Cursor1.GetString("county") & "  " & Cursor1.GetString("surveycom"))
    Next

 

[Erel]

You should use aliases.

Tip: use smart strings

MyQuery=$"SELECT S.surveyid, S.comment AS surveycom , S.county, Q.comment AS questioncom 
    FROM survey S 
   INNER JOIN question Q ON S.surveyid = Q.surveyid"$
    Cursor1=SQL1.ExecQuery(MyQuery)
    For i=0 To Cursor1.RowCount-1
        Cursor1.Position=i
        Log (Cursor1.GetString("surveyid") & "    " & Cursor1.GetString("questioncom") _
        & "  " & Cursor1.GetString("county") & "  " & Cursor1.GetString("surveycom"))
    Next

 

[Mahares]

Tip: use smart strings

If smart string is superior to regular string, how do you print all below 4 columns in one line, other than building a long single line without breaks.

Log ( $" ${Cursor1.GetString("surveyid")}    ${Cursor1.GetString("questioncom")}    
        ${Cursor1.GetString("county")}    ${Cursor1.GetString("surveycom")}"$)

If you can't , then I am not sure Smart String literal is always the best way.

 

[Erel]

If you can't , then I am not sure Smart String literal is always the best way.

Never said that it is always superior. For long SQL queries it is usually better.

For example:

query = $"
SELECT EMP_ID, LAST_NAME
FROM EMPLOYEE_TBL
WHERE CITY = 'INDIANAPOLIS'
ORDER BY 2, 1"$
'compared to:
query = "SELECT EMP_ID, LAST_NAME" & _
"FROM EMPLOYEE_TBL" & _
"WHERE CITY = 'INDIANAPOLIS'" & _
"ORDER BY 2, 1"

The second one will actually fail as there are no spaces between each line.

If you can't

It is possible:

Log ( $" ${Cursor1.GetString("surveyid")}  ${Cursor1.GetString("questioncom") _
}  ${Cursor1.GetString("county")}  ${Cursor1.GetString("surveycom")}"$)