Variations and Additional Information
The below topics offer information on varying approaches, and additional information that can be useful when using SenseTalk to work with databases.
Opening and Closing Connections
SenseTalk automatically establishes a connection when connection information has been provided and a table expression or other expression requiring information from the database is used. The connection is closed when no variables are populated with the table information or record data from the database.
However, it is also possible to explicitly open and close database connections. This is done using the open database and close database commands.
Open Database/ Close Database Commands
Behavior: Explicitly opens or closes the specified database connection.
Normally a database connection is only kept open as long as a variable that references it is in use, so connections will usually be closed at the end of any handler that creates them, or possibly even sooner. By explicitly opening a connection explicitly using the open connection command, it will be kept open until it is explicitly closed using the close connection command, including during calls to other scripts or handlers. This approach might be desirable if it is important to limit the number of individual connections made to the database. It also might be desirable for reasons of efficiency, if establishing a connection to that database is a particularly time-consuming process.
Parameter: A property list containing all necessary information for a database connection. For more information, see Database Connections.
Example: ODBC
Here is an example of opening an ODBC database connection, and then closing it again:
set myDB to {type:"odbc", DSN:"DataSource1", user:"root", password:""}
open database myDB
// Execute any database operations here
close database myDB
Example: Excel
Here is an example of opening an Excel database connection, and then closing it again:
set myExcelDB to {type: "excel", file: ResourcePath("MyCustomers.xlsx")}
open database myExcelDB
// Execute any database operations here
close database myExcelDB
Database Identifiers
When a database connection is opened, either explicitly by an open database command, or automatically when needed, a unique identifier is assigned to it.
databaseIdentifier() Function
Behavior: Returns the unique identifier associated with the database specified.
Parameter: A database reference (full property list defining the connection). For more information, see Database Connections.
Example:
set myDB to {type:"odbc", DSN:"mySQLDB", user:"root", password:decodetext("AR@ts:%j")} -- Define a database conection
put the databaseIdentifier of myDB -- Returns empty if myDB isn't open
openDatabases() Function
Behavior: Returns a list of the identifiers of all database connections that are currently open.
Parameter: A database reference (full property list defining the connection). For more information, see Database Connections.
Example:
put the openDatabases
Executing SQL Directly
For users experienced with SQL databases, an executeSQL() function is available to run any SQL statement directly. It returns a list of property lists for a select statement or for other statements that return information. SQL statements that perform operations without returning any information will return an empty list. Any errors throw an exception.
For SQL select statements that return one or more rows from a table, the executeSQL() function will not return SenseTalk record objects. The values that are returned in this case are ordinary property lists with no connection to the database. For more information, see Records vs. Property Lists.
executeSQL Function
Values: Two values must be passed to the executeSQL() function; the database connection, and the SQL statement to be executed.
Behavior: Runs the specified SQL statement directly and returns a property list.
Example:
This example defines a database connection and then executes SQL directly against that database using the executeSQL() function.
set myDB to {type:"odbc", DSN:"DataSource1", user:"root", password:""} -- Defines the database connection.
put executeSQL(myDB, "select * from Members") into listOfRowInformation -- Executes an SQL select statement against the defined database connection.
Example:
This example defines a database, and then executes SQL against it using the executeSQL() function, while using the SQL where clause (which is functionally the same as the SenseTalk where expression).
set myDB to {type:"odbc", DSN:"qaDB", user:"testplant", password:"eggplant"} -- Database connection definition
put executeSQL(myDB, "select* from Members where language_id='1'") into listOfRowInformation -- Executes an SQL select statement against the database that selects all of the records for the table called "Members" where the column "language_id" contains a value of 1, and stores that information in the listofRowInformation variable.
Automatic vs. Manual Database Updates
Database updates are saved automatically by default. However, using the autoSaveDatabaseUpdates global property, you have the ability to change this setting so that database updates are manual.
The AutoSaveDatabaseUpdates Global Property
Value: Boolean.
Default: True.
Behavior: Controls how updates are handled for all database tables. To turn off automatic database updates, set the autoSaveDatabaseUpdates global property to false.
To control the automatic save behavior for an individual table, set the autoSaveUpdates property of a table object. After disabling automatic saves, all changes to record values are recorded locally. Then you can write these changes to the database using the save changes command.
Example:
set the autoSaveDatabaseUpdates to false -- Turns off automatic updates
set infoto table "memberinfo" of myDB
set member to the record of info where memberNumberis16
Add 60 days to member's expirationDate
put "23" into member's memberNumber
put the records of info -- Displays: '[{expirationdate:"2020-04-13", membernumber:"16"},{expirationdate:"2019-12-30", membernumber:"18"}]'
save all changes to info -- Sends all pending updates to the table
put the records of info -- Displays: '[{expirationdate:"2019-12-30", membernumber:"18"},{expirationdate:"2020-06-12", membernumber:"23"}]'
When automatic saves are disabled, you can obtain the list of records fetched from a table that has unsaved updates by using the recordsToUpdate() function. These records are saved a when you use the save changes command. The following example shows how to disable the automatic saves and how to log the records that will be saved when you use the save changes command.
Example:
set myDB to {type: "odbc", DSN: "mySQLDB", user: "root", password: decodetext("AR@ts:%j")}
set the autoSaveDatabaseUpdates to false
set palette to table "colorpalette" of myDB
set myColor to the record of palette where color is "purple"
put "45" into myColor's brightness
log recordsToUpdate(palette) -- Logs '[{brightness:"45", color:"purple", ID:"3"}]'
If an error occurs when writing any record update to the database (either as a result of a save changes command or from an automatic save), SenseTalk throws an exception. If a try/catch block is in effect to catch that exception, the failedUpdateRecords() and failedUpdateExceptions() functions can be called on a table to get a list of records with changes that failed to update, and the exceptions that occurred for those records, respectively.
Example:
try
set info to table "memberinfo" of myDB
set member to the record of info where memberNumber is 18
put "05/25/2018" into member's expirationDate
catch
put failedUpdateRecords(info) -- Displays '[{expirationdate:"05/25/2018", membernumber:"18"}]'
put failedUpdateExceptions(info) -- Displays '(ST_ODBC_ResultError: [MySQL][ODBC 5.3(a) Driver][mysqld-5.7.11-log]Incorrect date value: '05/25/2018' for column 'expirationdate' at row 1, SQLSTATE=HY000)'
end try
Database Information Functions
The following functions can be used to get additional information about a database, table, or the database types that are available.
databaseVersion(<database>) Function
Behavior: Returns version information about the specified database server.
Parameter: A database reference (full property list defining the connection). For more information, see Database Connections.
schemaNames(<database>) Function
Behavior: Returns the names of all of the database schemas (sometimes just called databases) that are available through a connection. Some database adapters may not support this functionality.
Parameter: A database reference (full property list defining the connection). For more information, see Database Connections.
tableNames(<database>) Function
Behavior: Returns a list of the names of all of the tables in a database. Table names are required when using table expressions.
Parameter: A database reference (full property list defining the connection). For more information, see Database Connections.
recordInfo(<table>) Function
Behavior: Returns information about all of the table’s columns and relationships.
Parameter: A database reference (full property list defining the connection). For more information, see Database Connections.