Disconnecting ADO

1
Disconnecting ADO

• Or ADO Unplugged
• Rob Macdonald
• Salterton Hill Ltd
• rob_at_salterton.com

2
Objectives

• To present ADO as more than just a new way of
  executing SQL
• To get the most out of the ADO Cursor Library
• To place ADO under the microscope
• or at least, some interesting bits

3
Agenda

• What is a RecordSet Anyway?
• Three ways of Unplugging ADO
• Disconnecting, Fabricating, Do It Yourself
• Recordset Manipulation
• Marshalling, Filtering and Sorting
• Horizons
• Should we see RecordSets as a universal data
  structure?

4
Who Am I?

• Nine years running a Windows/client-server
  software house
• Two years as an independent software specialist
• Author of RDO/ODBC/ADO/COM/COM books and
  articles
• Trainer

5
What is a RecordSet Anyway ?
Connection
Tradition has it that there are three primary
objects in ADO ...
Command
RecordSet
6
What is a RecordSet Anyway ?
the question is, which three?
RecordSet
Fields
Field
Note ADO 2.5 introduces the Record object, which
also has a Fields collection
7
Question

• What do the following have in common?

rs("last_name") "Macdonald" rs("last_name").Valu
e "Macdonald" rs!last_name "Macdonald" rs.Move
Next rs.Fields("last_name").Value
"Macdonald" rs.Fields("last_name").Properties("OPT
IMIZE") True
8
Answer
They all make changes to Field objects
And why is this important...?
9
One Reason is Performance
rs.open "select from authors", "DSNPubs"

• rs.MoveFirst
• while not rs.EOF
• string1 rs("State")
• string2 rs("Zip")
• rs.movenext
• wend

• Set fState rs("state")
• Set fZip rs("zip")
• rs.MoveFirst
• while not rs.EOF
• string1 fState
• string2 fZip
• rs.movenext
• wend

takes 5.6 ms
takes 3.3 ms
10
Also, consider ...
Dim rs As New ADODB.Recordset Dim dict As New
Dictionary rs.open "select from authors",
"DSNPubs"
'add a Field object to a dictionary dict.Add
"name", rs("au_lname") Print dict("name") rs.Mov
eNext Print dict("name")
Prints
White Green
'add a Field's value to a dictionary dict.Add
"name", rs("au_lname").Value Print
dict("name") rs.MoveNext Print dict("name")
Prints
White White
11
Unplugging ADO

• Five good reasons
• reduce Server Load
• modify RecordSet Behaviour
• generate Custom Data
• create a stateless Middle Tier
• provide a consistent Data Presentation

12
Unplugging ADO - Server Load

• Holding Connections and RecordSets open is a
  drain on server resources
• can result in extensive database locking that
  reduces concurrency and performance further
• 'Fetch and Forget' is efficient but not always
  appropriate
• Disconnected RecordSets allow the server to
  forget
• but allow the application to remember

13
Unplugging ADO - Behaviour

• With disconnected RecordSets, you can allow
  client applications to operate directly on the
  RecordSet
• the data source will not be affected
• You can then validate the changes. . .
• check that the updates meet your business rules
• . . . before re-connecting to the data source

14
Unplugging ADO - Custom Data

• ADO provides a great model for working with all
  kinds of data, not just SQL results
• any type of data that can be thought of as
  'records'
• By creating custom RecordSets you can allow
  clients to use these same techniques with your
  own data
• mix and match with SQL data

15
Unplugging ADO - Middle Tier

• To create highly scalable applications, server
  and middle tier resources should be freed as
  quickly as possible

Web Server MTS App Code
1 - HTTP/DCOM Request
Client / Browser
2 -SQL
3 - HTTP/DCOM - ADO Data
4 - HTTP/DCOM - ADO Update
5 -SQL
Client holds Recordset between 3 and 4
Server holds Recordset between 2 and 3, and
between 4 and 5
16
Unplugging ADO - Consistency

• Making data look like ADO has many benefits
• users of data (ie client programmers) use known
  techniques
• data binding can be used
• ADO offers powerful data manipulation

17
Three ways of Unplugging ADO

• Disconnecting
• Fabricating
• Do It Yourself (Custom Provider)

18
Disconnecting

• Apply this technique when
• RecordSet manipulation is required
• (i.e. anything other than MoveNext)
• Returning whole RecordSets to clients
• RecordSet state is required when performing
  updates
• Primarily used with SQL RecordSets
• batch update functionality generates SQL

19
Read-Only Disconnection
Public Function getAuthors() As
ADODB.Recordset Dim rs As New ADODB.Recordset rs.
CursorLocation adUseClient rs.CursorType
adOpenStatic rs.Open "select au_fname, au_lname,
state, _ zip from authors",
"DSNpubs Set rs.ActiveConnection Nothing Set
getAuthors rs End Function
Must set the correct cursor properties

Disconnecting takes place here
20
Updateable Disconnection (1)

• Updates are held in the RecordSet
• Compare fd.Value and fd.OriginalValue
• When all updates have been made, call
• Public Function setRS(rs as ADODB.RecordSet) As
  Boolean
• 'validate data
• rs.UpdateBatch
• 'handle update conflicts
• End Function
• ADO automatically generates an SQL statement for
  each row modified . . .
• UPDATE, INSERT, DELETE

We'll discuss this more later
21
Updateable Disconnection (2)

• Updating requires setting the correct lock type
  before opening the RecordSet
• rs.LockType adBatchOptimistic
• Query must include the primary key
• all the required data must be in the RecordSet
• if the auto-update updates multiple rows
• an error will be raised
• the update will still take place!
• SQLServer 7 is a 'special case'
• uses 'built in' stored procedures instead of SQL

22
Updating Joins (1)

• Prior to ADO 2.1, ADO attempted to update each
  table in the query
• required each table's primary key
• rarely desired behaviour

AddNew will attempt to update both tables!
Trading Data
Currency LookUp
23
Updating Joins (2)

• ADO 2.1 added some control over this process
• identify a Unique Table
• rs.Properties("UNIQUE CATALOG") "pubs"
• rs.Properties("UNIQUE TABLE") "titles"
• Update/UpdateBatch/AddNew/Delete/Resync affect
  only the unique table

24
Fabricating RecordSets

• Simple technique for generating RecordSet
  structures
• You define a RecordSet structure
• wide range of data types supported
• including Hierarchical RecordSets
• can also use DataFactory.CreateRecordSet
• And add data using AddNew
• full range of RecordSet functionality supported

25
For Example
Public Function getMeetingTimes(dtGMT As Date) As
ADODB.Recordset Dim rs As New ADODB.Recordset Dim
vCols As Variant rs.Fields.Append "Location",
adVarChar, 15 rs.Fields.Append "Time",
adDate rs.Open vCols Array("Location",
"Time") rs.AddNew vCols, Array("London",
dtGMT) rs.AddNew vCols, Array("Washington",
DateAdd("h", -5, dtGMT)) rs.AddNew vCols,
Array("Tokyo", DateAdd("h", 9, dtGMT)) rs.AddNew
vCols, Array("Samoa", DateAdd("h", -11,
dtGMT)) rs.MoveFirst Set getMeetingTimes
rs End Function
Fields must be appended BEFORE the RecordSet is
opened
It's good manners (and inexpensive) to reposition
the cursor
26
Creating an OLE DB Provider

• Fabricated RecordSets are not a replacement for
  OLE DB Providers
• don't support Connection.Open
• can't be directly bound to controls
• not known to the registry as providers
• can't be fetched asynchronously
• You can create 'Simple' Providers in VB
• or full blown providers using C

27
5 Steps to being a provider writer...

• Create a VB6 ActiveX DLL
• Create a 'Connection' Class
• Set the DataSourceBehaviour property to 1
• Implement the GetDataMember event
• Create one or more 'RecordSet' classes
• Implement the OLEDBSimpleProvider interface
• Register the DLL with OLE DB
• Use like any other provider (!)

28
For example ...

• You have a Funds table in your database

id
ROR
Size
Name
Quartile
You want to create a RecordSet that
contains those funds meeting certain criteria a
quartile column ranking the selected columns
29
Simple Provider - Client

• Dim rs As New Recordset
• With rs
• .Open "250", "ProviderVB Custom Data"
• While Not .EOF
• Print !FundCol2, !FundCol3, !FundCol4,
  !FundCol5
• rs.MoveNext
• Wend
• End With

The command string
The Provider name
Fund Size ROR Quartile
IncomePlus 800 6.5 2
MegaBucks 550 7.1 4
PotLuck 300 9.1 2
OrbitRage 600 4.5 1
SuperGrowth 1200 7.8 3
CapitalGold 1500 6.4 4
Prints...
30
Simple Provider - 'Connection'
Must assign an object that 'Implements'
OLEDBSimpleProvider
Private Sub Class_GetDataMember( _
DataMember As String, Data As Object) Dim
oCustom As CustomRecords Set oCustom New
CustomRecords oCustom.Prepare CLng(DataMember) Se
t Data oCustom End Sub
The command string
Passes DataMember to the oCustom object
31
Simple Provider - RecordSet (1)
Implements OLEDBSimpleProvider Private lColCount
As Long Private rs As Recordset Public Sub
Prepare(lSize As Long) 'create disconnected
RecordSet Set rs New ADODB.Recordset rs.CursorLo
cation adUseClient rs.CursorType
adOpenStatic rs.Open "select from Funds where
Size gt " lSize, "DSNFunds" lColCount
rs.Fields.Count 1 Set rs.ActiveConnection
Nothing End Sub
Standard interface getVariant, setVariant,
getColumnCount, getRowCount, insertRows,
deleteRows, isAsync, find etc
Add 'extra' column
32
Simple Provider - RecordSet (2)
Private Function OLEDBSimpleProvider_getVariant(
_ ByVal iRow As Long, _ ByVal iColumn As Long,
_ ByVal format As MSDAOSP.OSPFORMAT) As
Variant Dim vValue As Variant If iRow 0 Then
vValue "FundCol" iColumn Else If iColumn lt
lColCount Then rs.Move iRow - 1,
adBookmarkFirst vValue rs(iColumn -
1).Value Else vValue CInt(Rnd 3) 1 End
If End If OLEDBSimpleProvider_getVariant
vValue End Function
Row 0 means - provide a column name
If the 'extra' column is selected, supply an
'internally generated' quartile number instead of
a RecordSet value
33
Simple Provider - Summary

• The client uses ADO in a standard way
• ' rs!col1' is translated into getVariant
• 'rs!col1 ' is translated into setVariant
• The Custom provider can do anything it likes
• so long as it implements OLEDBSimpleProvider
• The DLL must be registered with OLE DB
• download a complete example from
  www.salterton.com/hill

34
RecordSet Manipulation

• Marshalling
• what happens when you start passing RecordSets
  around?
• Sorting and Filtering
• how good are RecordSets at these standard data
  operations?

35
Marshalling

• You have an object that provides disconnected
  RecordSets
• Public Function getRS() As Recordset
• Public Function setRS( rs As Recordset) As
  Boolean
• And a client that uses this server

Dim obj As New ADOServer.Data Set rs
obj.getRS() 'make some changes to records
here obj.setRS rs
focus on this line!
What happens when the RecordSet is passed between
client and server?
36
Well, It Depends ...

• When both objects are in the same process, a
  pointer is passed
• but you can't pass pointers across process or
  machine boundaries
• http or DCOM
• Instead, marshalling is used to move the
  RecordSet between the processes
• your code looks the same
• but performance is very different!

37
It's all Smoke and Mirrors
obj.setRS rs
Client
Server
As the call is made, rs is copied to the server
process
rs
rs
At the end of the call, rs is copied back to the
client process

• Assume rs has 5000 rows
• Set rs obj.getRS() takes 2.2 s
• it involves passing rs once between the two
  processes
• obj.setRS rs will take 4.4 s
• rs gets passed in both directions !

38
Using ByVal
Typically, the client doesn't want to see any
changes that the server makes. So, change the
server function's definition to
Public Function setRS (ByVal rs As Recordset) As
Boolean
Client
Server
As the call is made, rs is copied to the server
process
rs
rs

• obj.setRS rs will now take 2.2 s
• because it isn't passed back to the client

39
Using MarshallOptions
Often, the server only needs to see the records
the client has changed. So, in the client
code, set
rs.MarshalOptions adMarshalModifiedOnly
Client
Server
As the call is made, only the changed records in
rs are copied to the server process
rs
rs
obj.setRS rs will now take 0.2 s
40
Sorting and Filtering

• ADO provides sort, filter and find operations
  through the client cursor library
• very easy and convenient to use
• Client-side index can be created to improve
  performance
• these were NOT reliable in ADO 2.0

rs.Fields("last_name").Properties("OPTIMIZE")
True
41
Data Structures Compared

• Figures below are based on a 5000 row, 4 column
  data structure.
• Times are normalised on Variant Array
  performance
• Array sorting is 400 times slower than iteration

1 - timings include iterating through the
results 2 - the faster time is achieved if
searching by collection key is possible
42
Horizons - How Universal is ADO?
Standardised Programming Model
XML
Data Binding
MegaFast Sorting
Microsoft Commitment
Providers
SQL
Easy Stateless Programming
Efficient Marshalling
Customisation
43
Summary

• What is a RecordSet Anyway?
• Three ways of Unplugging ADO
• Disconnecting, Fabricating, Do It Yourself
• Recordset Manipulation
• marshalling, filtering and sorting
• Horizons
• should we see RecordSets as a 'universal data
  structure'?