Methods of Data Processing

1. Methods of Data Processing Applied Database II

2. Masterfile Master file–collection of records holding the most up to date information about something. Eg a payroll master file, a student master file, a products master file. The information in the file is permanent eg payroll number and semi permanent eg address, pay to date.

3. Transactions • transactions – events that need to be recorded to do with buying, selling and distribution of goods or services. • A transaction will store details identifying a record in the masterfile and some change to that record • E.g. AccountNo and change to Balance

4. TRANSACTION PROCESSING SYSTEM • Records transactions as they happen • An information system that records company transactions • To record, process, validate, and store transaction that take place in the various functional areas of a business for future retrieval and use A transaction is defined as an exchange between two or more business entities

5. Step in Processing a Transaction • Data entry • Data validation • Processing and revalidation • Storage • Output generation • Query support

6. A Transaction Occurs Enter data Validate data and process data into information Figure 1. Data must be processed to become useful information. This figure shows the six steps involved in processing a transaction Validate the results Generate output Facilitate queries

7. Transaction Processing Batch processing Transactions are recorded over a period of time into transaction file which is subsequently used to update the master file Interactive processing (on-line) Transaction is not stored but processed immediately (generally within 2-3 seconds)

8. Online versus Batch Processing • Online processing immediately records the transaction in the appropriate database • Batch processing collects inputs over time and enters them into the system at one time in a batch • Batch processing simplifies data communications and other processes, master files are not updated real time

9. Example: Library In a particular school library borrowing books is processed interactively returning books is processed in batch issues of fine notices is processed .. in batch (end of the month) payment of fines is processed .. interactively

10. Record Structure • What fields would you expect to find in a library’s student masterfile? • What data is required to process the borrowing of a book? • What fields might you expect be stored in the library’s transaction file?

11. Interactive Processing(on-line/real time processing) • Processing interactive transactions on a serial device is slow – not very suitable When computers where first used by businesses (e.g. LEO) batch processing was the norm and interactive processing very rare – why? • Storage devices where all serial • Computers where slower so interactive not feasible • Made use of expensive hardware overnight

12. Batch Processing • Transactions stored serially in a transaction file • Transaction file is sorted (into same order as masterfile) • Batch Transaction Processing • Gathering source documents originated by business transactions, such sales order, into groups called batches • Recording transaction data on an input medium • Sorting the transactions in a transaction file and a variety of documents and reports • Processing transaction data and creating an updated master file • Capturing and sorting batches of transaction data at remote sites, and then transmitting it periodically to a central computer for processing (RJE)

13. Master file (Day 1) Transactions for Day 1 (Grandfather) Batch Transaction Update Process Transactions for Day 2 Master File (Day 2) (Father) Batch Transaction Update Process Master File (Day 3) (Son) File Generation Backup – Grandfather-father-son

14. Hit Rate Hit rate is the percentage of master file records that are updated over a period of time. e.g. Daily Hit Rate for returned books  1% (or less) Daily Hit Rate for Banking transactions  66% High Hit rate  Use Batch Processing Low Hit rate  Use Interactive Processing

15. On-line – Off-line • On-line means connected • Off-line means not connected In Processing terms this refers to being connected (or not) to the master file. If transactions occur in a location that is off-line, which type of processing in not possible? • Interactive processing is only possible on-line • In many batch systems transactions will be collected in a number of locations and then brought together for sorting [and subsequent processing]

16. Questions • Name some systems where processing is done • In batch • Interactively • Partly interactive, partly batch • Name a system where interactive processing is essential and explain why. • Name a system where transactions are collected in a number of remote (off-line) places and then processed together in a batch. • Does an ATM machine do batch or interactive processing, give reasons for your answer(s).

17. Batch Use of processing power overnight No human interaction (cheaper) Collect transaction off-line More efficient for high hit rate Some Processing naturally takes place at end of period of time Inherent backups Interactive Masterfile is always up to date Get instant response Quicker for low hit rate Comparison of Batch and Interactive processing

