AIR SQLite library updates

A couple of people have reported a bug in my AIR SQLite utility library. I also recently used it to help build a Robotlegs demo app for the 360|Flex Robotlegs training, and in the process I discovered a missing feature I needed (namely, the ability to get back the SQLResult objects after running a batch of statements using SQLRunner.executeModify()).

Warning! To add the missing feature I had to introduce a non-backwards-compatible api change. Read the details in the project history.

So, the bug is fixed, the feature is added, the version number is incremented (0.1.1 beta), and the code and SWC are live on Github.

Download the version 0.1.1 SWC

Read about the changes

In conjunction with the release I also added a couple of new examples to the project page, including a “bare bones” code only example for quick starters, and links to an example of using the library in a Robotlegs application (the example app from the 360|Flex training).

Enjoy, and as always feel free to report problems as issues in Github or in the comments on the project page.

You can leave a comment, or trackback from your own site.

5 Comments so far

1. May 8th, 2010 at 9:59 am
   rudi is reported to have said:

   Hi,
   I can not run correctly the new version. I’m trying to send to these two queries executeModify contemporary
   var sql: String = “UPDATE main.numeroOrdine set number = number +1, dataMod = DateTime (‘now’, ‘localtime’) = utenteMod: utenteMod WHERE date =: inizioSerata;
   var parameters: Object = new Object ();
   parameters.inizioSerata = inizioSerata;
   parameters.utenteMod modelPreferenze.getPreferenza = (modelPreferenze.UTENTELOGIN.) username;

   var SQL2: String = “SELECT * FROM WHERE date = main.numeroOrdine: inizioSerata;”
   parameters2 var: Object = new Object ();
   parameters2 = new Object ();
   parameters2.inizioSerata = inizioSerata;

   initially, for the first time, it works.
   but if I try to make the same request again no longer works.

   then, because in sqlrunner
   public function execute (sql: String, parameters: Object, handler: Function, itemClass: Class = null, data: Object = null): void
   function errorHandler no longer exists?

2. May 8th, 2010 at 10:36 am
   rudi is reported to have said:

   the problem seems to be more articulate:
   my first block of commands does one execute and then an executeModify, sometimes the same block is repeated 3 / 4 properly and then stops, sometimes I run the command first block correctly, the second time stops at the first execute, the third time the command stop to executeModify.
   I do not understand where the problem is!

