OpenScript Stored Procedures

OpenScript Stored Procedures

OpenBase SQL makes it easy to write server-side OpenScript stored procedures (OpenScript SP) to implement business logic. By centralizing business logic inside the database, OpenBase SQL enables any type of database client (web and client/server) to access and use the same processes, significantly reducing code duplication, complexity and time-to-deploy.

videoicon3.png Video: OpenScript Stored Procedures

Creating OpenScript Stored Procedures

There are two ways to create OpenScript stored procedures. The recommended way is to use the OpenBase Manager application on MacOS X because it has a built-in OpenScript editor which allows you to manage your procedures from remote locations. Alternatively you can edit the script files directly.

Using the OpenBase Manager to Create Scripts
The OpenBase Manager Script editor can be accessed by selecting a database and choosing Edit Database Scripts from the action menu. A list of OpenScript procedures will appear allowing you to Add and Remove. To edit a script script, double click the script name.

The only difference between the OpenBase Manager editor and the OpenScript files is that there are no Script SP… End SP keywords in the OpenBase Manager editor. If you are using the OpenBase Manager, ignore these keywords in the examples below.

Creating OpenScript Files

To create an OpenScript stored procedure create a file with the name of the stored procedure you want to create and add a ".script" extension. This will identify the file as an OpenScript file. You can use a basic text editor to create the file. It is important that whichever editor you use is set for plain text.

OpenScript files must be placed either in your database's Procedure directory or the global Procedures directory (located in /Library/OpenBase/ Procedures). To place the procedure in the dB bundle, control select the dB bundle and select 'Show Package Contents' from the context menu. This will then display the contents of the dB including the appropriate folders.

OpenScript stored procedures always start with the keywords "Script SP" and end with the phrase "End Script". The capitalized "SP" stands for Stored Procedure. The code entered between these lines can include multiple functions. However, it is required that one of the functions have the name of the stored procedure. That will be the starting point.

Note: If you are using OpenBase Manager to edit your scripts, "Scripts SP" and the closing "End Script" lines should not be entered. The OpenBase Manager automatically adds these lines when scripts are saved.

Example: HelloWorld
This example illustrates the creation of a stored procedure, for the script file "HelloWorld.script". Place this file in the procedures directory inside your database's .db directory (/Library/OpenBase/Databases/YOURDB.db/ Procedures). If there is no procedures folder then you need to create one. Ensure that you use all lower case characters when you do this. You will also need to have root access in order to amend the dB bundle and add the new folder and procedures.

HelloWorld will contain one parameter. The entire code of a function appears between the "on function()" and "end function", where function is the name of your function. In our case we define "on HelloWorld(name)" and "end HelloWorld". Parameters are placed in a comma-separated list of values, between the parentheses. All functions, regardless of whether they have parameters, must contain the parentheses.

Script SP
    on HelloWorld(name)
        return "Hello " & name
    end HelloWorld
End Script

The & operator acts as a string concatenation operator in this case. The return command tells when to stop execution and return to the calling routine or return a result.

To call this stored procedure using openisql:

openbase 1> call HelloWorld("Matthew")
openbase 1> go

Note: You need to introduce a newly created procedure to your database. The best way to do this is from OpenIsql. When you have logged on to the relevant database, you need to do:

openbase 1>reload_procedures
openbase 2>go

This will fill the various _SYS_ tables with metadata about the procedures and make them available for calling.

Alternatively you can restart your database and your procedure will be recognized.

More Complex Stored Procedures

In this next example, we add another parameter to a stored procedure and another function. For this stored procedure you will need to create a HowOldAreYou.script file and place it either in your database's Procedures directory or the global Procedures directory. Create the procedure as shown below:

on monthsFromYears(age)
    SET ageInMonths TO (age AS INTEGER) * 12 return ageInMonths
end monthsFromYears

on HowOldAreYou(name, age)
    return "Hello " & name & "! You are " & monthsFromYears(age) &" months old!"
end HowOldAreYou

How It Works: The SET variable TO value command shown in the monthsFromYears() function above allows you to assign a value to a variable. Variables do have types, but the default type is STRING. That is why we convert the age variable into an INTEGER using the "AS INTEGER" phrase before multiplying it by 12 months.

To call this stored procedure using openisql:

openbase 1> call HowOldAreYou("Scott", 36)
openbase 1> go

Calling a Stored Procedure

A stored procedure call can be invoked exactly like any other SQL command. You can use openisql to execute a stored procedure or call it directly from your program. Return values are returned exactly the same way as fetching the results of a select statement.

Parameters can be of any type. However, keep in mind that they are converted to string representations before being passed to stored procedures. Parameter values have a maximum length of 4,096 characters. If you want to pass something bigger than 4,096 characters, you may need to store the value in the database as a BLOB and then retrieve it from the stored procedure using a primary key.

