Arca Database Xtra ARCA DATABASE XTRA HELP: XTRA FUNCTIONS  
 

The following is a list of scripting functions available after installation of the Arca Database Xtra.


arcaregister([1111,2222,3333]) - global function, used to register the Arca Database Xtra. It can be called at any time, usually when the Director movie starts, before your Arca database is opened or created. Unregistered versions of the Xtra are fully functional for evaluation purposes, but they will display a warning the first time a database is opened or created.

Arca serial number are strings, and have the generic format ARCXX-1111-2222-3333, where XX is the major Xtra version. In order to protect your serial number from being included as a string in your Director projectors or dcr movies, the arcaregister function requires only the three groups of numbers 1111, 2222 and 3333 inside a Director list. Leading zeroes do not need to be entered.

An example: if your serial number is ARC10-0123-4567-0089 then you should register using the following command, usually on a startmovie handler:

Lingo:

arcaregister([123, 4567,89])

JavaScript syntax :

arcaregister(list(123, 4567,89))

 

 

new(object me) - Used to create an instance of the Arca Database Xtra. This is the standard command used in Director to create scripting xtra objects, and returns an instance of the Xtra required to use additional Lingo functions.

Lingo:

myDB = new(xtra "arca")

put myDB

-- <Xtra child "Arca" 2 239d10>

JavaScript syntax :

myDB = new xtra("arca")

trace (myDB)

//<<Xtra child "Arca" 2 239d10>>

 

 

xtrainstance.explainError(integer errorCode) - Used to translate Arca error codes into human readable strings. This command can be used to check and present results of error messages to the user of your application. For a list of error codes please consult the Arca error code table.

Lingo:

put myDB.explainError(604)

-- "Can not find the database file"

JavaScript syntax :

trace (myDB.explainError(604))

// "Can not find the database file

 

 

xtrainstance.openDB(string databasefile, optional string encryptionkey) - Open the file specified as a database. If your database file is encrypted you need to supply a second parameter, the encryption key. Returns a Lingo property list, containing any errors generated by the command as the #errorMsg property. Please notice that specifying a wrong encryption key does NOT immediately returns an error, but subsequent attempts to get/set data on the database will produce error message 26, "Database file is encrypted or is not valid". If you want to test for this condition immediately you can use the getDBschema() function after openDB, and check for any errors.

Lingo:

put myDB.openDB(the moviepath&"mydbfile")

