5 Replies Latest reply on Sep 9, 2008 3:39 PM by JasonMH

    Inserting a date into the SQLite database...

    photogas1578 Level 1
      I am having problems inserting a date into an sqlite database. Heres what I am doing:

      //Setup a connection
      var conn:SQLConnection = new SQLConnection();
      var dbFile:File = File.applicationDirectory.resolvePath("somedb.rsd");
      conn.open(dbFile);

      //Create a statement
      var stmt:SQLStatement = new SQLStatement;
      stmt.sqlConnection = conn;

      //Begin the insert
      conn.begin();

      stmt.text = "INSERT INTO SomeTbl (bdate) VALUES ('2008-03-04')";
      try
      {
      stmt.execute();
      conn.commit();
      }
      catch(error:SQLError)
      {
      trace("Error Message: ", error.message);
      conn.rollback();
      }

      //Close whatever needs to be closed and so on...

      A record gets inserted into the database just fine but the data in the date field is:
      2454529.5
      rather then
      2008-03-04

      The odd thing is when I just do an insert in SQLite Manager extension for FireFox I get the expected output:
      2008-03-04

      Only when I am doing an insert within the AS of the AIR App do I get the numbers. I know it must be something simple I am over looking but have tried everything I can think of. Any ideas out there?

      Bob

      *edit* I know I shouldn't use applicationDirectory for storing anything, just did that for testing. I will change it but that isn't the issue here ;)
        • 1. Re: Inserting a date into the SQLite database...
          photogas1578 Level 1
          Yep, I was right, it was something easy. AS stores the dates in Julian date format and I guess there is nothing I can do about that?

          So when doing a select out of the database I just had to do a strftime('%Y-%m-%d', somedate) to get the format I wanted. Had to search all over to find that though.
          • 2. Re: Inserting a date into the SQLite database...
            adobe_paul Adobe Employee
            As you noted, the date is stored in Julian date format.

            Although you don't show it, I'm assuming in your CREATE TABLE statement you defined the bdate column's data type as Date. SQLite by itself doesn't include a date affinity. In AIR, the Date affinity is intended for use with ActionScript or JavaScript Date objects. So to make it work as intended, change your SQL text and use a parameter to set the value, like this:

            stmt.text = "INSERT INTO SomeTbl (bdate) VALUES (:bdate)";
            stmt.parameters[":bdate"] = new Date(2008, 2, 4);

            If you want to store and retrieve dates as literal string values for some reason, you'll want to declare the column as TEXT/String and the date will be stored as text. I'm assuming that this is what the SQLite manager extension for Firefox does. AIR's implementation of data types is a bit different than "normal" SQLite (it's more in line with other databases like MySQL, Oracle, SQL Server, etc.).
            • 3. Re: Inserting a date into the SQLite database...
              Douglas McCarroll Level 1
              Is there any way to do this - i.e. insert a date value into a column with a Date data type - without using SQLStatement.parameters()? My application uses an architecture that creates SQL strings several layers before the point at which it creates SQLStatement instances.

              I suspect that SQLStatement is simply converting the Date object to a specific format under the hood, and adding it to the SQL string. If so, would it work to have my SQL string assembly code do this formatting, and include the date info in the SQL string that will (eventually) get passed into SQLStatement?

              Or am I limited to these two choices?:
              - Refactor my architecture
              - Define the column as a String data type
              • 4. Re: Inserting a date into the SQLite database...
                NeilHuy
                Did you get a solution to this Douglas? I'm using PureMVC with a delegate for the sql stuff and I can't use parameters.
                Is there any way forward?

                Thanks,

                Neil
                • 5. Re: Inserting a date into the SQLite database...
                  JasonMH Level 1
                  Here's some code to insert Julian dates without relying on parameters. Scroll down past the rambling to where it says "Inserting a Julian date via AIR (without parameters)"

                  http://www.verysimple.com/blog/2008/09/09/working-with-dates-in-flex-air-and-sqlite/

                  Not sure how PureMVC works, I'm using AIR Cairngorm and it expects the sql as a string. I thought about adding some functionality for parameters, though.