You can also call stored procedures from any other stored procedure. However, with transactions, you need to be very careful that you do not create a deadlock. There can be instances in which the stored procedure mechanism flies beneath the radar of the built-in OpenBase deadlock avoidance system. To avoid a deadlock, it is recommended that you do not call any other stored procedures while inside a transaction, unless the sub procedure does not access the database.

OpenScript Language Syntax

In this section, we will discuss the programming structure of the OpenScript Language in more detail. We begin with a short overview of the control commands and operators to help you get started. Later in this document we will show how these commands can be used in conjunction with database operators and commands to interact with database tables.

Calling a Function
To call a function you can either include the procedure call as part of an expression or as part of a call statement. Here is an example of a simple function:

on foo ( )
        ALERT MESSAGE "hello world (prints to database log) "
        RETURN "hello world"
    end foo

To call this function as part of an expression:

SET A TO "This is the return value of foo:" & foo()

If you don't care about the return value, then you can alternatively use CALL:

CALL foo ()

Local and Global Variables
In OpenScript, variables are dynamically created and do not need to be declared. However, since dynamic variables take on the type of the value that it is assigned to, there may be cases where you want to declare the variable's type so it won't change.

There are two types of OpenScript variables: local and global. Local variables are declared and used only within a single OpenScript function. While the values of local variables can be passed to other OpenScript functions, they cannot be accessed directly outside the scope of the function in which they are declared.

Global variables are stored in the database server's memory and can be accessed globally within OpenScript as well as other database clients that access the global variables through SQL commands (see 416 Server Variables for details on SQL commands).

Declaring Local Variables
Variables are declared at the beginning of an OpenScript function. They follow this format:
DECLARE <variable name> AS <type>
DECLARE <variable name> AS <type> INITIALIZE <value>
Here are some examples:

on foo ( )
        DECLARE name AS STRING INITIALIZE "Sam"
        DECLARE age AS INTEGER INITIALIZE 35
        ALERT MESSAGE "hello " & name & "you are "& age
        RETURN "success"
    end foo

The variable types supported follow:

  • Integer
  • Boolean
  • Double
  • Datetime
  • String
  • Money
  • Date
  • Time
  • Array
  • Dictionary

Declaring Global Variables
As mentioned above, global variables are stored on the database server. They will still always need to be declared in your OpenScript functions, but the declaration will only happen the first time on the database server. Whenever you use the variable, the value will actually be fetched from the database.

Like local variables, global variables are declared at the beginning of an OpenScript function. They follow this format:

DECLARE GLOBAL <variable name> AS <type> INITIALIZE <value>

Here are some examples:

on foo ( )
        DECLARE GLOBAL lock_variable AS INTEGER INITIALIZE 0
        DECLARE GLOBAL server_name AS STRING INITIALIZE "www.foobar.com "

        RETURN "success"
    end foo

When a global variable is declared, it is initialized on the server with the value specified. After that point it will not be initialized again even if the variable is declared again.

Using Global Variables For Coordination

Since global variables can be seen by all database clients connected to the database server, they can be used for coordinating services.
For instance, in some cases you may want to run a process where you do not want two procedures to run at the same time. By declaring a global variable and incrementing the value using the thread-safe INCREMENT and DECREMENT syntax, you can easily make sure that only one process is allowed to run.

Here is an example:

on MyProcess ( )
        DECLARE GLOBAL MyProcess_lock AS INTEGER INITIALIZE 0

        -- create the lock and exit if more than one is running
        IF INCREMENT MyProcess_lock != 1 THEN
            DECREMENT MyProcess_lock
            RETURN "exited: "& MyProcess_lock & "processes running"
        END IF

        -- process source code here        
        -- decrement the value
        DECREMENT MyProcess_lock    
        RETURN "success"
     end MyProcess

IF, THEN, ELSE Statements

IF THEN ELSE statements provide a way to enter program logic. The condition is made up of a boolean comparison yielding a result. The basic IF, THEN, ELSE statement structure is:

IF boolean expression THEN
        codeblock
    ELSE
        altcodeblock
    END IF
    IF boolean expression THEN
        codeblock
    END IF

If condition is 0, the altcodeblock will be executed. Otherwise the codeblock will be executed. The codeblock and altcodeblock can be made up of any OpenScript statement.

Example:

IF X is equal to Y THEN
        PRINT "X is equal to Y"
    ELSE
        PRINT "X is NOT equal to Y"
    END IF

Operators

Operator Symbol Definition

Sounds like Compare N/A sounds like

Like Compare N/A like

is like

Equal = equal

equals

equal to

is

is equal to

Not Equal != does not equal

doesn't equal

is not

is not equal [to]

isn't

isn't equal [to]

Greater Than > comes after

greater than

is greater than

is not less than or equal [to]

isn't less than or equal [to]

Greater than Equal >= does not come before

doesn't come before

greater than or equal [to]

is greater than or equal [to]

not less than

isn't less than

Less Than < comes before

is less than

is not greater than or equal [to]

isn't greater than or equal [to]

less than or equal [to]

Less Than Equal <= does not come after

