IDBC User's Guide

The SAP xApp Manufacturing Integration and Intelligence (SAP xMII) Database Connector (IDBC) allows the user to connect to and query any JDBC or ODBC compliant data source. Although the general syntax is the similar to other connectors, there are unique modes and parameter that are explained in this document.

Configuration

Configuration of the IDBC is very similar to configuring other connectors that utilize database access techniques to communicate with their data sources, with a few additional configuration parameters.  The following sections describe some of the unique features of the IDBC.

Configuring Specific Database Types

Oracle

The SAP xMII ships with Oracle's Type 4 JDBC drivers for direct access to a local or remote Oracle 7.X or greater database server.  The following table lists the values to use for the JDBCDriver and URL parameters when configuring an Oracle server for use with SAP xMII.

Microsoft SQL Server

The SAP xMII ships with a Type 4 JDBC drivers for direct access to a local or remote SQL Server 6.X or 7.X database server.  The following table lists the values to use for the JDBCDriver and URL parameters when configuring Microsoft SQL Server for use with SAP xMII.

ODBC Data Sources

The SAP xMII can utilize a JDBC/ODBC bridge to access a local or remote ODBC data sources such as Microsoft Access, Microsoft Excel, and specialty data sources.

Note:  It is very important to note that many ODBC drivers are prone to memory/resource leaks, crashes, and other faults.

Because these are not written in Java, they have the potential to crash the Web server and SAP xMII in extreme cases.  Thus, it is important to select your ODBC drivers carefully and test them before deploying them in a production Web server environment.

Other JDBC Drivers

If you will be using a third party JDBC driver with SAP xMII, please refer to the vendor's documentation to determine the driver class name, and be certain to add this to the servlet engine's class path.  Please contact technical support if you are at all unsure as to how to accomplish this.

Date Format Related Parameters

Many queries will require specification of date ranges.  It has historically been very difficult to write these types of queries, as each database vendor has its own concept of date/time comparisons and its own syntax for performing date range comparisons in a WHERE clause.  The SAP xMII includes a powerful infrastructure to dramatically reduce the complexity of authoring these types of queries.

In terms of specific issues related to the IDBC, there are three parameters defined when creating a server in the System Administration tool that affect this behavior.  These are the InternalDateFormat, DatePrefix, and DateSuffix parameters.  An example will help clarify the usage of these parameters.

Suppose we wish to query from March 1st at 12:00:00 Noon to March 3rd at 4:00 PM.  Perhaps the user wants the data in the format dd-MMM-yy HH:mm, and has provided StartDate and EndDate parameters in a query in this format (01-MAR-00 12:00 and 03-MAR-00 16:00).  Let us also presume that the database is Oracle, and the date column name was EventDate.  If the Oracle query processor received a query with a WHERE clause that included:

WHERE EventDate >= '01-MAR-00 12:00' AND EventDate <= '03-MAR-00 16:00'

...it would generate a syntax error and the query would not be executed.  However, if the parameters are configured as:

Typical Oracle Settings:

...the query would use the following WHERE clause, which would execute properly.

WHERE EventDate >= TO_DATE('03/01/2000 12:00:00','MM/DD/YYYY HH24:MI:SS') AND 
      EventDate <= TO_DATE('03/03/2000 16:00:00','MM/DD/YYYY HH24:MI:SS')

The key here is to provide the appropriate values for each database type.

Typical SQL Server Settings:

Typical ODBC/Microsoft Access Settings:

Capabilities

Namespace Query Modes

TableList

The TableList modes return the list of tables defined in the specified database.  The Group parameter can be used to filter the TableList query to select only system tables, user tables, or views.  The implementation is specific to each JDBC driver, but typical valid values are "TABLE", "VIEW", "SYSTEM TABLE".  The Mask parameter can also be used to filter the list of Tables returned from the query that match the specified mask pattern.  Not all JDBC drivers support the Mask parameter.

ColumnList

ColumnList returns a list of the columns available in the selected table. To select the table, the Group parameter must be used.  If no Group parameter is supplied then an error will be returned.  The Mask parameter can also be used to filter the list of columns returned from the query that match the specified mask pattern.  Not all JDBC drivers support the Mask parameter.

Data Query Modes

FixedQuery Mode

FixedQuery mode requires that the entire SQL statement be passed via the Query and QueryParams parameters.  For standard SQL queries, only the Query parameter would typically be used.  A simple SQL query string using these parameters could look like this:

Mode=FixedQuery&Query=SELECT * FROM MyTable

FixedQuery mode is also used when executing stored procedures in the database to return data.  In those cases, the Query and QueryParams might be used.  For example, if the database has a stored procedure called "sp_InventoryStatus" which takes a single parameter, the material code, a query might look like:

Mode=FixedQuery&Query=exec sp_InventoryStatus&QueryParams='ALUM001A'

The actual syntax for executing a stored procedure may vary from one database to another.

FixedQueryWithOutput

This mode is used to execute an Oracle stored procedure or function that returns a REF CURSOR as output.  The position of the REF CURSOR is marked by a "?" in the query.  For example:

1. Create a table.

create table usage (id int, name varchar(50));

insert into usage (id, name) values (1, 'test1');
insert into usage (id, name) values (2, 'test2');
insert into usage (id, name) values (3, 'test3');
insert into usage (id, name) values (4, 'test4');
insert into usage (id, name) values (5, 'test5');
insert into usage (id, name) values (6, 'test6');
insert into usage (id, name) values (7, 'test7');
insert into usage (id, name) values (8, 'test8');

