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

[previous]

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;
      UserD=my_userid;Password=my_pwd"
	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
        cmdStoredProc.Parameters.Append
       cmdStoredProc.CreateParameter("i_Procedure",
               adVarChar,adParamInput,30,P rocName)
    'Create recordset object.
	Set rsXXX = Server.CreateObject("ADODB.Recordset")
	rsXXX.CursorType = adOpenStatic
	Set rsXXX.Source = cmdStoredProc
        rsXXX.Open
    'Traverse through the recordset-build procedure call string in  array(0)
    '                              -build parm input data in
array(1)...array(n)
	while Not rsXXX.EOF
        if (IsNull(rsXXX.Fields("ARG_COL"))) then
        ' do nothing
        else
         'handle output parameters
            if (rsXXX.Fields("ARG_TYPE") = "1") then
                 oct=oct+1
                 if (outStrg = "") then
                    outStrg = rsXXX.Fields("ARG_COL")
                 else
                    outStrg = outStrg & "," & rsXXX.Fields("ARG_COL")
                 end if
            else
              'handle input parameters (this assumes no in-out parms)
               ict=ict+1
               outStrg2 = outStrg2 & "?,"  'builds question marks in
               the text string
               if (IsNull(rsXXX.Fields("ARG_DEF"))) then
                   w1 = 132  'adUserDefined - user defined parm
               else
                   w2 = rsXXX.Fields("ARG_DEF")
                   w1 = ""
                   if  ((w2 = "NUMBER") or (ws = "INTEGER") or (ws =
"SMALLINT")) Then
                       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
	  rsXXX.MoveNext
	Wend
	' Close the Recordset and the Connection / dereference the ADO  Objects
	rsXXX.Close
	cnnOracle.Close
	Set cmdStoredProc = nothing
	Set rsXXX = nothing
	Set cnnOracle = nothing
    if (outStrg2 = "" AND outStrg = "") then
       outStrg2=PkgName & "." & "ProcName"
    else
       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

(common_function)
   Dim arrX
   Dim kt
    arrX = GetParmData(Pkg_name, Proc_name) 'call my function
    cmd.CommandType=1    'adCmdText
    cmd.CommandText = arrX(0)
    kt = "N"
    cmd.Parameters.Refresh
    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)
        else
           i = 200
        end if
      else
        i = 200
      end if
    Next
   '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
	 Next
     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 [email protected].

[previous]