doesn't come after

is less than or equal [to]

is not greater than

isn't greater than

less than or equal [to]

SET Command

The SET command assigns the values of variables and the attributes of objects. For a complete list of object attributes available to be set, refer to the documentation on screen objects. The set command structure is:

SET variable TO value [AS type]
SET attribute OF object TO value [AS type]

AS type specifies that you want to change or set the target type of the variable. If the value is an Integer, for instance, adding "AS DOUBLE" will convert it to a double value. The variable types supported follow:

  • Integer
  • Boolean
  • Double
  • Datetime
  • String
  • Money
  • Date
  • Time
  • Array
  • Dictionary

Example:

SET myDateField TO "Jan 31, 1999" AS DATE

ARRAY Variables
Arrays can store a list of values that can be accessed by position. Arrays start at position 1. Here is an example of interacting with arrays:

DECLARE myArray AS ARRAY
    SET myArray TO ALLWORDS ( "Rainy day" )
    SET ITEM '1' OF myArray TO "Sunny"

The ALLWORDS function takes a string and separates the words. The third line changes the value in position 1 from Rainy to Sunny. The result is an array with the values "Sunny" and "day".

To print a description of the values you can do:

ALERT MESSAGE "PRINT:" & myArray

This will print all of the values to the database log. To append a value to this array you can do this:

SET myArray TO "Today"

… or …
SET ITEM 3 OF myArray TO "Today"

Arrays can also receive values from records or dictionaries. Here is how to get the values from a dictionary into an array:

DECLARE myArray AS ARRAY
DECLARE myDictionary AS DICTIONARY

-- prepare the dictionary
SET ITEM 'firstname' OF myDictionary TO "Joe"
SET ITEM 'lastname' OF myDictionary TO "Smith"

-- convert the dictionary to an array
SET myArray TO myDictionary
ALERT MESSAGE "NAME:" & myArray

The values Joe and Smith will be printed to the database messages file.

DICTIONARY Variables

Dictionaries are used to store key-value pairs.

DECLARE myDictionary AS DICTIONARY
-- initialize the dictionary
SET ITEM 'weather' OF myDictionary TO "Sunny"
SET ITEM 'firstname' OF myDictionary TO "Joe"
SET ITEM 'lastname' OF myDictionary TO "Smith"

To print a description of the values you can do:

ALERT MESSAGE "PRINT:" & myDictionary

This will print "weather = Sunny, firstname = Joe, lastname = Smith"

Dictionaries can also receive values from records or arrays. Here is how to get the values from a dictionary into an array:

-- convert the dictionary to an array
SET myDictionary TO myArray

In this example, myDictionary will receive the values from the array using the array position as the key. So the first value can be accessed using key "1", second value from the array will be assigned to "2", etc.

Converting a record to a dictionary will use the column names as the keys. This is done by setting the dictionary to the current location of an open cursor. The values will be copied.

SET myDictionary TO myCursor

Using Arrays & Dictionaries

OpenScript supports full array and dictionary manipulation and static declaration of values and key/value pairs. This section will introduce you to the basic operations that can be performed on arrays and dictionaries.

An array is a sequential set of values which can be referenced by position. An example of referencing a value in an array is:

SET var TO ITEM 1 OF myArray

A dictionary is a set of key/value pairs where each value is referenced by a key string. Here is an example where we are accessing the value for the string 'mykey':

SET var TO ITEM 'myKey' OF myDictionary

If you want to define an array or dictionary statically in your code, you would do it as follows:

SET array TO @('value','value','value')
SET dictionary TO @{'key1'='value','key2'='value','key3'='value'}

Arrays are defined with a @(). Dictionaries are defined by @{}.

Here are two examples of nesting arrays and dictionaries.

SET array TO @('value','value',@{'key1'='value','key2'='value','key3'='value'} )
SET dictionary TO @{'key1'='value','key2'='value','key3'=@('value','value','value')}

OpenScript also allows you to perform on dictionaries and arrays.

This combines two dictionaries:

SET new_dictionary TO dictionary1 & dictionary2

This adds the keys from a dictionary to the array:

SET new_array TO array & dictionary

This removes the keys in the array/dictionary from the dictionary:

SET new_dictionary TO dictionary - array
SET new_dictionary TO dictionary1 - dictionary2

This is how you set keys to a value in a dictionary:

SET ITEM 'key1' OF dictionary TO 'value1'

This is how you add an item to an array:

SET array TO array & string
SET array TO array + string

This copies the record values pointed to by the cursor into an array:

SET array TO cursor

This copies the record values pointed to by the cursor into a dictionary. Column names are used for the keys.

SET dictionary TO cursor

This is how you add a dictionary or array to an array

SET array TO array + array
SET array TO array + dictionary

Blowfish Encryption Commands

OpenScript supports Blowfish encryption routines which you can use to protect sensitive data. Here is a call that shows how you encrypt a value using a password:

