Arca Database Xtra is a Scripting Xtra. Scripting Xtras are used to extend the Lingo language with new functions and datatypes. Unlike Asset Xtras there is no visual representation of a scripting Xtra in the Director interface, and you can not create castmembers or sprites. The creation of Scripting Xtras is done in your Lingo or JavaScript syntax scripts, by using the #new Lingo keyword.
The first step is to download and install the Arca Database Xtra, following the instructions in the installation page. Now that Arca is installed, let's verify that the installation was successful. If you are using DirectorMX you should see the Arca entry in the Scripting Xtras context menu, appearing at the top of the message window. Selecting the ARCA submenu and the "put interface" entry will output a list of all commands understood by Arca in the message window. You can also use the following command
Lingo:
put the xtralist
JavaScript syntax:
trace(_player.xtraList)
to verify which Xtras are installed, including the version number for each one.
We will now walk through a simple Lingo session using Arca and the message window. It is not necessary to actually try it in Director right now since you probably do not have this database in your system. For now, just try to read the script and understand what it is supposed to be doing:
Lingo:
global gDB
gDB = new (xtra "arca")
gDB.openDB(the moviepath & "mydatabase")
JavaScript syntax:
_global.gDB = new xtra("arca")
_global.gDB.openDB(_movie.path + "mydatabase")
This script creates a new instance of the Arca Xtra, stores it in a global object called gDB, and opens a previously created database file named "mydatabase", located in the same directory of your current movie. The database file is not encrypted, so we only need to pass one parameter to the openDB function, the name of the database file.
Suppose you know that the database file has one table called "users", containing the fields "name" and "age". The following command will retrieve a list with the names of all users which are 18 years old or younger:
Lingo:
myresult = gDB.executeSQL("SELECT name, age FROM users WHERE age<=18")
put myresult
JavaScript syntax :
var myresult = _global.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]
Almost all Arca commands produce a property list as an output. You should always check the #errorMessage field for possible errors: 0 indicates that there was no error. A function called explainError() is available to translate numeric error codes into text messages, and we will cover it in the next pages.
The records matching your search criteria can be found using direct access to the result property list. You can use something like:
Lingo:
put myResult.rows.count -- to output the number of records
put myResult.rows[1] -- to get the first record
put myResult.rows[1][2] -- to get the second field (column) of the first record
JavaScript syntax :
trace (myResult.rows.count ) -- to output the number of records
trace (myResult.rows[1]) // to get the first record
trace ( myResult.rows[1][2]) //to get the second field (column) of the first record
When you have finished working with the database you should close the database file. The Xtra will automatically close the database for you if a problem occurs, if the Xtra object is destroyed or when the application exits as well, but it is good practice to close the database file in your code:
Lingo:
gDB.closeDB()
JavaScript syntax :
_global.gDB.closeDB()
Finally, you should dispose of the Xtra object:
Lingo:
gDB = 0
JavaScript syntax :
_global.gDB = 0
That's it. This sample script summarizes the basic process of interfacing with the Arca database, and retrieving data from it. Of course this only covers the basic functions of the Xtra: there are more powerful features like the ability to create selections and return rows or columns individually, as well as insert or update data and even create or define completely new databases. If you are in a hurry please consult the XTRA FUNCTIONS page for more information about specific commands.
You may have noticed that we have actually used only one function of the Xtra to interact with the database and retrieve data: the executeSQL() function. This function is used for almost anything in Arca, including querying the database, creating new records, creating new tables, deleting or updating records. There is one reason for this: Arca uses standard SQL queries for almost all data manipulation. SQL is the default query language used by almost all standard database engines, including Microsoft SQL Server, MySQL, PostgreSQL, DB2 and Oracle. A minimal knowledge of SQL is required in order to use Arca successfully. But don't worry, as we will cover the basic SQL syntax required to use Arca in the next pages. Learning a little bit of SQL will be very useful for any database related work you need to develop in the future, be it with Director or any other language or technology, including dynamic web sites.
Now that you have an idea about how Arca is used let's examine the basic structure of an Arca Database.