Problem
In your AIR Application you want to export data from the connected local SQLite database in SQL syntax to use it in other SQL databases without using a third format like CSV or XML.
Solution
Use the FileReference to create a .sql file which you can use for batch import into another SQL database.
Detailed explanation
The following example assumes that you already have a database
connection open and
that you know how to execute statements on this
SQLConnection.
/**
* Export the data from the table which name is defined by
TABLENAME. MyItem is the
* class of the Object that is stored in the row of the database
*
* /
public function exportTableData():void {
var select:SQLStatement = new SQLStatement();
select.text = "SELECT * FROM " + DB_TABLENAME;
select.itemClass = MyItem;
//call to database to execute the select statement and assign the
result and fault handle
// Simpliefied execution which is done in another method
executeStatement(select,handleExportTableDataSuccess,handleExportTableDataFault);
}
...
/**
* Export the data to the file
*/
private function handleExportTableDataSuccess(event:SQLEvent):void
{
var rows:Array = (event.currentTarget as
SQLStatement).getResult().data;
var fr:FileReference = new FileReference();
var SQLString:String = "";
rows.forEach(function(row:MyItem, index:int, arr:Array):void {
SQLString += "INSERT INTO " + TABLENAME+ "
(Key,property1,property2,property3) ";
SQLString += "VALUES (" + row.key + ","
+ row.property1 + "," //a number,int, uint property
+ (row.property2 ? "\'" + row.property2 + "\'" : "\'\'") + "," //a
String property
+ (row.property3 ? "\'" + getJulianDateString(row.property3) + "\'"
: row.property3) ; //a Date property
SQLString += ");\n";
});
//create the current date to make a nice file name.
var fileCreationDate:Date = new Date();
var dateString:String = fileCreationDate.date +
(fileCreationDate.month+1).toString() + fileCreationDate.fullYear;
fr.save(SQLString,"UC_" + TABLENAME+ "Table_BATCHINSERT_"+
dateString + ".sql");
}
To export dates correctly, for example to be able import them
back into an SQlite Db you have to convert the date into the
Julian Date
Format like "
YYYY-MM-DD HH:MM:SS" The used method(
getJulianDateString(date:Date)) is a sample
implementation to convert the
Date object into a Julian Date
String and is explained in more detail
here.
+
This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License. Permissions beyond the scope of this license, pertaining to the examples of code included within this work are available at Adobe.
Report abuse