SET encryptedValue TO ENCRYPT originalValue WITH PASSWORD "my password"

Here is how you decrypt the the value using a password:

SET originalValue TO DECRYPT encryptedValue WITH PASSWORD "my password"

File Operations

Below are examples of basic file operations:

OPEN FILE myfile AT "/tmp/test" FOR OUTPUT
    WRITE string TO myfile
    WRITELN string2 TO myfile
    FLUSH FILE myfile 
    CLOSE FILE myfile

    OPEN FILE myfile AT "/tmp/test" FOR APPEND
    WRITE string3 TO myfile
    CLOSE FILE myfile

Note: FLUSH FILE is optional. CLOSE FILE performs a flush.

OPEN FILE myfile AT "/tmp/test" FOR INPUT
    WHILE READ string FROM myfile DO
        PRINT string
    END WHILE
    CLOSE FILE myfile

Note: 'READ string FROM myfile' reads an entire line up to but not including the CR/LF.

OPEN FILE myfile AT "/tmp/test" FOR INPUT
    WHILE READ string LENGTH 5 FROM myfile DO
        PRINT string
    END WHILE
    CLOSE FILE myfile

Note: 'READ string LENGTH 5 FROM myfile' reads 5 character sized strings from the file. The CR/LF is included.

SLEEP

OpenScript offers a command to make a script sleep. The numerical value that follows the SLEEP keyword tells OpenScript how long it should sleep. Here is an example:

SLEEP 5

Process Control

OpenScript now allows you to control a process by reading and writing to the processes standard input/output. This capability can be used for many things, including remote terminal operations, install scripts, or multi-step processes.

Here is an example of opening a terminal shell and waiting until the ".app" output is received before continuing.

on checkForApp ( )
    OPEN PROCESS myProcess AT "/bin/sh" WITH TIMEOUT 10
    WRITELN "/bin/ls /Applications" TO myProcess
    SET ret TO EXPECT ".app" FROM myProcess
    CLOSE PROCESS myProcess
    return ret
END checkForApp

Note: Communication with the process is only bi-directional on Mac OS X.

SYSTEM CALLS

OpenScript also allows you to make system calls to the operating system using the SYSTEM() command. Here is an example:

CALL SYSTEM("cp /tmp/file1 /tmp/file2")

SWITCH Expression

The SWITCH statement allows you jump to a location based on the value passed to it. Each CASE is followed by an expression whose value must equal the switch expression in order for the code block to be executed. Only one code block is executed. SWITCH statement basic structure is:

SWITCH expression
        CASE expression:
            codeblock
        CASE expression:
            codeblock
    END SWITCH

Example:

SWITCH expression
        CASE 'hello':
            PRINT 'SWITCHED TO HELLO'
        CASE 'goodbye':
            PRINT 'SWITCHED TO GOODBYE' CASE 'hello world':
            PRINT 'SWITCHED TO HELLO WORLD' PRINT 'SECOND HELLO WORLD'
    END SWITCH

WHILE DO Statement

The WHILE DO statement allows you to perform loops. WHILE DO statement basic structure is:

WHILE condition DO codeblock END WHILE

The boolean expression "condition" is true as long as it evaluates to a nonzero value. The loop will continue to cycle as long as "condition" is true. The boolean expression "codeblock" can be comprised of a series of OpenScript statements.

Example:

SET A TO 0 AS INTEGER
WHILE A IS LESS THAN 10 DO
    PRINT "The value of A is " & A 
    SET A TO A + 1
END WHILE

Exception Handling: TRY… CATCH…

OpenScript now supports full exception handling to make it easier to trap and report error conditions.

The basic structure looks like this:

TRY
        - code goes here
    CATCH
        - exception code goes here
    END TRY

The basic idea is that if a problem is detected during the normal execution of your code which should be considered an error condition, you can thow an exception and the execution will jump to your exception code after CATCH .

There are two methods of throw an exception. They are REQUIRE and THROW.

REQUIRE condition

This throws an exception if the condition is not true. You could say something like this, for instance:

REQUIRE variable = 1

If variable is not equal to one, then the exception code is executed. If it is equal to one, the next command is executed.

THROW

This command is different than REQUIRE in that it does not check for an error condition, it just throws an exception. It is normally going to be used in conjunction with an IF THEN statemen, as follows:

IF variable != 1 THEN
        THROW
    END IF

Database Access

The core of the OpenScript Stored Procedure mechanism is the ability to execute SQL commands and create database cursors. This section describes various statements and commands which can be used to access your databases.

CREATE CURSOR Statement

This statement creates a database cursor using a dynamic SQL select string. The new cursor, designated by variable, can be used to retrieve data from the database. Below are a list of CREATE CURSOR syntax combinations which can be used to retrieve updateable cursors.

CREATE CURSOR variable AS sqlselectstring

Creates a result set using the SQL select statement or procedure call in sqlselectstring. If it is an SQL select statement, an updateable cursor will be created if it contains only one table and the _rowid column is included in the result.

