SQLError: ‘Error #3132: Data type mismatch’,details:”,operation:’execute’

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:

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:

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

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

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

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:

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.

3 thoughts on “SQLError: ‘Error #3132: Data type mismatch’,details:”,operation:’execute’

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