Basic problem with where clauses in SQL queries

[isr]

I'm having trouble executing any SQL query that has a WHERE clause in it. I have two years of experience with SQL, but I can't see where the problem is in my B4A code. I've searched the documentation and forums, and don't see any other reports of this basic difficulty.

In short, I can perform SELECT, INSERT, DROP, and CREATE TABLE queries with no problem (and probably other types of queries, but I haven't tried them yet in B4A) if I do not use a WHERE clause. However, when I use a WHERE clause, the typical error I get for the code below is "no such column: curruserid".

Here's the simplified code that illustrates my problem:

Sub Process_Globals
   Dim SQL1 As SQL
   Dim readqry As Cursor
   Dim username(40) As String
   Dim userid(40) As Int
   Dim currname As String
   Dim curruserid As Int
End Sub

Sub Globals
End Sub

Sub Activity_Create(FirstTime As Boolean)
   If FirstTime Then
      SQL1.Initialize(File.DirInternal, "testdelete.db", True)
   End If
   

   'drop query needed for repeated running of app because delete query doesn't work
SQL1.ExecNonQuery("DROP TABLE Users")

   SQL1.ExecNonQuery("CREATE TABLE IF NOT EXISTS Users(userid INTEGER PRIMARY KEY,name TEXT)")   

   curruserid=4
   currname="John"

   'insert query
   SQL1.ExecNonQuery2("INSERT INTO Users Values (?,?)", Array As Object(curruserid, currname))

   'select all query, before delete
   readqry = SQL1.ExecQuery("SELECT userid, name FROM Users")
   For i=1 To readqry.RowCount
      readqry.Position = i-1
      username(i)=readqry.GetString("name")
      userid(i)=readqry.GetInt("userid")
      Log("before delete: name = " & username(i) & " userid = " & userid(i))
   Next
   
   'delete query
   SQL1.ExecNonQuery("DELETE FROM Users WHERE name='currname'")

   'select query with where clause
   readqry = SQL1.ExecQuery("SELECT userid, name FROM Users WHERE userid=curruserid")
   For i=1 To readqry.RowCount
      readqry.Position = i-1
      username(i)=readqry.GetString("name")'
      userid(i)=readqry.GetInt("userid")
      Log("Select query with where clause: name = " & username(i) & " userid = " & userid(i))
   Next

   'select all query, after delete
   readqry = SQL1.ExecQuery("SELECT userid, name FROM Users")
   For i=1 To readqry.RowCount
      readqry.Position = i-1
      username(i)=readqry.GetString("name")
      userid(i)=readqry.GetInt("userid")
      Log("after delete: name = " & username(i) & " userid = " & userid(i))
   Next

End Sub   

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)   
   Activity.Finish
End Sub

I have been able to get queries with WHERE clauses to work when I set the WHERE condition to an actual, existing value, such as "WHERE name = 'John'".

I guess I am missing something very basic, but I don't know what it is. I would be very grateful for any advice.

Thank you!

 

[Merlot2309]

Hello,

Use SQL1.BeginTransaction and EndTransaction in insert, update, etc. queries.

The ExecQuery2 should do the trick.
SQL1.ExecQuery2("SELECT userid, name FROM Users WHERE userid= ?", Array as String(curruserid))

You probably also can change the .RowCount lines to
For i=0 To readqry.RowCount -1
readqry.Position = i

Succes,
Helen.

 

[klaus]

Or use this line:

readqry = SQL1.ExecQuery("SELECT userid, name FROM Users WHERE userid = " & curruserid)

curruserid is a variable.

Best regards.

 

[isr]

Thank you, Helen and Klaus. I have tried both of your solutions and they work, of course.

For Klaus' approach, what should I do when the WHERE condition involves a string variable? For instance,

readqry = SQL1.ExecQuery("SELECT userid, name FROM Users WHERE name = " & currname)

produces the same error as I received before ("no such column: currname") (currname is a string variable).

Thanks again for your help!

 

[Mahares]

Did you try:

readqry = SQL1.ExecQuery("SELECT userid, name FROM Users WHERE name = '" & currname & "'")

Explanation:single quote double quote & currname & double quote single quote double quote

 

[isr]

Thank you, Mahares, that works perfectly! I had tried single and double quotes around the string variable name, but not in the combination you indicated.