Below is a simple OpusScript example showing how you can use the OpusScript Database functions in a Script Object or Script Action. The example shows in which order you should use the Database functions.
The full script is:
var db = new Database("DSN=Products;")
var RecSet = db.ExecuteSQL("SELECT Price, Qty FROM Customer_Order WHERE OrderID=1;")
var total = 0
// Setup loop for all the records found
for (var i=1; i<= RecSet.GetNumberOfRecords(); i++)
{
total += RecSet.Price * RecSet.Qty
RecSet.NextRecord() // Move to the next record
}
1. The first thing you must do when using the OpusScript Database functions is to create a new Database object in your script:
var db = new Database("DSN=Products;")
Note:
In this example, the new Database object is connecting to a database named Products, which contains the data you want to work with. It connects to the database using the Data Source Name (DSN) protocol, which is described in Connecting to a Database in the main Help file.
2. Once you have established a connection to a database, your next task is to get the script to work with the exact data you want from the database. A database simply stores information in a structured format. Typically, a database is made up of a number of Tables that contain records. Each record contains fields that hold information. In this example, the database is called Products and it contains a Table named Customer_Order, every record in this table contains a Price, Qty and OrderID field.
Note:
A language called SQL (Structured Query Language) is typically used to select the exact data you want from a database. Any records that match the SQL statement are saved in a Record Set.
In OpusScript, the ExecuteSQL function allows you to enter an SQL statement, for example
var RecSet = db.ExecuteSQL("SELECT Price, Qty FROM Customer_Order WHERE OrderID=1;")
In this example, the ExecuteSQL function will select the Price and Qty fields from the table Customer_Order if the OrderID field contains the value 1. Each record that matches this SQL statement will be saved into the variable RecSet – this is the Record Set for this SQL statement.
Certain OpusScript Database functions work only with the Record Set that is created, these include the following functions: NextRecord, PreviousRecord; FirstRecord, LastRecord, GetRecordAt, GetRecordAtRelative, GetNumberOfRecords and GetCurrentRecordNumber. In order to use these functions, you must refer to the variable that contains the name of the Record Set (in this example, the variable is named RecSet). Three examples are given in this example and are described in the next paragraphs.
3. Now that you have built a Record Set of records that match your selection criteria, you can use the other OpusScript Database functions to perform a variety of other useful operations. In this example, a for loop function is used with the Database function GetNumberOfRecords:
for (var I=1; I <= RecSet.GetNumberOfRecords(); I++)
Note:
The GetNumberOfRecords function will find the number of records contained in the variable RecSet (the new Record Set this script has just created). It uses the number of records in the Record Set to calculate the number of times it must run the commands contained inside the loop.
4. The actual loop contains two commands. The first command is this:
total += RecSet.Price * RecSet.Qty
This command performs a calculation and adds the result of the calculation to the variable called total. The calculation uses the current value contained in the Price field of the currently selected record in the Record Set and multiplies it by the current value contained in the Qty field of the selected record in the Record Set.
The second command is this:
RecSet.NextRecord()
This command uses the OpusScript Database function NextRecord to go to the next record in the Record Set called RecSet. The loop is then run again until there are no more records in the Record Set.
This simple example, shows some three of the main things you need to include in a script when using the Database functions:
(i) First you must create a new Database object.
(ii) Then use an ExecuteSQL function to create a new Record Set.
(iii) Use the other OpusScript Database functions with the new Record Set.
Note:
Databases are described in the main Opus Help file. Select the Contents & Index option from the Help menu at the top of the Opus Editor. Search for the word database in the Index tab of the Help file to find topics related to the Database feature in Opus.