web-based programming lanjut pertemuan 6 matakuliah: m0492 / web-based programming lanjut tahun:...
Post on 21-Dec-2015
220 views
TRANSCRIPT
Web-based Programming Lanjut Pertemuan 6
Matakuliah : M0492 / Web-based Programming Lanjut Tahun : 2007
Bina Nusantara
ActiveX Data Object(ADO)
• Cursor• Locking• Creating Recordset• Filtering Recordset• Searching For Records• Modifying Records• Managing Errors
Bina Nusantara
Cursors• Manages the set of records and the current location within the recordset, the
latter being handled by the current record pointer.
• Cursor types:– Static (adOpenStatic = 3). Contain a static copy of the records; meaning
that the contents of the recordset are fixed at the time the recordset is created. Any records modified, added, or deleted by other users will not be visible. Movement through the recordset is allowed both forwards and backwards.
– Forward Only (asOpenForwardOnly = 0). The default cursor type, which is identical to a Static cursor except that you can only move forwards through the recordset.
– Dynamic (adOpenDynamic = 2). Doesn’t have a fixed set of records. Any changes, additions or deletions by other users will be visible in the recordset. Movement through the recordset is allowed both forwards and backwards.
– Keyset (adOpenKeyset = 1). Similar to Dynamic cursors, except that the set of record is fixed. You can see changes by other users, but new records are not visible. If other users delete records, then these will be inaccessible in the recordset. This functionality is achieved by the set of records being identified by their keys – so the keys remain, even if the records change or are deleted.
Bina Nusantara
Cursors• Cursor Location
– Depends upon the data source.
– Certain data source such as Microsoft SQL Server, have a cursor service of their own, whereas others, such as Microsoft Access, don’t have a cursor service.
– When open a recordset, you have to choose whether you want the data store to manage your cursor, or OLE DB and ADO to manage the cursor locally for you.
– OLE DB has its own cursor service.
Bina Nusantara
Cursors• The two options are decided by using the CursorLocation property
of either the Connection object or the Recordset object.Set this property to one of two values:– adUseServer – To let the data store manipulate the cursor– adUseClient – To let ADO manipulate the cursor
Set this property before open the connection or recordset.
Or
conn.CursorLocation = adUseServer
conn.Open strConn
rsAuthor.CursorLocation = adUseClient
rsAuthor.Open “authors”, conn
Bina Nusantara
Cursors• The default cursor is server-based.• Server cursor – the data store is responsibility to manage
the records.• Client cursor – managed by the local client cursor service
and the entire contents of the recordset are copied onto the client.
Bina Nusantara
Locking
• Locking is how we ensure the integrity of our data, making sure that changes aren’t overwritten.
• We need to avoid the classic lost update situation; where one user changes some data, and then another user changes it immediately afterwards.
• To manage this protection we lock records, just like we lock our house, and there are several different methods of ensuring that records are protected.
Bina Nusantara
Locking• Lock Types
– Read Only (adLockReadOnly = 1). The default locking type, which means the recordset is read-only, and records cannot be modified.
– Pessimistic (adLockPessimistic = 2). When amending a record, the provider will try to lock the record to guarantee the successful editing of the record. This is generally accomplished by locking the record as soon as editing takes place.
– Optimistic (adLockOptimistic = 3). The record is not locked until the changes to the record are committed to the data store, by way of the Update method.
– Batch Optimistic (adLockBatchOptimistic = 4). Batch optimistic locking allows multiple records to be modified, and the records are only locked when the UpdateBatch method is called.
Bina Nusantara
Locking• When you don’t need to modify any records, use a Read-Only
recordset.
• For general use, Optimistic is probably best, since the data store only locks the record for a small period of time – whilst it’s actually updating the data. This reduces the resources used.
• Pessimistic locking increases data integrity, but at the cost of concurrency. This is the ability of many users to view the data at once. A locked record is not available for viewing by other users, therefore concurrency is reduced.
• Optimistic locking locks the record for a shorter period of time and therefore increases concurrency, but there’s a greater chance of the data being modified by others.
Bina Nusantara
Creating Recordsets
• Creating a recordset is achieve by the open method of the Recordset object
Recordset.Open [Source], [ActiveConnection], [LockType], [Options]
Argument Description
Source The source of the data. This can be the name of the table from a database, a stored query or procedure, a SQL string, a Command object, or any other command applicable to the Provider.
ActiveConnection
The connection to use for the recordset. This can be a connection string or an open Connection object.
CursorType The type of cursor to use. This must be one of the defined cursor types. The default is adForwardOnly.
LockType The type of locking to use. This must be one of the defined lock types. The default is adLockReadOnly.
Options Tells the provider what the Source argument is – that is, whether it is a table, a text string, and so on.
Bina Nusantara
Creating RecordsetsFor example, to open a recordset on the authors table in the pubs database:Dim rsAuthorsSet rsAuthors = Server.CreateObject(“ADODB.Recordset”)rsAuthors.Open “authors”, strConn‘ Do something herersAuthor.CloseSetrsauthors = Nothing
Notice that several of the arguments have been left off. In fact, all of the argument are optional, and you could set their corresponding properties before opening the recordset:Dim rsAuthorsSet rsAuthors = Server.CreateObject(“ADODB.Recordset”)With rsAuthors .Source = “author” .ActiveConnection = strConn .CurcorType = adOpenForwardOnly .LockType = adLockReadOnly .OpenEnd With‘ Do something herersAuthor.CloseSetrsauthors = Nothing
Bina Nusantara
Creating Recordsets
Once the recordset has been opened, you are automatically placed on the first record. If there are no records in the recordset, then both BOF and EOF properties will be True
rsAuthors.Open “authors”, strConn
If rsAuthors.BOF and rsAuthors.EOF Then
‘ Recordset is empty
End If
Bina Nusantara
Creating Recordsets• The Option Argument
Allows to specify what the command text contains.It can be one of the following CommandTypeEnum constants:– adCmdText (1)– To Indicate a text command, such as a SQL String– adCmdTable (2) – To indicate the name of a table– adCmdStoredProc (4) – To indicate the name of a stored
procedure– adCmdFile (256)– To Indicate the file name of a saved recordset– adCmdTableDirect (512)– To indicate the name of a table– adCmdURLBind – To indicate a URL
The difference between adCmdTable and adCmdTableDirect is small, but if you want all columns from a table, then adCmdTableDirect is marginally faster, as ADO performs some internal optimization.
There are also 2 other options: adCmdUnspecified (-1), indicating that no type is specified, and adCmdUnknown (8), indicating that the command type is unknown. In general you won’t probably use this.
Bina Nusantara
Creating RecordsetsAdditional Options
The option argument can be one of the previous constants, but you can also add in one of the following ExecuteOptionEnum constants:– adAsyncExecute (16)– To indicate the command should be
executed asynchronously– adAsyncFetch (32)– To indicate that after the initial set of
rows has been fetched, and remaining rows are fetched asynchronously
– adAsyncFetchNonBlocking (64)– Similar to adAsyncFetch, except that the fetching of records never blocks commands
– adExecuteNoRecords (128)– To indicate that the command does not return any records
Asynchronous processing means that the actions happen in the background; so instead of running a command and waiting for it to finish (synchronously), you can run the command, and then carry on doing something else, whilst the command is executing in the background.
Bina Nusantara
Creating Recordsets
• The adExecuteNoRecords option is extremely useful. It tells ADO that the command you are executing does not return any data; therefore, there is no point in building a recordset. This can be speed up action queries.
• To add one of this options you can use the Or statement (Or = +)
• Moving Through the Recordset– MoveFirst– MoveNext– MoveLast– MovePrevious
adCmdStoredProc Or adExecuteNoRecords
adCmdStoredProc + adExecuteNoRecords
Bina Nusantara
Filtering Recordsets
• Filtering is a way of temporarily restricting the view of records in a recordset.
• Filtering with a CriteriaThe Filter property takes a variety of arguments, one of which is a criterion very much like a SQL WHERE clause:
rsAuthors.Filter = “au_lname = ‘Homer’ or au_lname = ‘Francis’ ”
rsAuthors.Filter = “au_lname LIKE ‘Ho%’ ”
rsAuthors.Filter = “state = ‘ca’ ”
rsAuthors.Filter = “”
Use the empty string to reset (clean) the filter, so that all records are once again shown:
Bina Nusantara
Filtering Recordsets• Filtering with a Constant
– adFilterNone (0)– To remove the current filter. This has the same effect as using an empty string.
– adFilterPendingRecords (1)– To view only records that have changed, but have not yet been sent to the server. This only applicable in batch update mode.
– adFilterAffectedRecords (2)– To view only records affected by the last Delete, Resync, UpdateBatch or CancelBatch method call.
– adFilterFetchedRecords (3)– To view records in the cache. This means the results of the last method call to retrieve records.
– adFilterConflictingRecords (5)– To view records that failed to update during the last batch update.
Bina Nusantara
Filtering Recordsets• Filtering with Bookmarks
The last method of filtering recordsets is to use an array of bookmarks. This allow you to build up a list of records, and then at a later date apply a filter.rsAuthors.Open “authors”, strConn, adOpenKeyset, adLockReadOnly, adCmdTableDirect
avarBkmk(0) = rsAuthors.Bookmark ‘Save bookmark for the first record
rsAuthors.MoveNext ‘Move forward two recordsrsAuthors.MoveNext
avarBkmk(1) = rsAuthors.Bookmark ‘Save Bookmark for the third record
rsAuthors.MoveLast ‘Move to the end and save the bookmarkavarBkmk(2) = rsAuthors.Bookmark
rsAuthors.Filter = Array (avarBkmk(0), avarBkmk(1), avarBkmk(2)) ‘Now apply the filter
While Nor rsAuthors.EOF ‘Now loop through the recordset Response.Write rsAuthors (“au_lname”) & “<BR>” rsAuthors.MoveNextWend
Bina Nusantara
Searching For Records
• Searching for individual is performed with the “Find” method.
• Also can use optional arguments to specify some extra option.
• If the record isn’t found, then the position is in one of two places :
rsAuthors.Find “au_lname=‘Lloyd’”
Recordset.Find Criteria, [SkipRows], [SearchDirection], [Start]
•SkipRows indicating the number of rows to skip before starting the search (default 0).•SearchDirection can be either adSearchForward or adSearchBackward•Start identifying the position from which to start the search.
•If Searching forwards, position is after the end of the recordset, and EOF is set
•If Searching backwards, position is before the start of the recordset, and BOF is set
Bina Nusantara
Searching For Records
• Using Bookmarks to Save the PositionThis re-positioning when records are not found is one place where bookmarks come in handy, since the current position bookmarked, search for a record, and if not found, move back to the saved position.‘Save the bookmark position
varBkmk = rsAuthors.Bookmark‘Find the recordrsAuthors.Find “au_lname = ‘Sussman’”‘Was it found If Not rsAuthors.EOF Then Response.Write “Found: “ & rsAuthors(“au_lname”) & “, “ & rsAuthors(“au_fname”) & “ <BR>Else Response.Write “Not found. Moving <BR>” rsAuthors.Bookmark = varBkmkEnd If
Bina Nusantara
Modifying Records
• Using the Recordset object’s methods, can use a locking type other than adLockReadOnly to be able to change the data.
• The default locking type is read-only
• Modifying can be :– Adding Records– Editing Records– Deleting Records
Bina Nusantara
Modifying Records
• Adding Records– There are two ways of using this. – The first (simply adds a new record, setting the four
mandatory fields)
With rsAuthors .Open “authors”, strConn, adOpenDynamic, adLockOptimistic, adCmdTableDirect .AddNew .Fields(“au_id”) = “123-12-1234” .Fields(“au_lname”) = “Lloyd” .Fields(“au_fname”) = “Janine” .Fields(“contract”) = 0 .UpdateEnd With
Bina Nusantara
Modifying Records
– An alternative way is to use the optional arguments of the AddNew method, which are two arrays – one of the field names and one of the fields values :
– This method doesn’t require a call to the Update method.
With rsAuthors .Open “authors”, strConn, adOpenDynamic, adLockOptimistic, adCmdTableDirect .AddNew Array (“au_id”, “au_lname”, “au_fname”, “contract”), _ Array (“123-12-1234”, “Lloyd”, “Janine”, 0)
End With
Bina Nusantara
Modifying Records
• Editing RecordsSimilar to the first method of inserting records – the difference being that you don’t need to call the AddNew method
strSQL = “SELECT * FROM authors WHERE au_lname=‘Lloyd’”
With rsAuthors
.Open strSQL strConn, adOpenDynamic, adLockOptimistic, adCmdText .Fields(“contract”) = 1 .Update
End With
Bina Nusantara
Modifying Records• Deleting Records
– To delete records, call the Delete method.– The optional argument, can be one of the AffectEnum
constants• adAffectCurrent (1)– to indicate only the current
record is deleted. This is the default action.• adAffectGroup (2)– to indicate that all records
matching the current filter are deleted.• adAffectAll (3)– to indicate all records in the recordset
are deleted.• adAffectAllChapters (4)– to indicate that records in all
chapters are deleted.rsAuthors.Delete
rsAuthors.Delete adAffectGroup
Bina Nusantara
Managing Errors• The Errors Collection
– Contains an Error Object for each error that occurred during a single ADO command.
– Multiple errors may be occur during a command, and the OLE DB provider needs a way to inform the client that more than one error has occurred.
– Two important points:• Each time ADO command is run, if an error occurs, the Errors
collection is cleared and filled with the new details. However, if no error occurred, the Errors collection is not touch. Therefore the collection may contain errors even though the ADO command completed successfully.
• The OLE DB Provider may fill the Errors collection with informational messages or warnings, which will have an error number of 0. Therefore you cannot just check the number of errors in the collection and assume that an error has occurred.
Bina Nusantara
Managing Errors
• The Errors collection is only accessible from the Connection object.
• If you haven’t explicitly created a Connection object, the Recordset object has an ActiveConnection property, which contains the Connection object for the current Recordset. You can get the Errors collection like so:
To see all errors that occur, loop through the collection:
rsAuthors.ActiveConnection.Errors
For Each errAuthorss In rsAuthors.ActiveConnection.Errors
‘ Display error
Next
Bina Nusantara
Managing Errors• Properties of the Error object
Property Description
Number The ADO error number
NativeError The error number from the data provider
SQLState The 5 digit SQL state code for the error, when connecting to SQL databases
Source The object that generated the error
Description Descriptive text of the error
For Each errAuthorss In rsAuthors.ActiveConnection.Errors Response.Write “Number: “ & errAuthors.Number & _
“<BR>NativeError: “ & errAuthors.NativeError & _ “<BR>SQL State: “ & errAuthors.SQLState & _ “<BR>Source: “ & errAuthors.Source & _ “<BR>Description: “ & errAuthors.Description& _ “<P>”
Next
Bina Nusantara
1. <HTML>2. <HEAD>3. <TITLE>ADO Errors in ASP Page</TITLE>4. </HEAD>
5. <BODY>6. <!-- #include file="dataconn.inc" -->7. <%8. On Error Resume Next ‘ The best way to check for errors
9. strSQL = "SELECT missColumn1,missColumn2, au_lname, au_fname FROM authors"
10. set rsAuthors = Server.CreateObject("ADODB.REcordset")
11. rsAuthors.Open strSQL, conn
12. If CheckForErrors(rsAuthors.ActiveConnection) = False then13. While Not rsAuthors.EOF14. Response.Write rsAuthors("au_lname") & ", " & rsAuthors("au_fname") &
"<BR>"15. rsAuthors.MoveNext16. Wend17. End if18. rsAuthors.Close19. Set rsAuthors = Nothing
20. %>21. </BODY>
Bina Nusantara
22. <SCRIPT LANGUAGE="VBScript" RUNAT="SERVER">
23. Function CheckForErrors(objConn)
24. Dim objError 'Error Object25.26. If objConn.Errors.Count >0 Then ‘Errors means the count will be greater than 027. For Each objError in objConn.errors28. If objError.Number<>0 Then 'Errors with number 0 are informational29. Response.Write "<P><TABLE BORDER=1>" & _30. "<TR><TD>Error Property</TD><TD>Contents</TD></TR>" & _31. "<TR><TD>Number</TD><TD>" & objError.Number & "</TD></TR>" & _32. "<TR><TD>NativeError</TD><TD>" & objError.NativeError & "</TD></TR>" & _33. "<TR><TD>SQLState</TD><TD>" & objError.SQLState & "</TD></TR>" & _34. "<TR><TD>Source</TD><TD>" & objError.Source & "</TD></TR>" & _35. "<TR><TD>Description</TD><TD>" & objError.Description & "</TD></TR></TABLE>"36. CheckForErrors = True37. End If38. Next39. 40. Else41. 42. CheckForErrors = False
43. End If44. End Function
45. </SCRIPT>46. </HTML>
Bina Nusantara
ADO Errors in ASP Page