18. ADO (Active Data Object) • ADO Objects • Command • The Command object defines the command to be given to a data source. This may be either a SQL statement or an invokation of a stored procedure. • Connection • The Connection object may be used by the Command and Recordset objects to provide a session with the data source. • Error • The Error object may be used if the OLE DB provider has implemented it. In its absence, errors are raised in the Visual Basic Err object. • Field • The Field object represents a column in a Recordset object.

19. ADO Objects • Parameter • The Parameter object is implemented as a collection and stores the parameters to be used by a Command object. • Property • The Property object may be used by the Connection, Command, Recordset, and Field objects. Each of the objects has a set of unique properties that either describe or control the behaviour of the object. The properties may either be built-in, or added to the Properties collection by the data provider. • Recordset • The Recordset object contains a set of records retrieved from the Connection object.

20. Data Connection using ADO Method Public Function clsData_Connect() As Boolean Dim strConnect As String On Error GoTo ADOConnectErr Set cnADO = New ADODB.Connection ‘OLE DB provider for SQL Server 7.0 'strConnect = "uid=sa;server=kcweb;database=pubs" 'cnADO.Provider = "SQLOLEDB" 'SQL Server 7.0 'MDSASQL OLE DB provider for ODBC strConnect = "driver={SQL Server};uid=sa;server=(local);database=pubs" cnADO.Provider = "MSDASQL" 'this is the default cnADO.ConnectionString = strConnect cnADO.ConnectionTimeout = 10 cnADO.CursorLocation = adUseNone 'open the connection cnADO.Open clsData_Connect = True Exit Function ADOConnectErr: MsgBox Err.Description clsData_Connect = False End Function

21. Data Update using Batch Method Public Function clsData_ExecQBatch() As Boolean Dim rsResults As ADODB.Recordset Dim myRows As Variant Dim myHeadings() As String Dim i As Integer On Error GoTo ADOExecSPBatchErr Set rsResults = New ADODB.Recordset //Pada ADO buka recordset menggunakan adLockBatchOptimistic dengan kursor sisi //Client dan tipe kursor Static atau Keyset rsResults.CursorLocation = adUseClient rsResults.Open "SELECT * FROM authors", _ cnADO, adOpenStatic, adLockBatchOptimistic, adCmdText

22. Continue 'At this point you can disconnect the recordset Set rsResults.ActiveConnection = Nothing If rsResults.EOF And rsResults.BOF Then MsgBox "No rows returned" Else 'Populate column headings For i = 0 To rsResults.Fields.Count - 1 ReDim Preserve myHeadings(2, i) myHeadings(1, i) = rsResults.Fields(i).Name myHeadings(2, i) = rsResults.Fields(i).DefinedSize Next 'Get the data and populate the grid myRows = rsResults.GetRows(adGetRowsRest) DisplayData myRows, myHeadings, _ frmDataTest.lvRows, frmDataTest.TextWidth("n")

23. Continue With rsResults .MoveFirst .Fields(1).Value = InputBox("Enter a new last name for the first record") .MoveNext .Fields(1).Value = InputBox("Enter a new last name for the second record") End With 'And here reconnect // Mengaktifkan koneksi sebelum memanggil Metode UpdateBatch Set rsResults.ActiveConnection = cnADO // adAffectAll adalah parameter untuk memerintahkan metode UpdateBatch untuk // mengirimkan semua data record yang sudah dimodifikasi ke server rsResults.UpdateBatchadAffectAll rsResults.Filter = adFilterAffectedRecords

24. Continue If rsResults.RecordCount = 0 Then 'no conflicts Else Do While Not rsResults.EOF 'this property will tell you why 'the row was rejected adRecUnmodified = 8 is OK // Untuk mengatasi jika terjadi collision (server menolak update data) If rsResults.Status <> adRecUnmodified And _ rsResults.Status <> adRecOK Then 'this shows the value that is currently in 'the database For i = 0 To rsResults.Fields.Count - 1 If rsResults.Fields(i).UnderlyingValue _ <> rsResults.Fields(i).Value Then

