ODBC Connection Pooling

ODBC connection pooling is necessary for scaling sites. As stated earlier, IIS provides ODBC connection pooling automatically, based on a registry setting, but this pooling is not done when you call an out-of-process component—you have to provide the pooling as a separate feature. Fortunately, MTS includes ODBC connection pooling for modified Visual Basic code that supports it.

When creating Visual Basic applications, most developers don't concern themselves with ODBC connection pooling. Front-end components routinely open connections at the beginning of a session and leave them open until the application is terminated. Under MTS, ODBC connections are not opened by the front end or by the Web page but rather by the business object itself. Therefore, the ActiveX DLLs you create in Visual Basic must open a connection, read or write to the data source, and then close the connection. The Web page never accesses the database directly.

You might wonder how the data could appear on the page if the Web page itself never has direct access to a recordset. A Web page calling MTS objects relies on the objects to return data from a data source in a predictable format. This format can be as simple as a delimited string or as complex as a collection of objects. In any case, the business object is responsible for selecting records from the database, packaging them, and returning them to ASP.

As a simple example, Listing 10-1 shows the complete code for a Visual Basic business object that reads entries from a data source and returns them as a delimited string. The ASP page parses the returned string and places the entries in a <SELECT> tag. This is a typical technique for creating lookup lists.

Listing 10-1. Returning a lookup list.


 Public Function GetPublishers() As String On Error GoTo GetPublishersErr     GetPublishers = ""     ' Open connection     Dim objConnection As RDO.rdoConnection     Dim objResultset As RDO.rdoResultset          Set objConnection = New RDO.rdoConnection     objConnection.Connect = "DSN=Biblio"     objConnection.EstablishConnection rdDriverNoPrompt, True          ' Run query     Set objResultset = objConnection.OpenResultset _         ("SELECT Name FROM Publishers ORDER BY Name")          ' Create return string     Dim strTemp As String     strTemp = ""          Do While Not objResultset.EOF         strTemp = strTemp & objResultset!Name & "|"         objResultset.MoveNext     Loop     GetPublishers = strTemp     ' Close connection     objResultset.Close     objConnection.Close     Set objResultset = Nothing     Set objConnection = Nothing      GetPublishersExit:     Exit Function GetPublishersErr:     GetPublishers = Err.Description     Resume GetPublishersExit End Function 

In the example, the business object is an ActiveX DLL with one method, GetPublishers. The object uses Remote Data Objects (RDO) to access an ODBC data source created from the BIBLIO.MDB database that ships with Visual Basic. The component opens a connection to the data source and reads out all publisher names. The names are added to a string that is delimited by pipe characters (|). The component is compiled and placed in MTS Explorer. Note how the object opens a connection at the beginning of the function and closes the connection before returning the string of publishers. This coding style is required by MTS to allow ODBC connection pooling.

The string is returned to an ASP page through a call to the business object. Listing 10-2 shows the complete Web page code for building a lookup list. The publishers are parsed and added as entries to a <SELECT> tag. Other applications, such as Visual Basic front ends, can use the same object to fill a lookup list as well.

Listing 10-2. Building a lookup list.


 <%@ LANGUAGE="VBSCRIPT" %> <HTML> <HEAD> <META NAME="GENERATOR" Content="Microsoft Visual InterDev 1.0"> <META HTTP-EQUIV="Content-Type" content="text/html;      charset=iso-8859-1"> <TITLE>Document Title</TITLE> </HEAD> <BODY> <%     ' Variables     Dim objQuery     ' Create the business object     Set objQuery = Server.CreateObject("QueryPublishers.List")     ' Run the query     Dim strReturn     strReturn = objQuery.GetPublishers          ' Fill the list box     Dim intStart     Dim intCurrent          intStart = 1     intCurrent = 1          ' Parse the returned string %>          <FORM>     <SELECT SIZE=20> <%     Do While intCurrent < Len(strReturn)         intCurrent = InStr(intStart, strReturn, "|")         If intCurrent = 0 Then Exit Do %>         <OPTION>         <%=Mid(strReturn, intStart, intCurrent - intStart)%>         </OPTION> <%         intStart = intCurrent + 1     Loop %>     </SELECT>     </FORM> </BODY> </HTML> 

As your site gets more complicated, you will want to return more than a simple list from a lookup table—you will want complete sets of records. This example uses an array to retrieve records and return them to the Web page. Listing 10-3 shows the code for a method that reads the BIBLIO.MDB data source and returns information about the authors contained in the database. The information is packed as entries in an array and returned to the client Web page.

The GetRows method of the Recordset object is specifically designed to create a Variant array from a Recordset. This array can be returned to the client, where it is unpacked and displayed.

Listing 10-3. Returning a set of records.


 Public Function GetAuthors(ByRef ReturnData As Variant) As Boolean On Error GoTo GetAuthorsErr     GetAuthors = False          ' Open connection     Dim objConnection As RDO.rdoConnection     Dim objResultset As RDO.rdoResultset          Set objConnection = New RDO.rdoConnection     objConnection.Connect = "DSN=Biblio"     objConnection.EstablishConnection rdDriverNoPrompt, True          ' Run query     Set objResultset = objConnection.OpenResultset _         ("SELECT Author, [Year Born] FROM Authors ORDER BY Author", _         rdOpenStatic)          ' Evaluate the query results     If objResultset.BOF And objResultset.EOF Then         Err.Raise vbObjectError, "QueryAuthors", "No Records Returned!"     End If          ' Fill the array     ReturnData = objResultset.GetRows(200)     GetAuthors = True     ' Close connection     objResultset.Close     objConnection.Close     Set objResultset = Nothing     Set objConnection = Nothing          GetAuthorsExit:     Exit Function GetAuthorsErr:     GetAuthors = False     ReDim ReturnData(0 To 1, 1 To 1)     ReturnData(0, 1) = Err.Description     ReturnData(1, 1) = Err.Source     Resume GetAuthorsExit End Function 

When the array arrives in the Web page, ASP reads all the entries and builds an HTML table with the data. In this way, a complete set of data can be returned without having direct access to an ODBC connection in the Web page. The page can also write data back by calling methods that perform SQL Update functions inside of Visual Basic business objects. Listing 10-4 shows the complete code for building a table of records.

Listing 10-4. Building a table of records.


 <%@ LANGUAGE="VBSCRIPT" %> <HTML> <HEAD> <META NAME="GENERATOR" Content="Microsoft Visual InterDev 1.0"> <META HTTP-EQUIV="Content-Type" content="text/html;      charset=iso-8859-1"> <TITLE>Document Title</TITLE> </HEAD> <BODY> <%     ' Create business object     Dim blnReturn     Dim objAuthors     Dim varReturn     Set objAuthors = Server.CreateObject("QueryAuthors.Table")     ' Run query     blnReturn = objAuthors.GetAuthors(varReturn) %> <H2>Here are the authors!</H2> <TABLE BORDER> <% ' Fill the grid with first 10 rows     For i = 0 To 9 %>     <TR>         <TD>         <%=varReturn(0, i)%>         </TD>         <TD>         <%=varReturn(1, i)%>         </TD>     </TR> <%     Next %> </TABLE> </BODY> </HTML> 

Add, edit, and delete functionality can be provided through objects under MTS. The objects are subsequently available, without rewriting, to all applications on the enterprise.



Programming Active Server Pages
Programming Active Server Pages (Microsoft Programming Series)
ISBN: 1572317000
EAN: 2147483647
Year: 1996
Pages: 84

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net