Advanced PowerBuilder

Format 2

While considering format 2, you need to be prepared to handle user-defined parameters, even though you still can't get the results' set. Before executing the SQL statement, it has to be prepared using the PREPARE command. The syntax is as follows:

PREPARE command FROM :string ;

Here, string is an expression of the host language, that yields the character string representation of an SQL statement, and command is the name of an SQL variable, used to refer the prepared version of that SQL statement.

The statement to be prepared can be any interactive SQL statement. An example for a Format 2 Dynamic SQL statement is shown below:

Int lProductNo ; Date PriceDate ; Decimal Price
LProductNo = 10 ; PriceDate = Today() ; Price = 120.43

PREPARE SQLSA FROM 'INSERT INTO "dba"."t_product_price1" VALUES (?,?,?)' ;

EXECUTE SQLSA USING :LProductNo, :PriceDate, :Price ;

If sqlca.sqlcode <> 0 Then
   MessageBox( "ERROR", string( sqlca.sqlcode ) + &
               SQLCA.SqlErrText )
End If
Return

Statements such as DECLARE CURSOR, OPEN, FETCH, CLOSE and so on can't be the subject for PREPARE, and the source form of a statement must not include UPDATE WHERE CURRENT OF, DELETE WHERE CURRENT OF or a statement terminator.

The question marks in the INSERT statement are place holders. PowerBuilder replaces these with the values supplied in the EXECUTE SQLSA statement.

Home	Previous Lesson: Format 1 Next Lesson: Format 3