1 Reply Latest reply on Dec 18, 2008 1:46 AM by anirudhs

    SQLite strong data type issue

      I am having trouble overcoming strong data types on my SQLite insert statements. Whenever I have an Integer data type, the sqlStatement syntax must not be a literal value i.e.

      VALUES("500","some text") will throw and error - type mismatch

      but instead must be

      VALUES(500,"some text").

      This requires one to construct the SQL statements according to the known data type of each column in the targeted table. argh......

      Is there anyway to avoid evaluating the target table schema before being able to insert records ?

      If I construct my insert statements as text with a great deal of extra (unnecessary ?) work I can be successful however how would one overcome this strong typing issue using sqlStatement.parameters[] ?
      as in this tutorial ( http://www.peterelst.com/blog/2008/04/07/introduction-to-sqlite-in-adobe-air/)

      I am sure that Adobe engineers have good reasons from adding strong typing to SQLite but after using native version of SQLite with manifest typing I am at a lost to understand the "improvement" Adobe has made here.
        • 1. Re: SQLite strong data type issue
          anirudhs Level 2

          The recommended way is to use parameterized SQL statements. The advantage in this approach is built-in prevention of SQL injection attacks and type safety.

          When you are using parameters, you can simply assign the value to a parameters object:
          sqlStmt.text = "SELECT FROM employees WHERE first = ? and age > ?";
          sqlStmt.parameters[0] = "stringval";
          // no worry about converting 10 to a string in a specific format. Just put it as it is.
          sqlStmt.parameters[1] = 10;