1000 Mindomo, the mind mapping web tool, is now available as an AIR application
Oct 17

In the first part of this serie, I explained how to split a SQL query and use each element of the returned array to queue each SQL statement.

In this second part, I would like to explain how to loop on a query. Very simple topic.

You use a loop in the case of “mass”- insertion, update or deletion of some records. The SQL statement remains always the same, only values change and the query is called in a loop…

Case 1 : to update the local database

Imagine that in your application, you want to apply the same action to all the items in a datagrid. Let’s say you want to mark them all as “Validated”.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
private function updateAllOrders() :void 
{
   for (var i:Number = 0 ; i < orders.length ; i++)
   {
     updateThisOrder(orders[i].id);
   } 
} 
 
private function updateThisOrder(id:Number) :void 
{ 
     sqlStatement = new SQLStatement(); 
     sqlStatement.sqlConnection = dbconn; 
 
     sqlStatement.text = 
        "UPDATE tblOrders " + 
        "SET status='V' " + 
        "WHERE id=" + id; 
 
     sqlStatement.addEventListener(SQLEvent.RESULT, function(e:SQLEvent):void 
     { 
	trace("Order " + id + " updated"); 
     }); 
 
     sqlStatement.addEventListener(SQLErrorEvent.ERROR, function(e:SQLErrorEvent):void 
     { 
	Alert.show("Error on :\n" + e.target.text + "\n" + e.error); 
     }); 
 
     sqlStatement.execute(); 
}

So simple actually

Case 2 : to insert records in a remote MySQL database

I adressed this case in an application that mainly works with data locally but that must store some data in a MySQL database to be used by an online Flex calendar. This allowed me to maintain the remote database synchronized with the local one. (I think it’s quiet unusual but that’s the way the application had to work ;).

The 2nd part of the code actually works with a MySQL database using the asSQL class. You will then meet the Connection and MySqlToken classes. So don’t be surprised but it is the same method working with the local SQLite databse.

1
2
3
4
5
6
7
8
9
10
11
// customerDP is an Array defined outside this code,
//it contains all values to be injected in the database
var minRow:Number = 0;
var maxRow:Number = customerDP.length;
 
customerDP.source.sortOn("id", Array.NUMERIC);
 
for (var i:Number = minRow ; i < maxRow ; i++)
{
     insertAllCustomers(i);
}

and the statement is built with the values of the array.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
private function insertAllClients(i:Number) :void
 {
    // the MySQL connection
    var tempconn:Connection = new Connection( mysqlhost, mysqlport, mysqllogin, mysqlpwd, mysqlbase );
 
    tempconn.addEventListener(Event.CONNECT, function() :void
       {
          var query:String =
          "INSERT INTO `tblClients` " +
          "  (`id`,`lname`,`fname`,`adr1`,`adr2`,`zip`,`city`,`tel`,`tel2`,`mail`) " +
          "  VALUES (" + customerDP[i].id + "," +
          "\"" + customerDP[i].lname + "\"," +
          "\"" + customerDP[i].fname + "\"," +
          "\"" + customerDP[i].adr1 + "\"," +
          "\"" + customerDP[i].adr2 + "\"," +
          "'" + customerDP[i].zip+ "'," +
          "\"" + customerDP[i].city + "\"," +
          "'" + customerDP[i].tel + "'," +
          "'" + customerDP[i].tel2 + "'," +
          "'" + customerDP[i].mail + "'); \n";
 
          // The asSQL part...
          var st:Statement = tempconn.createStatement();
          var tokenclient:MySqlToken = st.executeQuery(query);
 
          tokenclient.addResponder(new AsyncResponder(
 
             function(data:Object, token:Object) :void
                {
                   // Free the remote connection
                   tempconn.disconnect();
                },
 
             function(info:Object, token:Object) :void
                {
                   Alert.show("Error: " + info);
 
                },
 
                tokenclient
 
                ));
 
       });
 
    tempconn.connect();
 
 }

What are the minRow and maxRow used for ?

These variables actually represents a range of elements in the array customerDP. For my own need, I don’t insert all the items of customerDP but only few from the end. That’s why, actually, customerDP needs also to be sorted on ‘id’ just before the for loop and by this way, it is possible to insert only the 2 or 3… last items of the Array.

Now, how do I determine this range ?

I use the result of another query and this will be the topic of the 3dr part of the serie, dealing with dependancies.

 

Related posts

Written by Arnaud
Creative Commons License
Tags: , , ,

Share/Save/Bookmark

Help me improve my blog by rating this post or sending a comment.

not goodquite goodgoodvery goodexcellent (No Ratings Yet)
Loading ... Loading ...


Comments are closed.