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

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

To page 1current page

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

Example 5: Simplified Logic Snippet to retrieve parameter information

(Function GetParmData)
    'Create a connection object
	Set cnnOracle = Server.CreateObject("ADODB.Connection")
	cnnOracle.CursorLocation = adUseClient
	strConn = "Provider=MSDAORA.1; Data Source=abcd;
	cnnOracle.Open strConn
    'Create a command object.
	Set cmdStoredProc = Server.CreateObject("ADODB.Command")
	Set cmdStoredProc.ActiveConnection = cnnOracle
    'Call the above procedure.
	cmdStoredProc.CommandText="{call mypackage. myproc_getArgs(?,
      {resultset 1000, " & _
      inStrg & " })}"
	cmdStoredProc.CommandType = adCmdText
               adVarChar,adParamInput,30,P rocName)
    'Create recordset object.
	Set rsXXX = Server.CreateObject("ADODB.Recordset")
	rsXXX.CursorType = adOpenStatic
	Set rsXXX.Source = cmdStoredProc
    'Traverse through the recordset-build procedure call string in  array(0)
    '                              -build parm input data in
	while Not rsXXX.EOF
        if (IsNull(rsXXX.Fields("ARG_COL"))) then
        ' do nothing
         'handle output parameters
            if (rsXXX.Fields("ARG_TYPE") = "1") then
                 if (outStrg = "") then
                    outStrg = rsXXX.Fields("ARG_COL")
                    outStrg = outStrg & "," & rsXXX.Fields("ARG_COL")
                 end if
              'handle input parameters (this assumes no in-out parms)
               outStrg2 = outStrg2 & "?,"  'builds question marks in
               the text string
               if (IsNull(rsXXX.Fields("ARG_DEF"))) then
                   w1 = 132  'adUserDefined - user defined parm
                   w2 = rsXXX.Fields("ARG_DEF")
                   w1 = ""
                   if  ((w2 = "NUMBER") or (ws = "INTEGER") or (ws =
                       w1 = 131   'adNumeric
                   end if
                   if  (w2 = "VARCHAR2") then
                      w1 = 200   'adVarChar
                   end if
                   if  (w2 = "RAW") then
                      w1 = 128   'adBinary
                   end if
                   if  (w2 = "CHAR") then
                      w1 = 129   'adChar
                   end if
                   if  (w2 = "DATE") then
                      w1 = 135  'adDBTimeStamp
                   end if
                   if  (w2 = "DECIMAL") then
                      w1 = 14   'adDecimal - 8.0.x // use 131  (adNumeric)
if 8.1x +
                   end if
                   if  (w2 = "FLOAT") then
                      w1 = 5   'adDouble
                   end if
                   if  ((w2 = "LONG RAW") or (w2 = "BLOB")) then
                      w1 = 205   'adLongVarBinary
                   end if
                   if  ((w2 = "LONG") or (w2 = "CLOB")) then
                      w1 = 201   'adLongVarChar
                   end if
                   if  (w2 = "NCLOB") then
                      w1 = 203   'adLongVarWChar
                   end if
               end if
               wkStrg = w1
               arrOutput(ict) = wkStrg
            end if
          end if
	' Close the Recordset and the Connection / dereference the ADO  Objects
	Set cmdStoredProc = nothing
	Set rsXXX = nothing
	Set cnnOracle = nothing
    if (outStrg2 = "" AND outStrg = "") then
       outStrg2=PkgName & "." & "ProcName"
       outStrg2="{call " & PkgName & "." & ProcName & "(" & outStrg2 &  "
{resultset 1000, " & outStrg & " })}"
    end if
    arrOutput(0) = outStrg2
'End of Function

The above logic is a bit clunky but illustrates well what should be done. The initial value of the array is reserved for the text statement calling the procedure itself and all subsequent values define the input parameters' data type.

Upon returning from the function call, we are now ready to build the complete text for the call:

Example 6:Returning from the call to retrieve parameter data

   Dim arrX
   Dim kt
    arrX = GetParmData(Pkg_name, Proc_name) 'call my function
    cmd.CommandType=1    'adCmdText
    cmd.CommandText = arrX(0)
    kt = "N"
    for i = 1 to 100
      if (isNumeric(arrX(i))) then
        if (CInt(arrX(i)) > 1) then
           kt = "Y"
           cmd.Parameters.Append cmd.CreateParameter(,Cint(arrX(i)),1)
           i = 200
        end if
        i = 200
      end if
   'Separate XML parms into array
     if (kt = "Y") then
	 bSucc = oXmlDom.loadXML(para)
   'handle invalid return as you choose else separate XML parms into  array
	 Set ns = oXmlDom.selectNodes("//para")
	 For i = 1 To cmd.Parameters.count - 1
	    cmd(i-1) = ns(i-1).text
     end if
     set common_function=cmd.Execute  'use the execute statement

The entire log set described in Example 5 and 6 above could be rewritten to take advantage of smoother coding preferences and to accommodate growth and release expansions but is presented as shown to illustrate the required logic. The datatype cross reference in Example 5 can be determined either by trial and error or by utilizing one of the many cross references given out on the Web. Even using the latter can be difficult and requires testing regardless.

Special cases had to be handled on a case-by-case basis, such as those where ASP pages referenced multiple recordsets. These had to be rewritten so that the ASP reflected stored procedures that produced single recordsets accommodating the commonality of both databases.

Nevertheless, the bulk of our catalog display process could easily be handled with our unique version of database access and most ASP pages required no changes other than replacing the database procedure call coding with a call, instead, to the common function.

# # #

Kathy has 25 years of solid hands on experience in all phases of Data Processing software development and design. This experience encompasses both mainframe and client/server applications with emphasis on participation as technical and or architectural advisory lead in porting mainframe applications onto a client/server base for applications ranging from banking and imaging to procurement. She is currently adjunct professor of Computer Information systems for West Virginia Northern Community College and is a long time Member of Western PA Mensa. She can be reached at dhamu@access.hky.com.

To page 1current page

Created: April 6, 2001
Revised: April 6, 2001

URL: http://webreference.com/programming/asp/database/