ODBC

The ODBC cartridge allows applications to access data from other data sources using the Open Database Connect (ODBC) standard. Currently, Oracle supports access to Sybase, Informix, and Microsoft SQL Server, in addition to the Oracle RDBMS through the ODBC cartridge. As part of the configuration of the cartridge, you must specify which databases you want to access, and include some specific instructions to inform the cartridge how to access the particular database you want to include. These instructions are stored in the initialization file (.odbc.ini), which the cartridge refers to when it is invoked.

When invoking the cartridge, you have three “request modes” to choose from: Execute, Tableprint, and Stringprint. The differences have to do with how data is returned to the browser:

•  Execute mode: This mode is good for INSERTs, UPDATEs, and DELETEs. It returns a success or failure message, but no data.

•  Tableprint mode: This mode is good for SELECT statements whose output is rather standard. It returns the results of the SELECT statement in the form of an HTML table.

•  Stringprint mode: This mode is good for SELECT statements whose output requires special formatting. It enables you to integrate the output of the SELECT statement with fixed text, including hyperlinks and other HTML tags.

Each mode is chosen at invocation. For example, consider the following URL:

http://www.corbinian.com:87/odbc/stringprint?database=ORACLE7&dsn=

demo&username=scott&password=tiger&sql=’SELECT+name,state+FROM+contacts&

outputstring=%1+lives+in+%2.<br>

When the WRB receives this incoming request via the Web Listener, it sees the odbc string that follows the corbinian.com host name and 87 port and compares its mapping to see how to route this request. Assuming odbc is mapped to the ODBC cartridge in the WRB configuration, it determines that this is intended for the ODBC cartridge, and routes the request to the cartridge.

When the ODBC cartridge receives the request, it sees that the request is for stringprint mode, for the database that its configuration file maps to the ORACLE7 parameter, the demo instance, and logging in as username scott, password tiger. The SQL statement to execute is:

SELECT name, state

FROM  customers;

Finally, the output is returned as follows:

     JOE SMITH lives in VIRGINIA.<br>

     ALICE JOHNSON lives in MINNESOTA.<br>

     HOWARD JONES lives in FLORIDA.<br>

     (…etc…)

(Note: <br> is the HTML tag for a carriage return.)

To combine the flexibility of the stringprint mode with tables (which are built in to tableprint), you must invoke the ODBC cartridge from within another cartridge, and use the other cartridge to do the more specific custom formatting, HTML tags, and so on. This is, in fact, the real intent behind the ODBC cartridge: not to be used on its own, but rather to be incorporated into calls from other cartridges.

There are some security considerations with the ODBC cartridge. Since calls to the cartridge include the sql parameter, which allows any valid DML statement to be used, then a user could theoretically build his or her own URL to invoke the cartridge, with a SQL parameter such as DELETE contacts. There are some steps you can take to prevent this, such as only allowing certain IP addresses to access the system, or turning on password security for the ODBC cartridge. If password security is used, application developers must embed the password in their URL’s that call the ODBC cartridge. Therefore, developers must take extra steps to ensure that their applications’ calls to the ODBC cartridge do not include the password in any HTML that is written to the browser. Instead, any embedded hyperlinks to executables must first refer to a procedure that, in turn, redirects the call to the ODBC cartridge. Otherwiser, a savvy user can click the View -> View Source menu option in his or her browser, and in a couple of minutes identify the password to the ODBC cartridge from the HTML code.

The cartridge can use the WRB’s Inter-Cartridge Exchange features, or the browser can access the cartridge directly.

(Note: The ODBC cartridge is only available with the advanced version of WAS 3.0.)

OCI

The Oracle Call Interface (OCI) Cartridge employs the OCI that has already been used primarily for accessing Oracle databases from applications written in C. It offers some significant improvements in performance over ODBC. From the developer’s point of view, it behaves the same as the ODBC cartridge. It uses the same execute, tableprint, and stringprint modes, but it performs much faster than the ODBC.

Other Oracle-Provided Cartridges

Oracle is committed to creating and distributing additional cartridges for the WAS. Some examples are

•  C cartridge

•  Live HTML cartridge

•  Perl cartridge

In addition to these, you can create your own custom cartridges.

Custom Cartridges

You can also create your own custom cartridges, using the WRB’s Application Programming Interface (API). Currently, custom cartridges are written in C; Oracle is expected to support other languages for cartridge creation in future releases of the WAS. Creating a custom cartridge requires a healthy understanding of cartridge architecture, more than is described here. It involves coding the cartridge, defining the various functions that WRB expects of a cartridge, and configuring the WRB to use your cartridge. While it involves some work, it’s not terribly difficult. And it enables you to customize the WAS rather extensively.