ActiveX Data Object (ADO) in JavaScript

1
ActiveX Data Object (ADO)in JavaScript

• J.L.Wang, Yen-Cheng Chen
• Dept. of Infomation Management
• Ming-Chuan University
• Jan. 1999

2
Outlines

• Overview
• Object model of ADO
• ADO techniques

3
ADO Overview

• ADO allow us to write code in a scripting
  language that can interact with a database
• The missing link between the web page and almost
  any kind of stored data

4
ADO Data Interface

• To interface with database through ODBC
• Use it with any data source for which an ODBC
  driver is available
• ODBC
• Open DataBase Connectivity

5
Data Provider
Active Server Page
Active Database Component
Data Source
ODBC Driver
ADO
Data Provider Interface
6
ADO Object Model

• Connection Object
• Establish an active connection that allows us to
  gain access to data stored in a database
• Command Object
• Obtain records, excute SQL queries, or manipulate
  the data
• Recordset Object
• Access the data that is returned from executing
  an SQL query

7
ADO Object Hierarchy
Collection
Object
8
Connection Object
Recordset Object
Field collection
Property collection
Command object
Parameter collection
Property collection
Property collection
Error collection
9
Connection Object

• Connection
• Represent the physical link between applications
  and the remote database server
• All communications between Recordset or Commands
  and the back-end database is negotiated through
  the connection
• Transaction
• Make the interaction with the database
  bulletproof
• A series of changes can be grouped together to
  look like a single, all-or-nothing (atomic) change

10
Connection Object Basic Flow

• Create an instance of the Connection object
• global.asa Session_onStart or Application_onStart
  
• Open a connection data source name (DSN)
• Execute commandsSQL command
• Close the connection
• Release the object resource

11
Connection Object Basic Commands

• ObjConServer.CreateObject(ADODB.Conneciton)
• Create an instance of the Connection object
• ObjCon.Open(DSN)Open a connection
• ObjCon.Execute(SQL COMMAND)
• Execute an execution, the result can be stored in
  a recordset
• ObjCon.Close() Close the connection
• ObjCon.BeginTrans() Begins a new transaction
• ObjCon.CommitTrans() Saves any changes and ends
  the transaction, May also start a new transaction
• ObjCon.RollbackTrans() Cancel any changes and
  ends the transaction. May also start a new
  transaction

