Using SQL in
scripts
Contents
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 Using Embedded SQL in Connecting to Your Database.
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 ) VALUES ( 18900 ) ; |
The same statement using a PowerScript variable to reference the
constant might look like this:
|
1 2 3 |
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:
|
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 |
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 2 3 |
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:
-
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 2 3 |
string resselect 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.
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.