CREATE CURSOR variable FOR TABLE tablename

Example:

CREATE CURSOR myCursor AS "select * from addresses "

This version of create table will instantiate an updateable cursor containing the entire table.

CREATE CURSOR variable FOR TABLE tablename WHERE constraintString
ORDER BY orderByString RETURN RECORDS maxRecords

Example:

CREATE CURSOR myCursor FOR TABLE "addresses" WHERE "x=y"
    ORDER BY "company ASC" RETURN RECORDS 5

This version of create table will instantiate an updateable cursor containing the result set which meets the where constraints specified in constraintString and ordered by the SQL order string orderByString. maxRecords is the number of the maximum records you want to return.

Any of the commands after the tablename may be ommitted depending on your application.

CREATE CURSOR variable FOR TABLE tablename WHERE constraintString
ORDER BY orderByString RETURN RECORDS startRecord TO endRecord

Example:

CREATE CURSOR myCursor FOR TABLE "addresses" WHERE "x=y"
    ORDER BY "company ASC" RETURN RECORDS 6 TO 100

This statement is the same as the last except that it defines a range of records to select. The TO statement makes the first number into a starting position and endRecord into the last position.

CREATE CURSOR variable FOR RELATIONSHIP relationshipName
USING SELECTION anotherCursor

Example:

CREATE CURSOR myCursor FOR RELATIONSHIP "toaddresses" USING SELECTION myCursor1

This version of the CREATE CURSOR statement provides a method of drilling down through a relationship to another table. The relationship transversed is relative to the current selection of anotherCursor and the relationshipName is the name of the relationship in the table represented by anotherCursor. The new cursor is instantiated with a new updateable result set of the related records in the target table.

READING CURSOR VALUES

When creating a database cursor the database results are returned as an ordered list of records. Each time you call NEXT ROW OF cursor, or another cursor positioning command, the currently selected record is changed.

Once the cursor is pointing to a specific record you can read the column values. There are two basic ways doing this: you can access the values by column number or column name.

Example 1: Accessing a cursor with column names
In this case we are accessing the values of a cursor by column name. The column name must be specified exactly as it is specified in the database. Dictionaries use case sensitive keys, so the case must match exactly.

CREATE CURSOR movieSelect AS 'SELECT _rowid, CATEGORY, REVENUE, TITLE FROM MOVIE'
WHILE NEXT ROW OF movieSelect DO
    SET REVNUE TO ITEM 'REVENUE' OF movieSelect
    SET TITLE TO ITEM 'TITLE' OF movieSelect
END WHILE

Example 2: Accessing a cursor with column numbers
Accessing cursors as an array is simply a matter of specifying the column number instead of using the column name. In this example we set the variables CATEGORY, REVENUE, and TITLE to the values in the cursor by specifying the offset (starting with 1).

CREATE CURSOR movieSelect AS 'SELECT _rowid, CATEGORY, REVENUE, TITLE FROM MOVIE'
WHILE NEXT ROW OF movieSelect DO
    SET CATEGORY TO ITEM 2 OF movieSelect
    SET REVENUE TO ITEM 3 OF movieSelect
    SET TITLE TO ITEM 4 OF movieSelect
END WHILE

How It Works: Cursor arrays are accessed starting with column 1 (not 0). In this case, we are setting the values equal to variables in your program. These values can be referenced as many times as you want from withing the WHILE…DO loop. Nested database operations are also allowed.

NEXT ROW OF Statement

This statement is a boolean conditional which increments the cursor to the next row in the result set. If the cursor is already at the end of the result, FALSE is returned. Otherwise TRUE is returned.NEXT ROW OF statement structure is:

NEXT ROW OF cursor

This is normally combined with a WHILE DO statement to increment through the results of a result set. For example:

CREATE CURSOR movieSelect AS 'SELECT rowid, CATEGORY, REVENUE, TITLE FROM MOVIE'
WHILE NEXT ROW OF movieSelect DO
    SET CATEGORY TO ITEM 2 OF movieSelect
    SET REVENUE TO ITEM 3 OF movieSelect
    SET TITLE TO ITEM 4 OF movieSelect
END WHILE

PREVIOUS ROW OF Statement

This statement is a boolean conditional which rolls back the cursor to the previous data row in the result set. If the cursor is already at the beginning of the result, FALSE is returned. Otherwise TRUE is returned. PREVIOUS ROW OF statement structure is:

PREVIOUS ROW OF cursor

This statement might be used in conjunction with the ADVANCE CURSOR statement to read a result set backwards. Here is an example:

CREATE CURSOR movieSelect AS 'SELECT _rowid, CATEGORY, REVENUE, TITLE FROM MOVIE'
ADVANCE CURSOR movieSelect
WHILE PREVIOUS ROW OF movieSelect DO
    SET CATEGORY TO ITEM 2 OF movieSelect
    SET REVENUE TO ITEM 3 OF movieSelect
    SET TITLE TO ITEM 4 OF movieSelect
