Use flex in Adobe AIR to connect to the SQLite database (2) (add, delete, modify, and statement parameters)

SeriesArticleNavigation

1. Using flex in Adobe AIR to connect to the SQLite database (1) (creating databases and tables)
2. Use flex in Adobe AIR to connect to the SQLite database (2) (add, delete, modify, and statement parameters)
3. Connecting to the SQLite database using flex in Adobe AIR (3) (query)
4. Connecting to the SQLite database using flex in Adobe AIR (4) (Transaction)
5. Index of flex and fms3 articles
6. Fms3 and flex create online multi-person video conferences and video chats (with the original code)
7. Free beauty video chat, multi-person video conferencing feature enhanced version (fms3 and flex development (with source code ))

This chapter mainly summarizes the database insertion, deletion, modification, and usage of statement parameters.
If you do not understand the synchronous and asynchronous operations mentioned in this Chapter, read the previous article.
Http://www.cnblogs.com/aierong/archive/2009/01/22/flex_Sqlite_1.html

 

0. sqlstatement class Introduction
The sqlstatement instance is used to execute SQL statements for the local SQL database opened through the sqlconnection instance.
The sqlstatement instance is linked to the sqlconnection instance by setting the sqlconnection instance as the value of the sqlconnection attribute of the sqlstatement instance. The text attribute is filled with the actual text of the SQL statement to be executed. If necessary, you can use the parameters attribute to specify the value of the SQL statement parameter and call the execute () method to execute the statement.

1. Insert data
Synchronization version:
Import MX. Controls. Alert;
Private var con: sqlconnection;

Private function initapp (): void
{
Var file: file = file. applicationstoragedirectory. resolvepath ("mytestdb. DB ")
 
Con = new sqlconnection ();
VaR stmt: sqlstatement = new sqlstatement ();
 
Try
{
Con. Open (File );

Stmt. sqlconnection = con;
Stmt. Text = "insert into EMP (firstname, lastname, salary) values ('F', 'l', 88 )";
Stmt.exe cute ();
}
Catch (error: sqlerror)
{
Alert. Show (error. Message );
Alert. Show (error. Details );
}
}
CodeNote:
An instance of the sqlstatement class is used to execute SQL statements for the local SQL database opened through the sqlconnection instance.

 

2. Obtain the row ID generated by the database with Inserted rows.
Obtains the row id value of the automatically increasing column.
VaR stmt: sqlstatement = new sqlstatement ();
Stmt. sqlconnection = con;
Stmt. Text = "insert into EMP (firstname, lastname, salary) values ('F', 'l', 88 )";
Stmt.exe cute ();
 
VaR result: sqlresult = stmt. getresult ();
VaR primarykey: Number = result. lastinsertrowid;

Alert. Show (primarykey. tostring ());
Code Description:
Sqlresult class provides access to the data returned in response to SQL statement (sqlstatement instance) Execution
Lastinsertrowid attribute: the row identifier generated last time (generated by the SQL insert statement). If the statement executed is not an insert statement, the value is 0.

 

3. Use of statement Parameters
When you use an SQL statement multiple times but the values in the statement are different, the best way is to use an SQL statement that includes parameters rather than including the literal value in the SQL text. A parameter is a placeholder in the statement text. Each time a statement is executed, it is replaced with the actual value.
The parameter name consists of the ":" or "@" characters followed by a name, for example, itemname @ firstname
You can also use the Untitled parameter and "?" Character indicates the parameters in the SQL statement. In the order of parameters in the statement, each parameter is assigned a digital index, which starts from index 0 (indicating the first parameter.

Advantages of using parameters:
1. Better performance
2. explicitly specify the Data Type
3. Higher security

Instance code: Get the row id value of the automatically increasing column (Asynchronous version)
Private var stmt1: sqlstatement;
Private function getlastinsertrowid (): void
{
Stmt1 = new sqlstatement ();
Stmt1.sqlconnection = con;
 
Stmt1.text = "insert into EMP (firstname, lastname, salary) values (@ firstname, @ lastname, @ salary )";
Stmt1.parameters ["@ firstname"] = "F ";
Stmt1.parameters ["@ lastname"] = "L ";
Stmt1.parameters ["@ salary"] = 88;
 
Stmt1.addeventlistener (sqlevent. Result, okhandler );
Stmt1.addeventlistener (sqlerrorevent. error, errorhandler );
Stmt1.execute ();
}

Private function okhandler (EVT: sqlevent): void
{
Alert. Show ("inserted successfully ");
VaR re: sqlresult = This. stmt1.getresult ();
VaR ID: Number = Re. lastinsertrowid;
Alert. Show (Id. tostring ());
}

Private function errorhandler (EVT: sqlerrorevent): void
{
Alert. Show ("failed ");
Alert. Show (EVT. Error. Message );
Alert. Show (EVT. Error. Details );
}
Code Description:
In this example, the Statement parameters @ firstname, @ lastname, @ salary are used and assigned values respectively.
If you use unnamed parameters, modify the code as follows (Note that the number index starts from 0 ):
Stmt1.text = "insert into EMP (firstname, lastname, salary) values (?, ?, ?) ";
Stmt1.parameters [0] = "F ";
Stmt1.parameters [1] = "L ";
Stmt1.parameters [2] = 88;

 

4. delete operation
Private function del (): void
{
VaR stmt: sqlstatement = new sqlstatement ();
Stmt. sqlconnection = con;
Stmt. Text = "delete from EMP where salary =: salary ";
Stmt. Parameters [": salary"] = 88;
Stmt.exe cute ();
 
VaR result: sqlresult = stmt. getresult ();
VaR count: Number = result. rowsaffected;

Alert. Show ("successfully deleted" + count. tostring () + "row ");
}
Code Description:
Rowsaffected attribute: indicates the number of rows affected by this operation.
Note that when the related SQL operation is a delete statement without the WHERE clause (that is, this statement deletes all rows in the table), the rowsaffected attribute is always 0, no matter how many rows are deleted. To know the number of rows to be deleted, You can include the WHERE clause where 1 = 1. In this case, all rows will be deleted, and the rowsaffected attribute will precisely reflect the number of rows deleted

 

5. Modify operations
Private function updatedata (): void
{
VaR stmt: sqlstatement = new sqlstatement ();
Stmt. sqlconnection = con;
Stmt. Text = "Update EMP set lastname =: lastname where salary =: salary ";
Stmt. Parameters [": lastname"] = "La ";
Stmt. Parameters [": salary"] = 88;
Stmt.exe cute ();
 
VaR result: sqlresult = stmt. getresult ();
VaR count: Number = result. rowsaffected;

Alert. Show ("successfully modified" + count. tostring () + "row ");
}
Code Description:
Change the lastname of salary = 88 to "La"

 

6. Download Code
Http://files.cnblogs.com/aierong/Air_Test_SQLite2.rar

Favorites and sharing

Add QQ bookmarks to Baidu souzang and Yahoo favorites

RSS subscribe to me What is RSS?