3. May 8th, 2010 at 10:55 am
   rudi is reported to have said:

   this is the code:
   package mpc.app.ordini.command
   {

   import com.adobe.cairngorm.commands.ICommand;
   import com.adobe.cairngorm.control.CairngormEvent;

   import flash.data.SQLResult;
   import flash.display.Sprite;

   import mpc.app.ordini.business.InizializzaSerataDelegate;
   import mpc.app.ordini.business.IncrementaSerataDelegate;
   import mpc.app.ordini.business.VerificaSerataDelegate;
   import mpc.app.ordini.event.NewEvent;
   import mpc.app.ordini.model.Model;
   import mpc.app.preferenze.model.Model;

   import mx.collections.ArrayCollection;
   import mx.controls.Alert;
   import mx.core.FlexGlobals;

   //import com.adobe.cairngorm.view.ViewLocator;
   //import mpc.app.login.viewHelper.LoginHelper;

   public class NewCommand implements ICommand
   {

   private var modelPreferenze:mpc.app.preferenze.model.Model;
   private var modelOrdini:mpc.app.ordini.model.Model;

   public function NewCommand()
   {
   }

   public function execute(event:CairngormEvent):void
   {
   trace(“new x ordini”);
   modelPreferenze=mpc.app.preferenze.model.Model.getInstance();
   modelOrdini=mpc.app.ordini.model.Model.getInstance();

   var loadEvent:NewEvent=event as NewEvent;

   //genero un oggetto delegate a cui passo come responder le funzioni che quindi si occuperanno anche di rispondere
   var delegate:VerificaSerataDelegate=new VerificaSerataDelegate(this.result, undefined, this.fault, undefined);

   //trace(“SERATA:”+modelOrdini.serata.toString());

   var inizioSerata:Date= new Date(modelOrdini.serata.fullYear, modelOrdini.serata.month, modelOrdini.serata.date,0,0,0,0);
   //var fineSerata:Date= new Date(modelOrdini.serata.fullYear, (modelOrdini.serata.month + 1), modelOrdini.serata.date,23,59,59,999);

   trace(“inizioSerata:”+inizioSerata.fullYear+” - “+inizioSerata.month+” - “+inizioSerata.date);

   var obj:Object=new Object();
   //obj.azione=”query”; non serve lo applica il clienti attraverso il delegate in base alla funzione client chiamata query o querymodifiy
   obj.sql=”SELECT * FROM main.numeroOrdine WHERE data=:inizioSerata ;”;

   obj.parameters=new Object();
   obj.parameters.inizioSerata=inizioSerata;
   //obj.parameters.fineSerata=fineSerata;

   delegate.load(obj);

   }

   public function result(risposta:*):void
   {
   trace(“risposta:”+risposta);
   if(risposta)
   {

   trace(“risposta OK”);
   var inizioSerata:Date= new Date(modelOrdini.serata.fullYear, modelOrdini.serata.month, modelOrdini.serata.date,0,0,0,0);
   //var fineSerata:Date= new Date(modelOrdini.serata.fullYear, (modelOrdini.serata.month + 1), modelOrdini.serata.date,23,59,59,999);

   var delegate1:IncrementaSerataDelegate=new IncrementaSerataDelegate(this.result2, undefined, this.fault, undefined);

   //messaggio.azione=”queryModify”; viene aggiunta dal client in base alla funzione richiamata querymodify
   var sql:String=”UPDATE main.numeroOrdine SET numero=numero+1, dataMod=DATETIME(‘now’,’localtime’), utenteMod=:utenteMod WHERE data=:inizioSerata”;
   var parameters:Object=new Object();
   parameters.inizioSerata=inizioSerata;
   parameters.utenteMod=modelPreferenze.getPreferenza(modelPreferenze.UTENTELOGIN).nomeUtente;
   //parameters.eliminato=0; definito direttamente nella query

   //statements[statements.length] = new QueuedStatement(sql,parameters);
   var arrayQuery:Array=new Array();
   var query1:Object=new Object();
   query1.sql=sql;
   query1.parameters=parameters;
   arrayQuery.push(query1);

   //obj.azione=”query”; non serve lo applica il clienti attraverso il delegate in base alla funzione client chiamata query o querymodifiy
   var sql2:String=”SELECT * FROM main.numeroOrdine WHERE data=:inizioSerata ;”;
   var parameters2:Object=new Object();
   parameters2=new Object();
   parameters2.inizioSerata=inizioSerata;
   //parameters2.fineSerata=fineSerata;

   var query2:Object=new Object();
   query2.sql=sql2;
   query2.parameters=parameters2;
   arrayQuery.push(query2);

   delegate1.incrementa(arrayQuery);
   }
   else
   {

   trace(“risposta null creo nuovo record in numeroOrdine”);
   //la serata non era ancora stata inizializzata
   //creo nuovo contatore per serata

   //genero un oggetto delegate a cui passo come responder le funzioni che quindi si occuperanno anche di rispondere
   var delegate2:InizializzaSerataDelegate=new InizializzaSerataDelegate(this.result2, undefined, this.fault, undefined);

   //messaggio.azione=”queryModify”; viene aggiunta dal client in base alla funzione richiamata querymodify
   var sql:String=”INSERT INTO main.numeroOrdine ( data, numero, dataIns, utenteIns, dataMod, utenteMod) VALUES ( :data, :numero, DATETIME(‘NOW’,’localtime’), :utenteIns, DATETIME(‘NOW’,’localtime’), :utenteMod);”;
   var parameters:Object=new Object();

   parameters.data= new Date(modelOrdini.serata.fullYear, modelOrdini.serata.month, modelOrdini.serata.date,0,0,0,0);
   parameters.numero=1;
   parameters.utenteIns=modelPreferenze.getPreferenza(modelPreferenze.UTENTELOGIN).nomeUtente;
   parameters.utenteMod=modelPreferenze.getPreferenza(modelPreferenze.UTENTELOGIN).nomeUtente;
   //parameters.eliminato=0; definito direttamente nella query

   //statements[statements.length] = new QueuedStatement(sql,parameters);
   var arrayQuery:Array=new Array();
   var query1:Object=new Object();
   query1.sql=sql;
   query1.parameters=parameters;
   arrayQuery.push(query1);

   var inizioSerata:Date= new Date(modelOrdini.serata.fullYear, modelOrdini.serata.month, modelOrdini.serata.date,0,0,0,0);
   //var fineSerata:Date= new Date(modelOrdini.serata.fullYear, (modelOrdini.serata.month + 1), modelOrdini.serata.date,23,59,59,999);

   //obj.azione=”query”; non serve lo applica il clienti attraverso il delegate in base alla funzione client chiamata query o querymodifiy
   var sql2:String=”SELECT * FROM main.numeroOrdine WHERE data=:inizioSerata ;”;
   var parameters2:Object=new Object();
   parameters2=new Object();
   parameters2.inizioSerata=inizioSerata;
   //parameters2.fineSerata=fineSerata;

   var query2:Object=new Object();
   query2.sql=sql2;
   query2.parameters=parameters2;
   arrayQuery.push(query2);

   delegate2.inizializza(arrayQuery);

   }

   /*
   //messaggio.azione=”queryModify”; viene aggiunta dal client in base alla funzione richiamata querymodify
   var sql:String=”INSERT INTO main.utenti ( nomeUtente, descrizione, password, email, ico, colore, tipo, dataIns, utenteIns, dataMod, utenteMod, eliminato) VALUES ( :nomeUtente, :descrizione, :password, :email, :ico, :colore, :tipo, DATETIME(‘NOW’,’localtime’), :utenteIns, DATETIME(‘NOW’,’localtime’), :utenteMod, 0);”;
   var parameters:Object=new Object();
   parameters=modelUtenti.oggettoCorrente;
   parameters.utenteIns=modelPreferenze.getPreferenza(modelPreferenze.UTENTELOGIN).nomeUtente;
   parameters.utenteMod=modelPreferenze.getPreferenza(modelPreferenze.UTENTELOGIN).nomeUtente;
   //parameters.eliminato=0; definito direttamente nella query

   //statements[statements.length] = new QueuedStatement(sql,parameters);
   var arrayQuery:Array=new Array();
   var query1:Object=new Object();
   query1.sql=sql;
   query1.parameters=parameters;
   arrayQuery.push(query1);
   */
   /*
   var row:Object=risposta[0];

   //recupero dati login e li salvo nelle preferenze
   modelPreferenze.setPreferenza(modelPreferenze.UTENTELOGIN, row);
   //trace(“LOGIN COMMAND prima di usare loginHelper!!!!”);
   //per avere la possibilità di interagire con il view login e inoltrare l’evento di conferma login
   var loginHelper:LoginHelper = ViewLocator.getInstance().getViewHelper(“loginHelper”) as LoginHelper;
   loginHelper.loginConfermato();
   */

   }

   public function result2(risposta:*):void
   {
   trace(“risposta2:”+risposta.length);

   trace(“risposta2:”+risposta[1].data);
   trace(“risposta2:”+risposta[1].data[0].numero);

   }

   public function fault(event:Object):void
   {
   trace(“LOAD ARTICOLI x ordini!!!!”);
   var myAlertAccessoNegato:Alert=Alert.show(“LOAD ARTICOLI X ORDINI\nFALLITO”,”ERRORE”, Alert.OK, FlexGlobals.topLevelApplication as Sprite);
   myAlertAccessoNegato.maxWidth=150;

   }
   }

   }

