Accessing a database from a Jaguar component

Database connectivity

You can access a database from a Jaguar component. If you
want to take advantage of Jaguar’s support for connection
pooling and transaction management, you need to use one of the following
database interfaces to connect to the database:

• Open
  Database Connectivity (ODBC) database interface
• Sybase SYJ database interface, which provides connectivity
  to Sybase Adaptive Server Enterprise 11.5 via Jaguar
• JDS database interface, which provides connectivity
  through Sun’s Java virtual machine to a JDBC driver (such
  as Sybase jConnect or ORACLE Thin)
• Oracle 7.3 database interface

The SYC interface does not work with Jaguar Jaguar uses a different version of the Sybase Open Client
Client Library (CT-Lib) software from PowerBuilder. Therefore, at
runtime, you need to use SYJ rather than SYC to connect to an Adaptive
Server Enterprise database. The SYJ interface, however, does not
work in the PowerBuilder development environment. Therefore, during
the development phase (before the component has been deployed to
Jaguar), you must use SYC to connect to the database.

Using DataStores

Jaguar
components developed in PowerBuilder 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 useful in a distributed application: they
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 component
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 need to include a window in the client that has
a DataWindow control. Whenever data is retrieved on the server,
you need to 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 need to 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 on a client and a DataStore on a server.

Using connection caching

Benefits of connection caching

To optimize database processing, Jaguar provides support for connection caching.
Connection caching allows Jaguar components to share pools of preallocated
connections to a remote database server, avoiding the overhead imposed
when each instance of a component creates a separate connection.
By establishing a connection cache, a Jaguar server can reuse connections
made to the same data source.

Connection caching is very similar to the transaction pooling
support provided with distributed PowerBuilder.

How it works

Ordinarily, when a PowerBuilder application connects to a
database, PowerBuilder physically terminates each database connection
for which a DISCONNECT statement is issued. By contrast, when a
PowerBuilder component uses a Jaguar connection cache, Jaguar logically
terminates the database connection but does not physically remove
the connection. Instead, the database connection is kept open in
the connection cache so that it can be reused for other database
operations.

All connections in a cache must share a common user name,
password, server name, and connectivity library.

Accessing a cache by user

If you want to retrieve a connection from the cache that uses
a specified set of user name, password, server, and connectivity
library values, you do not need to modify your database access code
to enable it to use the cache. You simply need to create a new cache
in the Jaguar Manager that has the database connection properties
(user name, password, server name, and connectivity library) required
by the component. At runtime, when the component tries to connect
to the database, Jaguar automatically returns a connection from
the cache that matches the connection values requested by the component.

Accessing a cache by name

If you want to retrieve a connection from a cache by specifying
the cache name, you need to set the CacheName DBParm
to identify the cache you want to use. Accessing a cache by name
allows you to change the user name, password, or server in Jaguar
Manager without requiring corresponding changes to your component
source code.

Enabling cache-by-name access To access a cache by name, you need to select the Enable Cache-By-Name Access
option for the cache in Jaguar Manager. By default, this option
is not selected.

To enable cache-by-name access, you need to have jagadmin
rights.

Here’s some sample code for a PowerBuilder component
that shows how to access a cache by name:

What happens when all connections are in use

You can control what happens if all connections in a cache
are in use. To do this, set the GetConnectionOption DBParm
to one of the following values:

Value	Description
JAG_CM_NOWAIT	Causes the attempt to connect to fail with an error if no connection can be returned
JAG_CM_WAIT	Causes the component to wait until a connection becomes available
JAG_CM_FORCE	Allocates and opens a new connection. The new connection is not cached and will be destroyed when it is no longer needed

By default, PowerBuilder uses JAG_CM_FORCE.

What happens when a connection is released

You can also control what happens when a connection is released.
To do this, set the ReleaseConnectionOption DBParm
to one of the following values:

Value	Description
JAG_CM_DROP	Closes and deallocates the connection. If the connection came from a cache, a new connection will be created in its place. Use JAG_CM_DROP to destroy a connection when errors have made it unusable
JAG_CM_UNUSED	If the connection was taken from a cache, it is placed back in the cache. A connection created outside of a cache is closed and destroyed

By default, PowerBuilder uses JAG_CM_UNUSED.

Providing support for transactions

Benefits of Jaguar’s transaction support

Jaguar components that you develop in PowerBuilder can participate
in Jaguar transactions. A Jaguar transaction is
a transaction whose boundaries and outcome are determined by Jaguar.
You can mark components to indicate that they will provide transaction
support. When a component provides transaction support, Jaguar ensures
that the component’s database operations execute as part
of a transaction.

Multiple Jaguar components can participate in a single Jaguar
transaction; Jaguar ensures that database changes performed by the
participating components are all committed or rolled back. By defining
components to use Jaguar transactions, you can ensure that all work
performed by components that participate in a transaction occurs
as intended.

Indicating how the component will support transactions

Each Jaguar component has a transaction attribute that indicates
how the component participates in Jaguar transactions. When you
develop a Jaguar component in PowerBuilder, you can specify the
transaction attribute in the wizards. Here are the options:

Transaction type	Description
Not supported	The component never executes as part of a transaction. If the component is activated by another component that is executing within a transaction, the new instance’s work is performed outside the existing transaction
Supports Transaction	The component can execute in the context of a Jaguar transaction, but a transaction is not required to execute the component’s methods. If the component is instantiated directly by a client, Jaguar does not begin a transaction. If component A is instantiated by component B and component B is executing within a transaction, component A executes in the same transaction
Requires Transaction	The component always executes in a transaction. When the component is instantiated directly by a client, a new transaction begins. If component A is activated by component B and B is executing within a transaction, A executes within the same transaction; if B is not executing in a transaction, A executes in a new transaction
Requires New Transaction	Whenever the component is instantiated, a new transaction begins. If component A is activated by component B, and B is executing within a transaction, then A begins a new transaction that is unaffected by the outcome of B’s transaction; if B is not executing in a transaction, A executes in a new transaction

Using the transaction service context object

Component methods can call Jaguar’s transaction state
primitives to influence whether Jaguar commits or aborts the current
transaction. To give you access to Jaguar’s transaction
state primitives, PowerBuilder provides a transaction service context
object called TransactionServer.

To use the TransactionServer context object, you need to set
the UseContextObject DBParm parameter to
Yes. This tells PowerBuilder that you will be using the methods
of the TransactionServer object rather than COMMIT and ROLLBACK
to indicate whether the component has completed its work for the
current transaction.

Before you can use the transaction context service, you need
to declare a variable of type TransactionServer and call the GetContextService
function to create an instance of the service.

Example In the Activate (or Constructor) event for a component, you
could call GetContextService to instantiate the TransactionServer
service:

In one of the component methods, you could then update the
database and call SetComplete if the update succeeds or SetAbort
if it fails:

The TransactionServer interface provides these methods to
allow you to access Jaguar’s transaction primitives:

Method	Description
DisableCommit	Indicates that the current transaction cannot be committed because the component’s work has not been completed; the instance remains active after the current method returns
EnableCommit	Indicates that the component should not be deactivated after the current method invocation; allows the current transaction to be committed if the component instance is deactivated
IsInTransaction	Determines whether the current method is executing in a transaction
IsTransactionAborted	Determines whether the current transaction has been aborted
SetAbort	Indicates that the component cannot complete its work for the current transaction and that the transaction should be rolled back. The component instance will be deactivated when the method returns
SetComplete	Indicates that the component has completed its work in the current transaction and that, as far it is concerned, the transaction can be committed and the component instance can be deactivated

Automatic Demarcation/ Deactivation

If you want a component to be automatically deactivated after
each method invocation, you can enable Automatic Demarcation/Deactivation
for the component. This sets the component’s tx_vote
property to False. When Automatic Demarcation/Deactivation
is enabled, you do not need to make explicit calls to SetComplete
to cause deactivation because SetComplete is assumed by default.
To rollback the transaction, you can call SetAbort.

