Android Question Strange behaviour with SQL

[Serge Bertet]

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

    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

 

[RB Smissaert]

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

    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

 

[Serge Bertet]

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

 

[Serge Bertet]

I think this solution works:

    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

 

[RB Smissaert]

c

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:

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

 

[Serge Bertet]

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 :

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

 

[RB Smissaert]

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 :

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

 

[jimmyF]

https://www.b4x.com/android/forum/threads/b4x-keyvaluestore-2-simple-powerful-local-datastore.63633/

 

[Erel]

The code in the first post is wrong. You must set Cursor.Position.
I recommend you to use ResultSet instead of Cursor. It is a bit simpler to use and is cross platform.

Dim ResultSet1 As ResultSet = Starter.sql.ExecQuery(...)
Do While ResultSet1.Next 
'Work with Row 
Loop
ResultSet1.Close

 

[Serge Bertet]

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

 

[Mahares]

Do While ResultSet1.Next

It should be:

Do While ResultSet1.NextRow

You made the same mistake before. You are becoming like us, making the same mistake more than once. Be careful. It is contagious.

 

[jimmyF]

Serge, use the kvs.GetDefault(key, default value)

 

[Erel]

Tried to find the documentation ... nothing

Search first result: https://www.b4x.com/android/forum/pages/results/?query=KeyValueStore
https://www.b4x.com/android/forum/t...imple-powerful-local-datastore.63633/#content