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