Database Integrity

SQL scripts operate within the context of the existing Agile Business Suite transaction structure. A commit would destroy the atomicity of the transaction and potentially leave the database in an indeterminate state. For ispecs, this means that any command which would cause an implicit or explicit commit of database changes is specifically not allowed, and results in a validation error when the component is built. For reports, commands that force an implicit or explicit commit can be used; as such a capability is already available with the Sleep logic command.

Resilience and Recovery

Use of SQL scripts exposes the Agile Business Suite database to uncontrolled updates. It is possible to perform updates that do not take into consideration the internal semantics that Agile Business Suite imposes on a database table. Due care should be taken to ensure that the database is not corrupted or internal semantics invalidated.

OLEDB

Upon completion of the construct built-in method, the Glb.PreSQLCode built-in segment attribute is set to the Database return SQLCode value.

Upon completion of the main built-in method, the Glb.MainSQLCode built-in segment attribute is set to the Database return SQLCode value.

Upon completion of the destruct built-in method, the Glb.PostSQLCode built-in segment attribute is set to the Database return SQLCode value.

Upon completion of any non-built-in SQL script method, Glb.MainSQLCode is set to the Database return SQLCode value.

If there are multiple SQL statements in any method, only the last value of SQLCode is set.

The following example checks SQLCODE values:

Determine Actual MySQL
DoWhen Glb.PreSQLCode <> 0
Message "Construct method caused a warning"
: We don’t want to process this for each iteration, so clear the condition
Glb.PreSQLCode := 0
End

DoWhen Glb.MainSQLCode <> 0
Message "Main method caused a warning"
: Clear the condition for the next iteration
Glb.MainSQLCode := 0
End
: Logic
End : End of Determine Actual MySQL
: Check for any warnings from the destruct method
DoWhen Glb.PostSQLCode <> 0
Message "Destruct method caused a warning"
: Clear the condition
Glb.PostSQLCode := 0
End

Selective Data Retrieval with Join Example

This example uses the SQL script “LargePolicies” to iterate over data retrieved concurrently from two database tables by means of a join operation.

Construct

The construct method consists of the following SQL statements:

DECLARE Cursor1 CURSOR FOR
SELECTPolicy.Acct Cust.Name Cust.Address Policy.Value
FROMPolicy, Cust
WHEREPolicy.Acct = Cust.Acct AND Policy.Value > :MaxValue ;
OPEN Cursor1 ;

This declares a cursor “Cursor1” over the set of data represented by the SELECT and WHERE clauses. It then opens the cursor. As an example, this would retrieve the name and address of every customer with a policy value greater than $1,000,000.

Main

The main method consists of the following SQL statement:

FETCH Cursor1 INTO :PolicyAcct :CustName :CustAddress :PolicyValue ;

This fetches the next record into the instance variables defined by the DECLARE … CURSOR FOR statement in the construct method.

Destruct

The destruct method consists of the following SQL statement:

CLOSE Cursor1 ;

This closes the cursor Cursor1.

Attributes

The following attributes should be defined for the SQL script, for use as instance variables:

Invocation

The SQL script is invoked from a report as follows:

MaxValue := 1000000
Determine Actual LargePolicies
PolicyTotal := PolicyTotal + PolicyValue
F10_Acct := PolicyAccount
F10_Name := CustName
F10_Address := CustAddress
Frame10.Print()
End

Upon invocation, processing occurs as described in SQL Script Processing.

Selective Update Example

This example uses the SQL script “PremiumUpdate” to update all records from a single table that meet the selection criteria.

Construct

The construct method consists of the following SQL statements:

UPDATE Policy SET Policy.Premium = Policy.Premium * :PremiumIncr
WHERE Policy.StartDate > 19851103 ;

This is a singleton SQL statement that increases the policy premium by 11.5% (specified by PremiumIncr) for all policies started after November 3, 1985.

Main

The main method is not defined or empty.

Destruct

The destruct method is not defined or empty.

Attributes

The following attribute should be defined for the SQL script, for use as an instance variable:

Invocation

The SQL script should only be executed once. Consequently, an unconditional Break logic command should be invoked from within the Determine Actual logic command loop:

PremiumIncr := 1.115
Determine Actual PremiumUpdate
: Logic
Break
End

Upon invocation, processing occurs as described in SQL Script Processing.