SQL scripts (SQL script-stereotype classes) act as embedded containers for SQL statements. They can be used to optimize database queries and updates, by allowing access to native interface of the database. The performance gain that can potentially be achieved from the use of SQL scripts is dependent on the capabilities of the DBMS, the nature of the specific query or update, and the tuning of the physical database itself. The default window is Members.
Normal methods can also be in SQL. SQL Scripts have a processing cycle and are useful for iterating through the results of an SQL select.
Valid SQL commands are:
INSERT
DELETE
UPDATE
SELECT … INTO
DECLARE … CURSOR FOR
OPEN
FETCH … INTO
CLOSE
You should consult a SQL command reference for more information on these commands. You should also consult your SQL Server documentation for more information on their respective conformance with the ANSI SQL-92 standard. Invalid SQL commands in SQL scripts causes compilation errors.
SQL statements for SQL scripts are specified in the logic editor. Refer to the Agile Business Suite Developer Online Help for more information on using the logic editor.
SQL scripts have the following inherent characteristics:
SQL script processing.
Built-in construct, main, and destruct SQL script methods, which can be overridden using the Overrides Tab.
The built-in SQL script methods can only be invoked using the Determine Actual logic command. All other SQL script methods can be invoked directly.
Construct
The construct method (script) opens the cursor in preparation for iterating through the results of a SELECT statement.
To execute a set of SQL statements once only, they should be specified within the construct method. Additionally, a Break logic command should be invoked within the Determine Actual logic command loop. Refer to Break for more information.
Main
The main method (script) is executed for each iteration of the Determine Actual logic command statement. It iterates through the retrieved records.
Destruct
The destruct method (script) releases any resources used by the SQL script.
Attributes
Attributes of the SQL script that are referenced by embedded SQL statements need to be identified as such. They should be prefixed with a colon (':') when referenced in a SQL statement. In the following example, MyA, MyB, MyC, and MyZ are attributes (instance variables) of the SQL script:
SELECT A, B, C INTO :MyA, :MyB, :MyC FROM TAB WHERE Z = :MyZ
Debugger Restrictions
SQL script method calls are ignored during debug sessions. Debugger disregards any invocations of SQL script methods it encounters, and continues execution at the logic statement following the method invocation.
Examples
Selective Data Retrieval with Join Example
Selective Update Example
Database Issues
Refer to SQL Script Properties for properties applicable to SQL Scripts.
Multi-user session handling in SQL
AB Suite allows multiple users to access and update the AB Suite database. To reflect the changes made by another user, you need to manually refresh the domain. A domain here is the model element selected currently.
For example, when a user edits or deletes a model element, you will have to manually refresh that model element to see the updates. If you refresh a particular domain, only the changes made within that domain will be visible, whereas the changes made outside that domain will remain invisible. You can increase the domain scope by selecting its owner.