ADO Recordsets

1
ADO Recordsets

• Using SQL Select Queries and ADO to populate web
  pages.

2
ADO What is it?

• ActiveX Data Objects (ADO) is a cursor library.
• It allows SQL to be sent to the server and status
  messages, rows and columns to be returned to the
  client.
• It consists of a series of objects that are
  create-able with the Server.CreateObject()
  method.
• All ADO Objects are derived from two projects
  ADODB and ADOR.
• ADO is Microsofts ultimate replacement for all
  other cursor libraries.
• Fast and lightweight.

3
ADO Objects

• Connection
• Establishes and manages a connection to the data
  source
• Command
• Used for execute stored procedures, bulk changes
  and to manipulate database structure
• Recordset
• For returning rows to the client
• Errors
• Hold all of the errors returned from a provider

4
ADO Objects Organization

• Although nominally all objects are decedents of
  the connection object all ADO objects may be
  created and used separately.
• If you only need one instance of any of the ado
  objects, it is faster and cheaper to create just
  that one object without creating a connection
  object.
• However, if you need multiple instances, a
  connection object will let you "pool" the
  connections.

5
Creating and Using DSNs

• A DSN is a "Data Source Name"
• It is a series of instructions to the client
  engine about how and where to connect to the back
  end data base.
• In general there are three common kinds of DSNs
• System Stored in the registry (not portable)
• File Stored in a file
• DSNless Placed In Line In The Code

6
Which DSN to use? DSNless

• For ASP
• Placed in session variables in the global.asa
  file in the child web
• In a constant at the top of the page
• In a constant in a server side include
• In an INI file fetched by the page
• From the registry

7
Sample DSNs
These are DSNless strings. (They would be all on
one line no wrapping.)
SQLServer Const cDSN1 "DRIVERSQL Server
SERVERParana DATABASEpubs UIDstudentPWDstud
ent" MS Access const cDSN2 "DRIVERMicrosoft
Access Driver (.mdb)DBQ e\XyzClub.mdb
DefaultDire\FILMS AccessDriverId25
UIDadminPWD "
8
Creation/Disposal of ADO Objects
Dim CN 'Create a Connection Object Set CN
Server.CreateObject("ADODB.Connection") CN.Connect
ionString cDSN1 CN.Open 'Use connection
object... 'All done with connection object. We
have already disposed of all objects connected to
it or created from it. (e.g. we must dispose of
things in the exact opposite order we created
them in. Also we should always dispose of things
in the same scope we create them in CN.Close Set
CN Nothing
9
Methods and Properties of the Connection object

• Properties
• ConnectionString DSN
• CommandTimeOut How many seconds should ADO
  attempt to carry out a query before giving up and
  erroring.
• ConnectionTimeOut How many seconds should ADO
  try to connect to the data source before giving
  up and erroring.
• Methods
• Open See previous example
• Close See previous example
• Execute
• Execute command, recordsaffected, commandtype

10
.Execute Example
'Presume that the connection object is open to a
database. Dim RA 'as Long Dim mySQL 'as
String mySQL "Delete From Authors Where AuID
47" CN.Execute mySQL, RA If RA ltgt 1 Then
Response.Write "Error " Str(RA) " records
affected!" Else Response.Write "Auhor 47
Deleted" End if
11
The RecordSet Object

• Used to return rows to the client.
• 1 most used part of ADO
• Can be created alone or with a connection object
  to "pool" connections.
• Can be used to execute stored procedures without
  parameters.
• Can be created w/o a connection object
• Is created from "ADODB.Recordset"

12
Using the connection object to return a recordset
' You can use a select statement to 'create' a
snapshot forward only, ' read only cursor. (A
"firehose" cursor) dim rs 'as ADODB.Recordset
dim mySQL 'as string mySQL "Select from
authors" Set rs CN.Execute mySQL '... Use
cursor rs.Close set rs nothing
13
Example Snapshot (Read Only) Cursor (Rows)
Dim mySQL as String Dim rs As New
ADODB.Recordset 'Assume CN is an ADODB.Connection
Object and is Open mySQL "Select From
Authors" Set rs.ActiveConnection CN
rs.CursorType adOpenStatic rs.Source mySQL
rs.Open
14
Example R/W (Updatable) Cursor
Dim mySQL as String Dim rs As New
ADODB.Recordset 'Assume CN is an ADODB.Connection
Object and is Open mySQL "Select From
Authors" 'Notice use of SET verb Set
rs.ActiveConnection CN rs.CursorLocation
adUseClient rs.CursorType adOpenDynamic
rs.LockType adLockOptimistic rs.Source
mySQL rs.Open
15
Recordset Methods

