Hi,<\/p>\n
this is a quick post describing issue I have found when using SQLite in flash. I’ve tried to fetch some rows using following snippet, but couldn’t get correct results. Then after digging on the net I have found that there is a different syntax to compare null values. The problem was in comparing values that sometimes can be null<\/em>. <\/p>\n The idea was to get how many empty values I have in the database, the result was always 0. I have start with plain text query (no parameters), but it didn’t worked as I’ve found on the SQLite page you can’t just use equal to compare NULL<\/em> values, you have special syntax IS NULL or IS NOT NULL.<\/p>\n test snippet (simplified).<\/p>\n so this will not work, to actually have it working and return null or not null values in column b, you have to use proper syntax for comparing NULL<\/em> values, revised query:<\/p>\n now it will return what was expected, but this doesn’t solve the problem, because you may found that your query doesn’t return expected values, this will happen in queries where parameters<\/em> object is used to pass values to the query. <\/p>\n The example above was much simplified, normally you will use the parameters<\/em> object of the SQLStatement instance, as follows.<\/p>\n This will not return expected row (real life example of problem with comparing null values).<\/p>\n as you can see we have a query that accepts any parameter values, including null and because just comparing null value requires different syntax you may find this query to return unexpected results (if any) when either of parameters will be null.<\/p>\n The solution or workaround is to not to allow in database design for null values but some defaults instead i.e. “Not set” etc. and use this values in the query, sometimes it is not possible to change database schema, but always you can change the query:)<\/p>\n you can improve above simple example by adding logic branching and checking for null parameter:<\/p>\n It works but only for this simple case, it will be much harder for variable number of parameters where some or all can have null values. To actually make it useful one needs to built some classes that will support dynamically created SQLStatements for which this behaviour will be done automatically, i.e.<\/p>\n and in the hypothetical SelectQuery class:<\/p>\n Happy coding:)<\/p>\n","protected":false},"excerpt":{"rendered":" Hi, this is a quick post describing issue I have found when using SQLite in flash. I’ve tried to fetch some rows using following snippet, but couldn’t get correct results. Then after digging on the net I have found that … Continue reading
\nFirst sample data:<\/p>\n\r\n-- Create a test table with data\r\ncreate table t1(a int, b varchar(8), c int);\r\ninsert into t1 values(1,0,0);\r\ninsert into t1 values(2,0,1);\r\ninsert into t1 values(3,1,0);\r\ninsert into t1 values(4,1,1);\r\ninsert into t1 values(5,null,0);\r\ninsert into t1 values(6,null,1);\r\ninsert into t1 values(7,null,null);\r\n<\/pre>\n
\r\nvar statement:SQLStatement = new SQLStatement();\r\nstatement.sqlConnection = m_oConnection;\r\nstatement.text = "SELECT * FROM t1 WHERE b=NULL";\r\n<\/pre>\n
\r\nvar statement:SQLStatement = new SQLStatement();\r\nstatement.sqlConnection = m_oConnection;\r\nstatement.text = "SELECT * FROM t1 WHERE b IS NULL";\/\/or statement.text = "SELECT * FROM t1 WHERE b IS NOT NULL";\r\n<\/pre>\n
\r\nvar compare_a:int= 6;\r\nvar compare_b:String = null;\r\nvar compare_c:int= 1;\r\n\/\/expected 1 row in result\r\nvar statement:SQLStatement = new SQLStatement();\r\nstatement.sqlConnection = m_oConnection;\r\nstatement.text = "SELECT * FROM t1 WHERE a = ? AND b = ? AND c = ?";\r\nstatement.parameters[0] = compare_a;\r\nstatement.parameters[1] = compare_b;\r\nstatement.parameters[2] = compare_c;\r\n<\/pre>\n
\r\nvar compare_a:int= 6;\r\nvar compare_b:String = null;\r\nvar compare_c:int= 1;\r\n\/\/expected 1 row in result\r\nvar statement:SQLStatement = new SQLStatement();\r\nstatement.sqlConnection = m_oConnection;\r\n\r\nif (compare_b != null)\r\n{\r\n\tstatement.text = "SELECT * FROM t1 WHERE a = ? AND b = ? AND c = ?";\r\n\r\n\tstatement.parameters[0] = compare_a;\r\n\tstatement.parameters[1] = compare_b;\r\n\tstatement.parameters[2] = compare_c;\r\n}\r\nelse\r\n{\r\n\tstatement.text = "SELECT * FROM t1 WHERE a = ? AND b IS NULL AND c = ?"\r\n\r\n\tstatement.parameters[0] = compare_a;\r\n\tstatement.parameters[1] = compare_c;\r\n}\r\n<\/pre>\n\r\nvar query:SelectQuery = new SelectQuery();\r\n\/\/\r\nquery.addFilter("a",6);\r\nquery.addFilter("b",null);\r\nquery.addFilter("c",1);\r\n\/\/\r\n<\/pre>\n\r\n\/**\r\n * Returns string for filtering the query (WHERE clause).\r\n *\/\r\nprotected function getFilters():String\r\n{\r\n\tvar _sWhere:String = "";\r\n\tif (m_aFilters.length == 0) return _sWhere;\r\n\t_sWhere = " WHERE ";\r\n\t\/\/m_aFilters is an array of \r\n \/\/Objects {column_name, compare_value}\r\n\tfor (var i:int = 0; i < m_aFilters.length; i++)\r\n\t{\r\n\t\tvar filter:Object = m_aFilters[i];\r\n\t\t_sWhere += filter.column_name;\r\n\t\t\r\n\t\tif (filter.compare_value == null)\r\n\t\t{\r\n\t\t\t_sWhere += " IS NULL ";\r\n\t\t}\r\n\t\telse\r\n\t\t{\r\n\t\t\t_sWhere += " = ? ";\r\n \/\/? will be used in parameters \r\n \/\/object;\r\n\t\t}\r\n\t\tif(i < m_aFilters.length-1) _sWhere += " AND ";\r\n \/\/for simplicity \r\n \/\/it will always group with AND\r\n\t}\r\n\treturn _sWhere;\r\n}\r\n\/\/calling this method with previous snippets data (addFilter), \r\n\/\/produces: WHERE a = ? AND b IS NULL AND c = ? \r\n<\/pre>\n