Transcript ADO in JavaScript (AdoJS) - Yen

ActiveX Data Object (ADO)
in JavaScript
J.L.Wang, Yen-Cheng Chen
Dept. of Infomation Management
Ming-Chuan University
Jan. 1999
Outlines
 Overview
 Object
model of ADO
 ADO techniques
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
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
Data Provider
Active Server Page
Active Database
Component
ADO
Data
Source
ODBC
Driver
Data Provider
Interface
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
 Access
Object
the data that is returned from
executing an SQL query
ADO Object Hierarchy
Collection
Object
Connection Object
Recordset Object
Field collection
Property collection
Command object
Parameter collection
Property collection
Property collection
Error collection
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
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 commands:SQL command
Close the connection

Release the object resource



Connection Object: Basic Commands



ObjCon=Server.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
<%@language=JScript%>
<%
conn=Server.CreateObject("ADODB.Connection");
conn.Open("ExampleAdoDSN");
rs=conn.Execute("select * from Books");
Response.Write("<center><h2>Books</h2>");
Response.Write("<table border=1><tr>");
cnt=rs.Fields.Count;
for (i=0;i<cnt;i++) {
Response.Write("<th>"+rs(i).Name+"</th>");
}
Response.Write("</tr>\n");
while (! rs.EOF) {
Response.Write("<tr>");
for (i=0;i<cnt;i++) {
Response.Write("<td>"+rs(i)+"</td>");
}
Response.Write("</tr>\n");
rs.MoveNext();
}
Response.Write("</table>\n");
Response.Write("</center>");
conn.Close();
conn=null; %>
Connection Scope

global.asa
 Session_onStart
function Session_onStart() {
ObjCon=Server.CreateObject(“ADODB.Conneciton”)
...
}
 Application_onStart
function Application_onStart() {
ObjCon=Server.CreateObject(“ADODB.Conneciton”)
...
}
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
ObjCon=Server.CreateObject(“ADODB.Conneciton”)
ObjCon.Open("DSN")
ObjCon.BeginTrans()
ObjCon.Execute(“SQL COMMAND”)
If (Conditions) {
ObjCon.CommitTrans
// Serve any changes
}
Else {
ObjConn.RollbackTrans
// Cancel any changes
}
}
ObjCon.Close()
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),adCmdUnknown(3)
 Prepared:
Whether to create a prepare statement before
execution (a command could be executed for multiple times)
Command Object: Basic Commands

Create an instance of the Command object


Create an active connection



ObjCmd.ActiveConnection = “DSN”
ObjCmd.ActiveConnection = someConnectionObject
Execution a query





ObjCmd=Server.CreateObject(“ADOBE.Command”)
ObjCmd.CommandText = “SQL Command”
ObjCmd.CommandType = 1
// SQL query
ObjCmd.Prepared = true
// Compile the statement
ObjCmd.Execute()
Release the resource used

ObjCmd.ActiveConnection = null
<%@language=JScript%>
<%
cmd=Server.CreateObject("ADODB.Command");
cmd.ActiveConnection="ExampleAdoDSN";
cmd.CommandText="select * from Books";
rs=cmd.Execute();
cnt=rs.Fields.Count;
Response.Write("<center><h2>Books</h2>");
Response.Write("<table border=1><tr>");
for (i=0;i<cnt;i++) {
Response.Write("<th>"+rs(i).Name+"</th>");
}
Response.Write("</tr>\n");
while (! rs.EOF) {
Response.Write("<tr>");
for (i=0;i<cnt;i++) {
Response.Write("<td>"+rs(i)+"</td>");
}
Response.Write("</tr>\n");
rs.MoveNext();
}
Response.Write("</table>\n");
Response.Write("</center>");
cmd=null;
%>
function GenerateTable(rs, tableTitle) {
cnt=rs.Fields.Count;
Response.Write("<h2>tableTitle</h2>");
Response.Write("<table border=1><tr>");
for (i=0;i<cnt;i++) {
Response.Write("<th>"+rs(i).Name+"</th>");
}
Response.Write("</tr>\n");
while (! rs.EOF) {
Response.Write("<tr>");
for (i=0;i<cnt;i++) {
Response.Write("<td>"+rs(i)+"</td>");
}
Response.Write("</tr>\n");
rs.MoveNext();
}
Response.Write("</table>\n");
}
<%@language=JScript%>
<%
function GenerateTable(rs, tableTitle) {
...
}
cmd=Server.CreateObject("ADODB.Command");
cmd.ActiveConnection="ExampleAdoDSN";
cmd.CommandText="select * from Books";
rs=cmd.Execute();
GenerateTable(rs, "Books");
%>
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

Recordset Fundamentals
 Open
the recordset
Set rs=Server.CreateObject(“ADODB.Recordset”)
rs.Open(“select * from Books”, “DSN=ExampleAdoDSN;”)
 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()
}
<%@language=JScript%>
<%
rs=Server.CreateObject("ADODB.Recordset");
conn=Server.CreateObject("ADODB.Connection");
conn.Open("ExampleAdoDSN");
rs.Open("select * from Books", conn);
cnt=rs.Fields.Count;
Response.Write("<center><h2>Books</h2>");
Response.Write("<table border=1><tr>");
for (i=0;i<cnt;i++) {
Response.Write("<th>"+rs(i).Name+"</th>");
}
Response.Write("</tr>\n");
while (! rs.EOF) {
Response.Write("<tr>");
for (i=0;i<cnt;i++) {
Response.Write("<td>"+rs(i)+"</td>");
}
Response.Write("</tr>\n");
rs.MoveNext();
}
Response.Write("</table>\n");
Response.Write("</center>");
...
%>
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
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 aren’t 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
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
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,LockType,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
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
Recordset: Connection
ObjCon=Server.CreateObject(“ADODB.Conneciton”)
ObjCon.Open(“DSN”)
ObjRS = ObjCon.Execute (“SQL COMMAND”)
….
Recordset: Command
ObjCmd = Server.CreateObject(“ADOBE.Command”)
ObjCmd.ActiveConnection = “DSN”
ObjCmd.CommandText = “SELECT * FROM JobCon”
ObjCmd.CommandType = adCmdText
ObjRS = ObjCmd.Execute()
…
Recordset:Table/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()
…
Recordset:Iteration
ObjCon = Server.CreateObject(“ADODB.Connection”)
ObjCon.Open(“DSN”)
ObjRS = ObjCon.Execute(“TableName”, , adCmdTable)
ObjRS.MoveFirst()
While (! ObjRS.EOF) {
…
ObjRS.MoveNext()
}