Accessing a database

Using DataStores

The objects in a server application can use DataStores to
interact with the database. DataStores are nonvisual DataWindow
controls. DataStores act just like DataWindow controls except that
they do not have visual attributes.

DataStores can be very useful in a distributed application.
DataStores give you the ability to perform database processing on
a remote server instead of on each client machine.

RichText presentation style is not supported A server application cannot contain a DataStore that has a
DataWindow object that uses the RichText presentation style. Rich
text processing is not supported in distributed applications.

Sharing data between the server and the client

If you want to provide a visual interface to the data retrieved
on the server, you can include a window in the client that has a
DataWindow control. Whenever data is retrieved on the server, you
should refresh the DataWindow control to show the result set for
the DataStore on the server. Similarly, whenever the user makes
modifications to the data on the client, you should refresh the
contents of the DataStore on the server to reflect the current state
of the DataWindow control on the client.

To share data between a client and a server, you need to synchronize
the server DataStore and the client DataWindow control programmatically.
If you want your application to handle database updates, this involves
moving the DataWindow data buffers and status flags back and forth
between the client and the server.

For more information about synchronizing a
server DataStore with a client DataWindow, see “Performing updates”.

ShareData function is not supported in distributed
applications You cannot use the ShareData function to share data between
a DataWindow control (or DataStore) on a client and a DataStore
on a server.

Performing retrieval operations

To use a DataStore to perform retrieval operations in a server
application, you first create an instance of the DataStore object
in a script and assign the DataWindow object to the DataStore. Then
you set the Transaction object for the DataStore. Once these setup
steps have been performed, you can retrieve data into the DataStore,
print the contents of the DataStore, or perform other processing
against a retrieved result set.

Example 1: printing data on a server

Description

The following example shows how you can use a DataStore to
print data on a remote server. The server application has a nonvisual
user object called uo_employees that has a function called
print_employees. Print_employees creates an instance
of the DataStore ds_datastore, and then prints the contents of
this DataStore.

Function declaration

Here is the signature of the print_employees function:

Script

Here is the script for the print_employees function:

Example 2: passing an array by reference

Description

The following example demonstrates the use of a DataStore
to retrieve data in a server application. The server application
has a nonvisual user object called uo_customers that has
a function called retrieve_custlist. Retrieve_custlist generates
an instance of the DataStore ds_datastore and then uses
this DataStore to retrieve all of the rows in the Customer table.
Once the data has been retrieved, retrieve_custlist passes
the data back to the client application.

Function declaration

The retrieve_custlist function has an argument called
customers, which is defined as an array based on the structure st_custlist.
The structure st_custlist has the same layout as d_custlist,
the DataWindow object used to access the database. The return value
for retrieve_custlist, which is used to return the number
of rows retrieved, is of type long.

Here is the signature of the retrieve_custlist function:

Script

Here is the script for the retrieve_custlist function:

At the conclusion of processing, the function retrieve_custlist
destroys the DataStore and returns the number of rows retrieved
back to the client.

Performing updates

DataWindow synchronization

In a conventional client/server application, where
database updates are initiated by a single application running on
a client machine, PowerBuilder can manage DataWindow state information
for you automatically. But in a distributed application, the situation
is somewhat different. Because application components are partitioned
between the client and the server, you need to write logic to ensure
that the data buffers and status flags for the DataWindow control
on the client are synchronized with those for the DataStore on the
server.

PowerBuilder provides four functions for synchronizing DataWindows
and DataStores in a distributed application:

• GetFullState
• SetFullState
• GetChanges
• SetChanges

Although these functions are most useful in distributed applications,
they can also be used in nondistributed applications where multiple
DataWindows (or DataStores) must be synchronized.

Moving DataWindow buffers and status flags

To synchronize a DataWindow control on the client with a DataStore
on the server, you need to move the DataWindow data buffers and
status flags back and forth between the client and the server whenever
changes occur. The procedures for doing this are essentially the
same whether the source of the changes resides on the client or
the server.

To apply complete state information
from
one DataWindow (or DataStore) to another, you need to:

1. Invoke the GetFullState function
   to capture the current state of the source DataWindow.
2. Invoke the SetFullState function to apply the state
   of the source DataWindow to the target.

To apply changes
from one DataWindow
(or DataStore) to another, you need to:

1. Invoke the GetChanges function to capture changes
   from the source DataWindow.
2. Invoke the SetChanges function to apply changes
   from the source DataWindow to the target.

DataWindow state is stored in blobs

When you call GetFullState or GetChanges, PowerBuilder returns DataWindow
state information in a blob. The blob returned from GetFullState provides
everything required to recreate the DataWindow, including the data buffers,
status flags, and complete DataWindow specification. The blob returned
from GetChanges provides data buffers and status flags for changed and
deleted rows only.

Synchronizing after Update

