2 Replies Latest reply on Jul 28, 2009 5:05 PM by alevicki

    batch inserts in SQLite instance in AIR?

    calvink11

      Hi,

       

      is there a way to execute multiple insert statements as one batch in the SQLite instance of AIR?

       

      for example

      var sql:String = "insert into the_table values ( 'one', 'two' );

          insert into the_table values ( 'three', 'four' );
          insert into the_table values ( 'five', 'six' );"

       

      we want to reduce the overhead of event processing when using the async connection.

       

      Thanks,

      Calvin

        • 1. Re: batch inserts in SQLite instance in AIR?
          adobe_paul Adobe Employee

          There isn't any way to send multiple complete SQL statements to AIR as a single statement execution.

           

          In the case of INSERT statements specifically, you can insert multiple rows within a single SQL statement by using the INSERT ... SELECT syntax. It's the second syntax shown here:

          http://livedocs.adobe.com/flex/3/langref/localDatabaseSQLSupport.html#insert

           

          The idea is that you construct a well-formed SELECT statement with the same number of result columns as the input columns you define in the INSERT. The result of the SELECT statement gets used as the source of data for the INSERT statement. In the example you give, you could use the following SQL as a one-statement alternative to those three INSERT statements:

           

          INSERT INTO the_table (column1, column2)
          SELECT 'one', 'two' UNION
          SELECT 'three', 'four' UNION
          SELECT 'five', 'six'
          

           

          The key here is that you're using SELECT statements with only literal values (so you don't need to specify a source table) and you're using UNION to join the SELECT statements together into a single result set with multiple rows.

           

          Note, however, that it might be better from a performance and stability standpoint to execute multiple separate INSERT statements, especially as opposed to inserting hundreds or thousands of rows in a single INSERT statement.

           

          As a side note, I'm working on a framework for SQL statement execution (soon to be released as open source) that, among other things, allows you to define batches of statements to execute (with progress events and a complete event at the end). But of course under the covers the framework would really be executing multiple SQL statements separately. I'm not sure if that would give you the benefits you're looking for or not.

          1 person found this helpful
          • 2. Re: batch inserts in SQLite instance in AIR?
            alevicki

            Transactions may be what you are looking for.  Look at the documentation here:

            http://livedocs.adobe.com/flex/3/langref/flash/data/SQLConnection.html#begin()