Advanced PowerBuilder

Format 4

This format is similar to Format 3, except that you don't know the result set, hence you can't issue a FETCH statement. Typically, this format is used to accept the SQL statement from the user ( ad-hoc queries ), and execute and present the results back to the user. The following example uses a simple SELECT statement:

String lSQLStr
Integer lParm1

// We are hard-coding the SQL here. But, you can accept
// from the user and use it for ad-hoc queries.

lSQLStr = "select product_description " + &
          " from product_master where product_balance > ?"

lParm1 = 10

PREPARE SQLSA from :lSQLStr ;
DESCRIBE SQLSA into SQLDA ;
DECLARE lCursor1 DYNAMIC CURSOR for SQLSA ;

// Setting value for the first variable in the
// WHERE clause.
SetDynamicParm( SQLDA, 1, lParm1 )

// Now, SQLDA has the values for the parameters.
OPEN DYNAMIC lCursor1 USING descriptor SQLDA ;
FETCH lCursor1 USING descriptor SQLDA ;

DO while SQLCA.SQLCODE = 0
   // Since we hard-coded the SQL, we know the result
   // set. You need to call different function depending
   // on the datatype. You can check datatype by referring
   // to OutParmType array of SQLDA.
   lb_1.AddItem( GetDynamicString( SQLDA, 1 ) )
   FETCH lCursor1 USING descriptor SQLDA ;
LOOP

close lCursor1 ;

As described earlier, the PREPARE command formats the input SQL statement using the information specified in the FROM clause, and populates SQLSA, the object specified after the PREPARE command, with these statements.

Handling the Results

A similar process is required for handling results. PowerBuilder needs to know where to populate the information about the results, so, the DESCRIBE and PREPARE statements are used. Typically, this information storage container is SQLDA, unless you create another object of type SQLDA to connect to more data sources.

Before the actual execution of the SQL statement, you need to call SetDynamicParm() to specify parameters. This command takes three parameters: Dynamic Staging Area, the parameter number and the parameter itself. The FETCH command used here is different from the one used in cursors and Format 3. In this format, you don't specify the variables to store the result set, since, you don't know the number of columns in the result set, their data type and length of each column in the result set.

You can check both NumOutputs attribute of SQLDA, to establish the number of columns in the result set, and   OutparmType array, for the datatype of each column in the result set. The data type can be any one of the following enumerated examples:

	TypeDate!
	TypeTime!
	TypeDateTime!
	TypeString!
	TypeDecimal!
	TypeDouble!
	TypeInteger!
	TypeLong!
	TypeReal!
	TypeBoolean!

After checking for the data type, call one of the following corresponding functions, depending on the data type, to get the actual value:

	GetDynamicDate()
	GetDynamicTime()
	GetDynamicDateTime()
	GetDynamicString()
	GetDynamicNumber()

Home	Previous Lesson: Format 3 Next Lesson: Summary