010405.html

((((((((((((((((( WEBREFERENCE UPDATE NEWSLETTER ))))))))))))))))) April 5, 2001

_____________________________SPONSORS_____________________________

Sponsored by: Building Dynamic Web Sites Conference __________________________________________________________________

This week we're back with Kathy Pendracky, author of the previous WebReference article "Lessons Learned from a Failing Dot-Com." Kathy writes today on a topic we've all heard of, serving pages from a database. She writes on using ASP with both an Oracle database and an SQL database server.

******************************************************************

Building Dynamic Web Sites * April 25 * Chicago, IL Still building Web sites the old fashioned way with static HTML pages? This one-day crash-course April 25th in Chicago will show you how to move from a flat-file nightmare to an easy-to-manage database-driven set up. For more information and to register: https://seminars.internet.com/webdev/chicago01/index.html

**********************************************************adv.****

https://www.webreference.com *- link to us today https://www.webreference.com/new/ *- newsletter home https://www.webreference.com/new/submit.html *- submit article

New this week on WebReference.com and the Web:

1. TWO NEW CONTESTS: Submit & Win HoTMetaL Pro 6.0 and XMetaL 2.0! Signup & Win! 2. FEATURED ARTICLE: Encapsulating Your ASP Page: One Common Method for Database Access: to SQL Server and Oracle 3. NET NEWS: * Apache 2.0 beta released * Better times ahead for man, machine * Installing and Configuring Tomcat -- Part 2

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1. TWO NEW CONTESTS: Submit & Win HoTMetaL Pro 6.0 and XMetaL 2.0! Signup & Win

>Submit & Win HoTMetaL Pro 6.0 and XMetaL 2.0!

Submit your article today and you could win SoftQuad's HoTMetaL Pro 6.0 and XMetaL 2.0! If your article makes the cut, and we publish it on the site or in this newsletter, you win! See the submission page for details:

https://www.webreference.com/new/submit.html

>New Signup & Win!

Sign up for the WebReference Update newsletter, and you could win a killer software bundle from Pegasus Imaging, JPEG Wizard and PICShow. Each week we'll draw new winners from our new subscribers - you could be next. Already a subscriber? Not a problem - just fill out the form, and you'll be automatically entered to win. Tell your friends!

https://www.webreference.com/new/contest.html

This week Kathy Pendracky wins HoTMetaL Pro 6.0 and XMetaL 2.0 for her article on integrating ASP with both Oracle and SQL Server. Congratulations! Enjoy the software.

******************************************************************

Having trouble keeping up with the latest Internet product releases and updates? Let Internet Product Watch do it for you! Browse our categorized product briefings, check out our top product listings, and sign up for our free daily and weekly newsletters. Visit today, and keep up with the Web! https://internetproductwatch.com/ Free weekly newsletter! Send a blank E-Mail to: mailto:[email protected]

**********************************************************adv.**** ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 2. FEATURED ARTICLE:

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 a link that should bring you up to speed: https://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. A. 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.

B. 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.

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 am a long time Member of Western PA Mensa. She can be reached at mailto:[email protected].

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 3. NET NEWS: Apache 2.0 beta released Better times ahead for man, machine Installing and Configuring Tomcat -- Part 2

>Apache 2.0 beta released

The Apache Software Foundation announced Thursday that the first beta version of Apache Web server 2.0 in now available. https://www.geek.com/news/geeknews/2001apr/gee20010405005232.htm

>Better times ahead for man, machine

Check out all the cool high-tech gizmos that were showed-off at the latest exhibition. https://archives.seattletimes.nwsource.com/cgi-bin/texis/web/vortex/display?s lug=computers040&date=20010404

>Installing and Configuring Tomcat -- Part 2

Learn everything you need to know about the Tomcat Server, including how to install it. https://www.onjava.com/pub/a/onjava/2001/03/29/tomcat.html

That's it for this week, see you next time.

Andrew King Managing Editor, WebReference.com [email protected]

Alexander Rylance Assistant Editor, WebReference.com [email protected]

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Advertising: If you are interested in advertising in our newsletters, call Frank Fazio on 1-203-662-2997 or send email to mailto:[email protected] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ For contact information on sales offices worldwide visit https://www.internet.com/mediakit/salescontacts.html ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ For details on becoming a Commerce Partner, contact David Arganbright on 1-203-662-2858 or mailto:[email protected] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ To learn about other free newsletters offered by internet.com or to change your subscription visit https://e-newsletters.internet.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ internet.com's network of more than 150 Web sites are organized into 16 channels: Internet Technology https://internet.com/it E-Commerce/Marketing https://internet.com/marketing Web Developer https://internet.com/webdev Windows Internet Technology https://internet.com/win Linux/Open Source https://internet.com/linux Internet Resources https://internet.com/resources ISP Resources https://internet.com/isp Internet Lists https://internet.com/lists Download https://internet.com/downloads International https://internet.com/international Internet News https://internet.com/news Internet Investing https://internet.com/stocks ASP Resources https://internet.com/asp Wireless Internet https://internet.com/wireless Career Resources https://internet.com/careers EarthWeb https://www.earthweb.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ To find an answer - https://search.internet.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Looking for a job? Filling an opening? - https://jobs.internet.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ This newsletter is published by Jupitermedia Corp. https://internet.com - The Internet & IT Network Copyright (c) 2001 Jupitermedia Corp.. All rights reserved. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ For information on reprinting or linking to internet.com content: https://internet.com/corporate/permissions.html ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~