-- [#errorMsg: 0]

put myDB.openDB(the moviepath&"myencrypteddbfile", "mysecretkey" )

-- [#errorMsg: 0]

JavaScript syntax:

trace (myDB.openDB(_movie.path + "mydbfile"))

// [#errorMsg: 0]

trace (myDB.openDB(_movie.path + "mydbfile", "mysecretkey"))

// [#errorMsg: 0]

 

 

xtrainstance.createDB(string databasefile, optional string encryptionkey) - Creates a new, empty database file at the location specified. To create an encrypted database you can supply a second parameter, the encryption key to be used. Returns a Lingo property list, containing any errors generated by the command as the #errorMsg property.

Example:

put myDB.createDB("E:\mydocuments\newdatabase")

-- [#errorMsg: 601]

put myDB.explainError(601)

-- "Can not create database file"

put myDB.createDB("C:\mydocuments\newencrypteddatabase", "mysecretkey" )

-- [#errorMsg: 0]

 

JavaScript syntax:

trace (myDB.createDB("E:\mydocuments\newdatabase"))

// [#errorMsg: 601]

trace ( myDB.explainError(601))

// "Can not create database file

trace (myDB.createDB("C:\mydocuments\newencrypteddatabase", "mysecretkey" ))

// [#errorMsg: 601]

 

 

xtrainstance.changeKey(string newencryptionkey) - Changes the encryption key for the current database file. This function should be used after the database file is opened with openDB or createDB. To remove encryption and make the file compatible with standard SQLite 3.x databases you can use an empty string as the new key. The encryption key should be a string longer than 6 standard characters (numbers or letters). Returns a Lingo property list, containing any errors generated by the command as the #errorMsg property.

Example:

put myDB.changeKey("mynewkey")

-- [#errorMsg: 0]

--Setting an empty key removes encryption

put myDB.changeKey("" )

-- [#errorMsg: 0]

 

JavaScript syntax:

trace (myDB.changeKey("mynewkey"))

// [#errorMsg: 0]

// Setting an empty key removes encryption

trace (myDB.changeKey("" ))

// [#errorMsg: 0]

 

 

xtrainstance.executeSQL(string sqlquery , (optional) list values) - This function is the heart of the Arca Database Xtra. It can be used to create tables and indexes, update, delete or insert data, and to retrieve information from the database, all using standard SQL queries. The sqlquery parameter is the sqlquery to be executed. An optional list of Lingo values may be passes as a second parameter, and the Xtra will automatically replace all occurrences of the interrogation mark (?) character in the SQL query with the corresponding values in the list, already quoted and prepared for SQL compliance. A list of SQL commands supported by Arca can be found at the Supported SQL table, and is directly inherited from the strong SQL support offered by the SQLite engine used in Arca.


This command returns a property list containing the following properties:

#errorMsg: any error messages generated during the execution of the command. You can use the explainError function to retrieve a text explanation of the error, or consult the error table in this guide.

#rowschanged: the number of rows affected by the SQL command. This value is only useful when UPDATE, INSERT and DELETE commands are issued, and contains the number of records changed after the command was executed.

#rows: the records returned after execution of an SQL command, usually SELECT. Each record is returned as an individual list in the #rows list value, so the developer can use rows.count to determine the number of records available.

#columns: a list containing the name of the fields retrieved by your query, usually a SELECT statement.

#columntype: a list with the types of each field retrieved, matching the number of elements in the #columns list.


For more information about detailed usage of the executeSQL function please read the Inserting, Deleting and Updating and Retrieving Information pages in this user guide.

Lingo examples:

put myDB.executeSQL("INSERT INTO users VALUES(?,?,?)", [member(1).text, gMyAge, sprite(2).member.picture])

-- [#rowschanged: 1, #columns: [], #columntype: [], #rows: [], #errorMsg: 0]

put gDB.executeSQL("SELECT name, age FROM users WHERE age<=18")

-- [#rowschanged: 0, #columns: ["name", "age"], #columntype: ["text", "integer"], #rows: [["John", 15], ["Mary", 13], ["Paulo", 10]], #errorMsg: 0]

JavaScript syntax examples:

trace (myDB.executeSQL("INSERT INTO users VALUES(?,?,?)", list(member(1).text, gMyAge, sprite(2).member.picture))

// [#rowschanged: 1, #columns: [], #columntype: [], #rows: [], #errorMsg: 0]

trace (gDB.executeSQL("SELECT name, age FROM users WHERE age<=18"))

// [#rowschanged: 0, #columns: ["name", "age"], #columntype: ["text", "integer"], #rows: [["John", 15], ["Mary", 13], ["Paulo", 10]], #errorMsg: 0]

 

 

xtrainstance.createSelection(string sqlquery , (optional) list values) - This function has the same syntax as the executeSQL function. The sqlquery parameter is the SQL query to be executed. An optional list of Lingo values may be passes as a second parameter, and the Xtra will automatically replace all occurrences of the interrogation mark (?) character in the SQL query with the corresponding values in the list, already quoted and prepared for SQL compliance. This function is used with an SQL query containing a SELECT statement, in order to produce a selection to be explored with the getRows and getField function.


This command returns a property list containing the following properties:

#errorMsg: any error messages generated during the execution of the command. You can use the explainError function to retrieve a text explanation of the error, or consult the error table in this guide.

#selectionid: a numeric id of the selection generated by this SQL query, to be used with the getRows and getField functions to access records.

#numrows: the number of rows contained in the selection.

#columns: a list containing the name of the fields retrieved by your query, usually a SELECT statement.

#columntype: a list with the types of each field retrieved, matching the number of elements in the #columns list.


For more information about detailed usage of the createSelection function please read the second part of the Retrieving Information page in this user guide.

Lingo:

mySelection = myDB.createselection("SELECT * FROM users")

put mySelection

-- [#errorMsg: 0, #selectionid: 27881928, #numrows: 1, #columns: ["name", "age"], #columntype: ["text", "text"]]

JavaScript syntax :

mySelection = myDB.createselection("SELECT * FROM users")

trace (mySelection)

// [#errorMsg: 0, #selectionid: 27881928, #numrows: 1, #columns: ["name", "age"], #columntype: ["text", "text"]]

 

 

xtrainstance.getRows(integer selectionid, integer firstRow, integer lastRow) - Used to retrieve specific rows from a previously created selection value. This can be used to control how may rows are retrieved and accessing only the data that needs to be displayed at any given time, optimizing the performance of your application.


This command returns a property list very similar to the one returned by the executeSQL command, containing the following properties:

#errorMsg: any error messages generated during the execution of the command. You can use the explainError function to retrieve a text explanation of the error, or consult the error table in this guide.

#rows: the records specified by the firstRow and lastRow values. Each record is returned as an individual list in the #rows list value, so the developer can use rows.count to determine the number of records available.

#columns: a list containing the name of the fields retrieved by your query.

#columntype: a list with the types of each field retrieved, matching the number of elements in the #columns list.


For more information about detailed usage of this function please read the second part of the Retrieving Information page in this user guide.

Lingo:

mySelection = myDB.createselection("SELECT * FROM users")

put myDB.getRows(mySelection.selectionid , 1, 3)

-- [#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]

JavaScript syntax :

mySelection = myDB.createselection("SELECT * FROM users")

trace (myDB.getRows(mySelection.selectionid , 1, 3))

// [#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]

 

 

xtrainstance.getField(integer selectionid, integer rownumber, string fieldname) - Used to retrieve a specific Lingo value from a selection. This commands takes the row number and the name of the field to be retrieved, and returns a Lingo value directly. Field names are not case sensitive. This can be more convenient since the developer does not need to parse a result list.

This command returns the Lingo value requested if all parameters are valid, or <Void> if there is a problem (for example you specify a field name that does not exist, or an invalid selection id.)

For more information about detailed usage of this function please read the second part of the Retrieving Information page in this user guide.

Lingo:

mySelection = myDB.createselection("SELECT * FROM users")

put myDB.getField(mySelection.selectionid, 200, "age")

-- 32

put myDB.getField(mySelection.selectionid, 200, "nonvalidfieldname")

--<Void>

JavaScript syntax :

mySelection = myDB.createselection("SELECT * FROM users")

trace (myDB.getField(mySelection.selectionid, 200, "age"))

// 32

trace (myDB.getField(mySelection.selectionid, 200, "nonvalidfieldname"))

//<Void>

 

xtrainstance.freeSelection(integer selectionid) - This function frees memory allocated to the specified selection object. You should also release all selections allocated by createSelection commands when you no longer need them. The Xtra will automatically release memory when the Xtra instance object is destroyed or a database file is closed, but it is good practice to control memory usage at all times.

A developer can also use the parameter 0 as the selectionid to cause the Xtra to release memory and discard all selections currently active for this Arca instance.

This command returns a Lingo property list, containing any errors generated by the command as the #errorMsg property.

For more information about usage of this function please read the second part of the Retrieving Information page in this user guide.

Lingo:

mySelection = myDB.createselection("SELECT * FROM users")

put myDB.getField(mySelection.selectionid, 200, "age")

-- 32

put myDB.freeSelection(mySelection.selectionid)

--[#errorMsg: 0]

JavaScript syntax :

mySelection = myDB.createselection("SELECT * FROM users")

trace (myDB.getField(mySelection.selectionid, 200, "age"))

// 32

trace (myDB.freeSelection(mySelection.selectionid))

//[#errorMsg: 0]

 

 

xtrainstance.closeDB() - Close the database file associated with this instance of the Arca Database Xtra. Returns a Lingo property list, containing any errors generated by the command as the #errorMsg property. Database files are also automatically closed by Arca when the Xtra instance object is destroyed.

Lingo:

put myDB.closeDB()

-- [#errorMsg: 0]

JavaScript syntax :

trace (myDB.closeDB())

// [#errorMsg: 0]

 

 

xtrainstance.compactDB() - Compacts the database file on disk, removing any empty spaces that are left unused when records are deleted and no new records are created to fill the space. This is usually not necessary unless you are deleting a large number of records and do not plan on adding new data: empty space in the database file will be reused automatically when new data is inserted. Returns a Lingo property list, containing any errors generated by the command as the #errorMsg property. Compacting the database is a relatively expensive operation, and it takes some time to complete. It also can change the value of the internal ROWID field, so if you are depending on ROWID as a fixed key this operation should not be used (most applications with this requirement should use an explicit INTEGER PRIMARY KEY field, however).

Lingo:

put myDB.compactDB()

-- [#errorMsg: 0]

JavaScript syntax :

trace (myDB.compactDB())

//[#errorMsg: 0]

 

 

xtrainstance.getDBSchema() - This utility command is meant to be used for tools that need to inspect the structure of the database using Lingo. It returns a property list containing the #rows value: each row is one object in the database structure (a table or an index), and they type of object, name, referring table (for indexes) and sql command used to create it are displayed. Developers working with database files may prefer to use the DATABASE STRUCTURE view of the Arca Database Browser application for easier inspection of Arca database structures.

Lingo:

put myDB.getDBschema()

-- [#errorMsg: 0, #rowschanged: 0, #columns: ["type", "name", "tbl_name", "sql"], #columntype: ["text", "text", "text", "text"], #rows: [["table", "users", "users", "create table users(name text, age text)"]]]

JavaScript syntax :

trace (myDB.getDBschema())

// [#errorMsg: 0, #rowschanged: 0, #columns: ["type", "name", "tbl_name", "sql"], #columntype: ["text", "text", "text", "text"], #rows: [["table", "users", "users", "create table users(name text, age text)"]]]

 

 

xtrainstance.getLastInsertRowid() - This utility command returns the rowid of the record created by the last INSERT SQL command, or <VOID> if there has been no inserts in the current database session. It is meant to be used when your application wants to insert an empty record in the database to be filled later, and you need some way to keep a reference to the newly created row.

Lingo:

myDB.executeSQL("INSERT INTO users VALUES('john',22,'2345678')")

put myDB.getlastinsertrowid()

-- 1345666

JavaScript syntax:

myDB.executeSQL("INSERT INTO users VALUES('john',22,'2345678')")

trace (myDB.getlastinsertrowid())

// 1345666

 

 

xtrainstance.setEncoding(symbol side, symbol newencoding) - In Director 8.5, MX and MX 2004, Arca by default uses the #MACROMAN client encoding when translating data on the Macintosh platform, and the #LATIN1 client encoding when translating string data on Windows. The default database encoding is UTF8. In Director 11 (all platforms) the client encoding is also UTF8, so no translation is needed. For almost all applications there is no need to change the default encoding, so this function should never be used. However, if you are trying to use Arca to store double byte text on older versions of Director it might be necessary to disable the translation, so string data will stored exactly as passed. Valid values for the side parameter are #CLIENT and #DB. Valid values for the newencoding parameter are #UTF8, #LATIN1, #MACROMAN and #NONE. Please check the additional features page for more information on database encodings.

Lingo:

myDB.setEncoding(#CLIENT, #NONE)

myDB.setEncoding(#DB, #NONE)

JavaScript syntax:

//for compatibility with databases converted from Arca 1.x

myDB.setEncoding(symbol("DB"), symbol("LATIN1"))