25. Continue 'show the current data from the database MsgBox rsResults.Fields(i).UnderlyingValue End If Next End If rsResults.MoveNext Loop End If rsResults.Filter = adFilterNone rsResults.Requery myRows = rsResults.GetRows(adGetRowsRest) 'close the result set rsResults.Close Set rsResults = Nothing

26. Continue 'Populate the grid DisplayData myRows, myHeadings, _ frmDataTest.lvRows, frmDataTest.TextWidth("n") End If clsData_ExecQBatch = True Exit Function ADOExecSPBatchErr: MsgBox Err.Description clsData_ExecQBatch = False End Function

27. Transaction – Update with ADO Public Function clsData_ExecTranUpdate() Dim rsResults As ADODB.Recordset Dim myRows As Variant Dim myHeadings() As String Dim i As Integer Dim strNewName As String Dim strNewName2 As String On Error GoTo ADOExecTranUpdateErr Set rsResults = New ADODB.Recordset rsResults.CursorLocation = adUseClient rsResults.Open "SELECT * FROM authors", _ cnADO, adOpenKeyset, adLockOptimistic, adCmdText

28. Continue If rsResults.EOF And rsResults.BOF Then MsgBox "No rows returned" Else 'Populate column headings For i = 0 To rsResults.Fields.Count - 1 ReDim Preserve myHeadings(2, i) myHeadings(1, i) = rsResults.Fields(i).Name myHeadings(2, i) = rsResults.Fields(i).DefinedSize Next 'Get the data and populate the grid myRows = rsResults.GetRows(adGetRowsRest) DisplayData myRows, myHeadings, _ frmDataTest.lvRows, frmDataTest.TextWidth("n") strNewName = InputBox("Enter a new last name for the first record") strNewName2 = InputBox("Enter a new last name for the second record")

29. Continue 'Begin a transaction cnADO.BeginTrans //Manipulasi recordset menggunakan koleksi objek Fields. Transaksi pada server //terhadap multiple record Metode ADO lebih sederhana daripada DAO dan RDO, karena pada ADO tidak perlu memanggil metode EDIT dan UPDATE berkali-kali seperti yang dilakukan pada DAO dan RDO With rsResults .MoveFirst .Fields(1).Value = strNewName .MoveNext .Fields(1).Value = strNewName2 .Update 'this changes the data on the server but End With 'does not commit the changes. In SQL Server

30. Continue 'the pages will also be locked cnADO.CommitTrans MsgBox "Changes were saved" rsResults.Requery myRows = rsResults.GetRows(adGetRowsRest) 'close the result set rsResults.Close Set rsResults = Nothing 'Populate the grid DisplayData myRows, myHeadings, _ frmDataTest.lvRows, frmDataTest.TextWidth("n") End If

31. Continue clsData_ExecTranUpdate = True Exit Function ADOExecTranUpdateErr: MsgBox Err.Description clsData_ExecTranUpdate = False End Function Prosedur DisplayData (Modul – basGlobal) Public Sub DisplayData(pRows As Variant, pHeadings As Variant, pListView As ListView, pCharsize As Single) Dim i As Integer Dim y As Integer Dim itmx As ListItem pListView.ListItems.Clear pListView.ColumnHeaders.Clear

32. Continue For i = 0 To UBound(pHeadings, 2) pListView.ColumnHeaders.Add , , pHeadings(1, i) pListView.ColumnHeaders(i + 1).Width = _ pHeadings(2, i) * pCharsize Next For i = 0 To UBound(pRows, 2) 'load the list view Set itmx = pListView.ListItems.Add(, , pRows(0, i)) For y = 1 To UBound(pRows, 1) itmx.SubItems(y) = pRows(y, i) & "" Next Next End Sub