1 Reply Latest reply on Feb 7, 2008 8:39 AM by Starlover_jacob

    sqlite multiple record insert

    Starlover_jacob Level 1
      Hi there,
      I have a little question about the sqlite database that goes with air applications.

      I've done the following tutorial: (not functional for flex3)
      http://www.adobe.com/devnet/air/flex/articles/flex_to_air_migration_02.html
      And than particularly the part where there is data inserted into the sqlite database.
      They insert only one record into the database... witch works fine..
      But now my question: How do you insert multiple lines into the sqlite database?

      used code for inserting one line:


      // insert a record into the table
      public function insertMonthRecords():void
      {
      var sqlText:String = "INSERT INTO month( monthname) " +
      "VALUES('Januari')";
      insertStatement = new SQLStatement();
      insertStatement.sqlConnection = conn;
      insertStatement.addEventListener(SQLEvent.RESULT, insertResult);
      insertStatement.addEventListener(SQLErrorEvent.ERROR, errorHandler);
      insertStatement.text = sqlText;
      trace(sqlText);
      insertStatement.execute();
      }
      // insertResult
      private function insertResult(event:SQLEvent):void {
      trace("The record was inserted successfully");
      }


      Now I've got it working by creating a new sqlText2 like this:


      // insert a record into the table
      public function insertMonthRecords():void
      {
      var sqlText:String = "INSERT INTO month( monthname) " +
      "VALUES('Januari')";
      insertStatement = new SQLStatement();
      insertStatement.sqlConnection = conn;
      insertStatement.addEventListener(SQLEvent.RESULT, insertMonthResult);
      insertStatement.addEventListener(SQLErrorEvent.ERROR, errorHandler);
      insertStatement.text = sqlText;
      trace(sqlText);
      insertStatement.execute();

      var sqlText2:String = "INSERT INTO month( monthname) " +
      "VALUES('Februari')";
      insertStatement = new SQLStatement();
      insertStatement.sqlConnection = conn;
      insertStatement.addEventListener(SQLEvent.RESULT, insertMonthResult);
      insertStatement.addEventListener(SQLErrorEvent.ERROR, errorHandler);
      insertStatement.text = sqlText2;
      trace(sqlText2);
      insertStatement.execute();
      }
      // handle a successful insert
      private function insertMonthResult(event:SQLEvent):void {
      trace("The record was inserted successfully into months");
      }


      This works.. But it gets me a lot of code when i want to create a static table with all the months in it.
      Or lets say i want a table with product-types like; vegetables, alcoholic drinks, milk products, and lots of other product types.
      In PHP/MYSQL you can insert multiple records like this;
      values(product1),(product2),(product3)
      Is there some trick with sqlite that gives me less code?

      Please let me know.

      Greetz, Jacob

      Is there an easy way?
        • 1. Re: sqlite multiple record insert
          Starlover_jacob Level 1
          Unlike in php (values(product1),(product2),(product3))
          you cannot insert records like that in a sqlite db.

          What i did is the following


          public function insertMonthRecords():void
          {
          var sqlText:String = "INSERT INTO month( monthname) " +
          "VALUES('Januari')";
          insertStatement = new SQLStatement();
          insertStatement.sqlConnection = conn;
          insertStatement.addEventListener(SQLEvent.RESULT, insertResult);
          insertStatement.addEventListener(SQLErrorEvent.ERROR, errorHandler);
          insertStatement.text = sqlText;
          insertStatement.execute();

          insertStatement.text = "INSERT INTO month(monthname) VALUES('Februari')";
          insertStatement.execute();
          insertStatement.text = "INSERT INTO month(monthname) VALUES('March')";
          insertStatement.execute();
          insertStatement.text = "INSERT INTO month(monthname) VALUES('April')";
          insertStatement.execute();
          }

          And so on.. To bad i didn't found a solution to execute all the inserts at once. Inserting this way takes time :-(

          If you have a better solution, just let me know.

          Greetz, Jacob