[thelist] Friday Freebie

[Scott Dexter]

<tip type="little perf kicks" author="Scott Dexter"
email="sgd at thinksafely.org">
Let's say we want to pull some information out of a database table. I know,
old hat, right?

We can squeeze some performance out by referencing the columns by their
ordinal position rather than their names. If you're mushing through a large
recordset, it amounts to quite a bit. There are some catches, though. Since
we're taking control away from VBScript, we have to be better programmers to
make sure the stuff don't break:

1) Specify the column names in the query, DO NOT use Select * --you can't
guarantee the order of the columns otherwise (and its an easy reference when
you're writing the code)
2) Cast the values when you set them to local variables --when specifying by
column name, ADO looks up the name and data type of the value for you, and
casts it appropriately when you assign it to a variable. Now that we're
taking away that lookup, *we* have to cast it (unless you like "type
mismatch" errors)
3) ADO uses zero-based numbering; the first column is column 0, the second
is column 1, etc

Example:
<%
' *** Select 0,1,2,3 from table
sqlstr="Select uid,username,email,createdate from tbUsers order by username"

' *** SetupSQL is a function we wrote, code below
Set oConn=SetupSQL("DSN=UserDB;uid=IISuser;pwd=yomama")

Set oRS = oConn.Execute(sqlstr)
if oRS.BOF and oRS.EOF then
 response.write "<i>No Records found</i>"
else
 Response.write "<table>"
 oRS.MoveFirst
 
 While not oRS.EOF
  '*** Casting the right types. look ma, I'm in pictures!!
  uid = CLng(oRS(0))
  username=CStr(oRS(1))
  email = CStr(oRS(2))
  createdate=CDate(oRS(3))

  userrow="<tr><td>"& uid &"</td><td>"& username &"</td><td>"& _
	email &"</td><td>"& createdate &"</td></tr>"
  response.write userrow & vbNewLine
  oRS.MoveNext
 Loop

 response.write "</table>"
 ' *** CloseSQL is also something we wrote, code below
 CloseSQL oRS
end if
CloseSQL oConn

Function SetupSQL(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 CloseSQL(byref DBOBJ)
DBOBJ.Close
Set DBOBJ = Nothing
end sub
%>
</tip>

sgd
--
work: http://www.ti3.com/
non: http://thinksafely.org/