Hi,
I have noticed weird error:
SQLError: ‘Error #3132: Data type mismatch’,details:”,operation:’execute’
this only happens in certain situations, this is how you can reproduce it:
Tables:
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 | CREATE TABLE [table_a] ([id] INTEGER NOT NULL PRIMARY KEY,[name] VARCHAR(256) NOT NULL,[address_id] INTEGER DEFAULT '-1' NULL);CREATE TABLE [table_b] ([id] INTEGER NOT NULL PRIMARY KEY,[town] VARCHAR(256) NOT NULL);CREATE TABLE [table_c] ([id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,[phone_number] VARCHAR(256) DEFAULT 'Not Set' NOT NULL,[person_id] INTEGER DEFAULT '-1' NOT NULL); |
Data:
01 02 03 04 05 06 07 08 09 10 11 | INSERT INTO table_b ('town') VALUES ("buffalo");INSERT INTO table_b ('town') VALUES ("ipswitch");INSERT INTO table_b ('town') VALUES ("pcim");INSERT INTO table_a ('name','address_id') VALUES ("john",1);INSERT INTO table_a ('name','address_id') VALUES ("charles",2);INSERT INTO table_a ('name','address_id') VALUES ("edek",3);INSERT INTO table_c ('phone_number','person_id') VALUES("+1 0123456789",1);INSERT INTO table_c ('phone_number','person_id') VALUES("+44 9876543210",2);INSERT INTO table_c ('phone_number','person_id') VALUES("+48 0011223344",3); |
Code that works without the error first
01 02 03 04 05 06 07 08 09 10 11 12 | var query1:SQLStatement = new SQLStatement();query1.sqlConnection = db;query1.text = "SELECT * FROM table_a AS a, table_b AS b, table_c AS c WHERE a.address_id=:add_id AND c.person_id=:per_id";query1.parameters[":add_id"] = "b.id";query1.parameters[":per_id"] = "a.id";query1.addEventListener(SQLErrorEvent.ERROR, onQueryError, false, 0, true);query1.addEventListener(SQLEvent.RESULT, onQueryResult, false, 0, true);query1.execute(); |
Now code that generates aforementioned error (notice change in order of assignments) :
01 02 03 04 05 06 07 08 09 10 11 | var query1:SQLStatement = new SQLStatement();query1.sqlConnection = db;query1.text = "SELECT * FROM table_a AS a, table_b AS b, table_c AS c WHERE a.address_id=:add_id AND a.id=:per_id";query1.parameters[":add_id"] = "b.id";query1.parameters[":per_id"] = "c.person_id";query1.addEventListener(SQLErrorEvent.ERROR, onQueryError, false, 0, true);query1.addEventListener(SQLEvent.RESULT, onQueryResult, false, 0, true);query1.execute(); |
Now, this can be fixed either by reposition the column in WHERE clause or use OR instead! following is modified above code with OR instead of AND
01 02 03 04 05 06 07 08 09 10 11 | var query1:SQLStatement = new SQLStatement();query1.sqlConnection = db;query1.text = "SELECT * FROM table_a AS a, table_b AS b, table_c AS c WHERE a.address_id=:add_id OR a.id=:per_id";query1.parameters[":add_id"] = "b.id";query1.parameters[":per_id"] = "c.person_id";query1.addEventListener(SQLErrorEvent.ERROR, onQueryError, false, 0, true);query1.addEventListener(SQLEvent.RESULT, onQueryResult, false, 0, true);query1.execute(); |
the third method, to avoid this error, is actually to put it all in a string passed to text property of SQLStatement:
1 | query1.text = "SELECT * FROM table_a AS a, table_b AS b, table_c AS c WHERE a.address_id=b.id AND a.id=c.person_id"; |
of course by changing logical operator to OR we are changing results but no error is thrown.
Now is it a bug or feature? If anyone has an answer please let me know.
I have put this query on adobe forum: forums.adobe.com, but still no answer.
I have a similar issue. I went through my SQLIte and figured that the in my select statement i am trying to compare two values (a.id = b.id) where a.id was declared as Integer in one table whereas the b.id was not declared as anything.
Will correct that and see what I get and will post it here..
Did you ever find an answer to this question. I have the same problem.
I’m afraid not, but try one of the solutions presented in post, eg. transposing columns in query