END WHILE

RESET CURSOR Statement

The RESET CURSOR statement resets the database cursor so that it points to the beginning of the result set. RESET CURSOR statement structure is:
RESET CURSOR cursor

ADVANCE CURSOR Statement

The ADVANCE CURSOR statement sets the cursor to the end of the result set. ADVANCE CURSOR statement structure is:
ADVANCE CURSOR cursor

This statement might be used in conjunction with the PREVIOUS ROW statement to read a result set backwards. Here is an example:

CREATE CURSOR movieSelect AS 'SELECT _rowid, CATEGORY, REVENUE, TITLE FROM MOVIE'
ADVANCE CURSOR movieSelect
WHILE PREVIOUS ROW OF movieSelect DO
    SET CATEGORY TO ITEM 2 OF movieSelect
    SET REVENUE TO ITEM 3 OF movieSelect
    SET TITLE TO ITEM 4 OF movieSelect
END WHILE

UPDATING CURSOR VALUES

Updating cursors works the same way as reading cursors. As you increment through the list of values a column value may be changed using

the SET command. This will hold the change in memory until you call SAVE ALL CHANGES TO DATABASE. This command will generate all of the SQL to modify the database. See the following examples of updating cursors:

Example 1: Updating a cursor with column names

In this case the values of a cursor are using the column name to identify the target column. The column name must be specified exactly as it is specified in the database. They are case sensitive.

In the example below the REVENUE is set to a specific value. For the TITLE column the TITLE value is read and ( ) are added around the title, and then that string is used to update the database.

CREATE CURSOR movieSelect FOR TABLE 'MOVIE'
WHILE NEXT ROW OF movieSelect DO
    SET ITEM 'REVENUE' OF movieSelect TO '999000.00'
    SET ITEM 'TITLE' OF movieSelect TO "(" & ITEM 'TITLE' OF movieSelect & ")"
END WHILE

Example 2: Accessing a cursor with column numbers
Updating cursors as an array is simply a matter of specifying the column number instead of using the column name. In this example we are using an SQL statement instead of a table reference so we will know the column numbers of the columns selected. Here we set the variables REVENUE, and TITLE to the values in the cursor by specifying the offset (starting with 1).

CREATE CURSOR movieSelect AS 'SELECT _rowid, CATEGORY, REVENUE, TITLE FROM MOVIE'
WHILE NEXT ROW OF movieSelect DO
    SET ITEM 3 OF movieSelect TO'999000.00'
    SET ITEM 4 OF movieSelect TO"(" & ITEM 4 OF movieSelect & ")"
END WHILE

DELETING CURSOR RECORDS

Deleting is a matter of selecting a cursor, finding the row that you want to delete, and calling the DELETE command. Here is the syntax:
DELETE CURRENT FOR cursor
This command deletes the currently selected record. The record will not actually be physically deleted from the database until the SAVE CHANGES TO DATABASE command is called. The following example deletes all movies with revenue of greater than 4 million.

CREATE CURSOR movieSelect FOR TABLE 'MOVIE'
WHILE NEXT ROW OF movieSelect DO
    IF ITEM 'REVENUE' OF movieSelect is greater than 4000000 THEN
        DELETE CURRENT FOR movieSelect
    END IF
END WHILE

INSERTING CURSOR RECORDS

Inserting records requires you to first create a cursor to insert into. If you don't need to a select, we recommend that you create a cursor and specify constraints which do not exist in the database. However, many inserts will probably take place as you perform other operations on the table.

Please remember that you must call SAVE CHANGES TO DATABASE before the changes will actually be converted to SQL and sent to the database.

INSERT INTO cursor

If you need to create an empty cursor to insert into,do the following:

CREATE CURSOR movieSelect FOR TABLE 'MOVIE' WHERE '_rowid = -1'
INSERT INTO movieSelect
SET ITEM 'REVENUE' of movieSelect TO '999000.00'
SET ITEM 'TITLE' of movieSelect TO "A New Movie Title"

Otherwise it is best to create a real cursor as follows:

CREATE CURSOR movieSelect FOR TABLE 'MOVIE'
WHILE NEXT ROW OF movieSelect DO
    SET REVNUE TO ITEM 'REVENUE' OF movieSelect
    SET TITLE TO ITEM 'TITLE' OF movieSelect
END WHILE
-- now add a new record
INSERT INTO movieSelect
SET ITEM 'REVENUE' of movieSelect TO '999000.00'
SET ITEM 'TITLE' of movieSelect TO "A New Movie Title"

When adding a record, it is important to note that you must specify all foreign keys yourself (values that allow the record to relate to other tables). However, the primary key _rowid is automatically generated.

GENERATING UNIQUE KEY VALUES

OpenBase offers its own unique key generation mechanism in order to offer fast key generation and take care of the special primary key generation requirements for mirroring and synchronization. So for all your unique key generation needs we strongly recommend using the UNIQUEVALUE() function. Here is an example of the syntax:

