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:
|
1 |
INSERT INTO EMPLOYEE ( SALARY ) |
|
1 |
VALUES ( 18900 ) ; |
The same statement using a PowerScript variable to reference
the constant might look like this:
|
1 |
int Sal_var |
|
1 |
Sal_var = 18900 |
|
1 |
INSERT INTO EMPLOYEE ( SALARY ) |
|
1 |
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:
|
1 |
:Name, :Address, :City |
The same HostVariableList with indicator
variables looks like this:
|
1 |
: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:
|
1 |
if IndVar2 = -1 then... |
You can also use the PowerScript IsNull function
to accomplish the same result without using indicator variables:
|
1 |
if IsNull( Address ) then ... |
This statement uses the indicator variable IndVar3 to
set City to null:
|
1 |
IndVar3 = -1 |
You can also use the PowerScript SetNull function
to accomplish the same result without using indicator variables:
|
1 |
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 |
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:
|
1 |
IF SQLCA.SQLCode = -1 THEN |
|
1 |
MessageBox("SQL error", SQLCA.SQLErrText) |
|
1 |
END IF |
Painting standard SQL
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.
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:
|
1 |
string res<br>select user_name() into:res from dummy;<br>select db_name() into:res from dummy;<br>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.
Caution
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.