| Home | Previous Lesson: Using Host Variables Next Lesson: The UPDATE Statement |
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 li_ProductNo
li_ProductNo = Integer( sle_itemno.Text )
SELECT "product_master"."item_product_description"
INTO :lProductDesc
FROM "product_master"
WHERE "product_master"."product_no" = :li_ProductNo ;
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:
|
Value |
Result |
|
0 |
Success |
|
-1 |
Error |
|
100 |
No results returned |
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 li_ProductNo, lDescInd1
li_ProductNo = Integer( sle_itemno.Text )
SELECT "product_master"."item_product_description"
INTO :lProductDesc:lDescInd1
FROM "product_master"
WHERE "product_master"."product_no" = :li_ProductNo;
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:
|
Value |
Result |
|
0 |
Valid, not a NULL |
|
-1 |
NULL |
|
-2 |
Conversion error |
| Home | Previous Lesson: Using Host Variables Next Lesson: The UPDATE Statement |