12
lt_at_languageJScriptgt lt connServer.CreateObject(
"ADODB.Connection") conn.Open("ExampleAdoDSN") r
sconn.Execute("select from Books") Response.Wr
ite("ltcentergtlth2gtBookslt/h2gt") Response.Write("ltta
ble border1gtlttrgt") cntrs.Fields.Count for
(i0iltcnti) Response.Write("ltthgt"rs(i).N
ame"lt/thgt") Response.Write("lt/trgt\n") while
(! rs.EOF) Response.Write("lttrgt") for
(i0iltcnti) Response.Write("lttdgt"rs(i
)"lt/tdgt") Response.Write("lt/trgt\n")
rs.MoveNext() Response.Write("lt/tablegt\n") Res
ponse.Write("lt/centergt") conn.Close() connnull
gt
13
(No Transcript)
14
(No Transcript)
15
(No Transcript)
16
Connection Scope

• global.asa
• Session_onStart
• function Session_onStart()
• ObjConServer.CreateObject(ADODB.Conneciton)
• ...
• Application_onStart
• function Application_onStart()
• ObjConServer.CreateObject(ADODB.Conneciton)
• ...

17
Connection Transaction

• Perform a series of updates on a data source
• Get the system to store up all the changes, and
  then commit them in one go
• Before actually commit the change, the chnages
  can be rolling back

ObjConServer.CreateObject(ADODB.Conneciton) Obj
Con.Open("DSN") ObjCon.BeginTrans() ObjCon.Execute
(SQL COMMAND) If (Conditions)
ObjCon.CommitTrans // Serve any changes
Else ObjConn.RollbackTrans // Cancel any
changes ObjCon.Close()
18
Command Object

• Provide methods and properties to manipulate
  individual commands
• Methods
• CreateParameter Create a new Parameter object
  that can be appended to the Parameters
  collections
• Execute Execute the SQL statement or stored
  procedure
• Property
• ActiveConnection Active one connection to be
  used by command object (DSN)
• CommandText Text of a command to be execute
• CommandTimeout No. of second for finishing a
  command
• CommandType
• adCmdText(1), adCmdTable(2), adCmdStoreProc(3),ad
  CmdUnknown(3)
• Prepared Whether to create a prepare statement
  before execution (a command could be executed for
  multiple times)

19
Command Object Basic Commands

• Create an instance of the Command object
• ObjCmdServer.CreateObject(ADOBE.Command)
• Create an active connection
• ObjCmd.ActiveConnection DSN
• ObjCmd.ActiveConnection someConnectionObject
• Execution a query
• ObjCmd.CommandText SQL Command
• ObjCmd.CommandType 1 // SQL query
• ObjCmd.Prepared true // Compile the statement
• ObjCmd.Execute()
• Release the resource used
• ObjCmd.ActiveConnection null

20
lt_at_languageJScriptgt lt cmdServer.CreateObject("
ADODB.Command") cmd.ActiveConnection"ExampleAdoD
SN" cmd.CommandText"select from
Books" rscmd.Execute() cntrs.Fields.Count Res
ponse.Write("ltcentergtlth2gtBookslt/h2gt") Response.Wr
ite("lttable border1gtlttrgt") for (i0iltcnti)
Response.Write("ltthgt"rs(i).Name"lt/thgt")
Response.Write("lt/trgt\n") while (! rs.EOF)
Response.Write("lttrgt") for (i0iltcnti)
Response.Write("lttdgt"rs(i)"lt/tdgt")
Response.Write("lt/trgt\n") rs.MoveNext() Re
sponse.Write("lt/tablegt\n") Response.Write("lt/cent
ergt") cmdnull gt
21
function GenerateTable(rs, tableTitle)
cntrs.Fields.Count Response.Write("lth2gttableT
itlelt/h2gt") Response.Write("lttable
border1gtlttrgt") for (i0iltcnti)
Response.Write("ltthgt"rs(i).Name"lt/thgt")
Response.Write("lt/trgt\n") while (! rs.EOF)
Response.Write("lttrgt") for
(i0iltcnti) Response.Write("lttdgt"r
s(i)"lt/tdgt") Response.Write("lt/trgt
\n") rs.MoveNext()
Response.Write("lt/tablegt\n")
22
lt_at_languageJScriptgt lt function
GenerateTable(rs, tableTitle) . . .
cmdServer.CreateObject("ADODB.Command") cmd.
ActiveConnection"ExampleAdoDSN" cmd.CommandText
"select from Books" rscmd.Execute() GenerateT
able(rs, "Books") gt
23
Recordset Object

• Assign the query results to a Recordset object
• Like a table in memory
• Can create recorsets containing the data returned
  from that query
• Can even create a recordset directly, without
  having to open a connection or execute a command
  first

24
Recordset Fundamentals

• Open the recordset
• Set rsServer.CreateObject(ADODB.Recordset)
• rs.Open(select from Books, DSNExampleAdoDSN
  )
• Access the data field
• firstname rs(fieldname)
• firstname rs.Fields(fieldname)
• n rs.Fields.Count // get the number of fields
• Navigate the records
• while (! rs.EOF)
• // do something with the data
• rs.MoveNext()

25
lt_at_languageJScriptgt lt rsServer.CreateObject(
"ADODB.Recordset") connServer.CreateObject("ADOD
B.Connection") conn.Open("ExampleAdoDSN") rs.Ope
n("select from Books", conn) cntrs.Fields.Coun
t Response.Write("ltcentergtlth2gtBookslt/h2gt") Respo
nse.Write("lttable border1gtlttrgt") for
(i0iltcnti) Response.Write("ltthgt"rs(i).N
ame"lt/thgt") Response.Write("lt/trgt\n") while
(! rs.EOF) Response.Write("lttrgt") for
(i0iltcnti) Response.Write("lttdgt"rs(i
)"lt/tdgt") Response.Write("lt/trgt\n")
rs.MoveNext() Response.Write("lt/tablegt\n") Res
ponse.Write("lt/centergt") ... gt
26
Recordset Properties

• AbsolutePage Page of current position
• AbsolutePosition The original position of the
  current record
• ActiveConnection Active connection object
• BOF Before of first record ( True or False )
• Bookmark Return/set a bookmark
• CacheSize Number of records cached
• CursorLocation Server, client, or client batch
• CursorType Forwarde, static, dynamic, keyset
• EditMode The editing status ( backward
  compatible with DAO)
• EOF End of file ( True or False )
• Filter Hide types of records
• LockType Record locking for edits or updates
• MaxRecords Maximum records retrieved
• PageSize Number of pages total
• RecordCount Number of total records
• Source Source command
• Status Status of the last action

27
CursorType

• Dynamic adOpenDynamic
• Fully updateable recordset
• All actions made by other users while the
  recordset is open are visible
• All types of movement ( up and down )
• Keyset adOpenKeyset
• Updateable recordset
• It prevents access to records that other users
  add after it was created
• All types of movement
• Static adOpenStatic
• Static non-updateable recordset ( retrieve data
  )
• Changes made by other users while the recordset
  is open arent visible
• All types of movement
• Forward-only adOpenForwardOnly (default)
• Static non-updateable recordset
• Only Scroll forward through the records
  (MoveNext, GetRows)

actions additions, changes deletion
28
Recordset Method

• AddNew Create a new record in an updateable
  recordset
• CancelBatch Cancels a pending batch update
• CancelUpdate Cancel any changes made to the
  current or a new record
• Clone Create identical Recordset
• Close Close an open recordset
• Delete Delete the current record
• GetRows Get multiple records
• Move Move the position of the current record
• MoveFirst, MoveLast, MoveNext, MovePrevious
• NextRecordset Move to the next set in multi-set
  query
• Open Establish a connection and execute the
  query
• Requery Refresh the data ( re-execute the
  original query )
• Resync Synchronize data with server
• Supports Determine supported features
• Update Save any changes made to the current
  record
• UpdateBatch Write all pending batch updates to
  disk

29
Recordset Create Recordset Directly

• Create a recordset
• ObjRS Server.CreateObject(ADODB.Recordset)
• Fill the new recordset with values from the data
  source
• ObjRS.Open(Source,ActiveConnection,CursorType,Loc
  kType,Options)
• Source A Command object, SQL statement, table
  name or stored procedure
• ActiveConnection Data Source Name
• CursorTYpe adOpenForwardOnly (default)
• LockType adLockReadOnly (default)
• Options The type of query or table represented
  by Source
• adCmdUnknows(0) Unknown(default)
• adCmdText(1) SQL statement
• adCmdText(2) Table name for creating a recordset
• adCmdStoredProc(3) A stored procedure

30
Recordset Moving

• ObjRS.Move(n) Moving
• -n move backward n records
• n forward ( interger )
• ObjRS.AbsolutePosition
• the current record number
• Return value
• -1 (adPosUnknown No current record (be deleted)
• -2 (adPosBOF) Before the first record
• -3 (adPosEOF) After the last record

31
Recordset Connection

• ObjConServer.CreateObject(ADODB.Conneciton)
• ObjCon.Open(DSN)
• ObjRS ObjCon.Execute (SQL COMMAND)
• .

32
Recordset Command

• ObjCmd Server.CreateObject(ADOBE.Command)
• ObjCmd.ActiveConnection DSN
• ObjCmd.CommandText SELECT FROM JobCon
• ObjCmd.CommandType adCmdText
• ObjRS ObjCmd.Execute()

33
RecordsetTable/Command

• ObjCmd Server.CreateObject(ADOBE.Command)
• ObjCmd.ActiveConnection DSN
• ObjRS ObjCmd.Execute (TableName,,adCmdTable)
• ...
• ObjRS.Close()
• ObjCmd Server.CreateObject(ADOBE.Command)
• ObjCmd.ActiveConnection DSN
• ObjCmd.CommandText TableName
• ObjCmd.CommandType adCmdTable
• ObjRS ObjCmd.Execute()
• ObjRS .Close()

34
RecordsetIteration

• ObjCon Server.CreateObject(ADODB.Connection)
• ObjCon.Open(DSN)
• ObjRS ObjCon.Execute(TableName, , adCmdTable)
• ObjRS.MoveFirst()
• While (! ObjRS.EOF)
• ObjRS.MoveNext()