1069-1071

Previous Table of Contents Next

Page 1069

When SQL Select is chosen as the data source, the user must first select the tables or views to be used in the SELECT statement. Next, columns must be chosen for the result set. Selected columns are displayed in the lower-left side of the tabbed window within the DataWindow painter. The first tab is used to create the ORDER BY clause. Columns can be dragged from the left side to the right side of the window to be included in the ORDER BY, and a check box is used to indicate ascending or descending order. The next tab to the right is the WHERE clause definition.

Drop-down combos can be used to select the columns, comparison operators, and comparison values for each expression in the WHERE clause. The Logical drop-down is used to apply AND/OR logic to the next expression in the list. The Group tab is used to define a GROUP BY clause for SELECT statements containing aggregate functions. It operates on the same drag-and-drop principle as the Order tab. The Having tab is used to define a HAVING clause and operates in much the same way as the WHERE tab. The Compute tab is used to define functions and aggregate functions that can be added to the column list.

The last tab on the far right is used to display the SQL syntax that has been generated based on these selections. You can edit the syntax manually, if necessary. From the Options menu, select Convert to Syntax to invoke the editor. SQL Selects can be constructed in this manner instead of using the graphical interface, if desired. To toggle back to the graphical interface, select Convert to Graphics from the Options menu.

TIP

Right-clicking in the Where, Having, and Compute tabs displays a pop-up menu that can be used to provide drop-down lists of columns, functions, and arguments that can be used.

To create a UNION, select Create Union from the Objects menu. This will open a new Select Painter, and the unioned result can be defined in exactly the same manner. To specify DISTINCT, select this option from the Options menu.

The SQL used by the DataWindow can be parameterized to some degree, using the Retrieval Arguments option of the Objects menu. These arguments can be used as the comparison values in the WHERE or HAVING clauses and in computed columns. Arguments are passed to the DataWindow at runtime using the Retrieve() function of the data window, as illustrated by the following:

 dw_1.Retrieve(arg1, arg2); 

In addition to parameterized SQL, the DataWindow object's SetSQLSelect() function can be used to modify the SQL statement at runtime. However, the columns of the result set must always match the output defined at design time.

If a transaction object other than the default SQLCA will be used for the DataWindow, the transaction object should be specified using the SetTransObject() method. DataWindows are

Page 1070

displayed by placing a DataWindow object on a window, specifying the name of the DataWindow, and making two function calls ( assuming that the DataWindow will be using a transaction object that is already connected). The following code segment is used to display a DataWindow using a declared transaction object that is connected to the data source:

 dw_1.SetTransObject(sqlca_sps); dw_1.Retrieve(arg1); 

The DataWindow will also use its own internal transaction object. Some attributes of this internal transaction object are accessible through the DBErrorCode, DBErrorMessage, and SetTrans functions. DBErrorCode and DBErrorMessage are used to get the native error code and text from the DataWindow's internal transaction object. SetTrans can be used to copy a transaction object's values to the internal DataWindow transaction object, as in the following example:

 dw_1.SetTrans(sqlca_sps); 

The difference between SetTrans and SetTransObject is that SetTrans supplies only connection information, and it allows the DataWindow to process all transaction logic (commits and rollbacks ) internally. SetTrans should be used for read-only DataWindows.

Oracle-stored procedures were not designed to retrieve result sets. However, if the PBORCAT.SQL script is run in Oracle, a workaround for retrieving result sets to PowerBuilder is available. The script creates a procedure called Put_Line, which is used by other procedures to create SQL to be used for this purpose. Listing 46.2 is an example of an Oracle-stored procedure that will return a result set to PowerBuilder.

Listing 46.2. This script creates a stored procedure that can be used to return a result set to a PowerBuilder DataWindow.

 CREATE OR REPLACE PROCEDURE get_emps IS BEGIN     PBDBMS.Put_Line(`SELECT dept, last_name, first_name `);     PBDBMS.Put_Line(`FROM employees `);     PBDBMS.Put_Line(`ORDER BY dept, last_name, first_name'); END; 

To use this function, select Stored Procedure as the data source in the DataWindow painter. After selecting the stored procedure name, the definition of the DataWindow proceeds normally.

When the DataWindow result set definition is complete, you can use the Layout Editor to change the appearance of the output. Depending on the style of the DataWindow, this will include the width of columns, fonts, border styles, line art and graphics, headers and footers, and numerous other elements.

Page 1071

In addition to SQL and stored procedures, DataWindows can use other DataWindows as a source, or non-DBMS sources such as DDE can be accessed. The options available for retrieving and displaying result sets using DataWindows are too numerous to discuss in full detail in this chapter. The preceding overview of DataWindows highlights the basics.

There are two alternatives to DataWindows that can be used to retrieve results in PowerBuilder. Cursors can be declared and used for multiple-row result sets, and for single-row results, a simple SELECT statement can be issued. Listing 46.3 demonstrates the use of cursors to retrieve results in PowerBuilder scripts.

Listing 46.3. This script declares a cursor and retrieves results, placing column data in PowerBuilder variables .

 DECLARE order_det CURSOR FOR     SELECT quantity, price     FROM order_details     WHERE order_no = :lb_order_no.Text;     Integer iQuantity;     Dec{2}  dPrice;     Dec{2}  dTotal;     OPEN order_det;     Do While (SQLCA.sqlcode = 0)         FETCH order_det INTO :iQuantity, :dPrice;         dTotal = dTotal + (iQuantity * dPrice);     Loop     CLOSE order_det;     st_total.Text = String(dTotal); 

A single-row result set can be retrieved using an embedded SELECT statement, as illustrated here:

 SELECT order_date, status INTO :szDate, :szStatus FROM view_orders WHERE order_no = :lb_orders.Text 

The same methods used to retrieve results can be used to perform transactions. DataWindows and cursors can be used for in-place UPDATEs and DELETEs and embedded SQL, in addition to DataWindows and cursors, can be used to insert, update, and delete individual records. Stored procedures in Oracle can be called from PowerBuilder to perform any of these operations, either in aggregate or on individual records.

Columns that may be updated through a DataWindow must be defined in the DataWindow painter, unless the default behavior is acceptable. By default, if the DataWindow operates on a single table, all columns are updatable. If it operates on multiple tables or a view, no columns are updatable. This behavior can be modified by using the Update option from the Rows menu. Update syntax is defined using the dialog box displayed in Figure 46.3.

Previous Table of Contents Next