Advanced PowerBuilder

Embedded SQL - PowerBuilder Implementation

SQL and infact, the whole theory of relational databases, is based on sets. The subject of database theory is a book by itself, so we can't teach you everything here, but we can do is look at some of PowerBuilder's implementations.

The simplest way of using SQL in the PowerScript code is to use the Paste SQL option in the Script painterbar. When you click on this icon , PowerBuilder presents you with the following options:

This allows you to select the type of SQL statement you want to insert, after which PowerBuilder launches the usual query selection dialog boxes, designed to speed up the creation of the statement.

PowerBuilder for your reference, allows you to select the tables to be displayed in the SQL statement. Before we take a look at the various SQL structures that we can use, we must take a look at the concept of Host Variables.

Using Host Variables

SQL statements in PowerBuilder can make use of the existing variables by prefixing the variable name with a colon (:). For example, in the following code we make reference to the lProductNo variable in the SQL statement:

Int lProductNo

lProductNo = Integer( sle_itemno.Text )

SELECT "product_master"."item_product_description"
   INTO :lProductDesc
   FROM "product_master"
   WHERE "product_master"."product_no" = : lProductNo ;

Host variables are most commonly used in the following areas:

	WHERE clause.
	HAVING clause.
	INTO variables.
	VALUES clause in INSERT statements.

They are also used in place of values in the SET clauses in UPDATE statements and as parameter values to the Stored Procedures.

SELECT INTO

When the SQL statement returns a single row, you can use the SELECT INTO statement, to pass the results into the host variables. For example:

String lProductDesc
Int lProductNo

lProductNo = Integer( sle_itemno.Text )

SELECT "product_master"."item_product_description"
   INTO :lProductDesc
   FROM "product_master"
   WHERE "product_master"."product_no" = :lProductNo ;

If SQLCA.SQLCODE <> 0 then
// ....
End if

This stores the result of the SELECT statement in the host variable lProductDesc, earlier initialized as a string. SQLCA contains the status of the last executed SQL statement, so, we can use it to check the success of the operation. The SQLCODE attribute can have three possible values:

As we saw in a previous session that, you can check other attributes of SQLCA for more information such as, error messages in SQLCA.SqlErrText, and database error numbers in the SQLCA.SQLDbCode.

There are times when the result column returns NULL values. Even though PowerBuilder doesn't provide you with a method to   check the value in the normal SQL, there is a way to check it in the Embedded SQL. This method is to suffix the host column variable name with an indicator, separated by a colon.

Note that the indicator for NULL value checking should be declared as an integer.

The following example assumes that the product_description column in product_master allows NULL values:

String lProductDesc
Int lProductNo, lDescInd1

lProductNo = Integer( sle_itemno.Text )

SELECT "product_master"."item_product_description"
   INTO :lProductDesc:lDescInd1
   FROM "product_master"
   WHERE "product_master"."product_no" = :lProductNo;

If SQLCA.SQLCODE <> 0 then
// ....
End if

lDescInd1 is the indicator we have used in the above example, and it can have one of the following values:

The UPDATE Statement

You can explicitly execute an UPDATE statement through Embedded SQL. For example, in the w_transactions window in our application, you have to update the product_balance in product_master table for each transaction, i.e., receipt, return or receipt. Calling the Update() function for the DataWindow would update the transaction table 'trans' but not product_master unless you have defined triggers on the 'trans' table in the database. The alternat solution from the client application is update 'product_master' table using Embedded SQL as shown in the following example:

UPDATE "product_master"
SET "product_balance" = product_balance + :lQuantity
WHERE product_no = :lProductNo;

If SQLCA.SQLNRows <> 1 Then
   RollBack Using SQLCA;
   SQLCA.uf_display_error()
   Return 0
End If

To find the number of rows affected by the UPDATE statement, we can check SQLCA.SqlNRows after the UPDATE command is successfully executed.

The INSERT Statement

You can execute two forms of the INSERT statement. The following format inserts a single row into the product_master table.

INSERT INTO guest.product_master
( product_no, product_description, product_balance,
product_reorder_level,product_measuring_unit )
VALUES ( :lProductNo, :lDesc, :lBal, :lROrdLvl, :lMsUnt);

The second format inserts more than one row using a single statement. Suppose you wanted to insert all records from  product_master table into a history table product_master. You could use the following code:

INSERT INTO product_master ( <column list> )
SELECT < column list > FROM product_master ;

where you replace <column list> with the list of all the columns in the table. If you have a table with many columns, this can turn into a long SQL statement. However, PowerBuilder does support the asterisk wildcard character (*), which allows you to select all the columns at once:

// Sybase supports selecting values from a table
// and inserting into the same table.
INSERT INTO product_master
SELECT * FROM product_master ;

Inserting into the table from which you are selecting may not work with all databases, so you will have to check with your specific database vendor's documentation.

The DELETE Statement

The DELETE statement can be used to delete selected entries from a database table. It can be used as follows:

Int iDelYears
iDelYears = Integer( em_DelYears.Text )

DELETE FROM trans
   WHERE datediff( Year, GetDate(), tran_date ) > :iDelYears ;

This uses the datediff() function to check the difference, in years, between the current date and the tran_date column, in the table. It deletes any record whose difference is greater than the value in the iDelYears variable, entered by the user. Here, we could also check SQLNRows to determine the rows affected.

Home	Previous Lesson: Introduction Next Lesson: Cursors