Using DataStore Objects

About DataStores

A DataStore is a nonvisual DataWindow control. DataStores act just like DataWindow controls except that they do not have many of the visual characteristics associated with DataWindow controls. Like a DataWindow control, a DataStore has a DataWindow object associated with it.

When to use a DataStore

DataStores are useful when you need to access data but do not need the visual presentation of a DataWindow control. DataStores allow you to:

Perform background processing against the database without having to hide DataWindow controls in a window

Suppose that the DataWindow object displayed in a DataWindow control is suitable for online display but not for printing. In this case, you could define a second DataWindow object for printing that has the same result set description and assign this object to a DataStore. You could then share data between the DataStore and the DataWindow control. Whenever the user asked to print the data in the window, you could print the contents of the DataStore.
Hold data used to show multiple views of the same information

When a window shows multiple views of the same information, you can use a DataStore to hold the result set. By sharing data between a DataStore and one or more DataWindow controls, you can provide different views of the same information without retrieving the data more than once.
Manipulate table rows without using embedded SQL statements

In places where an application calls for row manipulation without the need for display, you can use DataStores to handle the database processing instead of embedded SQL statements. DataStores typically perform faster at execution time than embedded SQL statements. Also, because the SQL is stored with the DataWindow object when you use a DataStore, you can easily reuse the SQL.
Perform database access on an application server

In a multitier application, the objects in a remote server can use DataStores to interact with the database. DataStores let you take advantage of the computer resources provided by a server machine, removing the need to perform database operations on each client.

DataStore methods

Most of the methods and events available for DataWindows are also available for DataStores. However, some of the methods that handle online interaction with the user are not available. For example, DataStores support the Retrieve, Update, InsertRow, and DeleteRow methods, but not GetClickedRow and SetRowFocusIndicator.

Prompting for information

When you are working with DataStores, you cannot use functionality that causes a dialog box to display to prompt the user for more information. Here are some examples of ways to overcome this restriction:

SetSort and SetFilter

You can use the SetSort and SetFilter methods to specify sort and filter criteria for a DataStore object, just as you would with a DataWindow control. However, when you are working with a DataWindow control, if you pass a NULL value to either SetSort or SetFilter, the DataWindow prompts the user to enter information. When you are working with a DataStore, you must supply a valid format when you call the method. Moreover, you must supply a valid format when you share data between a DataStore and a DataWindow control; you cannot pass the NULL value to the DataWindow control rather than the DataStore.

Prompt for Criteria

You can define your DataWindow objects so that the user is prompted for retrieval criteria before the DataWindow retrieves data. This feature works with DataWindow controls only. It is not supported with DataStores.

SaveAs

When you use the SaveAs method with a DataWindow object, you can pass an empty string for the filename argument so that the user is prompted for a file name to save to. If you are working with a DataStore, you must supply the filename argument.

Prompt for Printing

For DataWindow controls, you can specify that a print setup dialog box display at execution time, either by checking the Prompt Before Printing check box on the DataWindow object's Print Specifications property page, or by setting the DataWindow object's Print.Prompt property in a script. This is not supported with DataStores.

Retrieval arguments

If you call the Retrieve method for a DataWindow control that has a DataWindow object that expects an argument, but do not specify the argument in the method call, the DataWindow prompts the user for a retrieval argument. This behavior is not supported with DataStores.

DataStores have some visual methods

Many of the methods and events that pertain to the visual presentation of the data in a DataWindow do not apply to DataStores. However, because you can print the contents of a DataStore and also import data into a DataStore, DataStores have some visually oriented events and methods. For example, DataStores support the SetBorderStyle and SetSeriesStyle methods so that you can control the presentation of the data at print time. Similarly, DataStores support the ItemError event, because data imported from a string or file that does not pass the validation rules for a column triggers this event.

For a complete list of the methods and events for the DataStore object and information about each method, see the DataWindow Reference.

DataStores require no visual overhead

Unlike DataWindow controls, DataStores do not require any visual overhead in a window. Using a DataStore is therefore more efficient than hiding a DataWindow control in a window.

Working with a DataStore

To use a DataStore, you first need to create an instance of the DataStore object in a script and assign the DataWindow object to the DataStore. Then, if the DataStore is intended to retrieve data, you need to set the transaction object for the DataStore. Once these setup steps have been performed, you can retrieve data into the DataStore, share data with another DataStore or DataWindow control, or perform other processing.