By default, the Update function resets the update flags after
successfully completing the update. Therefore, when you call the
Update function on the server, the status flags are automatically
reset for the server DataStore. However, the update flags for the
corresponding client DataWindow control are not
reset.
Therefore, after issuing the Update function on the server DataStore,
you need to recapture the status flags for the server DataStore
by using GetChanges, then pass the resulting blob back to the client
and call SetChanges to apply the status flags to the client DataWindow.

One source, one target

You can synchronize a single DataWindow (or DataStore) with
a single target DataWindow (or DataStore). Do not try
to synchronize a single source with multiple targets, or vice versa.

Typical usage scenario

Suppose the server has a DataStore called DS_1 that
is the source of data for a target DataWindow called DW_1
on the client. After the server issues a Retrieve function for DS_1,
it calls GetFullState and passes the resulting blob to the client.
The client calls SetFullState to apply the state information from this
blob to DW_1.

After the user inserts some new rows in DW_1 and
changes and deletes some of the existing rows, the client calls
GetChanges and passes the resulting blob back to the server. The
server then calls SetChanges to apply the modifications to DS_1.
The result of these operations is that DS_1 contains new
rows from DW_1 and changed and deleted rows that originally
came from DS_1. Because the blobs keep track of source
information, DS_1 knows the source DataWindow where each
row originated.

After applying the client modifications to DS_1,
the server updates the database. Then it calls GetChanges on DS_1
and passes the resulting blob back to the client. To ensure that
DW_1 is synchronized with DS_1, the client calls SetChanges
to apply the status flags from the DS_1 to DW_1:

Example 1: performing updates in a distributed application

Suppose the client has a window called w_employee
that has buttons that allow the user to retrieve and update data.

Client window definition

The Retrieve button on the client window has the following
script:

The Update button on the client window has the following script:

Server object definition

The server has an object called uo_employee that
has the following functions:

• RetrieveData
• UpdateData

Constructor

The Constructor event for the uo_employee object
creates the DataStore that will be used to access the database:

Script for the RetrieveData function

The RetrieveData function takes an argument called ablb_data,
which is passed by reference. The function returns a long value.

Here is the script for the RetrieveData function:

Script for the UpdateData function

The UpdateData function takes an argument called ablb_changes,
which is passed by reference. The function returns a long value.

Here is the script for the UpdateData function:

Example 2: using a generic DataStore

In this example, the client uses the same user interface as
in Example 1; however, the server uses a generic DataStore
to
handle database access. Before initiating a retrieval request, the
client captures the definition of a DataWindow object into a blob
and then passes the blob to the server. The server then uses this
blob to set the DataWindow object for the generic DataStore. Because
the client determines which DataWindow object will be used to access
the database, the server PBL does not need to contain the DataWindow
object definition.

You can use generic DataStores on the server or on
the client This example shows how to use a generic DataStore on the server;
however, you can use a DataStore on the client instead of the server.
This removes the need to have the DataWindow object definitions
on the client, thereby reducing the size and complexity of the client.

Client window definition

The Retrieve button on the client window has the following
script:

The Update button on the client window has the following script:

Server object definition

The server has an object called uo_employee that
has the following functions:

• RetrieveData
• UpdateData

Constructor

The Constructor event for uo_employee creates the
DataStore that will be used to access the database:

Script for the RetrieveData function

The RetrieveData function takes an argument called ablb_data,
which is passed by reference. The function returns a long value.

Here is the script for the RetrieveData function:

Script for the UpdateData function

The UpdateData function takes an argument called ablb_changes,
which is passed by reference. The function returns a long value.

Here is the script for the UpdateData function:

Pooling database transactions

To optimize database processing, a server application can
pool database transactions. Transaction pooling maximizes database
throughput while also controlling the number of database connections
that can be open at one time. By establishing a transaction pool,
a server application can reuse connections made to the same data
source.

How it works

When a server connects to a database without using transaction
pooling, PowerBuilder physically terminates each database transaction
for which a DISCONNECT statement is issued. When transaction pooling
is in effect, PowerBuilder logically terminates the database connections,
but does not physically remove them. Instead, the database connections
are kept open in the transaction pool so that they can be reused
for other database operations.

When to use transaction pooling

Transaction pooling can enhance the performance of a server
application that services a high volume of short transactions to
the same data source.

What you need to do

To establish a transaction pool, you use the SetTransPool
function. You can code SetTransPool anywhere in your application,
as long as it is executed before the server connects to the database.
A logical place to execute SetTransPool is in the server application’s
Open event.

Example

This statement specifies that up to 16 database connections
will be supported through this server and that 12 connections will
be kept open once successfully connected. When the maximum number
of connections has been reached, each subsequent connection request
will wait for up to 10 seconds for a connection in the pool to become
available. After 10 seconds, the server will return an error to
the client:

For more information about the SetTransPool
function, see the PowerScript Reference
.

---