3 Replies Latest reply on Aug 14, 2010 2:56 PM by Чернявский

    Save datagrid contents to SQLite

    Чернявский
      Hi All!

      I have a datagrid with a unknown number of rows....How can I can I dynamically save all the data to a SqLite database,and also update the database when the user changes a value(s)...

      Are there any examples of something like this out there.

      Thanks

        • 1. Re: Save datagrid contents to SQLite
          wise_qwerty

          What language are you using for the back-end? examples:php, java,...

          Often back-end language works with all database stuff, not flex. Flex is just front-end (GUI).

          • 2. Re: Save datagrid contents to SQLite
            Чернявский Level 1

            I use a local SQLite database for desktop applications without the use of php, java.

            • 3. Re: Save datagrid contents to SQLite
              Чернявский Level 1

              <?xml version="1.0" encoding="utf-8"?>

              <s:WindowedApplication

               

               

              xmlns:fx="http://ns.adobe.com/mxml/2009"

              xmlns:s="

              library://ns.adobe.com/flex/spark"

              xmlns:mx="

              library://ns.adobe.com/flex/mx"

              width="

              800" height="600"

              creationComplete="onCreationComplete()"

              >

               

               

               

               

              <fx:Script>

              <![CDATA[

               

               

              import mx.controls.Alert;

               

              import mx.events.DataGridEvent;

               

              import mx.collections.ArrayCollection;

               

              [

              Bindable]

               

              public var dp:ArrayCollection = new ArrayCollection();

               

               

               

              private var connection:SQLConnection;

               

              public var dbStatement:SQLStatement;

               

              public var dbFile:File;

               

              public var dbFileString:String = "exampledb.db";

               

               

               

               

              public function onCreationComplete():void

              {

              connection =

               

              new SQLConnection();

               

              //@lis dbFile = File.applicationStorageDirectory.resolvePath(dbFileString);

              dbFile = File.desktopDirectory.resolvePath(dbFileString);

               

               

               

              // does the database exist?

               

               

              if(dbFile.exists)

              {

               

              // the database exists, so let's open it and read in the data

              connection.open(dbFile);

              loadData();

              }

               

              else {

               

              // the database does not exists, so let's create it

              createDB();

              }

              }

               

               

               

               

              public function createDB():void

              {

              connection.addEventListener(SQLEvent.OPEN, onDBCreateResult);

              connection.addEventListener(SQLErrorEvent.ERROR, onDBError);

               

              // the following line creates the database if it does not exists, but we already know it doesn't

              connection.open(dbFile);

              }

               

               

               

               

              private function onDBCreateResult(event:SQLEvent):void

              {

               

              // we successfully created the database, so let's create our table

              createTable();

              }

               

               

               

              /*

              For this example we can use this error result handler for all our transactions

              */

               

              private function onDBError(event:SQLErrorEvent):void

              {

              Alert.show(event.error.name +

              " " + event.error.errorID + "\n" +

              event.error.details +

              "\n" + event.error.message);

              }

               

               

               

              public function createTable():void

              {

               

              var sql:SQLStatement = new SQLStatement();

              sql.sqlConnection = connection;

               

              var sqlString:String = "CREATE TABLE Users (" +

               

              " uid INTEGER PRIMARY KEY AUTOINCREMENT, " +

               

              " name TEXT, " +

               

              " phonemob TEXT, " +

               

              " phonewrk TEXT, " +

               

              " phonehom TEXT)";

              sql.text = sqlString;

               

              sql.addEventListener(SQLEvent.RESULT, onDBCreateTableResult);

              sql.addEventListener(SQLErrorEvent.ERROR, onDBCreateTableError);

              sql.execute();

               

               

              sqlString =

              "INSERT INTO Users (name, phonemob, phonewrk, phonehom) " +

               

              "VALUES ('Чернявский А.В.', '099-99999999', '99999999', '9999999')";

              sql.text = sqlString;

               

               

               

              //sql.addEventListener(SQLEvent.RESULT, onDBAddResult);

              sql.addEventListener(SQLErrorEvent.ERROR, onDBError);

              sql.execute();

              }

               

               

               

               

              private function onDBCreateTableResult(event:SQLEvent):void

              {

               

              trace('table created');

              loadData();

              }

               

               

               

              private function onDBCreateTableError(event:SQLErrorEvent):void

              {

              Alert.show(event.error.name +

              " " + event.error.errorID + "\n" +

              event.error.details +

              "\n" + event.error.message);

               

               

              trace(event.error.name + " " + event.error.errorID + "\n" +

              event.error.details +

              "\n" + event.error.message);

              }

               

               

               

              private function loadData():void

              {

              dbStatement =

              new SQLStatement();

              dbStatement.sqlConnection = connection;

               

               

              var sqlQuery:String = "select * from Users";

              dbStatement.text = sqlQuery;

               

              dbStatement.addEventListener(SQLEvent.RESULT, onRetrieveDataResult);

              dbStatement.addEventListener(SQLErrorEvent.ERROR, onDBError);

              dbStatement.execute();

              }

               

               

               

               

              private function onRetrieveDataResult(event:SQLEvent):void

              {

               

              var result:SQLResult = dbStatement.getResult();

              dp =

              new ArrayCollection();

               

              for each(var el:Object in result.data)

              {

              dp.addItem({ uid:el.uid, name:el.name, phonemob:el.phonemob, phonewrk:el.phonewrk, phonehom:el.phonehom });

              }

               

              trace('cache retrieved');

              }

               

               

               

               

              public function add():void

              {

               

              var sql:SQLStatement = new SQLStatement();

              sql.sqlConnection = connection;

              dbStatement =

              new SQLStatement();

              dbStatement.sqlConnection = connection;

               

               

              var sqlString:String = "INSERT INTO Users (name, phonemob) " +

               

              "VALUES ('наберите ФИО', '0XX-YYYYYYY')";

              sql.text = sqlString;

               

              sql.addEventListener(SQLEvent.RESULT, onDBAddSuccess);

              sql.addEventListener(SQLErrorEvent.ERROR, onDBError);

              sql.execute();

              }

               

               

               

               

              public function onDBAddSuccess(event:SQLEvent):void

              {

               

              var result:SQLResult = dbStatement.getResult();

               

              var uid:Number = event.currentTarget.sqlConnection.lastInsertRowID;

               

              loadData();

              }

               

               

               

               

              public function remove():void

              {

               

              var selectedItemUid:Number = dg.selectedItem.uid;

               

              var selectedIndex:Number = dg.selectedIndex;

               

               

              if(selectedIndex >= 0)

              {

               

              var sql:SQLStatement = new SQLStatement();

              sql.sqlConnection = connection;

              dbStatement =

              new SQLStatement();

              dbStatement.sqlConnection = connection;

               

               

              var sqlString:String = "DELETE FROM Users WHERE uid=" + selectedItemUid;

              sql.text = sqlString;

               

              sql.addEventListener(SQLEvent.RESULT, onDBRemoveResult);

              sql.addEventListener(SQLErrorEvent.ERROR, onDBError);

              sql.execute();

              }

              else {

              Alert.show(

              "You do not have a row selected to delete.");

              }

              }

               

               

               

               

              public function onDBRemoveResult(event:SQLEvent):void

              {

              loadData();

              }

               

               

               

              public function update(event:DataGridEvent):void

              {

               

              var sql:SQLStatement = new SQLStatement();

              sql.sqlConnection = connection;

              dbStatement =

              new SQLStatement();

              dbStatement.sqlConnection = connection;

               

               

              var sqlString:String = "UPDATE Users SET name = '" + event.itemRenderer.data.name +

               

              "', phonemob = '" + event.itemRenderer.data.phonemob +

               

              "', phonewrk = '" + event.itemRenderer.data.phonewrk +

               

              "', phonehom = '" + event.itemRenderer.data.phonehom +

               

              "' WHERE uid = " + event.itemRenderer.data.uid;

              sql.text = sqlString;

               

               

              //sql.addEventListener(SQLEvent.RESULT, onDBAddResult);

              sql.addEventListener(SQLErrorEvent.ERROR, onDBError);

              sql.execute();

              }

              ]]>

               

              </fx:Script>

               

               

               

               

              <mx:DataGrid id="dg" itemFocusOut="update(event)"

              dataProvider="

              {dp}"

              editable="

              true"

              wordWrap="

              true" variableRowHeight="true"

              left="

              5" right="5" top="150" bottom="5">

               

              <mx:columns>

               

              <mx:DataGridColumn editable="false" dataField="uid" headerText="Номер" width="30"/>

               

              <mx:DataGridColumn editable="true" dataField="name" headerText="ФИО" width="200" />

               

              <mx:DataGridColumn editable="true" dataField="phonemob" headerText="Телефоны мобильные" />

               

              <mx:DataGridColumn editable="true" dataField="phonewrk" headerText="Телефоны рабочие" />

               

              <mx:DataGridColumn editable="true" dataField="phonehom" headerText="Телефоны домашие" />

               

              </mx:columns>

               

               

              </mx:DataGrid>

               

               

              <s:Button y="100" click="add()" label="Добавить строку" right="5"/>

               

              <s:Button y="72" click="remove()" label="Удалить строку" right="5" enabled="{dg.selectedIndex != -1}"/>

               

               

               

              </s:WindowedApplication>