2. Define the stored procedure.

DROP PACKAGE foopkg;

CREATE PACKAGE foopkg IS

  TYPE cursortype is ref cursor;

  PROCEDURE test (mycursor in out cursortype);

END foopkg;

CREATE PACKAGE BODY foopkg IS

  PROCEDURE test (mycursor in out cursortype) AS

  BEGIN

     open mycursor for select * from usage;

  END;

END foopkg;

3. Define a query template for calling the stored procedure.  Enter the following in the FixedQuery tab:

call foopkg.test(?)

This template returns all rows from the Usage table.

Query Mode

In the Query mode, the SQL query is dynamically constructed by the IDBC using the following parameters:

• Tables

• Columns

• JoinExpr

• FilterExpr

• SortExpr

• GroupingExpr

• DateColumn

Refer to the detailed Parameter Reference for a description of each of these parameters, however, an example may help clarify their usage:

The primary reason for separating the WHERE clause into the JoinExpr and FilterExpr parameters is to make it easier to dynamically change the query in Web pages at runtime, based on user input or when drilling down or cross-correlating with items selected in one of the SAP xMII Java applets.  By separating the two, the "static" elements of the WHERE clause, such as table relations and unchanging conditional expressions, can be put in the JoinExpr parameter, whereas the FilterExpr could be changed as needed via Web page scripting or by specifying the parameter in a URL.

The DateColumn parameter performs a special function, which will result in the WHERE clause automatically being extended to include a date range comparison, based on the value of the DateColumn parameter, the value of the date-range parameters (StartDate, EndDate, Duration, etc.), and the values of the InternalDateFormat, DatePrefix, and DateSuffix parameters configured for the server.

Using Date Placeholders

The SAP xMII includes a powerful and easy-to-use subsystem for selecting date/time values.  For SQL Queries, the easiest mechanism for leveraging this is to use the DateColumn parameter, as described in the previous section.  However, for some advanced or complex SQL queries, this is not possible.  In this case, the StartDate and EndDate placeholders can be used to instruct SAP xMII to dynamically insert the appropriate date/time values into the SQL query string at runtime.

The available placeholders are [SD] and [ED], corresponding to the StartDate and EndDate, respectively.  These dates are dynamically calculated by SAP xMII based on the query parameters (TimePeriod, Time, Duration, DurationUnits, StartDate, EndDate) provided.  These placeholders can be used in the JoinExpr or FilterExpr parameters for Query mode and the Query or QueryParams parameters for FixedQuery or Command mode.

As an example, suppose that we have a stored procedure called "DailyYields" which accepts three parameters, a line number, start date, and end date.  In this case, we might use FixedQuery mode and configure the following:

Query:       exec DailyYields
QueryParams: 'Line1',[SD],[ED]

For another case, suppose that we have a table called "CycleCounts" which has two columns named "RunStart" and "RunStop" indicating when the production run occurred.  In this case, we might use Query mode and configure the following:

Tables:     CycleCounts
Columns:    CountDate,Product,Counts
FilterExpr: RunStart >= [SD] AND RunStop <= [ED]

The SAP xMII will automatically recognize queries utilizing the date placeholders as "time-based" queries, and will enable the time navigation bar on applets that are used to display the data from these queries.

Using Parameter Placeholders

The IDBC provides a means for inserting parameter values into virtually any aspect of a query, using a group of parameter placeholders ranging from [Param.1] to [Param.32].  These placeholders can be used to instruct SAP xMII to dynamically insert the appropriate dynamically provided values into the SQL query string at runtime.

As an example, suppose that we have a stored procedure called "InsertManualData" which accepts four parameters, the current date, a location, and observation code, and a numeric value.  In this case, we might use Command mode and configure the following:

Query: INSERT INTO ManualDataTable Values(GetDate(),'[Param.1]','[Param.2]',[Param.3])

Note that you must explicitly provide quotes to surround string literal values passed as parameters.

In the following example, a query is passed to an alarm subsystem and the value of the alarm group of interest is passed as one parameter (in this case, Param.1) and the name of the column to sort on is passed as another.

Tables:     AlarmDetails
Columns:    AlarmTime,TagName,GroupName,AlarmCause,Severity
FilterExpr: GroupName = '[Param.1]'
SortExpr:   [Param.2]

Command Mode

The Command mode allows for the use of SQL commands that do not return data, such as UPDATE, INSERT, DELETE, or execution of a stored procedure that performs some type of data management function.  The Query parameter is used in conjunction with the Command mode to tell the IDBC what SQL command to execute.  For example, a query string could consist of:

Mode=Command&Query=INSERT INTO QCTests Values(GETDATE(),'pH','12345',5.7)

This example would insert a value into the specified table.  Command mode, of course, needs to be carefully controlled, as the potential for inadvertent database changes exists.  It is suggested that the database's underlying security subsystem be used, along with the UserID and Password used to connect to the database, to carefully limit the database permissions when using the Command mode in your applications.

On Demand Connections

You can connect to a database using unique credentials rather than connecting through a connection pool.  To use this type of connection, add ServerUserName and ServerUserPassword parameters to your applet code or to URL command line name-value pairs.  In your applet code, you can access the user name from the session via the {IllumLoginName} session variable, or you can add script to prompt the user for their user name.  You must add script to the applet to prompt the user for a password since SAP xMII does not contain session passwords.

The database connection is created for the individual request and then immediately destroyed.