Records or rows are the places in your database tables where you store actual data. Each row has all the fields defined in the table, and each field contains a single value as we saw previously. Values stored in an Arca database are automatically translated to the Lingo types supported: string, integer, float, picture, media and image objects, and ByteArrays in Director 11.5 as well.
You can use the Arca Database Browser application to add data to your database using a familiar spreadsheet-like interface. There is no need to write functions or Lingo code to import your data, unless you want to do so. Click the BROWSE DATA tab in the application, and use the ADD RECORD button to create a new, empty record. You can type data directly in the fields, or use copy and paste shortcuts. There are also commands to delete records, and a FIND button that you can use to locate information on large databases. If you need more help using the Arca Database Browser application try selecting the HELP->WHAT'S THIS? menu item, and clicking on a button or control in the application. The figure below shows a minimized BROWSE DATA view in the middle of a data editing session.
The Arca Database Browser is an ideal solution to insert, delete and update strings and numeric data, but it can also be used to import images from PNG or JPEG files. PNG files are converted to image objects, and retain their alpha channel, if present. JPEG files are imported as media values wrapping the original JPEG file, with the original compression intact. The browser application is also only useful when you are preparing a database to be used in your Director projector, and you can even redistribute it freely to your customers if they have a need to edit or consult the database files you have generated. However in most cases you will want to insert and update records using Lingo, directly from your Director application. In this case you have to use the Lingo functions available in the Arca Xtra.
The Arca Xtra uses standard SQL commands and the executeSQL function to insert data in your databases. The syntax for this uses the standard SQL command INSERT. For example to insert information about a new user in our sample table we could use the following SQL command:
gDB.executeSQL("INSERT INTO users VALUES('john', 33, '178 Bond Street')")
Notice how you always have to quote string values, or the command will generate a SQL error. That's the reason why you need to use 'john' and not simply john in your SQL command string. You will of course notice that this can quickly become a problem: what if you want to insert the string ' john's "good old" house' in the database? This value has both the ' and the " characters, and it would cause Lingo errors if you enter it directly in the executeSQL command. The standard way to deal with this problem in other SQL databases is called "escaping". You use a special character (\) to mark the characters to be escaped. In this case, the standard SQL way to insert this string would be to modify it to 'john\'s \"good old\" house'. You can do this if you want, but it is more complicated than what it needs to be.
Fortunately Arca provides a way for you to enter values without worrying about quoting and escaping. Just mark with question marks the positions in the SQL string where your values need to appear, and include the values in a Lingo list following the SQL query string. In our case, you could write:
Lingo:
gDB.executeSQL("INSERT INTO users VALUES(?,?,?)", ["john", 33, "178 Bond Street"])
JavaScript syntax :
gDB.executeSQL("INSERT INTO users VALUES(?,?,?)", list( "john", 33, "178 Bond Street"))
In most cases the values you are going to insert or update are coming from a text or field member, or a scripting variable. This method allows you to use these values directly without worrying about special characters, as any of the acceptable Director value types (string, float, integer, media, picture, image) can be included in the list. For example:
Lingo:
gDB.executeSQL("INSERT INTO users VALUES(?,?,?)", [member(1).text, gMyAge, sprite(2).member.image])
JavaScript syntax:
gDB.executeSQL("INSERT INTO users VALUES(?,?,?)", list(member(1).text, gMyAge, sprite(2).member.picture))
Arca will verify, convert and quote/escape the values automatically for you, so there is no need to worry about special characters not supported in SQL commands. It will even compress the media and picture data automatically for you, using lossless compression algorithms in order to reduce the size of your database!
The same syntax if valid for the SQL UPDATE command, which is used to update data in a table. One example:
Lingo:
gDB.executeSQL("UPDATE users SET age=? WHERE name=?", [gNewAge, gUserName])
JavaScript syntax :
gDB.executeSQL("UPDATE users SET age=? WHERE name=?", list(_global.gNewAge, _global.gUserName))
The command above will look for records with the NAME field matching the value of the global gUserName script variable, and will update the AGE field of each one to the value of gNewAge.
A tip: this technique of using a Director list and '?' characters inside the SQL string can be used for any SQL command you use with Arca, both in the executeSQL and createSelection Arca commands, as you can see in the example below:
Lingo:
minimumAge = 18
myselection = gDB.createSelection("SELECT * from users WHERE age>?", [minimumAge])
JavaScript syntax :
var minimumAge = 18
var myselection = gDB.createSelection("SELECT * from users WHERE age>?", list(minimumAge)
Finally you can also use SQL commands to delete records. A sample line would be something like:
Lingo:
gDB.executeSQL("DELETE FROM users WHERE name LIKE ?", ["ma%"])
JavaScript syntax:
gDB.executeSQL("DELETE FROM users WHERE name LIKE ?", list( "ma%"))
This command uses the SQL 'LIKE' operator to delete all records in the user table where the name starts with the letters 'ma'. This will delete the records of users named "mark" and "MARCUS" for example, as well as all other records in the table that match the criteria following the WHERE keyword. If you want to delete all records in a table you can use something like:
gDB.executeSQL("DELETE FROM users")
Since there are no operators the query will match all records, and all rows will be removed.
Now that you know how to insert and update values in the Arca Database let's see how you can retrieve information stored in it.