• .MoveFirst, .MoveLast, .MovePrevious, .MoveNext
  (moves cursor in rows, error if .EOF or .BOF or
  if .RecordCount 0)
• .Move n (moves number of rows specified by, see
  note above)
• .AddNew (adds a new row to recordset, applies
  defaults if any, needs to be followed by .Update)
• .Update (commits changes to .Recordset)
• .UpdateBatch (use in addition to update with
  batch locking)

16
Recordset Example 1
'Assuming rs is a ADODB.Recordset object If
rs.State adStateOpen Then If rs.RecordCount
ltgt 0 Then rs.MoveFirst While not rs.EOF
' do something with records rs.MoveNext
Wend End IF End If
17
Explanations 1a

• EOF and BOF are functions that return true if we
  are at the end or beginning of a recordset. To
  check to make sure we are actually on a good
  (usable record) we should
• If not (rs.EOF or rs.BOF) Then

18
Explanations 1b

• The .RecordCount property returns the number of
  records
• -1 Unknown (Any updatable recordset has this
  value (use the previous EOF/BOF test)
• 0 No records
• 1n (only valid for static type (read only)
  recordsets)
• Never use RecordCount to loop
• Always test for ltgt 0, not a value

19
Explanations 1c

• .State Property
• adStateOpen (Recordset is opened and has a valid
  cursor, notice this does not imply there are
  rows!)
• adStateClosed (Recordset is closed, cursor is
  invalid)

20
Recordsets w/o connection objects.
Set rs Server.CreateObject("ADODB.Recordset")
' Not no SET, creates a unique connection rs.Activ
eConnection cDSN 'DSNless string rs.CursorLocati
on adUseClient rs.CursorType
adOpenStatic rs.LockType adLockBatchOptimistic r
s.Source mySQL rs.Open 'If you only need one
recordset, period, use this.
21
ADOASP.Inc
lt 'Constant Values for ADO/file
operations const adOpenStatic 3 const
asOpenDynamic 2 const adOpenKeySet 1 const
adUseClient 3 const adUseServer 2
const adLockBatchOptimistic 4 const
adLockReadOnly 1 const adOpenForwardOnly
0 const ForReading 1 ' Open a file for reading
only. You can't write to this file. const
ForWriting 2 ' Open a file for writing only.
You can't read from this file. const
ForAppending 8 ' Open a file and write to the
end of the file. gt
22
The recordset has fields

• A field is a column of data. For each row all of
  the fields will be of the same order, name and
  type.
• These are kept in the fields collection
• You may use early or late binding syntax to
  address fields.

23
Limiting the number of rows returned from a
recordset.

• The .MaxRecords property controls how many
  records are return by the recordset to the
  client. ( 0 no limit )
• You are at the mercy of the provider, which
  records will be returned.
• Usually, the query will be fully executed on the
  server then the specified number of rows are
  returned. (Sometimes this only works on server
  side cursors.)

24
Field Binding Syntax

• ASP does not support the Early Binding syntax
• rs!FieldName
• Late Binding
• rs.Fields(i).Value
• rs.Fields("FieldName").Value
• All are equivalent and are listed in ascending
  order of performance.
• ASP Programmers can use a short cut
• rs("FieldName")

25
Field Binding Notes

• As always early binding is faster, however you
  can't use early binding in scripts (yet) or ASP.
• You can't iterate the fields collection with
  early binding.
• You may mix early and late binding if early
  binding is allowed.
• For safeties sake ASP Programmers should use the
  short cut which insure that the .Value property
  is being examined.
• Indexed iteration is a fast way to spit out all
  of the columns

26
Iterating the fields collection
'Assuming rs is an open recordset object 'Notice
like most collections, starts at ZERO to Count
-1 'Prints name and type (a table of which is on
the next slide) For i 0 to rs.Fields.Count -1
response.write rs.fields(i).Name " "
rs.Fields(I).Type Next 'Prints name and
value For i 0 to rs.Fields.Count -1
response.write rs.fields(i).Name " "
rs.Fields(I).Value Next
27
Type Constants
Constant Value Access VB adBoolean 11 Yes/No Bo
olean adTinyInt 16 Number/Byte Byte adSmallInt 2
Number/Integer Integer adInteger 3 Number/Long L
ong adCurrency 6 Currency Currency adDBTimeStamp
135 Date Date/Time adSingle 4 Number/Single Sin
gle adVarChar 200 Text String adLongVarChar 201
Memo String
28
Output of a Recordset to HTML
'Assume the record rs contains data and is at the
beginning and you want all columns 'Create The
Table and Print the columns response.write
"lttable border'1' cellpadding'2'
cellspacing'0'gt" response.write "lttrgt" for I 0
to rs.fields.Count - 1 response.write
"lttdgtltbgt" rs.fields(I).Name
"lt/bgtlt/tdgt" next response.write "lt/trgt" While not
rs.EOF response.write "lttrgt" for I 0 to
rs.Fields.Count - 1 response.write "lttdgt"
rs.fields(I).value "lt/tdgt" next
response.write "lt/trgt" rs.MoveNext wend respons
e.write "lt/tablegt"
29
Doing updates in cursors