UNIQUEVALUE('myTable', 'MyColumn')

Example:

SET ITEM 'MOVIE_ID' OF movieSelect TO UNIQUEVALUE('MOVIE', 'MOVIE_ID')

In order for this function to work, the column specified MUST be defined as a unique index in the database.

EXECUTE SQL command

The EXECUTE SQL command is used to send SQL commands to the database. They can contain update, inserts, deletes, creates, procedure calls or database specific commands. When the SQL successfully runs, EXECUTE SQL returns TRUE. If the command generates an error, EXECUTE SQL returns FALSE.

Here are a few examples:

EXECUTE SQL "CREATE PRIMARY KEY MyTable (MyKeyValue)"
EXECUTE SQL "cleanup"
EXECUTE SQL "UPDATE MOVIES SET REVENUE = 999000.00 WHERE TITLE LIKE 'THE*' "

When performing updates, inserts and deletes on a table it is customary to encase this statement inside a transaction and a TRY CATCH exception handler. This is especially true if you have multiple table modifications to do where you want to make sure modifications either all succeed or all fail.

START TRANSACTION
TRY
    REQUIRE EXECUTE SQL "Update MOVIE SET REVENUE = '999000.00'
        WHERE TITLE LIKE 'The*' "
    REQUIRE EXECUTE SQL "Update MOVIE SET REVENUE = '888.00'
        WHERE TITLE LIKE 'A*' "
    REQUIRE COMMIT
CATCH
    ROLLBACK
    ALERT ERROR 'Failed to save changes'
END TRY

FETCH SQL

This command is the same as EXECUTE SQL except that it returns a single row result with a single command. If the row contains a single value, it is translated to a single-value type. Otherwise an Array should be used to receive the result.

Here is an example:

DECLARE foo AS STRING
    SET foo TO FETCH SQL "select sum(total) from orders "

… or …

DECLARE foo AS ARRAY
    SET foo TO FETCH SQL "select sum(total), max(total) from orders "

SQLMERGE(pattern,param1,param2…)

This function takes an SQL pattern and replaces the question marks (?) with the values for the parameters. For each parameter quotes are added and quotes, tabs and CRs are correctly escaped.

SET sqlString TO SQLMERGE("Update MOVIE SET REVENUE = ? ", amount)

The SQLMERGE function automatically places quotes around strings and escapes quotes so the values are transferred correctly to the server. It can contain any number of parameters. Here is an example that shows two parameters:

SET sqlString TO SQLMERGE("select _rowid from authors where au_fname = ? and au_lname = ? ", fname, lname)

This method can be used for any type of parameter value and makes writing SQL statements easy to write.

SAVING CHANGES TO DATABASE

The SAVE CHANGES TO DATABASE command takes all the modifications made to the database by your OpenScript program and generates SQL to commit them to the database. It returns TRUE if the SQL successfully runs without error. Or it returns FALSE if one of the modifications violates a rule and needs to be rolled back.

When saving changes to the database, we recommend encasing the call within a transaction and inside a TRY REQUIRE condition CATCH exception handling structure. Here is an example using a standard transaction:

START TRANSACTION
TRY
    REQUIRE SAVE CHANGES TO DATABASE
    REQUIRE COMMIT
CATCH
    ROLLBACK
    ALERT ERROR 'Failed to save changes'
END TRY

In this example, we require a TRUE value for both the SAVE CHANGES TO DATABASE and the COMMIT command. Otherwise all changes are rolled back and an error is written to the database message log.

USING TRANSACTIONS

As discussed in the SAVING CHANGES TO DATABASE section, we recommend that you encase all modifications to the database within a transaction and inside a TRY REQUIRE condition CATCH exception handling structure.

Here is an example using the EXECUTE SQL command and regular update statements. Keep in mind that you can combine regular SQL updates, inserts and deletes with changes made using cursors and the SAVE CHANGES TO DATABASE command.

Here is an example of using transactions:

START TRANSACTION
TRY
    REQUIRE EXECUTE SQL "Update MOVIE SET REVENUE = '999000.00'
        WHERE TITLE LIKE 'The*' "
    REQUIRE COMMIT
CATCH
    ROLLBACK
    ALERT ERROR 'Failed to save changes'
END TRY

ALERT MESSAGES

Alert messages are extremely useful for notifying system administrators of an error condition. Each level of error condition is designated with a different color in the log and a different alarm in the OpenBase Manager. When something fails you can call one of these routines to make an appropriate entry in the database log.

ALERT MESSAGE 'Just a note in the log file'
ALERT WARNING 'Warning highlighted in blue'
ALERT ERROR 'Error highlighted in red'
ALERT SEVERE 'Severe error highlighted in red and yellow'

OpenScript Database Example

This OpenScript Stored Procedure example demonstrates most of the new concepts added in OpenScript version 2.0. It can be added to the demo database called RADPalmDatabase and run from openisql using the SQL "call boo()".

