Bug? ExecQueryAsync with "?"

[Emme Developer]

Hi! I've this 3 queries, that should send the same values. But i don't know why, if i use "?" i don't get the right value

Right value is 3

Log: 0

Dim SenderFilter As Object = Utils.sql.ExecQueryAsync("SQL","Select count(*) from scheduled where id = ?",Array as Int(1))

Log: 0

Dim SenderFilter As Object = Utils.sql.ExecQueryAsync("SQL","Select count(*) from scheduled where id = ?",Array(1))

Log: 3

Dim SenderFilter As Object = Utils.sql.ExecQueryAsync("SQL","Select count(*) from scheduled where id = "&1,null)

​

Log: 3

Utils.sql.ExecQuerySingleResult2("Select count(*) from scheduled where id = "&idnotifica,Null)

Log: 0

Utils.sql.ExecQuerySingleResult2("Select count(*) from scheduled where id = ?",array(idnotifica))

​

I don't understand some thing or it is wrong?​

 

[Erel]

Can you upload a project with the database that reproduces this issue?

 

[Emme Developer]

Can you upload a project with the database that reproduces this issue?

I try to reproduce it in a small project

 

[Emme Developer]

@Erel
Attacched

 

[Erel]

It has something to do with the way you created the tables.

This code produces the correct results:

Utils.sql.ExecNonQuery("CREATE TABLE scheduled2 (id INTEGER, starttime INTEGER)")
For i = 0 To 3
   Utils.sql.ExecNonQuery($"INSERT INTO scheduled2 (id, starttime) VALUES (${1}, ${150000000+i})"$)
Next
Log(Utils.sql.ExecQuerySingleResult2("Select count(*) from scheduled2 where id = " & 1,Null) '4
Log(Utils.sql.ExecQuerySingleResult2("Select count(*) from scheduled2 where id = ?",Array As String(1))) '4

You should make sure that the id column type is INTEGER as you are adding numeric values. It is also possible that you created the database with a SQLite version newer than the OS version.

The queries are passed as-is to the native API.

 

[Emme Developer]

It has something to do with the way you created the tables.

This code produces the correct results:

Utils.sql.ExecNonQuery("CREATE TABLE scheduled2 (id INTEGER, starttime INTEGER)")
For i = 0 To 3
   Utils.sql.ExecNonQuery($"INSERT INTO scheduled2 (id, starttime) VALUES (${1}, ${150000000+i})"$)
Next
Log(Utils.sql.ExecQuerySingleResult2("Select count(*) from scheduled2 where id = " & 1,Null) '4
Log(Utils.sql.ExecQuerySingleResult2("Select count(*) from scheduled2 where id = ?",Array As String(1))) '4

You should make sure that the id column type is INTEGER as you are adding numeric values. It is also possible that you created the database with a SQLite version newer than the OS version.

The queries are passed as-is to the native API.

I'm sure that id is integer, as ID is primary key with autoincrement option. To create the database i use SQLiteStudio 3.1.0, i don't know the version of SQLite is newer that OS version (i'm developing in Android 7.0 on Samsung Galaxy S6 Edge with custom rom). This is the table in SQLiteStudio

Scheduled.id is a foreign key of notification.id. If i try to export database, this is the query i got from software

Spoiler: Query

--
-- File generated with SQLiteStudio v3.1.0 on lun ott 30 16:44:07 2017
--
-- Text encoding used: System
--
PRAGMA foreign_keys = off;
BEGIN TRANSACTION;

-- Table: notification
CREATE TABLE notification (
    id      INTEGER     PRIMARY KEY AUTOINCREMENT,
    enabled INTEGER (1) DEFAULT (1)
                        NOT NULL,
    idrete  INTEGER     NOT NULL
                        DEFAULT (0),
    tipo    TEXT        NOT NULL
                        DEFAULT "",
    text    TEXT        NOT NULL
                        DEFAULT "",
    ring    TEXT        NOT NULL
                        DEFAULT "",
    custom  TEXT        NOT NULL
                        DEFAULT ""
);


-- Table: scheduled
CREATE TABLE scheduled (
    id     INTEGER    REFERENCES notification (id) ON UPDATE CASCADE
                                                   MATCH [FULL]
                      NOT NULL,
    starttime INTEGER NOT NULL
                      DEFAULT (0),
    idtemp    INTEGER PRIMARY KEY AUTOINCREMENT
);


COMMIT TRANSACTION;
PRAGMA foreign_keys = on;

 

[Erel]

You can see in the screenshot that the data type is empty. This is why it doesn't treat the string "1" correctly. All bound parameters in Android are set as strings.

Try to create the table on the Android and see whether the type is set properly.

 

[Emme Developer]

You can see in the screenshot that the data type is empty. This is why it doesn't treat the string "1" correctly. All bound parameters in Android are set as strings.

Try to create the table on the Android and see whether the type is set properly.

Tried using exported query to make db, and it works. I tried also to reset the integer type in scheduled.id and now it works. Maybe there is a bug in SQLiteStudio when it create the foreing key. I will send a report to them, many thanks for help