Help with SQLite query syntax

[mangojack]

Hi .. Starting to tear my hair.. I have tried every concoction I can thow at this and have given up , There was another similar post where Klaus supplied the right query syntax that should have solve my problem, but did not.

I am trying to get the value of a key that matches a string combined with the adjusted value of the loop counter.
Or would it be just as easy to concoct a string prior the the query and just insert the variable instead.

I do need the loop as dbCount will increase in the future

'Recorded keys .. db1 db2
'dbCount = 2

Dim dbName(dbcount) As String

   For  i = 0 To dbCount - 1         'Trying to achieve  WHERE Key =  ' db  & ( i+1) '
      
      Cursor1 = SQL1.ExecNonQuery("SELECT Value FROM Settings WHERE Key = 'db' & '"&  (i+1) &"' ")   
      
      'Cursor1 = SQL1.ExecNonQuery2("SELECT Value FROM Settings WHERE Key= ?, 'db' & '"& (i + 1) &"' ")   

      Cursor1.Position = 0
      dbName(i) = Cursor1.GetString("Value")
   Next

Many Thanks Cheers mj

 

[barx]

So for example, when i = 1 you want the query string to be?

SELECT Value FROM Settings WHERE Key = 'db2'

If so, try this

Cursor1 = SQL1.ExecNonQuery("SELECT Value FROM Settings WHERE Key = 'db" & (i+1) & "'")

 

[mangojack]

Barx.. Thanks for quick response .. But no go on that one.

' Error description: Cannot assign void value. '

Cheers mj

 

[klaus]

Try this one:

Cursor1 = SQL1.ExecQuery("SELECT Value FROM Settings WHERE Key = 'db" & (i+1) & "'")

Best regards.

 

[barx]

just noticed you are using ExecNonQuery.

This is for SQL queries that don't require data to be returned, i.e. an INSERT query. Try

Cursor1 = SQL1.ExecQuery("SELECT Value FROM Settings WHERE Key = 'db" & (i+1) & "'")

 

[mangojack]

Ouch .. ! Thanks Klaus and barx .. Must be time for a break . I think it might work now.

Cheers mj

 

[mangojack]

Klaus or barx .. can you point me somewhere I can read on the finer points of sytax's etc. as in above query .. thats Query NOT NonQuery'

I thought I was getting comfortable with it regarding the quotes ,double quotes, ampersands etc with variable TableNames and query variables but this one just seems to have no pattern , and I think even without my stupid error I would not have stumbled on that structure.

Many thanks Cheers mj

Edit . Staring at the correct syntax , would I be right in saying the syntax (single/double quotes is different due to the variable being Integer and not String ??

 

[barx]

To be honest, i've never really followed any tutorials on string concatenation. I just learnt from trial and error. A good way to check if a string is building right in b4a is to output it to the log and see if it looks like what you expect.

I also tend to use a variable to store the SQLString, this way it is easy to output it to the log if needed.

Dim SQLString as String
SQLString = "SELECT Value FROM Settings WHERE Key = 'db" & (i+1) & "'" 
'Log(SQLString) 'Un-comment to output to log
Cursor1 = SQL1.ExecQuery(SQLString)

For complex strings, write down on paper (on on notepad) what you want the final string to look like, then chop out the bit you want to be dynamic and replace with

" & SomeVariableName & "

That will end the current string, concatenate the variable into it and start the string back up. If that makes sense.

If all else fails, just ask!

 

[mangojack]

Thanks barx , that' s very helpful and also answered a previous post that gained no response re using a query string.

Many thanks Cheers mj

 

[klaus]

You should distinguish between the B4A syntax and the SQLite syntax.
In B4A you need double quotes to define a String like :

txt = "db"

If you want to append the variable i you must write it like this:

txt = "db" & i

If you want to append a calculation you must write it like this:

txt = "db" & (i + 1)

In SQLite you need single quotes for TEXT field contents like:

query = "SELECT Value FROM Settings WHERE Key = 'db1'"

This line means that the Key content is a number:

query = "SELECT Value FROM Settings WHERE Key = 1"

And in your case with the calculation:

query = "SELECT Value FROM Settings WHERE Key = 'db" & (i + 1) & "'"

In your code you expect only a single answer so you could use this code:

For i = 0 to dbCount - 1
  dbName(i) = SQL1.ExecQuerySingleResult("SELECT Value FROM Settings WHERE Key = 'db" & (i + 1) & "'"
Next

or like this, changing the limits of i :

For i = 1 to dbCount
  dbName(i - 1) = SQL1.ExecQuerySingleResult("SELECT Value FROM Settings WHERE Key = 'db" & i & "'"
Next

Best regards.

 

[mangojack]

Klaus .. much appreciated for this. I am all clear with the first examples, but just cant get my head around this one,

query = "SELECT Value FROM Settings WHERE Key = 'db" & (i + 1) & "'"

I'm playing with some query strings and the log to try and see the light if you know what I mean.

But , I was unaware of ' ExecQuerySingleResult ' . A lot of my queries are for what I know will be single result .. so I will read up on that one .

Many thanks again Cheers mj

Edit .. Klaus ExecQuerySingleResult much nicer , Thanks again

 

[barx]

Yeah, if you KNOW the result will only be a single record, then ExecQuerySingleResult is neater

 

[klaus]

query = "SELECT Value FROM Settings WHERE Key = 'db" & (i + 1) & "'"

First we have the string:
SELECT Value FROM Settings WHERE Key = 'db
Then we calculate (i + 1)
Then we concatenate the result to the previous string & (i + 1), example i = 1 so (i + 1) = 2 and the string is:
SELECT Value FROM Settings WHERE Key = 'db2
Then we add the single quote & "'" where "'" is a string with only the single quote ' and the final string:
SELECT Value FROM Settings WHERE Key = 'db2'

Best regards.

 

[mangojack]

Klaus .. thanks , That makes sense and I now see the makeup of that query string a lot clearer. I also see the benefit if dim'ing a query string and sending to log to confirm if correct .. thanks barx. I will be swapping single and double quotes in my sleep tonight !

Again thanks for your time gents ... Stay safe . Cheers mj