Executing multi-statements queries in AIR applications - Part 3/3 : dependant queries Du CMMI agile et vice versa : Décisions formelles
Oct 30

In this serie (that was supposed to be closed) about executing a multi-statement query in AIR (ie a query with several statements as INSERT, UPDATE, DELETE and even CREATE TABLE), I gave examples of queuing queries, looping on queries and at last, working with dependant queries.
Let’s now mix everything.

In some cases, you may need to execute more than 1 query at a time that will call another query or more than one function. Imagine you select a customer in a DataGrid or make a search in a TextInput. The application then executes differents queries and displays some additionnal information about this customer.

I would like to give just one example as there is of course an infinity of possibilities. But the particularity of this one is that queries are all launched in a unique for() loop. So how to define a specific response on results of each individual query ?

In this example, the user action calls a loop of 2 simples queries and each query will call its specific response.
The solution for this is to use an Array of Objects. Each object contains the text of the query to be executed and the response to be invoked. The objects are pushed into an array to loop on it.

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
49
50
private function loopingDependantQueriesWithResponder() :void
{
  // The array of objects
  var queryLoop:Array = new Array();
  // The object that will contain query text and response function
  var queryObject:Object = new Object();
 
// The query will find a customer id using the name entered in a TextInput.
  // That could be a selectedIem of a DataGrid
  queryObject.text =
 	"SELECT id FROM tblClients " +
 	"WHERE nom=\"" + tiSearch.text + "\"";
 
  // Assigns a function to the query
  // The function countRentals is defined outside of the function called by the user action
  queryObject.response = countRentals;
 
  // Push the new object into the array
  queryLoop.push( queryObject );
 
  // Same for a second query
  queryObject.text =
  	"SELECT adr1, adr2, zip, city FROM tblClients " +
 	"WHERE name=\"" + tiSearch.text + "\"";
 
  queryObject.response = findAddress;
  queryLoop.push( queryObject );
 
  // The loop that will execute each query
  for (var i:int = 0 ; i < queryLoop.length ; i++ )
  {
     var mainStatement:SQLStatement = new SQLStatement();
     mainStatement.sqlConnection = dbconn;
 
     // Assigns the text property of the object to the text property of the statement
     mainStatement.text = queryLoop[i].text;
     mainStatement.execute( -1, new Responder(
 	function( result )
 	{
 	   // Calls the function assigned to the response property of the query object
 	   // and passes the result of the query as an array
 	   queryLoop[i].response( result.data );
 	},
 	function( error ) :void
 	{
 	   Alert.show( error.message );
 	}
        ));
  }
}

The loop is now defined and you can create the 2 functions required for each response. Let’s create the first one countRentals :

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
// The array of result is passed to the function 
private function countRentals(r:Array) :void
{
   var st:SQLStatement = new SQLStatement();
   st.sqlConnection = dbconn;
 
   // creates the text of the query including a parameter that we get from the passed result array
   st.text =
	"SELECT COUNT(id) AS Count " +
 	"FROM tblRentals " +
 	"WHERE id_client = :foundcustomer";
 
st.parameters[":foundcustomer"] = r[0].id;
 
// Executes the query and associates a responder
st.execute( -1, new Responder(
	function( result )
	{
	   // Show the result of the 'dependant' query in a TextArea
	   output.text += "Number of rentals by " + tiSearch.text + ": " + result.data[0].Count + "\n";
 	},
	function( error )
	{
	   Alert.show( error.message );
	}
        ));
}

 

That’s all. I hope this was clear and that will help some of you.

 

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 (1 votes, average: 4 out of 5)
Loading ... Loading ...


Comments are closed.