{"id":279,"date":"2011-02-21T23:09:40","date_gmt":"2011-02-21T23:09:40","guid":{"rendered":"https:\/\/greladesign.co\/blog\/?p=279"},"modified":"2011-09-15T09:25:01","modified_gmt":"2011-09-15T08:25:01","slug":"sqlerror-error-3132-data-type-mismatchdetailsoperationexecute","status":"publish","type":"post","link":"https:\/\/greladesign.co\/blog\/2011\/02\/21\/sqlerror-error-3132-data-type-mismatchdetailsoperationexecute\/","title":{"rendered":"SQLError: ‘Error #3132: Data type mismatch’,details:”,operation:’execute’"},"content":{"rendered":"

Hi,
\nI have noticed weird error:<\/p>\n

SQLError: ‘Error #3132: Data type mismatch’,details:”,operation:’execute’<\/p><\/blockquote>\n

this only happens in certain situations, this is how you can reproduce it:
\n
\nTables:<\/strong><\/p>\n

\r\nCREATE TABLE [table_a] (\r\n[id] INTEGER  NOT NULL PRIMARY KEY,\r\n[name] VARCHAR(256)  NOT NULL,\r\n[address_id] INTEGER DEFAULT '-1' NULL\r\n);\r\n\r\nCREATE TABLE [table_b] (\r\n[id] INTEGER  NOT NULL PRIMARY KEY,\r\n[town] VARCHAR(256)  NOT NULL\r\n);\r\n\r\nCREATE TABLE [table_c] (\r\n[id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,\r\n[phone_number] VARCHAR(256) DEFAULT 'Not Set' NOT NULL,\r\n[person_id] INTEGER DEFAULT '-1' NOT NULL\r\n);\r\n<\/pre>\n
Data:<\/strong><\/p>\n
\r\nINSERT INTO table_b ('town') VALUES ("buffalo");\r\nINSERT INTO table_b ('town') VALUES ("ipswitch");\r\nINSERT INTO table_b ('town') VALUES ("pcim");\r\n\r\nINSERT INTO table_a ('name','address_id') VALUES ("john",1);\r\nINSERT INTO table_a ('name','address_id') VALUES ("charles",2);\r\nINSERT INTO table_a ('name','address_id') VALUES ("edek",3);\r\n\r\nINSERT INTO table_c ('phone_number','person_id') VALUES("+1 0123456789",1);\r\nINSERT INTO table_c ('phone_number','person_id') VALUES("+44 9876543210",2);\r\nINSERT INTO table_c ('phone_number','person_id') VALUES("+48 0011223344",3);\r\n<\/pre>\n
Code that works without the error first<\/p>\n
\r\nvar query1:SQLStatement = new SQLStatement();\r\nquery1.sqlConnection = db;\r\n\r\nquery1.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";\r\n\r\nquery1.parameters[":add_id"] = "b.id";\r\nquery1.parameters[":per_id"] = "a.id";\r\n\r\n\r\nquery1.addEventListener(SQLErrorEvent.ERROR, onQueryError, false, 0, true);\r\nquery1.addEventListener(SQLEvent.RESULT, onQueryResult, false, 0, true);\r\nquery1.execute();\r\n<\/pre>\n
Now code that generates aforementioned error (notice change in order of assignments) :<\/p>\n
\r\nvar query1:SQLStatement = new SQLStatement();\r\nquery1.sqlConnection = db;\r\nquery1.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";\r\n\r\nquery1.parameters[":add_id"] = "b.id";\r\nquery1.parameters[":per_id"] = "c.person_id";\r\n\r\n\r\nquery1.addEventListener(SQLErrorEvent.ERROR, onQueryError, false, 0, true);\r\nquery1.addEventListener(SQLEvent.RESULT, onQueryResult, false, 0, true);\r\nquery1.execute();\r\n<\/pre>\n
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<\/p>\n
\r\nvar query1:SQLStatement = new SQLStatement();\r\nquery1.sqlConnection = db;\r\nquery1.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";\r\n\r\nquery1.parameters[":add_id"] = "b.id";\r\nquery1.parameters[":per_id"] = "c.person_id";\r\n\r\n\r\nquery1.addEventListener(SQLErrorEvent.ERROR, onQueryError, false, 0, true);\r\nquery1.addEventListener(SQLEvent.RESULT, onQueryResult, false, 0, true);\r\nquery1.execute();\r\n<\/pre>\n
the third method, to avoid this error, is actually to put it all in a string passed to text<\/em> property of SQLStatement<\/em>:<\/p>\n
\r\nquery1.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";\r\n<\/pre>\n
of course by changing logical operator to OR we are changing results but no error is thrown.<\/p>\n
Now is it a bug or feature? If anyone has an answer please let me know.<\/p>\n
I have put this query on adobe forum: forums.adobe.com<\/a>, but still no answer.<\/p>\n","protected":false},"excerpt":{"rendered":"
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:<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":""},"categories":[17,72,9,58],"tags":[12,85,84,51,54,86],"_links":{"self":[{"href":"https:\/\/greladesign.co\/blog\/wp-json\/wp\/v2\/posts\/279"}],"collection":[{"href":"https:\/\/greladesign.co\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/greladesign.co\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/greladesign.co\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/greladesign.co\/blog\/wp-json\/wp\/v2\/comments?post=279"}],"version-history":[{"count":22,"href":"https:\/\/greladesign.co\/blog\/wp-json\/wp\/v2\/posts\/279\/revisions"}],"predecessor-version":[{"id":412,"href":"https:\/\/greladesign.co\/blog\/wp-json\/wp\/v2\/posts\/279\/revisions\/412"}],"wp:attachment":[{"href":"https:\/\/greladesign.co\/blog\/wp-json\/wp\/v2\/media?parent=279"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/greladesign.co\/blog\/wp-json\/wp\/v2\/categories?post=279"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/greladesign.co\/blog\/wp-json\/wp\/v2\/tags?post=279"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}