4. May 25th, 2010 at 7:14 am
   rudi is reported to have said:

   I have a new question:
   how can I have this in air sqlite?:

   transaction = connection.BeginTransaction();

   command = new SQLiteCommand();
   command.Transaction = transaction;
   command.CommandText = “INSERT INTO tbl1 (data) VALUES (‘blargh’)”;
   command.ExecuteNonQuery();

   command = new SQLiteCommand();
   command.Transaction = transaction;
   command.CommandText = “SELECT last_insert_rowid()”;
   SQLiteDataReader reader = command.ExecuteReader();
   while (reader.Read()) {
   rowId = (long)reader[0];
   }

   command = new SQLiteCommand();
   command.Transaction = transaction;
   command.CommandText = “INSERT INTO tbl2 (id, tbl1Id) VALUES (2, @rowId)”;
   command.Parameters.Add(new SQLiteParameter(“@rowId”, rowId));
   command.ExecuteNonQuery();

   command = new SQLiteCommand();
   command.Transaction = transaction;
   command.CommandText = “INSERT INTO tbl3 (id, tbl1Id) VALUES (3, @rowId)”;
   command.Parameters.Add(new SQLiteParameter(“@rowId”, rowId));
   command.ExecuteNonQuery();

   command = new SQLiteCommand();
   command.Transaction = transaction;
   command.CommandText = “INSERT INTO tbl3 (id, tbl1Id) VALUES (4, @rowId)”;
   command.Parameters.Add(new SQLiteParameter(“@rowId”, rowId));
   command.ExecuteNonQuery();

   transaction.Commit();

