Android Question SQL - no rows in resultset with ExecQuery2

Alexander Stolte

Expert
Licensed User
Hello,

with this i have 0 results
B4X:
Dim DR As ResultSet = Main.content_sql.ExecQuery2("SELECT * FROM dt_content WHERE id = ?;",Array As String(c_id))
with this i have results
B4X:
Dim DR As ResultSet = Main.content_sql.ExecQuery("SELECT * FROM dt_content WHERE id = " & c_id & ";")

c_id is a integer.

did someone else have the same problem? or am I doing something wrong?
I've tried to reproduce it in a sample program, but it works...
 

JohnC

Expert
Licensed User
Longtime User
I'm not a query expert by any means, but maybe the format needs to be something like this when using an array:
B4X:
SELECT * FROM myTable WHERE title IN ($newarray);
 
Upvote 0

mangojack

Well-Known Member
Licensed User
Longtime User
While my queries do not contain a semicolon after the question mark ... your first query produced a valid result from my db table.

B4X:
res = Starter.oSQL.ExecQuery2("SELECT * FROM  tbl_Items WHERE Id = ?;", Array As String(c_id)) 'c_id = Int

but along with @JohnC I am no expert here as well.
 
Upvote 0

rosippc64a

Active Member
Licensed User
Longtime User
cases like this I try to debug exact value of c_id and examine the content of the table dt_content (also table structure).
can you show them? Is it sqlite db?
 
Upvote 0

Alexander Stolte

Expert
Licensed User
i have made a test, i have packed both variants with each other, swapped them and you can always see "here2" in the log.
B4X:
    Dim DR As ResultSet = Main.content_sql.ExecQuery("SELECT * FROM dt_content WHERE id = " & c_id & ";")
    Do While DR.NextRow
        Log("here2")
    Loop
    DR.Close
       
    Dim DR As ResultSet = Main.content_sql.ExecQuery2("SELECT * FROM dt_content WHERE id = ?;",Array As String(c_id))
    Do While DR.NextRow
        Log("here")
    Loop
    DR.Close
Log:
here2

I don't understand it at all, I already spent 2 hours on Friday trying to find a solution. In a test program everything works perfectly.

In my case "dt_content" currently contains only one row, but the query should still work, no matter how many rows are in my table...
 
Upvote 0

JohnC

Expert
Licensed User
Longtime User
What happens if you try adding another value to the array:

B4X:
Dim DR As ResultSet = Main.content_sql.ExecQuery2("SELECT * FROM dt_content WHERE id = ?",Array As String(c_id,3))
 
Last edited:
Upvote 0

JohnC

Expert
Licensed User
Longtime User
Also, what is the database date type for the field "id"?
 
Upvote 0

Alexander Stolte

Expert
Licensed User
Replace String by Object.
This is not allowed:
B4X:
java.lang.IllegalArgumentException: method anywheresoftware.b4a.sql.SQL.ExecQuery2 argument 2 has type java.lang.String[], got java.lang.Object[]
B4X:
Dim DR2 As ResultSet = Main.content_sql.ExecQuery2("SELECT * FROM dt_content WHERE id = ?",Array As Object(c_id))

Also, what is the database date type for the field "id"?
B4X:
"id":DBUtils.DB_INTEGER

What happens if you try adding another value to the array:
the same...
but if i add a "OR id = 39" 39 is the id i see in the logs for the row i want, then i get a result:
B4X:
Log(c_id) 'output: 39
Dim DR2 As ResultSet = Main.content_sql.ExecQuery2("SELECT * FROM dt_content WHERE id = ? OR id = 39",Array As String(c_id))
Do While DR2.NextRow
    Log("here")
Loop
DR2.Close
The output of Log(c_id) is 39.

And "c_id" is int.
 
Upvote 0

Alexander Stolte

Expert
Licensed User
The primary key of "id" was the problem.
B4X:
DBUtils.CreateTable(Main.content_sql,"dt_content",CreateMap("id":DBUtils.DB_INTEGER),"id")

without the primary key:
B4X:
DBUtils.CreateTable(Main.content_sql,"dt_content",CreateMap("id":DBUtils.DB_INTEGER),"")
it works...
 
Upvote 0

JohnC

Expert
Licensed User
Longtime User
Upvote 0

JohnC

Expert
Licensed User
Longtime User
When you look at the pop-up help for Sql.ExecQuery2 it says that sql lite tries to convert the arguments passed in the string array into the applicable database data type for the ? query field type, and I was thinking that it was not converting the type right, and that's why the query was failing.
 
Upvote 0

Alexander Stolte

Expert
Licensed User
When you look at the pop-up help for Sql.ExecQuery2 it says that sql lite tries to convert the arguments passed in the string array into the applicable database data type for the ?
both INT

it was not the primary key, it must be the "CreateTable" function of DBUtils2, because now that I create everything by hand everything works.

Thanks for all the help!
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
It seems to be a bug in DBUtils2, because when I do that:
Although I do not use DBUtils2 when I create my tables, there is no bug in DBUtils2 because when you set a field to id INTEGER PRIMARY KEY and insert a record in the table, it automatically inserts the id value because the id field is an alias for the rowid. It will not keep it blank and cannot be blank. So, for the first record the id is 1 and so on as it gets in most cases incremented for the subsequent records. When the id is not a primary key, you can leave it blank.
 
Upvote 0
Top