If you do not want the component to be automatically deactivated
after each method invocation, you need to disable the Automatic Demarcation/Deactivation
setting for the component. This sets the component’s tx_vote
property to True. When you disable Automatic Demarcation/Deactivation,
Jaguar waits for notification before completing transactions; therefore,
you need to be sure to deactivate programmatically by making an
explicit call to SetComplete (or SetAbort).

COMMIT and ROLLBACK

You have the option to disable the TransactionServer context
object and use the COMMIT and ROLLBACK statements instead to specify
the Jaguar transaction state for a component. This capability is
provided to allow you to migrate PowerBuilder 6 objects to Jaguar
without modifying the code. To disable the TransactionServer context
object, you need to set the UseContextObject DBParm
parameter to No. When you do this, COMMIT is equivalent to SetComplete
and ROLLBACK is equivalent to SetAbort.

COMMIT and ROLLBACK in nontransactional components In nontransactional components that disable the TransactionServer
context object, COMMIT does not invoke SetComplete and ROLLBACK
does not invoke SetAbort. For example, if you specify Not Supported
as the transaction type, disable Automatic Demarcation/Deactivation
(set tx_vote to True), and set the UseContextObject parameter
to No, the PowerBuilder virtual machine will not
issue
a SetComplete when you execute a COMMIT (or a SetAbort when you
execute a ROLLBACK). In this case, Jaguar will never release the component
because it is waiting for a call to SetComplete or SetAbort.

If you disable Automatic Demarcation/Deactivation
for a component that performs no database access whatsoever, then
you must use the TransactionServer object to call SetComplete (or
SetAbort) to deactivate the component. Otherwise, the component
will never be deactivated.

Transactions and the component lifecycle

Jaguar’s transaction model and the component lifecycle
are tightly integrated. Component instances that participate in
a transaction are never deactivated until the transaction ends or
until the component indicates that its contribution to the transaction
is over (its work is done and ready for commit or its work must
be rolled back). An instance’s time in the active state
corresponds exactly to the beginning and end of its participation
in a transaction.

For more information, see the Jaguar CTS documentation
.

Performing retrieval operations

To use a DataStore to perform retrieval operations, you first
need to create an instance of the DataStore object in a script and
assign the DataWindow object to the DataStore. Then you need to
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

This example shows how you can use a DataStore to print data
on a remote server. The server component uo_employees has
a function called print_employees. Print_employees
generates an instance of the DataStore ds_datastore, and
then prints the contents of this DataStore.

Platform note The following example would not work on a UNIX machine. On
UNIX, Jaguar uses a windows-free version of the PowerBuilder runtime
environment that does not provide support for graphical operations
such as printing.

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

This example demonstrates the use of a DataStore to retrieve
data in a server component. The server component uo_customers
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.

SetChanges can be applied to an empty DataWindow You can call SetChanges to apply changes to an empty DataWindow
(or DataStore). The target DataWindow does not need to contain a
result set from a previous retrieval operation. However, the DataWindow
must have access to the DataWindow definition. This means that you
need to assign the DataWindow object to the target DataWindow before
calling SetChanges.

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
a successful 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, if
the Update function on the server DataStore succeeds, you need to
call ResetUpdate on the client DataWindow to reset the flags.

One source, one target

You can synchronize a single source 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 Jaguar server has a component that uses a DataStore
called DS_1. This DataStore is the source of data for a
target DataWindow called DW_1 on the client. In the Activate
event, the component connects to the database, creates a DataStore,
and assigns the DataWindow object to the DataStore.

In one of its methods, the server component issues a Retrieve
function for DS_1, calls GetFullState on DS_1,
and then passes the resulting blob to the client. Because the component’s
Automatic Demarcation/Deactivation setting is disabled,
it also calls SetComplete before the method returns to cause the component
instance to be deactivated.