Examples

The following script uses a DataStore to retrieve data from the database. First it instantiates the DataStore object and assigns a DataWindow object to the DataStore. Then it sets the transaction object and retrieves data into the DataStore:

datastore lds_datastore
lds_datastore = CREATE datastore
lds_datastore.DataObject = "d_cust_list"
lds_datastore.SetTransObject (SQLCA)
lds_datastore.Retrieve()
/* Perform some processing on the data... */

Using a custom DataStore object

This section describes how to extend a DataStore in PowerBuilder by creating a user object.

You might want to use a custom version of the DataStore object that performs specialized processing. To define a custom DataStore, you use the User Object painter. There you specify the DataWindow object for the DataStore, and you can optionally write scripts for events or define your own methods, user events, and instance variables.

Using a custom DataStore involves two procedures:

In the User Object painter, define and save a standard class user object inherited from the built-in DataStore object.
Use the custom DataStore in your PowerBuilder application.

Once you have defined a custom DataStore in the User Object painter, you can write code that uses the user object to perform the processing you want.

For instructions on using the User Object painter in PowerBuilder, see the section called “About the User Object painter” in Users Guide.

To define the standard class user object:

Select Standard Class User Object on the PBObjects tab in the New dialog box.
Select datastore as the built-in system type that you want your user object to inherit from, and click OK.

The User Object painter workspace displays so that you can define the custom object.
Specify the name of the DataWindow object in the DataObject box in the Properties view and click OK.
Customize the DataStore by scripting the events for the object, or by defining methods, user events, and instance variables.
Save the object.

To use the user object in your application:

Select the object or control for which you want to write a script.
Open the Script view and select the event for which you want to write the script.
Write code that uses the user object to do the necessary processing.

Here is a simple code example that shows how to use a custom DataStore to retrieve data from the database. First it instantiates the custom DataStore object, then it sets the transaction object and retrieves data into the DataStore:
```
uo_cust_dstore lds_cust_dstore
 lds_cust_dstore = CREATE uo_cust_dstore
 lds_cust_dstore.SetTransObject (SQLCA)
 lds_cust_dstore.Retrieve()
 /* Perform some processing on the data... */
```
Notice that this script does not assign the DataWindow object to the DataStore. This is because the DataWindow object is specified in the user object definition.

Changing the DataWindow object at execution time

When you associate a DataWindow object with a DataStore in the User Object painter, you are setting the initial value of the DataStore's DataObject property. During execution, you can change the DataWindow object for the DataStore by changing the value of the DataObject property.
Compile the script and save your changes.

Accessing and manipulating data in a DataStore

To access data using a DataStore, you need to read the data from the data source into the DataStore.

If the data source is a database

If the data for the DataStore is coming from a database (that is, the data source was defined as anything but External in the DataWindow painter), you need to communicate with the database to get the data. The steps you perform to communicate with the database are the same steps you use for a DataWindow control.

For more information about communicating with the database, see Accessing the database.

If the data source is not a database

If the data for the DataWindow object is not coming from a database (that is, the data source was defined as External in the DataWindow painter), you can use the following methods to import data into the DataStore:

ImportClipboard

ImportFile

ImportString

You can also get data into the DataStore by using a DataWindow data expression, or by using the SetItem method.

For more information on accessing data in a DataStore, see Accessing Data in Code in DataWindow Reference.

About the DataStore buffers

Like a DataWindow control, a DataStore uses three buffers to manage data:

Buffer	Contents
Primary	Data that has not been deleted or filtered out (that is, the rows that are viewable)
Filter	Data that was filtered out
Delete	Data that was deleted by the user or in a script

About the Edit control

The DataStore object has an Edit control. However, the Edit control for a DataStore behaves in a slightly different manner from the Edit control for a DataWindow. The Edit control for a DataWindow keeps track of text entered by the user in the current cell (row and column); the Edit control for a DataStore is used to manage data imported from an external source, such as the clipboard or a file. The text in the Edit control for a DataStore cannot be changed directly by the user. It must be manipulated programmatically.

Programming with DataStores

There are many methods for manipulating DataStore objects. These are some of the more commonly used:

