Android Question Strange behaviour with SQL

Serge Bertet

Member
Licensed User
Hello,

I'm recording some app's variables as key/values pairs in a table on the fly so I don't know in advance if a variable is present. I try to use Cursor.GetColumnName but I ran into this problem when i=3 (see my code).

B4X:
    csr = Starter.db.ExecQuery("SELECT * FROM misc")
    For i = 0 To csr.RowCount-1 ' <-- RowCount = 4
        If csr.GetColumnName(i) = "autoplay" Then ' <- error : ArrayIndexOutOfBoundsException: length=3; index=3
            AutoPlay.Checked = (csr.GetString("autoplay") = "true")
            csr.Close
            Exit
        End If
    Next

I don't know how to deal with that. Any help?


Edit: found that the index of GetColumnName is related to the number of columns in the table.

Serge
 
Last edited:

RB Smissaert

Well-Known Member
Licensed User
Hello,

I'm recording some app's variables as key/values pairs in a table on the fly so I don't know in advance if a variable is present. I try to use Cursor.GetColumnName but I ran into this problem when i=3 (see my code).

B4X:
    csr = Starter.db.ExecQuery("SELECT * FROM misc")
    For i = 0 To csr.RowCount-1 ' <-- RowCount = 4
        If csr.GetColumnName(i) = "autoplay" Then ' <- error : ArrayIndexOutOfBoundsException: length=3; index=3
            AutoPlay.Checked = (csr.GetString("autoplay") = "true")
            csr.Close
            Exit
        End If
    Next

I don't know how to deal with that. Any help?

Serge
I is a row iterator, not a column iterator.
Why check column name?
Wouldn't you already?

RBS
 
Upvote 0

Serge Bertet

Member
Licensed User
Actually I'm looking for a SQL syntax to find if a given key already exists in a table for example 'autoplay'=>'false' in 'misc' table

Each row in the table has 3 columns : 'ID' 'key' and 'val'

Serge
 
Last edited:
Upvote 0

Serge Bertet

Member
Licensed User
I think this solution works:

B4X:
    If Starter.db.ExecQuerySingleResult("SELECT * FROM misc WHERE key='autoplay'") = 1 Then
        AutoPlay.Checked = (Starter.db.ExecQuerySingleResult("SELECT val FROM misc WHERE key='autoplay'") = "true")
    End If

Thx
 
Upvote 0

RB Smissaert

Well-Known Member
Licensed User
Actually I'm looking for a SQL syntax to find if a given key already exists in a table for example 'autoplay'=>'false' in 'misc' table

Each row in the table has 3 columns : 'ID' 'key' and 'val'

Serge

You want something like this:

B4X:
Dim strKey As String
Dim bKeyPresent As Boolean

strKey = "autoplay"
strKey = SQL1.ExecQuerySingleResult2("select key from table1 where key = ? limit 1", Array As String(strKey))
bKeyPresent = strKey <> Null

RBS
 
Upvote 0

Serge Bertet

Member
Licensed User
OK, it's working like that. The question mark is replaced with the string "autoplay" in this syntax I don't know?
If so may I replace it with :
B4X:
If Starter.db.ExecQuerySingleResult("SELECT * FROM misc WHERE key='autoplay'") <> Null Then
 
Upvote 0

RB Smissaert

Well-Known Member
Licensed User
I don't think that will work well.
Problem is that your SQL could produce more than a single value.
I would stick to the way I showed, but you could do:

If Starter.db.ExecQuerySingleResult("SELECT key FROM misc WHERE key ='autoplay' limit 1") <> Null Then

RBS



RBS

OK, it's working like that. The question mark is replaced with the string "autoplay" in this syntax I don't know?
If so may I replace it with :
B4X:
If Starter.db.ExecQuerySingleResult("SELECT * FROM misc WHERE key='autoplay'") <> Null Then
 
Upvote 0

Serge Bertet

Member
Licensed User
It looks like the keyvaluestore library is the solution.
Tried to find the documentation ... nothing
I just wanna know what is the result of kvs.get(key) if key does not exists (Null I think) .. I'll log message to know.

Thx Erel, I'll use ResultSet instead of cursor, good idea

Serge
 
Upvote 0

jimmyF

Active Member
Licensed User
Serge, use the kvs.GetDefault(key, default value)
 
Upvote 0
Top