Help with SQLite query syntax

mangojack

mangojack

Expert
Licensed User
Longtime User
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

B4X: 
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
 
Sort by date Sort by votes
barx

barx

Well-Known Member
Licensed User
Longtime User
So for example, when i = 1 you want the query string to be?

B4X: 
SELECT Value FROM Settings WHERE Key = 'db2'

If so, try this

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

mangojack

Expert
Licensed User
Longtime User
Barx.. Thanks for quick response .. But no go on that one.

' Error description: Cannot assign void value. '

Cheers mj
 
Upvote 0

barx

Well-Known Member
Licensed User
Longtime User
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

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

mangojack

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

Cheers mj
 
Upvote 0

mangojack

Expert
Licensed User
Longtime User
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 ??
 
Last edited:
Upvote 0

barx

Well-Known Member
Licensed User
Longtime User
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.

B4X: 
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
B4X: 
" & 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!
 
Upvote 0

mangojack

Expert
Licensed User
Longtime User
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
 
Upvote 0

klaus

Expert
Licensed User
Longtime User
You should distinguish between the B4A syntax and the SQLite syntax.
In B4A you need double quotes to define a String like :
B4X: 
txt = "db"
If you want to append the variable i you must write it like this:
B4X: 
txt = "db" & i
If you want to append a calculation you must write it like this:
B4X: 
txt = "db" & (i + 1)
In SQLite you need single quotes for TEXT field contents like:
B4X: 
query = "SELECT Value FROM Settings WHERE Key = 'db1'"
This line means that the Key content is a number:
B4X: 
query = "SELECT Value FROM Settings WHERE Key = 1"
And in your case with the calculation:
B4X: 
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:
B4X: 
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 :
B4X: 
For i = 1 to dbCount
  dbName(i - 1) = SQL1.ExecQuerySingleResult("SELECT Value FROM Settings WHERE Key = 'db" & i & "'"
Next
Best regards.
 
Upvote 0

mangojack

Expert
Licensed User
Longtime User
Klaus .. much appreciated for this. I am all clear with the first examples, but just cant get my head around this one,
B4X: 
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
 
Last edited:
Upvote 0

barx

Well-Known Member
Licensed User
Longtime User
Yeah, if you KNOW the result will only be a single record, then ExecQuerySingleResult is neater
 
Upvote 0

klaus

Expert
Licensed User
Longtime User
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.
 
Upvote 0

mangojack

Expert
Licensed User
Longtime User
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
 
Upvote 0
You must log in or register to reply here.

Similar Threads

  • Article
Android Code Snippet Using SQLite
Replies
0
Views
1K
Cadenzo
  • Question
Android Question SQLite syntax request error
Replies
14
Views
4K
LucaMs
  • Article
B4J Code Snippet [B4X] MiniORMUtils - SQL Query Builder
2 3
Replies
45
Views
13K
Mashiane
  • Question
Android Question SQLite queries (date syntax)
Replies
12
Views
2K
rbsoft
  • Locked
  • Article
Android Tutorial Remote Database Connector (RDC) - Connect to any remote DB
20 21 22
Replies
423
Views
334K
Erel