Method	Purpose
DeleteRow	Deletes the specified row from the DataStore.
Filter	Filters rows in the DataStore based on the current filter criteria.
InsertRow	Inserts a new row.
Print	Sends the contents of the DataStore to the current printer.
Reset	Clears all rows in the DataStore.
Retrieve	Retrieves rows from the database.
RowsCopy	Copies rows from one DataStore to another DataStore or DataWindow control.
RowsMove	Moves rows from one DataStore to another DataStore or DataWindow control.
ShareData	Shares data among different DataStores or DataWindow controls. See Sharing information.
Sort	Sorts the rows of the DataStore based on the current sort criteria.
Update	Sends to the database all inserts, changes, and deletions that have been made since the last Update.

For information about DataStore methods, see Methods for the DataWindow Control in DataWindow Reference.

Dynamic DataWindow objects

The methods in the table above manipulate data in the DataStore but do not change the definition of the underlying DataWindow object. In addition, you can use the Modify and Describe methods to access and manipulate the definition of a DataWindow object. Using these methods, you can change the DataWindow object during execution. For example, you can change the appearance of a DataWindow or allow your user to create ad hoc reports.

For more information, see Dynamically Changing DataWindow Objects.

Property and data expressions

You can use the same property and data expressions as for the DataWindow control. For information, see DataWindow Reference.

Using DataStore properties and events

This chapter mentions only a few of the properties and events that you can use to manipulate DataStores. For more information about DataStore properties and events, see DataWindow Reference.

Sharing information

Example: printing data from a DataStore
Example: using two DataStores to process data

The ShareData method allows you to share a result set among two different DataStores or DataWindow controls. When you share information, you remove the need to retrieve the same data multiple times.

The ShareData method shares data retrieved by one DataWindow control or DataStore (called the primary DataWindow) with another DataWindow control or DataStore (the secondary DataWindow).

Result set descriptions must match

When you share data, the result set descriptions for the DataWindow objects must be the same. However, the SELECT statements can be different. For example, you could use the ShareData method to share data between DataWindow objects that have the following SELECT statements (because the result set descriptions are the same):

SELECT dept_id from dept
SELECT dept_id from dept where dept_id = 200
SELECT dept_id from employee

You can also share data between two DataWindow objects where the source of one is a database and the source of the other is external. As long as the lists of columns and their datatypes match, you can share the data.

What is shared?

When you use the ShareData method, the following information is shared:

Primary buffer

Delete buffer

Filter buffer

Sort order

ShareData does not share the formatting characteristics of the DataWindow objects. That means you can use ShareData to apply different presentations to the same result set.

When you alter the result set

If you perform an operation that affects the result set for either the primary or the secondary DataWindow, the change affects both of the objects sharing the data. Operations that alter the buffers or the sort order of the secondary DataWindows are rerouted to the primary DataWindow. For example, if you call the Update method for the secondary DataWindow, the update operation is applied to the primary DataWindow also.

Turning off sharing data

To turn off the sharing of data, you use the ShareDataOff method. When you call ShareDataOff for a primary DataWindow, any secondary DataWindows are disassociated and no longer contain data. When you call ShareDataOff for a secondary DataWindow, that DataWindow no longer contains data, but the primary DataWindow and other secondary DataWindows are not affected.

In most cases you do not need to turn off sharing, because the sharing of data is turned off automatically when a window is closed and any DataWindow controls (or DataStores) associated with the window are destroyed.

Crosstabs

You cannot share data with a DataWindow object that has the Crosstab presentation style.

Example: printing data from a DataStore

Suppose you have a window called w_employees that allows users to retrieve, update, and print employee data retrieved from the database:

The sample DataWindow object displayed in the DataWindow control has three columns with the headings Employee ID, First Name, and Last Name. An icon of a hand points at the employee ID that has the edit control placed on it. To the right of the DataWindow buttons are displayed for Update and Print.

The DataWindow object displayed in the DataWindow control is suitable for online display but not for printing. In this case, you could define a second DataWindow object for printing that has the same result set description as the object used for display and assign the second object to a DataStore. You could then share data between the DataStore and the DataWindow control. Whenever the user asked to print the data in the window, you could print the contents of the DataStore.

When the window or form opens

The code you write begins by establishing the hand pointer as the current row indicator for the dw_employees DataWindow control. Then the script sets the transaction object for dw_employees and issues a Retrieve method to retrieve some data. After retrieving data, the script creates a DataStore using the instance variable or data member ids_datastore, and assigns the DataWindow object d_employees to the DataStore. The final statement of the script shares the result set for the dw_employees DataWindow control with the DataStore.