• The following slides use recordsets to do the 3
  action queries (insert, update, delete) however
  because of the episodic nature of the internet,
  these techniques should be avoided in favor of
  the techniques next lecture.
• Skim these slides only.

30
Updating values in recordsets
'Assuming rs is an open recordset and is
updateable. Rs("LastName") "Smith" rs("FirstNa
me") "Tom" rs("CatsOwned") 77 rs("Crazy")
True rs.Update 'Commit Changes rs.UpdateBatch
Send changes to server
31
Adding a new record
'Assuming rs is an open recordset and is
updatable. rs.AddNew rs("LastName")
"Smith" rs("FirstName") "Tom" rs("CatsOwned")
7 rs("Crazy") -1 rs.Update 'Commit
Changes rs.UpdateBatch Send changes to server
32
Undoing edits in progress.

• .CancelUpdate executed before .Update will "undo"
  the changes you have made.
• After the update method is executed for
  optimistic locking (no batching) it is too late
  to fix. (consider using a transaction).
• In the case where you have Batch Optimistic
  locking, .CancelBatch before .UpdateBatch
  "undoes" the changes.

33
Deleting the current record.

• The .Delete method deletes the current record.
• Both .EOF and .BOF will be set to true, you may
  therefore have trouble looping through the rest
  of the recordset.
• Consider using an execute query instead.
• No undo except in a transaction.

34
Notes on previous two examples

• If you specified 'BatchLocking' for LockType e.g.
  adLockBatchOptimistic, you must (at some point)
  execute the .BatchUpdate Method to commit changes
  to database.
• Batch locking is more efficient, but you have to
  manage batch collisions and remember to do batch
  update.

35
Collisions.

• Most database engines keep an invisible marker
  for each record, when you get a record a time
  stamp is grabbed with it.
• If you update that record, that time stamp is
  checked against the row in the table, if they are
  different then someone has updated the record
  since you grabbed it and your update will fail,
  generating a trap-able run time error.
• Your program must handle this.

36
Collisions, what are the choices

• When you get the error your choices are
• Discard the changes your user made, warn them and
  make the user reapply them after your re-acquire
  the conflicted record (so you have the new time
  stamp) or you can make your program do it.
• Dont update at all.

37
ADO Fields() Value Properties

• ADO Fields Collection has 3 value properties
• .Value (the value of that field in your cursor's
  copy of the row, with any new values you have
  applied)
• .OriginalValue (the value it was when you first
  fetched the row)
• .UnderlyingValue (the value it is in the database
  now, this actually executes a query!)

38
Value properties and collisions

• Hence you can "pre-check" to see if you will
  collide, and warn your user.
• If you elect to re-aquire the rows and reapply
  the changes you can save off the fields and
  values that are different (.Value ltgt
  .OriginalValue) into a collection or array and
  then re-apply the changes.
• Which you do is up to you.

39
Re-aquiring rows.

• The .Refresh method should in most cases fetch
  the current cursor from the database thus
  re-aquiring the rows and letting you see any
  changes that have happened.
• However, sometimes this does not work. To be safe
  you should Close, Deallocate and Recallocate, and
  re-open the recordset.
• Test, this for your situation.

40
Best Refresh Method
Sub GoodRefresh(byRef rs as ADODB.Recordset) Dim
mySQL 'as String 'Sample saved property mySQL
rs.Source 'Saved Property If rs.State
adStateOpen Then if rs.Status ltgt adRecOK Then
rs. rsw.CancelUpdate End if rs.Close End
if set rs Nothing DoEvents set rs
CreateObject("ADODB.RecordSet") Set
rs.ActiveConnection CN .Source mySQL '
reapply rest of saved properties End Sub
41
Choosing rows.

• ADO has no .Find commands.
• Instead it offers the .Filter property.
• The .Filter property may be slower then simply
  closing the recordset, changing he .Source
  property (select query) and re-opening the
  recordset.
• In general do not use filtering. Again the
  episodic nature of the web generally prohibits
  making scrolling cursors in web applications.
• However, if you only want to display 20 records
  at a time, the combination of filters and
  bookmarks is essential.

42
Filter

• .Filter "Where part of a select query without
  the Where keyword"
• Examples
• rs.Filter "LastName 'Smith'"
• rs.Filter "Sales gt 200"
• rs.Filter "FirstName Like 'M'"
• Clearing the filter
• .Filter "" (or use the constant adFilterNone)

43
Moving to specific records with bookmarks.
Dim B 'as Variant Always uses variant Dim mySQL
'as String Lets look for smith B Null
initialize it mySQL Name Smith rs.Filter
mySQL Search by filtering if rs.RecordCount
ltgt 0 then Found at least one? B
rs.Bookmark remember it
rs.Filter adFilterNone clear filter now
were at BOF rs.BookMark B
snap back to record else rs.Filter
adFilterNone clear filter end if if
isNull(B) then We did not find it...
Bookmarks can not be used between invocations of
a recordset, even with the same source (SQL).
44
Transactions

• Only by using a connection object can you have
  transactions.
• With in the bounds of a transaction, all of the
  changes to a database succeed or fail together.
• If you want transactions across databases (like
  an ATM funds transfer) you must use a distributed
  transaction coordinator like Microsoft
  Transaction Server (MTS).

45
Transactions Example
On local error goto BAD_TRANS 'Start
Transaction CN.BeginTrans '... Do a bunch of
changes to database 'All is well, complete
transaction, update DB CN.CommitTrans Exit
Sub 'Oh, no we got an error, undo all
changes BAD_TRANS 'Handle error in here
somewhere CN.RollBackTrans Exit Sub
46
Transactions

• Always, always, always, FINISH the transaction no
  matter what with either commit or rollback.
• Keep them SHORT, do all external compositions
  external to the transaction block
• Ideally all execute statements (for speed).
• Even more ideally for the web move all
  transactions to MTS or stored procedures on the
  database server.
• Always think episodic on the web!