5. May 25th, 2010 at 8:14 am
   Paul Robertson is reported to have said:

   Hi Rudi,

   I assume the important thing you’re trying to do is get the primary key of an inserted row and use it in subsequent statements, all within a single transaction.

   If you’re specifically interested in doing thta using my AIR SQLite library, then unfortunately that capability isn’t available yet. (I’ve started working on building that, but since the library is just something I build in my spare time it’s not necessarily going to happen very soon.)

   However, it’s definitely available in AIR SQLite if you’re managing the connections and statements yourself. The section “Retrieving a database-generated primary key of an inserted row” in the documentation talks about how to do that.

   (I wrote that section of the docs, so if it’s unclear or you have more questions, definitely feel free to ask.)

Add your comment

Comment notes

Please keep comments on topic. Comments that are inappropriate or offensive will be edited or removed.

Paragraphs and line breaks are automatically converted to HTML, and quotation marks are converted to “smart” quotes.

The following XHTML tags can be used: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> . All others will be removed.

Articles by Type

• Articles by Paul
  • Opinions
  • Tutorials
• Elsewhere on the web
  • Articles to remember
  • Sites to remember

Articles by Topic

• .NET (5)
  • ASP.NET (3)
• ActionScript (71)
  • AS3 (35)
  • Vector (typed arrays) (2)
• AIR (33)
  • local SQL database (20)
  • Privacy/security (3)
• Animation (1)
• Application Design (17)
• Book reviews (3)
• Coding (9)
• Conferences (4)
• Contextual design (1)
• Design Process (1)
• Dreamweaver (4)
• Flash (55)
• Flex (36)
  • ColdFusion Flash Forms (10)
• Flex Builder (5)
• Frameworks (9)
• iPhone (3)
• JavaScript (2)
• Life at Adobe (4)
• MS SQL Server (4)
• Multi-screen (1)
• Pixel Bender (1)
• Presentations (13)
• Projects (12)
• SaaS (1)
• Semantic Web (1)
• SQL (14)
• Surveys (1)
• Unit testing (3)
• User-centered design (14)
• Web Browsers (6)
• Web Design (16)
  • CSS (6)
• Writing (13)

Random Reading

• Phil Chung
• Blake M. Scarbrough
• Jason Kester
• Mike Chambers
• Scott Guthrie

Currently...

• 
  follow probertson at http://twitter.com

Subscribe

• Subscribe to my feed

• Copyright, terms of use, etc.
• Powered by WordPress