Using ADO to Query the eProcess Database
Querying
eProcess queues, rosters, or logs is a typical action performed by
a Step Processor (or "work performer") application. These queries
normally use appropriate
eProcess APIs to retrieve the data. While the application
queries a queue, roster, or log, it is understood that the data retrieved by
these queries actually resides in the underlying
eProcess database tables (either
a SQL Server or Oracle database). However, in certain instances, for example,
where the query is to retrieve a large amount of data, performance can become
an issue (for details, see Why use ADO?
Query Performance Issues below).
In these cases, an alternative approach that can improve performance, is to
bypass the API and query the eProcess database views directly, using an Active
Directory Object (ADO) Connection object or ODBC to directly connect to the
database. While there is no requirement for using this approach (using the eProcess
API classes and methods is the standard way to do queries), the ADO approach
can significantly improve performance when you need to query large numbers of
data records.
This topic describes how to use ADO to query the eProcess database, as follows:
Note Use of this approach requires an understanding of the
eProcess database
schema, ADO, and some SQL.
ADO
eProcess Query Overview
The basic premise of the ADO approach is that instead of querying on a queue,
roster, or log via the eProcess APIs, you instead query on database views generated
by the eProcess server. These views are of the underlying eProcess database
tables associated with the queues, rosters, and logs of a given isolated region.
Using the ADO approach, you can directly query on exposed fields of these database
views via an ADO Connection object, completely bypassing the eProcess API. For
a step-by-step procedure on how to do this, see Using
the ADO Query Procedure below.
A typical use case for the ADO query approach involves situations where a user
application needs to query for a large amount of data. In this case, you might
retrieve the data records with an ADO query, then use the data items retrieved
to query specific work items in eProcess. For example, you might use the F_WobNum
field retrieved as part of the data record to query for a specific work item
using the eProcess API.
To accomplish these steps involves an understanding of the following issues:
Caution It is strongly recommended that you not proceed without first
reviewing these issues. In addition, under no circumstances should you attempt
to directly modify the eProcess database.
Back to top
Understanding eProcess Database Views
Each time a user performs a commit operation on configuration changes on the
workflow server, or a transfer on a workflow definition, the eProcess server
automatically creates database views of those database tables related to the
isolated region's queues, rosters, and logs for the workflow definition. This
occurs automatically, with no customization required (or recommended). The existence
of these database views enables exposed fields associated with the tables in
these views to be directly queried from your application or applet via ADO or
ODBC (not all exposed fields, only those useful in a practical way for queries
are actually available for additional information, see Fields
available for queries below).
The following naming conventions are used for the database views:
- Queue database view: VWVQ<isolated region>_<workflow
definition queue name>; for example, for isolated region 21 and the
Inbox queue: VWVQ21_Inbox
- Roster database view: VWVR<isolated region>_<workflow
definition roster name>; for example, for isolated region 21 and the
DefaultRoster view: VWVR21_DefaultRoster
- Log database view: VWVL<isolated region>_<workflow
definition queue name>; for example, for isolated region 21 and the
mylog view: VWVQ21_mylog
It is important to note that the read/write permissions associated with these
database views created by the eProcess server vary according to whether the
underlying database is Oracle or MS SQL Server. The Oracle database views created
by the eProcess server are read-only. MS SQL Server data views are read/write
(SQL Server does not provide a way to create read-only views.)
Warning If you are accessing a SQL Server database,
it is essential that you do not directly modify the database directly, even
though the DBMS allows it. If you do modify a SQL Server database directly,
eProcess processing will not work correctly. It is recommended that the Database
Administrator make the underlying tables (on which the database views are based)
Read-only for users.
Back to top
Why Use ADO? Query Performance
Issues
eProcess query performance, is affected by a number of factors. The most important
of these factors are:
- The types of objects returned by the query: Normal API eProcess queries
can return any of three types of objects: data records (queue element and
roster element objects), step element objects, and work objects. Of these,
the most expensive in terms of performance, are work objects, because they
require deblobbing. Next most expensive are step elements, that require some
deblobbing. Data records are the least expensive objects, requiring no deblobbing.
In addition, ADO queries are more efficient because they return only a subset
of the information retrieved by API queries. In particular, ADO queries do
not return additional isolated region data that is returned by API queries.
- The data transformations: The number and types of data transformations
between the client and the database can also affect performance. For example,
with an eProcess API query, there is the data transformation between the SQL
data records returned by the DBMS and the C++ data objects handled by the
eProcess server, then the data transformation between C++ and the Java objects
used by the eProcess Java API. If the Windows client application is non-Java,
an additional transformation between Java and COM (via JiGlue) is also required.
On the other hand (regardless of the source language you write your application
in), you specify the ADO query using SQL, thereby reducing the number of data
transformations needed.
- The use of indexes: Although the use of indexes is recommended for
the eProcess
API, some developers may not use indexes for their custom application or processor
queries. As a result, query data retrieval is less efficient, particularly
if they need to retrieve large amounts of data from the database. The developer
(or the Database Administrator) can add indexes to the database to additionally
improve performance.
Back to top
Fields Available for Queries
You query on exposed fields provided in the database view. Therefore, each
of the fields that you need to query must exist as an exposed field on a queue,
roster, or log, as defined by a workflow definition. Fields that are not exposed
are not included in the database view. Of all the exposed fields, those exposed
system fields that are not useful for ADO queries are automatically excluded
from the view. Similarly, large binary fields and BLOBS are also not included.
In other words, only those exposed fields that are useful in a practical way
for ADO queries are actually available.
The fields in the database views have the same names as the exposed fields
in the underlying database tables from which they are derived. However, the
following database fields are not allowed in the database views:
- Fields that contain database reserved words.
- Fields whose names may present conflict with eProcess server software.
- Fields whose names are longer than 32 characters.
- Field names that differ from the names of their respective fields in the
underlying database.
Back to top
Before You Start Things to Keep in Mind
Before you start using ADO queries, you should keep the following important
guidelines and limitations in mind:
- Reminder: ADO queries are based on database views. Database records
should not be updated via ADO.
- Each client that is to use ADO eProcess queries must be individually configured.
- The database client and the database server (whether using Oracle or SQL
Server) cannot be on the same machine.
- If you are using an Oracle database, Listener software must be set up first
on the server. Then the Oracle network client must be set up before you can
configure the ADO/ODBC connection.
- As indicated by the previous Warning,
if you are using an MS SQL Server database, since SQL Server does not provide
a way to create read-only views, it is essential that you do not modify the
SQL Server database. If you do so, eProcess processing will not work correctly.
Back to top
Using the ADO Query Procedure
To use ADO or ODBC to query the eProcess database views for queue, roster,
and log data, requires the following basic steps, which are performed in the
order shown:
Note This procedure varies somewhat, depending upon whether the underlying
database is Oracle or MS SQL Server.
- Configuring the DBMS server (Oracle
only).
- Configuring the client to connect to the
database (Oracle only).
- Configuring an ADO connection
(Oracle and MS SQL Server)
- Identifying the queues, rosters, and logs
(and their associated database views).
- Specifying the query.
These steps are described in detail below.
Configuring the DBMS Server (Oracle
only)
This step describes how to configure the Oracle server for ADO
eProcess queries.
If you are using a SQL Server database, skip to the step, Configuring
an ADO Connection.
Caution The database client and the database server cannot be on the
same machine. (The Oracle database server and the eProcess
server can, at your option, be on the same machine.)
To configure an Oracle server for ADO, use the following procedure:
- Logon to the Oracle DBMS server as the fnsw
user.
- cd to $ORACLE_HOME. Verify that you are logged on as the fnsw
user.
- Type netca at the command line to bring up the Oracle Net(work) Configuration
Assistant.
- Using the Net Configuration Assistant, install and configure Oracle network
configuration on the Oracle server by configuring (in the order shown
if you make a mistake, the Assistant allows you to backtrack):
- Listener configuration
- Naming methods configuration
- Local net service name configuration
Start by selecting Listener Configuration and click Next.
- Select Add (to add a Listener) and click Next.
- Enter the Listener name (for example, LISTENER)
into the Listener Name field and click Next.
- Select the network protocol (for example, TCP)
and click Next. If you are using TCP/IP, select the standard port number of
1521 and click Next.
- Select No when prompted to configure another Listener and click Next. Listener
configuration is complete.
- Now proceed to net service naming configuration. Select Naming Methods configuration
and click Next.
- Select Local as the net service naming method
(to understand why, see the next step) and click Next. Naming methods configuration
is complete.
- Select Local Net Service Name configuration (to enable you to use a local
name for the eProcess
server to access the Oracle database using net service) and click Next.
- Select Add (to specify you want to add a local name) and click Next.
- Select the version of the Oracle DBMS (Oracle 8i) and click Next. Then enter
the Oracle database System Identifier (SID), for example, IDB, into the Database
SID field.
- Select the network protocol to use to access the Oracle database (for example,
TCP) and click Next.
- Enter the eProcess
server host name (for example, eprocess_svr) into
the Host Name field. If you are using TCP/IP, select the standard port number
of 1521 and click Next.
- Select No, do not test, to specify that you don't want to test the database
connection at this time (you will do so later in the procedure) and click
Next.
- Enter the net service name (for example, IDBGlobal) into the Net Service
Name field and click Next.
- Select No when prompted to configure another net service name and click
Next. Net service name configuration is complete. Click Finish to complete
Oracle network setup and exit the Net Configuration Assistant.
- Type netasst at the command line to configure the Oracle network
service you just created. The Oracle Net8 Assistant displays.
- In the Explorer-like pane, under Net8 Configuration, select Local > Listeners
> LISTENER. In the main pane, select Database Services. Edit the fields
using the entries you specified while creating the net service name (for example,
IDBGlobal in the Global Database Name field, IDB
in the SID field). Specify the Oracle root directory (for example, /usr/ora/817)
in the Oracle Home Directory field. Then select Save from the File menu to
save the network configuration.
- To verify that the Listener has been successfully created, type Isnrctl
at the command line. Then type status. Verify that the SID you specified
(for example, IDB) is listed as a Listener. If
yes, go to the next step. If not, type stop, then type exit.
Restart the Listener (by typing start and pressing Enter). Type Isnrctl
at the command line, then type status to verify again that the SID
specified is listed as a Listener.
- You can verify whether the connection to the Oracle database is working
using the Oracle Net8 Assistant or manually (see next step). To verify that
the Oracle database connection is working using the Net8 Assistant, in the
Explorer-like pane, under Net8 Configuration, select Local > Service Naming,
then select your net service name (for example, IDBGlobal).
To test the connection, select Test Service from the Command menu.
If successful, close the Connection Test dialog and exit the Net8 Assistant.
- If the test is not successful, the Connection Test dialog displays a
message similar to:
The test did not succeed.
ORA-01017: Invalid username/password; logon denied
There may be an error in the fields entered,
or the server may not be ready for a connection.
If the test is not successful, click the Change Login button. In the
dialog, change the login to username=system and password=manager. Click
OK and rerun the test by click the Test button on the Connection Test
dialog.
- To manually test whether the connection to the Oracle database is working,
type sqlplus system/manager@idbglobal at the command line (where idbglobal
is the SID name). This starts SQL *Plus. Perform a simple SQL query. For example,
specify: show parameters db_name to verify that you are connecting
to the correct database.
Back to top
Configuring the Client to Connect to
the Database (Oracle Only)
This step describes how to configure an Oracle client to connect to the eProcess
database. If you are using a SQL Server database, skip to the step, Specifying
an ADO Connection.
This procedure assumes that you have already installed the Oracle 8i client
on a Windows machine. Each client that is to use ADO
eProcess queries must be
individually configured.
Caution The database client and the database server cannot be on the
same machine.
Note Oracle 8i is the only Oracle client currently supported. If you
install the Oracle 8i client on a Windows 2000 machine, be sure to run the Oracle
8i patches that allow the Oracle Installer to run on Windows 2000 before you
start Oracle 8i client installation.
- Click on Start and select Programs > Oracle - OraHome81 to start the
Oracle client.
- Select Network Administration > Launch Net8 Configuration Assistant to
bring up the Oracle Net(work) Configuration Assistant.
- Using the Net Configuration Assistant, configure the net service on the
client by configuring (in the order shown if you make a mistake, the
Assistant allows you to backtrack):
- Naming methods configuration
- Local net service name configuration
Start by selecting Naming Methods Configuration and click Next.
- Select Local as the net service naming method
and click Next. Naming methods configuration is complete.
- Select Local Net Service Name configuration (to enable you to use a local
name for the eProcess server to access the Oracle database using net service)
and click Next.
- Select Add (to specify you want to add a local name) and click Next.
- Select the version of the Oracle DBMS (Oracle 8i) and click Next, then enter
the Oracle database System Identifier (SID), for example, IDB, into the Database
SID field.
- Select the network protocol to use to access the Oracle database (for example,
TCP) and click Next.
- Enter the eProcess server host name (for example, eprocess_svr)
into the Host Name field. If you are using TCP/IP, select the standard port
number of 1521 and click Next.
- Select Yes, perform a test, and click Next, to have the Net8 Configuration
Assistant test whether database connection is working. (You can also test
the connection manually see step 13.)
If the test is successful, the Connection Test dialog displays a message
similar to:
Connecting...Test successful.
If successful, click Next.
- If the test is not successful, the Connection Test dialog displays a message
similar to:
Connecting...ORA-01017: Invalid username/password;
logon denied
The test did not succeed.
Some of the information you provided may be incorrect.
Press Back to review the information provided for net service
name, or Change Login to change username.
If the test is not successful, click the Change Login button. In the dialog,
change the login to username=system
and password=manager and
click OK to rerun the test.
- Enter the net service name (for example, IDBGlobal) into the Net Service
Name field and click Next.
- Select No when prompted to configure another net service name and click
Next. Net service name configuration is complete. Click Finish to complete
Oracle network setup and exit the Net Configuration Assistant.
- To manually test whether the connection to the Oracle database is working,
type sqlplus system/manager@idbglobal at the command line (where idbglobal
is the SID name). This starts SQL *Plus. Perform a simple SQL query. For example,
specify: show parameters db_name to verify that you are connecting
to the correct database.
Alternatively, you can also verify that the database connection is working
by using the SQL *PLUS utility from the Start menu and entering idbglobal
as the Host String (where idbglobal is the SID name).
Back to top
Configuring an ADO Connection on
the Client
This step describes how to configure an ODBC data source on the client for
ADO eProcess queries (assumes a Windows client platform). Each client that is
to use ADO eProcess queries must be individually configured to specify the driver,
the Data Source Name (DSN) and the net service name you created previously.
The procedure varies, depending upon whether you are configuring for either
an Oracle or a MS SQL Server database, as follows:
Specifying ADO ODBC for Oracle
To set up an ADO ODBC connection for an Oracle eProcess database, use the following
procedure (the procedure varies somewhat, depending upon whether you use the
Oracle ODBC driver or the Microsoft ODBC for Oracle driver):
- Click on Start and select Settings > Control Panel > Administrative
Tools. Double-click on Data Sources (ODBC) to open the ODBC Data Source Administrator
dialog. Click on the System DSN tab, then click the Add button.
- Select either of the following drivers for Oracle (both are supported) in
the Create New Data Source dialog.
- Oracle ODBC Driver if you use this driver, go to the next step
(Step 3).
- Microsoft ODBC for Oracle if you use this driver, go to Step
4.
Then click Finish.
- On the Oracle8 ODBC Driver Setup dialog:
- In the Data Source Name field, enter the DSN (for example, eprocess_svr_dsn).
- In the Description field, enter an appropriate description (for example,
eProcess
data source name).
- In the Service Name field, enter the net service name you created previously
(for example, IDBGlobal). You must use the same net service name that
you configured for this client.
- You may leave the UserID field blank or enter a userid, as is appropriate.
- Check Application Options as appropriate.
Once you have specified the configuration, click OK. You are returned to
the ODBC Data Source Administrator dialog, where you should see the new
DSN you just configured. Click OK. ODBC setup using the Oracle ODBC driver
is complete.
- On the Microsoft ODBC for Oracle Setup dialog:
- In the Data Source Name field, enter the DSN (for example, process_svr_dsn).
- In the Description field, enter an appropriate description (for example,
eProcess
data source name).
- You may leave the UserID field blank or enter a userid, as is appropriate.
- In the Server field, enter the net service name you created previously
(for example, IDBGlobal). You must use the same net service name that
you configured for this client.
Once you have specified the configuration, click OK. You are returned to
the ODBC Data Source Administrator dialog, where you should see the new
DSN you just configured. Click OK. ODBC setup using the Microsoft ODBC driver
for Oracle is complete.
Specifying ADO ODBC for MS SQL
Server
To set up and test an ADO ODBC connection for a SQL Server eProcess database,
use the following procedure:
Note If you are using SQL Server as the DBMS for the WorkFlo server,
no additional configuration is needed on the server.
- Click on Start and select Settings > Control Panel > Administrative
Tools. Double-click on Data Sources (ODBC) to open the ODBC Data Source Administrator
dialog. Click on the System DSN tab, then click the Add button.
- Select the SQL Server in the Create New Data Source dialog and click Finish.
- On the Create a New Data Source to SQL Server dialog:
- In the Name field, enter the DSN (for example, eprocess_svr_dsn).
- In the Description field, enter an appropriate description (for example,
eProcess data source name).
- In the Server field (in response to the question: Which
SQL Server do you want to connect to?), enter the net service name
you created previously (for example, IDBGlobal). You must use the same
net service name that you configured for this client.
- Click Next.
- Specify the authentication method. If you specify using: With
SQL Server authentication using a login ID and password entered by the
user, then:
- Check the Connect to SQL Server to obtain default settings for the
additional configuration options.
- Specify the DSN login ID and password.
- Click Next.
- Check the Change the default database to
checkbox and enter enter the database name as the default (for example,
appsdb). It is recommended that you also check the Use
ANSI quoted identifiers and Use ANSI nulls,
paddings and warnings checkboxes. Click Next.
- The default SQL Server system messages is English.
- Check the Perform translation for character data checkbox.
- The default query log file is C:\QUERY.LOG and the default Long query
time is 30,000 milliseconds.
- The default ODBC driver statistics log file is: C:\STATS.LOG.
- Click Finish to complete setup.
- The ODBC Microsoft SQL Server Setup dialog displays the SQL Server ODBC
driver version and the settings you specified for the data source you just
created.
- To test the connection, click Test Data Source. If the test is successful,
the following message is displayed:
TESTS COMPLETED SUCCESSFULLY!
Click OK.
- You are returned to the ODBC Data Source Administrator dialog, where you
should see the new DSN you just configured. Click OK. ODBC setup for Microsoft
SQL Server is complete.
Back to top
Identifying Queues, Rosters, and Logs
In order to query a database view, you will need to identify the database tables
associated with the queues, rosters and logs within a given isolated region.
To identify the appropriate table views:
Back to top
Specifying the Query
You can perform an ADO query by specifying a SQL query in your application.
The SQL SELECT statement should reference the database views (of a queue, roster,
or log) rather than the physical database tables. The database views were identified
in the previous step, Identifying Queues,
Rosters, and Logs.
When selecting from VW database views, it is normally necessary to use quotation
marks around column names (the column names in the view definition are often
defined using quoted identifiers). This is necessary because a user-defined
column may actually be a SQL reserved word (e.g., "SELECT" or "FROM").
Or the user-definied column name may not be unique unless the name is defined
in a case-sensitive way (i.e., "name" and "Name"). Quoting
a column name in a SQL query indicates to the DBMS that the quoted name is a
column name, even if it happens to be an otherwise Reserved word. It also indicates
to the DBMS that the name is case-sensitive.
When the view being queried is for a queue or roster, you can omit the quotation
marks around the VW Server field names (the ones that begin with "F_").
However, all user-defined column names should be quoted.
For example:
select F_WobNum, F_WorkSpaceId, "userfieldname1",
"userfieldname2" from f_sw.VWVQ94_testmodswp;
Note that f_sw identifies the
eProcess database.
When the view being queried is for a log, all column names should be quoted,
including the VW Server field names. For example:
select "F_WobNum", "F_WorkSpaceId",
"userfieldname1", "userfieldname2" from f_sw.vwvl94_testparentlog;
For an additional example of how the query is used, see ADO
eProcess Query Example Code below.
Back to top
ADO
eProcess Query Example Code
The Visual Basic code fragment example below illustrates the use of the ADO
query based on database views created on
eProcess tables. In this example, the
ADO Connection object, ADODB.Connection, is set and the SQL SELECT statement
queries for all records for the Inbox queue of isolated region 262.
Private Sub Form_Load()
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' % Setup the System DSN, UserID, Password
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
dsn = "eprocess_svr_dsn"
dbuser = "f_maint"
dbpassword = "f_maint"
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' % Create the Connection Object and open it
' % with the supplied parameters
' % System DSN, UserID, Password
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Set connDB = CreateObject("ADODB.Connection")
connDB.Open dsn, dbuser, dbpassword
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' % Build the SQL Statement and assign it
' % to the variable SQLStatement. Execute
' % the SQL statement
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
SQLStatement = "SELECT * FROM f_sw.VWVQ262_Inbox"
Set rs = connDB.Execute(SQLStatement)
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' % Loop through Fields Names and print out the Field Names
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
For i = 0 To rs.Fields.Count - 1
List2.AddItem (rs(i).Name)
Next
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' % Loop through rows, displaying each field
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Do While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
List1.AddItem (rs(i))
Next
List1.AddItem ("============================")
rs.MoveNext
Loop
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' % Make sure to close the Result Set and the Connection object
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
rs.Close
connDB.Close
End Sub
Back to top