There are two ways to retrieve rows (or records) from an Arca database file using the Arca Database Xtra. Both use SQL queries to determine which records and fields are going to be retrieved, and present the results as Lingo lists, ready to be used.
A) Using executeSQL
The first technique uses the standard executeSQL command, and the SQL SELECT keyword. This was explained briefly in the GETTING STARTED page, but let's see it again:
Lingo:
myresult = gDB.executeSQL("SELECT name, age FROM users WHERE age<=18")
put myresult
JavaScript syntax:
var myresult = gDB.executeSQL("SELECT name, age FROM users WHERE age<=18")
trace (myresult)
The result will be a property list, containing the information you have requested from the database. A sample output would look like:
[#rowschanged: 0, #columns: ["name", "age"], #columntype: ["text", "integer"], #rows: [["John", 15], ["Mary", 13], ["Paulo", 10]], #errorMsg: 0]
As you can see, the SELECT...FROM statement allows you to specify the fields and table to be used, and the WHERE operator filters the results. The result of this call is a Lingo property list. Always check the #errorMsg property before you use the other values. Example
Lingo:
if myResult.errorMsg<>0 then
alert("Problem getting data")
end if
JavaScript syntax:
if (myResult.errorMsg!=0){
_player.alert("Problem getting data")
}
You should also pay attention to the other values returned in the result property list. The #rows property will be a list containing the rows retrieved by your query. You can use for example
myResult.rows.count
to get the number of rows available, and
myResult.rows[1]
to get the first record retrieved as a Lingo list. You can take it a step further, of course. Suppose you want to retrieve the second field in the third row of the result, simply use
myResult.rows[3][2]
The #columns property is a list containing the name of the fields retrieved by your query. In the same way, the #columntype property contains a list with the types of each field retrieved. This is usually not needed but it may be necessary if you are building for example a database browser or some complex front end to a database catalog.
The final element is the #rowsChanged property. In the case of a SELECT command this will always be 0, since SELECT only retrieves data from the database, it does not modify it.
It is possible to retrieve an entire table in this way, with a command like:
gDB.executeSQL("SELECT * users")
Of course, this has the potential to create a very large list. It is a known fact that Director lists are not very fast, so for situations where you want to retrieve lots of data at the same time you should use a more advanced technique.
B) Using createSelection
The second method to retrieve data from an Arca database involves creating a selection, which is a collection of results in the database that can be explored using additional functions in the Xtra. This makes sense if your selection is expected to retrieve lots of matches (for example the whole database), and you want to retrieve just one row or a number of rows at a time. This is usually the case when you are browsing a database table, for example: you can instruct the Xtra to create a selection containing the whole table, but only present 10 or 20 records to the user at each page in your application.
You create a selection using a command very similar to the executeSQL technique:
myresult = gDB.createSelection("SELECT * FROM users WHERE age>18")
Any valid SELECT statement can be used to create a selection. As with the executeSQL method this will also generate a Lingo property list, but with different properties. In the example above, the following could be generated:
[#errorMsg: 0, #selectionid: 28556312, #numrows: 2442, #columns: ["name", "age", "address"], #columntype: ["text", "numeric", "text"]]
You can see that we will get the familiar #errorMsg, #columns and #columntype properties, with the same information found in the executeSQL results. But instead of a number of rows we will get two additional properties: #numrows and #selectionid.
The #numrows property is simply the number of rows available in your selection. In this case, it shows that 2442 records are available and match the search criteria (age>18). But how do you retrieve this rows? You do this with two additional functions in the Xtra: getRows and getField. In order to use these commands you are required to use the #selectionID property, so that the Xtra knows which selection you are trying to access. You can have several selections opened at the same time in your database.
Suppose you want to retrieve the first 3 rows in this result. You could use the getRows command, which takes the following parameters: selectionID, startingRow, endRow.
Lingo:
put gDB.getRows(myResult.selectionid , 1, 3)
JavaScript syntax:
trace (gDB.getRows(myResult.selectionid , 1, 3))
Result:
[#columns: ["name", "age", "address"], #columntype: ["text", "numeric", "text"], #rows: [["john", 22, "178 Bond Street"], ["mary", 70, "23 Circle Park"], ["anthony", 31, "4567 Privet Drive"]], #errorMsg: 0]
This command will produce a property list containing the same fields used in a normal executeSQL command, with a #rows property containing the requested data. The advantage of using this method is that Lingo only has to generate a #rows list with only 3 elements, instead of your whole selection. This is much faster, of course. In the same way, you can retrieve one unique row of data, using something like
myval = gDB.getRows(myResult.selectionid , 300, 300)
This will return the record number 300 in your selection in the #rows property of the result.
Another useful technique when working with selections is to retrieve a named field directly. Suppose you want to get the "age" field for the 1256th record in your selection, simply use
ageValue = gDB.getField(myResult.selectionid, 1256, "age")
Notice that this will return the Director value directly (or Void if the field could not be found), without the need to parse a list. This is the easiest way to get results, but it may not be as fast as getting the rows property directly if you are retrieving large amounts of data at the same time.
Finally, it is important to understand that selections take up memory when you create them. You should always keep track of your selections, and release the memory used by them when you no longer need them. The freeSelection command is used for this:
gDB.freeSelection(myResult.selectionid)
The Xtra will free the selections for you when you close your database file, but it is good practice to free the selections in your code when you are no longer using them. If you have lost track of your selection objects for some reason (maybe you have not saved a reference to the scripting variable that refers to it) you can use the command
gDB.freeSelection(0)
to free all selections currently referenced by this database.