This code is for the window's Open event:

dw_employees.SetRowFocusIndicator(Hand!)
 dw_employees.SetTransObject(SQLCA)
 dw_employees.Retrieve()
  
 ids_datastore = CREATE datastore
 ids_datastore.DataObject = "d_employees"
 dw_employees.ShareData(ids_datastore)

Code for the Update button

Code for the cb_update button applies the update operation to the dw_employees DataWindow control.

This code is for the Update button's Clicked event:

IF dw_employees.Update() = 1 THEN
       COMMIT using SQLCA;
       MessageBox("Save","Save succeeded")
 ELSE
       ROLLBACK using SQLCA;
       MessageBox("Save","Save failed")
 END IF

Code for the Print button

The Clicked event of the cb_print button prints the contents of ids_datastore. Because the DataWindow object for the DataStore is d_employees, the printed output uses the presentation specified for this object.

This code is for the Print button's Clicked event:

ids_datastore.Print()

When the window or form closes

When the window closes, the DataStore gets destroyed.

This code is for the window's Close event:

destroy ids_datastore

Example: using two DataStores to process data

Suppose you have a window called w_multi_view that shows multiple views of the same result set. When the Employee List radio button is selected, the window shows a list of employees retrieved from the database:

This sample screen titled Employee Information has two radio buttons labeled Options. The Employee list radio button is selected, and the window displays a view with four columns of employee data from the result set: Department, Name (first and last), and Salary.

When the Employee Salary Information radio button is selected, the window displays a graph that shows employee salary information by department:

This sample screen titled Employee Information has two radio buttons labeled Options. The Employee salary information button is selected, and the window displays a view of the result set that is a bar chart titled Average Salary by Department. The x axis lists departments, and the y axis lists salaries in 10000 dollar increments. Shaded bars indicate average salary for each department.

This window has one DataWindow control called dw_display. It uses two DataStores to process data retrieved from the database. The first DataStore (ids_emp_list) shares its result set with the second DataStore (ids_emp_graph). The DataWindow objects associated with the two DataStores have the same result set description.

When the window or form opens

When the window or form opens, the application sets the mouse pointer to the hourglass shape. Then the code creates the two DataStores and sets the DataWindow objects for the DataStores. Next the code sets the transaction object for ids_emp_list and issues a Retrieve method to retrieve some data.

After retrieving data, the code shares the result set for ids_emp_list with ids_emp_graph. The final statement triggers the Clicked event for the Employee List radio button.

This code is for the window's Open event:

SetPointer(HourGlass!)
 ids_emp_list = Create DataStore
 ids_emp_graph = Create DataStore
  
 ids_emp_list.DataObject = "d_emp_list"
 ids_emp_graph.DataObject = "d_emp_graph"
  
 ids_emp_list.SetTransObject(sqlca)
 ids_emp_list.Retrieve()
 ids_emp_list.ShareData(ids_emp_graph)
 rb_emp_list.EVENT Clicked()

Code for the Employee List radio button

The code for the Employee List radio button (called rb_emp_list) sets the DataWindow object for the DataWindow control to be the same as the DataWindow object for ids_emp_list. Then the script displays the data by sharing the result set for the ids_emp_list DataStore with the DataWindow control.

This code is for the Employee List radio button's Clicked event:

dw_display.DataObject = ids_emp_list.DataObject
ids_emp_list.ShareData(dw_display)

Code for the Employee Salary Information radio button

The code for the Employee Salary Information radio button (called rb_graph) is similar to the code for the List radio button. It sets the DataWindow object for the DataWindow control to be the same as the DataWindow object for ids_emp_graph. Then it displays the data by sharing the result set for the ids_emp_graph DataStore with the DataWindow control.

This code is for the Employee Salary Information radio button's Clicked event:

dw_display.DataObject = ids_emp_graph.DataObject
ids_emp_graph.ShareData(dw_display)

When the window or form closes

When the window closes, the DataStores get destroyed.

This code is for the window's Close event:

Destroy ids_emp_list
Destroy ids_emp_graph

Use garbage collection

Do not destroy the objects if they might still be in use by another process -- rely on garbage collection instead.

Prev	Up	Next
Dynamically Changing DataWindow Objects	Home	Manipulating Graphs

DataWindow Programmers GuideDataWindow and DataStore basics