Using SQL in scripts

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.

Referencing PowerScript variables in scripts

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 ) ;

Using indicator variables

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

NoteError 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 ) 

Error handling in scripts

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

Painting standard SQL

You can paint the following SQL statements in scripts and functions:

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.

Supported SQL statements

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;

Disabling database connection when compiling and building

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.

NoteCaution Select the check box only when you want to compile without signing on to the database. Compiling without connecting to a database prevents the build process from checking for database errors and may therefore result in runtime errors later.