If Automatic Demarcation/Deactivation were
enabled If the Automatic Demarcation/Deactivation setting
were enabled for the component, it would not need to call SetComplete
after the retrieval because the component instance would automatically
be deactivated when the method finished execution.

Once the client has the DataWindow blob, it calls SetFullState
to apply the state information from the blob to DW_1. At
this point, the user can insert new rows in DW_1 and change
or delete some of the existing rows. When the user makes an update
request, the client calls GetChanges and invokes another component
method that passes the resulting blob back to the server. The component
method then calls SetChanges to apply the changes from DW_1
to DS_1. After synchronizing DS_1 with DW_1,
the server component updates the database and calls SetComplete
or SetAbort to indicate whether the update was successful.

If the update was successful, the client calls ResetUpdate
to reset the status flags on the client DataWindow.

After the completion of the first update operation, the client
and server can pass change blobs (rather than complete state information)
back and forth to handle subsequent updates. From this point on,
the update process is an iterative cycle that begins with Step 7
and concludes with Step 14.

Example

The following example shows how you might synchronize DataWindows between
a PowerBuilder client and a Jaguar server component. This example uses
a stateless component.

Client window definition

Suppose the client has a window called w_employee
that has buttons that allow the user to retrieve and update data.
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

Instance variables

The uo_employee object has these instance variables:

Activate

The Activate event for the uo_employee object instantiates
the TransactionServer service. In addition, it connects to the database
and 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 a blob 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_data,
which is a blob passed by reference. The function returns a long
value.

Here is the script for the UpdateData function:

Deactivate

The Deactivate event for the uo_employee object destroys
the DataStore and disconnects from the database:

Passing result sets

PowerBuilder provides two system objects to handle getting
result sets from components running in Jaguar and returning result
sets from PowerBuilder user objects running as Jaguar components.
These system objects, ResultSet and ResultSets, are designed to
simplify the conversion of transaction server result sets to and
from DataStore objects and do not contain any state information.
They are not designed to be used for database updates. You use the
CreateFrom and GenerateResultSet functions on the DataStore object
to convert the result sets stored in these objects to and from DataStore
objects.

About GenerateResultSet GenerateResultSet has an alternative syntax used for returning
a Tabular Data Stream result set when using MASP (Method as Stored
Procedure) with Jaguar. For more information, see the DataWindow
Reference
.

Component methods that return result sets use the TabularResults
module. Single result sets are returned as TabularResults::ResultSet
structures. Multiple result sets are returned as a sequence of ResultSet
structures using the TabularResults::ResultSets data type.

Accessing result sets in Jaguar components from
PowerBuilder clients

When you generate a Jaguar proxy object in PowerBuilder for
a Jaguar component method that returns TabularResults::ResultSet,
the method on the proxy object returns a PowerBuilder ResultSet
object. Methods that return multiple result sets return a PowerBuilder
ResultSets object.

Viewing proxies in the Browser You can view the properties and methods of Jaguar proxy objects
on the Proxy tab in the PowerBuilder Browser.

For example, the Sybase Virtual University sample SVUBookstore
component has several methods that return a TabularResults::ResultSet
value. When you create a Jaguar proxy object for the component,
this is how the GetMajors method displays as a PowerBuilder user
object function in the Browser:

You can access the result set from a PowerBuilder client by
creating an instance of the component, calling the method, and then
using the result set to populate a DataStore object with the CreateFrom
function. This example creates an instance of the SVUBookstore component
and calls the GetMajors method:

Returning result sets from
Jaguar components

To pass or return result sets from a PowerBuilder user object
that will be deployed to Jaguar, set the data type of a function’s
argument or return value to ResultSet (for a single result set)
or ResultSets (for multiple result sets). When the user object is
deployed as a Jaguar component, the ResultSet and ResultSets return
values will be represented in the IDL interface of the component
as TabularResults::ResultSet and TabularResults::ResultSets data types.

In this example, a DataStore object is created and data is
retrieved into it, and then the GenerateResultSet function is used
to create a result set that can be returned to a client:

---