[thelist] Friday Freebie

Scott Dexter sgd at ti3.com
Fri Aug 11 12:21:04 CDT 2000


Let's see, what shall I write about today?

oh, this is a gooder:

<tip type="ASP, minimizing DB object use">
You've been through the WROX book(s). You've glanced through the O'Reilly
book and you've soaked in the MS docs. You're ready to do some databasing!
So what's with having to create three objects just to get some records out
of one table?? All that parameter setup crap to execute a stored procedure??

There is a better way, both performance and sanity wise: The Connection
object is really all you need. Seriously.

Set up your connection:
<%
	Set oConn = Server.CreateObject("ADODB.CONNECTION")
	oConn.Open "DSN=MySQLServer;UID=NewsUser;PWD=yomamma;Database=News"
%>

And now do what you want through the Execute method:
Grab some records; the recordset object is created implicitly, with no
performance penalty
<%
	Set oRS = oConn.Execute("Select title,body from tbArticles where
art_id="&articleid)
%>

Update a record. Don't you *DARE* use the recordset object to update or
insert data
<%
	oConn.Execute "Update tbArticles set title='"&newtitle&"' where
art_id="&articleid
%>

-- Even fire off a stored procedure
<%
	oConn.Execute "Exec procUpdateArticle @newtitle='"&newtitle&"',
@artid="&articleid
%>
 
How about a stored procedure that returns a recordset?
<%
	Set oRS = oConn.Execute("Exec procReturnUpdatedArticle
@newtitle='"&newtitle&"', @artid="&articleid)
%>

Considering you also start/commit/roll back transactions through the
connection object, its a one-stop shop to talk to your db. Look, Ma, less
code I have to write!

Ignoring the ADODB.Command object will work for probably 99.9% of your ASP
career. There are some advantages to using it, and the only ones I've found
notable are when using stored procedures that have output parameters (and I
work around that by returning a recordset instead), and having the query
cached for reuse later on the page (which I work around by doing an
oRS.GetRows, which loads the entire recordset into a two dimensional array).

To make things even easier on your fingers, try these two functions to help
you set up and tear down your db connections:

<%
Function SetupDB(byval connstr)
Dim DBCON
Set DBCON = Server.CreateObject("ADODB.Connection")
DBCON.ConnectionTimeout = 15
DBCON.CommandTimeout = 60
DBCON.Open connstr
Set SetupSQL = DBCON
Set DBCON = Nothing
End Function

Sub CloseDB(byref DBCON)
DBCON.Close
Set DBCON = Nothing
end sub
%>

So to set up a connection, I'd say:
<%
Set oConn =
SetupDB("DSN=MySQLServer;UID=readwriteuser;PWD=yomamma;Database=Articles")
%>

And to kill it:
<%
CloseDB oConn
%>

So get those extra Server.CreateObject calls outta your head. Ya really
don't need 'em. Your web server's CPU(s) will thank you, and so will your
clients who thought all db driven sites were slow.

</tip>
sgd
--
think safely




More information about the thelist mailing list