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 commands:SQL command • Close the connection • Release the object resource

11. 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

12. <%@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; %>

16. Connection Scope • global.asa • Session_onStart function Session_onStart() { ObjCon=Server.CreateObject(“ADODB.Conneciton”) ... } • Application_onStart function Application_onStart() { ObjCon=Server.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 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()

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),adCmdUnknown(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 • ObjCmd=Server.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. <%@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; %>

21. 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"); }

22. <%@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"); %>

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 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() }

25. <%@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>"); ... %>

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 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

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,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

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 ObjCon=Server.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. 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() …

34. Recordset:Iteration ObjCon = Server.CreateObject(“ADODB.Connection”) ObjCon.Open(“DSN”) ObjRS = ObjCon.Execute(“TableName”, , adCmdTable) ObjRS.MoveFirst() While (! ObjRS.EOF) { … ObjRS.MoveNext() }