Here is a list of things that it accomplishes:

  1. It selects people from our database that have orders in the orders table
  2. It modifies the company name to include an "Inc." at the end.
  3. It prints each company name to the RADPalmDatabase database log.
  4. It then transverses a relationship to the orders table.
  5. It prints the order date and the invoice number of each order to the RADPalmDatabase database log.
  6. It inserts a new person record and sets the company name.
  7. It creates a new primary key which we do not use. We just print it to the log to show what it returned.
  8. It creates a transaction and saves the changes to the database within an exception handling structure.
  9. It writes a bunch of messages of varying severity to the RADPalmDatabase database log. These will set off all sorts of alarms in your OpenBase Manager.
on boo ( )
    -- put your code here
    CREATE CURSOR myCursor FOR TABLE "addresses"
        WHERE "_rowid in (select addresses_id from orders) "
        ORDER BY "company DESC"
    WHILE NEXT ROW OF myCursor DO
    TRY
        -- get the current company name
        SET myCompany TO ITEM 'company' OF myCursor

        -- Update cursor and add Inc. at the end of each one
        SET ITEM 'company' OF MyCursor TO myCompany & " Inc."
        ALERT MESSAGE 'COMPANY:' & ITEM 'company' OF MyCursor
        CREATE CURSOR ordersCursor FOR RELATIONSHIP "toorders"
            USING SELECTION myCursor
        WHILE NEXT ROW OF ordersCursor DO
            ALERT MESSAGE "    ORDER:" & ITEM 'orderDate' OF ordersCursor & " " & ITEM 'invoiceNumber' OF ordersCursor
        END WHILE
    CATCH
        ALERT ERROR 'Exception Updating Company'
    END TRY
    END WHILE
    INSERT INTO myCursor
    SET ITEM 'company' OF MyCursor TO myCompany & " Inc."
    ALERT MESSAGE 'NEW PRIMARY KEY:' & UNIQUEVALUE ( 'addresses' , '_rowid' )

    -- start a transaction, make all the changes, commit the transaction
    START TRANSACTION
    TRY
        REQUIRE SAVE CHANGES TO DATABASE
        REQUIRE COMMIT
    CATCH
        ROLLBACK
        ALERT ERROR 'Failed to save changes'
    END TRY

    - now print some messages to the log
    ALERT MESSAGE 'Just a note in the log file'
    ALERT WARNING 'Warning highlighted in blue'
    ALERT ERROR 'Error highlighted in red'
    ALERT SEVERE 'Severe error highlighted in red and yellow'
    return 'success'
END boo

String Constants and Functions

String Constants

SPACE - Represents a string of one space.

CR - Represents a string of one carriage return.

TAB - Represents a string of one tab.

String Functions

LENGTH(string) - returns the length of string

INDEXOFSTRING(string, substring) - returns the numerical index of substring in string or -1 if the substring cannot be found

REPLACE(string, startpos, length, replacestring) - inserts replacestring into string at startpos and replaces length characters. The replacestring is not truncated by length. The entire replacement string is inserted at the specified point.

SUBSTRING(string, startpos, length) - returns the substring of string starting at startpos and including length characters

TOUPPER(string), TOLOWER(string) - converts string to upper or lower case

RIGHT(string, length) - returns the right portion of string with length characters

LEFT(string, length) - returns the left portion of string with length characters

TRIMSTRING(string) - removes extra blank spaces from string

PROPERSTRING(string) - capitalizes the first letter of each word in string

WORDATINDEX(sentence index) - returns the word at position index in the sentence string

ALLWORDS(sentence) - returns an array of words in the sentence.

Database String Functions

• QUOTEDSTRING(value) - Returns a string with correctly escaped quotes. For instance, QUOTEDSTRING("pigs can't fly") returns 'pigs can\'t fly'. It is sometimes necessary to create database friendly strings when building SQL or creating search using OpenScript.

Date and Time Functions

  • CURRENTDATE() - returns the current date as a DATE variable type
  • MONTHOFDATE(dateValue) - returns the MONTH component of the date
  • MONTHNAMEOFDATE(dateValue) - returns the name of the month represented by the date
  • YEAROFDATE(dateValue) - returns the YEAR represented by the date
  • DAYOFDATE(dateValue) - returns the DAY of the month represented by the date
  • CURRENTTIME() - returns the current time as a TIME variable type
  • HOUROFTIME(timeValue) - returns the HOURS of the time
  • MINUTEOFTIME(timeValue) - returns the MINUTES of the time
  • PROPERSTRING(string) - capitalizes the first letter of each word in string

Misc Functions

RANDOM(max_num) function - generates a random integer between 0 and max_num

Math Functions

ACOS(x), ACOSH(x), ASIN(x), ASINH(x), ATAN(x), ATANH(x), TAN2(x,y), ABS(x), COS(x), COSH(x), EXP(x), LOG(x), LOG10(x), POW(x), SIN(x), SINH(x), SQRT(x), TAN(x), TANH(x)

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License