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:

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:

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:

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:

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.

  1. Configuring the DBMS server (Oracle only).
  2. Configuring the client to connect to the database (Oracle only).
  3. Configuring an ADO connection (Oracle and MS SQL Server)
  4. Identifying the queues, rosters, and logs (and their associated database views).
  5. 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:

  1. Logon to the Oracle DBMS server as the fnsw user.
  2. cd to $ORACLE_HOME. Verify that you are logged on as the fnsw user.
  3. Type netca at the command line to bring up the Oracle Net(work) Configuration Assistant.
  4. 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):

    Start by selecting Listener Configuration and click Next.

  5. Select Add (to add a Listener) and click Next.
  6. Enter the Listener name (for example, LISTENER) into the Listener Name field and click Next.
  7. 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.
  8. Select No when prompted to configure another Listener and click Next. Listener configuration is complete.
  9. Now proceed to net service naming configuration. Select Naming Methods configuration and click Next.
  10. Select Local as the net service naming method (to understand why, see the next step) and click Next. Naming methods configuration is complete.
  11. 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.
  12. Select Add (to specify you want to add a local name) and click Next.
  13. 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.
  14. Select the network protocol to use to access the Oracle database (for example, TCP) and click Next.
  15. 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.
  16. 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.
  17. Enter the net service name (for example, IDBGlobal) into the Net Service Name field and click Next.
  18. 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.
  19. Type netasst at the command line to configure the Oracle network service you just created. The Oracle Net8 Assistant displays.
  20. 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.
  21. 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.
  22. 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.

    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.

  23. 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.

  1. Click on Start and select Programs > Oracle - OraHome81 to start the Oracle client.
  2. Select Network Administration > Launch Net8 Configuration Assistant to bring up the Oracle Net(work) Configuration Assistant.
  3. 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):

    Start by selecting Naming Methods Configuration and click Next.

  4. Select Local as the net service naming method and click Next. Naming methods configuration is complete.
  5. 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.
  6. Select Add (to specify you want to add a local name) and click Next.
  7. 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.
  8. Select the network protocol to use to access the Oracle database (for example, TCP) and click Next.
  9. 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.
  10. 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:

  11. Connecting...Test successful.

    If successful, click Next.

    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.

  12. Enter the net service name (for example, IDBGlobal) into the Net Service Name field and click Next.
  13. 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.
  14. 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):

  1. 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.
  2. Select either of the following drivers for Oracle (both are supported) in the Create New Data Source dialog.

    Then click Finish.

  3. On the Oracle8 ODBC Driver Setup dialog:

    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.

  4. On the Microsoft ODBC for Oracle Setup dialog:

    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.

  1. 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.
  2. Select the SQL Server in the Create New Data Source dialog and click Finish.
  3. On the Create a New Data Source to SQL Server dialog:
  4. 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.
  5. To test the connection, click Test Data Source. If the test is successful, the following message is displayed:

    TESTS COMPLETED SUCCESSFULLY!

    Click OK.

  6. 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