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
Comments are closed.