Android Question SQL - no rows in resultset with ExecQuery2

[Alexander Stolte]

Hello,

with this i have 0 results

Dim DR As ResultSet = Main.content_sql.ExecQuery2("SELECT * FROM dt_content WHERE id = ?;",Array As String(c_id))

with this i have results

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]

I'm not a query expert by any means, but maybe the format needs to be something like this when using an array:

SELECT * FROM myTable WHERE title IN ($newarray);

 

[mangojack]

While my queries do not contain a semicolon after the question mark ... your first query produced a valid result from my db table.

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.

 

[Mahares]

Although you do not need the semi colon and it is ok to leave it, both statements are identical and are correct.

 

[rosippc64a]

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?

 

[Alexander Stolte]

i have made a test, i have packed both variants with each other, swapped them and you can always see "here2" in the log.

    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

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

 

[Alexander Stolte]

i have removed the semicolons once but the result remains the same. i put a sleep(0) in between and renamed the resultset. The result remains the same...

 

[JohnC]

What happens if you try adding another value to the array:

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

 

[JohnC]

Also, what is the database date type for the field "id"?

 

[klaus]

Have you tried this?

Dim DR As ResultSet = Main.content_sql.ExecQuery2("SELECT * FROM dt_content WHERE id = ?", Array As Object(c_id))

Replace String by Object.

 

[Alexander Stolte]

Replace String by Object.

This is not allowed:

java.lang.IllegalArgumentException: method anywheresoftware.b4a.sql.SQL.ExecQuery2 argument 2 has type java.lang.String[], got java.lang.Object[]

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"?

"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:

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.

 

[Alexander Stolte]

I got the bug!

 

[Alexander Stolte]

The primary key of "id" was the problem.

DBUtils.CreateTable(Main.content_sql,"dt_content",CreateMap("id":DBUtils.DB_INTEGER),"id")

without the primary key:

DBUtils.CreateTable(Main.content_sql,"dt_content",CreateMap("id":DBUtils.DB_INTEGER),"")

it works...

 

[JohnC]

And "c_id" is int.

I know c_id is, but I wanted to know what "id" is in the database.

 

[Alexander Stolte]

It seems to be a bug in DBUtils2, because when I do that:

Main.content_sql.ExecNonQuery("CREATE TABLE dt_content (id INT PRIMARY KEY)")

it works too.

 

[JohnC]

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.

 

[Alexander Stolte]

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!

 

[Mahares]

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.