Encapsulating Your ASP Page: One Common Method for Database Access to SQL Server and Oracle

A primary definition of good object-oriented design is to promote true encapsulation of objects. Within the confines of an application dedicated for multi-user sites, this requirement becomes much more than a theoretical requirement, and can be the "make-or-break" difference in whether you are able to reach a customer.

When our application reached a point where it was obvious that we needed to support ActiveX Data Objects(ADO) access through ASP to both an ORACLE as well as an SQL Server database, we worked to determine the best method to do this. In case you are not familiar with ADO, here is an article that should bring you up to speed: http://www.microsoft.com/accessdev/articles/movs202.htm. We realized that how we set up an ADO access would probably be the same setup that we employ to provide access to other future databases. All of our ASP pages were initially written for SQL Server database and relied on ADO code processes to access stored procedures. Immediately we recognized a need to provide the same advantages of stored procedures and began our conversion process. We also realized that in a few cases existing stored procedures utilized techniques unavailable through Oracle, such as returning multiple recordsets, and reliance on specific data types to be returned for calculations. Luckily, these problems were atypical. The most formidable challenge was that ADO access to Oracle required the addition of code for defining and appending parameter information to the procedure call on EACH ASP page. Oracle requires a call to the stored procedure in a format somewhat like the following:

cmdStoredProc.CommandText = "{call package_name.procedure_name(?,..,n,
{resultset max#_recs_returned,
                output_parm1, output_parm2, etc. })}"
cmdStoredProc.CommandType = adCmdText
cmdStoredProc.Parameters.Append
cmdStoredProc.CreateParameter("input_parm1",adInteger,adParamInput,1,1)
                                                          :
cmdStoredProc.Parameters.Append
cmdStoredProc.CreateParameter("input_parmn",adInteger,adParamInput,1,1)

In the latter case, the worse case scenario suggested we might be required to maintain a separate set of pages and/or code if the stored procedures changed. To offset this we opted to define a process that would encapsulate the ASP pages from Database access code to ensure the ease of future accessibility.

To address the multiple problems associated with each, we came up with the following solution.

Use XML to define database requirements

Our final resolution relied on the use of XML (XMLDOM) to define and derive the database characteristics of the current catalog and a common VB solution for inclusion on each page to build the ASP ADO parameter lines if the XML warranted it. Our XML database definition looked something like the following (example shows Oracle definition):

Example 1:

<Database>
	<DataType>Oracle</DataType>
	<DataSource>my_datasource</DataSource>
	<initial_catalog>my_catalog</initial_catalog>
	<UserID>my_userID</UserID>
	<Password>my_password</Password>
</Database>

Upon initial entry, we relied on the normal server methods of creating the XMLDOM object and then map to the XML path. Connection to the database was then done as the DataType entry value required. The requirement information could then be stored in session variables as needed or, if desired, access to the XML data could even be cached. Our own version was included on each page so that entry did not have to be reliant on a specific access path.

The use of XML fulfilled our basic requirement of a flexible method of defining your database and access requirements.

Building a Universal VB function to Access both Databases

Our next challenge was to determine how to address the problem of having to modify each ASP page for the additional lines that Oracle access required. We certainly did not want to maintain a separate copy of each page with lines dedicated to database access, and we also chose not to opt to include asps for each access type. We ultimately decided to replace all database access code with a single call to a VB function that required the name of the procedure and the output parameters.

Example 2:

myparms="<x>"+xml_para(parm1)+
xml_para(parm2)+xml_para(parm3)+xml_para(parm4) +"</x>"
set rs=common_function("my_procedure_name",myparms)

The VB function had to be able to handle the specifics of building the structure of the call for either SQL Server or Oracle. We decided to build the parameters to the function call as XML as noted in the example above. If the Database Definition XML indicated 'SQL Server', we could set the CommandType = 4 (adCmdStoredProc), and build the CommandText to equate to the full procedure name followed by a Parameters.Refresh to gain accessibility to the parameters from the catalog procedures. Then after loading XML input, we utilized XMLDOM techniques to append the Parameters as passed into the function. The example below gives the general procedural outline. This example can also be modified to utilize caching of parameter information if the programmer elects to do so.

Example 3: Building SQL Server Procedure calls

cmd.CommandType =4
   cmd.CommandText="my_procedure_name"
   cmd.Parameters.Refresh
   bSucc = oXmlDom.loadXML(parmx)
   :
   'handle false response code
   :
   Set xy = oXmlDom.selectNodes("//para")
   For ct = 1 to cmd.Parameters.count - 1
                cmd.Paramters(ct).Value = xy(ct-1).text
   Next

Constructing the call to the Oracle database was more formidable since "Parameters.Refresh" is not an option. After examining several alternatives, we opted to build our parameters in VB employing the use of a stored procedure to return vital information from the USER_ARGUMENTS table and incorporating returned data into functions that would construct the remaining code required. Our stored procedure looked something like the following:

Example 4: A General procedure to Retrieve Oracle Procedure parameter information

CREATE OR REPLACE PACKAGE BODY mypackage
AS
PROCEDURE myproc_getArgs
      (i_Procedure IN VARCHAR2,
       ARG_COL OUT tvarchar30,
       ARG_TYPE OUT tvarchar9,
       ARG_DEF OUT tvarchar30,
       ARG_USER_DEF OUT tvarchar30,
       ARG_SEQ OUT tnumber12,
       ARG_PKG OUT tvarchar30,
       ARG_PROC OUT tvarchar30)
  IS
  CURSOR argcur is
     SELECT ARGUMENT_NAME AS ARG_COL,
            IN_OUT                     AS ARG_TYPE,
            DATA_TYPE            AS ARG_DEF,
            TYPE_SUBNAME    AS ARG_USER_DEF,
            SEQUENCE              AS ARG_SEQ,
            PACKAGE_NAME  AS ARG_PKG,
            OBJECT_NAME     AS ARG_PROC
       FROM USER_ARGUMENTS
      WHERE OBJECT_NAME = UPPER(i_Procedure)AND ARGUMENT_NAME IS NOT NULL
      ORDER BY IN_OUT, SEQUENCE;
      argct NUMBER DEFAULT 1;
BEGIN
     FOR d1 IN argcur LOOP
        ARG_COL(argct) := d1.ARG_COL;
        ARG_TYPE(argct) := d1.ARG_TYPE;
        ARG_DEF(argct) := d1.ARG_DEF;
        ARG_USER_DEF(argct) := d1.ARG_USER_DEF;
        ARG_SEQ(argct) := d1.ARG_SEQ;
        ARG_PKG(argct) := d1.ARG_PKG;
        ARG_PROC(argct) := d1.ARG_PROC;
                         argct := argct+1;
     END LOOP;
     END myproc_GetArgs;
END mypackage;
/

If your site uses the same procedure names across multiple packages then you would want to modify the above procedure to test for a valid package name as well.

Using the above procedure to build the calls to the procedure was not too difficult after examining and thoroughly determining a valid cross-reference for our release of Oracle between Data types. Our version of logic returned an array of strings to the caller, first traversing the recordset returned through the input parms to collect the number of "?" to be displayed in the call statement for each input parm so that when the output parms are read, they are appended to the same string. This string was built in the first array element. While the input parms were being read, a data type determination was made to pass back to the calling routine as well for each so that the proper format of the append statement could be realized. This information was passed in succeeding elements of the array. The example below is a loosely formulated version of our processing function. It does not process input-output parameter types for simplicity sake.