Mindomo, the mind mapping web tool, is now available as an AIR application Executing multi-statements queries in AIR applications - Extra Part
Oct 25

In this last chapter, I want to focus on dependant queries.
What I call dependant queries is queries that use the result of a previous one into their own statement.

There is actually not so many solutions to pass a query result to the next query.

1- You can use a variable : when the 1st query got results, this result is assigned to the variable that will be passed to the second query. This can be useful if you intend to reuse the variable for something else.

2- The other solution is to pass the results of the 1st query using the getResult() method of an event or the result property inside a Responder to the next one. To do so, the execution of the “dependant” query has then to be nested in the Listener or Responder of the first query.

This is the only ways that I found by myself or searching the web.

On the code side, here is how the implementation can be :

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
// This function is called on a user action
// Its purpose is to search a name in the customer table of the local database
// and to return the number of rentals by this customer from the table where rentals are stored.
private function dependantQueries() :void
{
	// The main statement is built including a value entered by the user in a TextInput
	var mainStatement:SQLStatement = new SQLStatement();
	// dbconn is the main connection to the local database
	mainStatement.sqlConnection = dbconn;
	mainStatement.text =
		"SELECT * FROM tblCustomers " +
		"WHERE name=\"" + tiSearch.text + "\"";
	// let's use an EventListener (but you can use a Responder)
	mainStatement.addEventListener(SQLEvent.RESULT, function(e:SQLEvent):void
		{
			// Define the second statement that will use the results of the main one
			var dependantStatement:SQLStatement = new SQLStatement();
			dependantStatement.sqlConnection = dbconn;
			dependantStatement.text =
				"SELECT COUNT(id) AS Count " +
				"FROM tblRentals " +
				"WHERE customer_id = :foundcustomer";
			// the result of the main query is passed as parameter of the second query
			// more precisely, this is the id of the extracted customer that will be used
			// to do so, you use the getResult() method of the SQLEvent
			dependantStatement.parameters[":foundcustomer"] = e.target.getResult().data[0].id;
			// then the second query is executed
			// using Responder this time (but you can a listener)
			dependantStatement.execute( -1, new Responder(
				// function if ok
				function( result )
				{
					// Just show the result in a popup
					Alert.show( tiSearch.text + " has rent " + result.data[0].Count + " time(s)");
				},
				// function if error
				function( error ) :void
				{
					Alert.show( error.message );
				}
				));
		});
	// once everything is set, the main query can be launched
	mainStatement.execute();
}

Listener or Responder ?

You can use either EventListener or Responder. It’s up to you actually. I am used to use EventListeners but Responders have advantages too and I think I will use them more often now : I think they are easier to defin, resulting in a clearer code. But this is only my point of view. And I would be interested in comparing Listeners and Responders from a performance point of view.

Code organization.

The first time I had to define a dependant query I also had to define queued queries… and the draft of code was awfullly complicated with at least 3 levels of nested listeners: readibility, zero, maintainability, zero.

So if I have an advice to keep a code readable now, is to “extract” the functions called by the Listeners or Responders and place them outside the definition of the Listener / Responder it self.

You have an excellent article on the same topic here. The author proposes an interesting solution to standardize / normalize the second solution by build a Prototype class.

 

 

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