SQLite data types

After searching the web for a clear list of the data types allowed in SQLite with Adobe AIR I found this link:  http://livedocs.adobe.com/flex/3/langref/localDatabaseSQLSupport.html#dataTypes (from the flex 3 docs – I hope it gets updated for AIR 2.0)
It has information on the types allowed.

Here are the main points:

The following column affinity types are not supported by default in SQLite, but are supported in Adobe AIR:

  • STRING: corresponding to the String class (equivalent to the TEXT column affinity).
  • NUMBER: corresponding to the Number class (equivalent to the REAL column affinity).
  • BOOLEAN: corresponding to the Boolean class.
  • DATE: corresponding to the Date class.
  • XML: corresponding to the ActionScript (E4X) XML class.
  • XMLLIST: corresponding to the ActionScript (E4X) XMLList class.
  • OBJECT: corresponding to the Object class or any subclass that can be serialized and deserialized using AMF3. (This includes most classes including custom classes, but excludes some classes including display objects and objects that include display objects as properties.)

The following literal values are not supported by default in SQLite, but are supported in Adobe AIR:

  • true: used to represent the literal boolean value true, for working with BOOLEAN columns.
  • false: used to represent the literal boolean value false, for working with BOOLEAN columns.

Each column in the database is assigned one of the following type affinities:

  • TEXT (or STRING)
  • REAL (or NUMBER)
  • DATE
  • XML
  • NONE

For a full description of the above see the docs: http://livedocs.adobe.com/flex/3/langref/localDatabaseSQLSupport.html#dataTypes

I’m quite excited that you can store an ActionScript Object in a table; I’ll have to do some experiments to see how good this is.

This entry was posted on Monday, November 30th, 2009 at 12:56 pm and is filed under Air, SQLite. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

6 Comments so far

  1. Hi,

    Were you able to perform any experiments on storing actionscript objects in SQLite tables and retrieving them? If yes were you successful?

  2. No I haven’t had a chance to store a custom object but I am using Date objects in a current project which is saving a lot of time

  3. […] CV « SQLite data types […]

  4. I’ve posted an example of storing ActionScrip Objects in SQLite: http://samhassan.co.uk/blog/2010/01/20/storing-an-actionscript-object-class-in-sqlite-with-air/

  5. Hi! Thanks very much for this post, which helped me a lot. But I am still stuck with this. The custom class I have been using extends Sprite rather than Object and when I use the above solution I get the following error messages:

    TypeError: Error #1034: Type Coercion failed: cannot convert Object@14911cb9 to flash.media.SoundTransform.
    TypeError: Error #1034: Type Coercion failed: cannot convert Object@14911221 to flash.geom.Transform.

    I seem to get two of these for every row in the database (quite why it refers to soundTransform and Transform, I don’t know since I am not using either of these).

    I tried simply turning your Custom Class DataVO into extends Sprite to see what would happen (you have to make one more change because the name property then clashes). When you run it you get exactly the same error messages as above. I am guessing therefore that the register alias thing doesn’t work for Sprite????

    I am scratching my head over this and wondered if you had any advice?

  6. Hi Laurence,

    The Sprite class has properties: soundTransform and transform which are of the type flash.media.SoundTransform and flash.geom.Transform.
    This goes some way to explaining the type errors you’re getting.
    As a guess it may not be interpreting the object as a sprite correctly, try tracing whats inside the object that’s returned and see wat you get. If you’re not already make sure you specify the type it should be ie: var newItem : MyCustomClass = MyCustomClass(returnedObj) where the returnedObj is whats coming back from the database.
    ** Have you you seen my post about using registerClassAlias: http://samhassan.co.uk/2010/01/20/storing-an-actionscript-object-class-in-sqlite-with-air/

    One possibility is that registerClassAlias doesn’t work with Sprite or other complex classes, you could try using the itemClass property of SQLStatement class, it’s probably a better way or storing custom data types in SQLite but I havent tried it myself.
    Have a look at the documentation here: http://help.adobe.com/en_US/FlashPlatform/reference/actionscript/3/flash/data/SQLStatement.html#itemClass

    Hope that helps.