PowerScript supports standard embedded SQL statements and dynamic SQL statements in scripts. In general, PowerScript supports all DBMS-specific clauses and reserved words that occur in the supported SQL statements. For example, PowerBuilder supports DBMS-specific built-in functions within a SELECT command.
For information about embedded SQL, see online Help.
Wherever constants can be referenced in SQL statements, PowerScript variables preceded by a colon (:) can be substituted. Any valid PowerScript variable can be used. This INSERT statement uses a constant value:
INSERT INTO EMPLOYEE ( SALARY )
VALUES ( 18900 ) ;
The same statement using a PowerScript variable to reference the constant might look like this:
int Sal_var
Sal_var = 18900
INSERT INTO EMPLOYEE ( SALARY )
VALUES ( :Sal_var ) ;
PowerBuilder supports indicator variables, which are used to identify null values or conversion errors after a database retrieval. Indicator variables are integers that are specified in the HostVariableList of a FETCH or SELECT statement.
Each indicator variable is separated from the variable it is indicating by a space (but no comma). For example, this statement is a HostVariableList without indicator variables:
:Name, :Address, :City
The same HostVariableList with indicator variables looks like this:
:Name :IndVar1, :Address :IndVar2, :City :IndVar3
Indicator variables have one of these values:
Page |
Meaning |
|---|---|
0 |
Valid, non-null value |
-1 |
Null value |
-2 |
Conversion error |
Error reporting
Not all DBMSs return a conversion error when the datatype
of a column does not match the datatype of the associated variable.
The following statement uses the indicator variable IndVar2 to see if Address contains a null value:
if IndVar2 = -1 then...
You can also use the PowerScript IsNull function to accomplish the same result without using indicator variables:
if IsNull( Address ) then ...
This statement uses the indicator variable IndVar3 to set City to null:
IndVar3 = -1
You can also use the PowerScript SetNull function to accomplish the same result without using indicator variables:
SetNull( City )
The scripts shown in the SQL examples above do not include error handling, but it is good practice to test the success and failure codes (the SQLCode attribute) in the transaction object after every statement. The codes are:
Value |
Meaning |
|---|---|
0 |
Success. |
100 |
Fetched row not found. |
-1 |
Error; the statement failed. Use SQLErrText or SQLDBCode to obtain the detail. |
After certain statements, such as DELETE, FETCH, and UPDATE, you should also check the SQLNRows property of the transaction object to make sure the action affected at least one row.
About SQLErrText and SQLDBCode The string SQLErrText in the transaction object contains the database vendor-supplied error message. The long named SQLDBCode in the transaction object contains the database vendor-supplied status code:
IF SQLCA.SQLCode = -1 THEN
MessageBox("SQL error", SQLCA.SQLErrText)
END IF
You can paint the following SQL statements in scripts and functions:
Declarations of SQL cursors and stored procedures
Cursor FETCH, UPDATE, and DELETE statements
Noncursor SELECT, INSERT, UPDATE, and DELETE statements
For more information about scope, see “Where to declare variables”.
You can declare cursors and stored procedures at the scope of global, instance, shared, or local variables. A cursor or procedure can be declared in the Script view using the Paste SQL button in the PainterBar.
You can paint standard embedded SQL statements in the Script view, the Function painter, and the Interactive SQL view in the Database painter using the Paste SQL button in the PainterBar or the Paste Special>SQL item from the pop-up menu.
In general, all DBMS-specific features are supported in PowerScript if they occur within a PowerScript-supported SQL statement. For example, PowerScript supports DBMS-specific built-in functions within a SELECT command.
However, any SQL statement that contains a SELECT clause must also contain a FROM clause in order for the script to compile successfully. To solve this problem, add a FROM clause that uses a “dummy” table to SELECT statements without FROM clauses. For example:
string res
select user_name() into:res from dummy;
select db_name() into:res from dummy;
select date('2001-01-02:21:20:53') into:res from dummy;
When PowerBuilder compiles an application that contains embedded SQL, it connects to the database profile last used in order to check for database access errors during the build process. For applications that use multiple databases, this can result in spurious warnings during the build since the embedded SQL can be validated only against that single last-used database and not against the databases actually used by the application. In addition, an unattended build, such as a lengthy overnight rebuild, can stall if the database connection cannot be made.
To avoid these issues, you can select the Disable Database Connection When Compiling and Building check box on the general page of the System Options dialog box.
