Advanced PowerBuilder

Format 3

The method of handling SELECT in dynamic SQL is different from other statements. This is because, it returns data to the program; all other statements simply return feedback information.

A program using SELECT, needs to know something about the data values to be retrieved, since, it has to specify a set of target variables to receive those values. In other words, it needs to know the number of values there will be in each result row, and also the data types and lengths of those values.

String l_Product_description ; Int Product_no

DECLARE Cursor1 DYNAMIC CURSOR FOR SQLSA ;
PREPARE SQLSA FROM "SELECT product_no, &
product_description from product_master";

OPEN DYNAMIC Cursor1 ;

FETCH Cursor1 INTO :Product_no, :l_Product_description ;

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

DO WHILE Sqlca.Sqlcode = 0
   MessageBox( "Format 3 Results", String( Product_no)+
               " " + l_Product_description )
   FETCH Cursor1 INTO :Product_no, :l_Product_description ;
LOOP

CLOSE Cursor1

At compilation time you know the result set, and that's why you can use the FETCH statement to return the results.

A typical use of this command is to select information from a temporary table, a table which doesn't exist at compile time. PowerBuilder doesn't check the existence of the table, since, it doesn't check the syntax of the SQL statements and therefore doesn't generate any errors.

Home	Previous Lesson: Format 2 Next Lesson: Format 4