Database Management Systems

1. Database Management Systems Chapter 7 Calculations and Data Manipulation

2. Variables Computations Standard Functions Debug Output Input Conditions Loops Arrays Data on Forms Properties Events DoCmd: Internal Commands Data Transfer Across Forms Data Lookup Programming SQL Database access programming Functions & statements grouped by task Database Programming

3. Should you save totals in the database or just use queries and reports to obtain the values as needed? Queries and reports are the preferred method. Easier to create. Always have current values. But sometimes totals are stored to improve performance. If data rarely changes, faster to compute it once (e.g., sales tax). Calculations can lead to difficult queries. Also, totals are sometimes stored to prevent them from being changed automatically. Computing Totals

4. Create code (1) In forms and reports (2) Within the query system (3) Hosted in external programs Programming Environment DBMS Tables Queries (2) If ( . . ) Then SELECT . . . Else . . . UPDATE . . . End If External Program Forms & Reports (3) C++ if (. . .) { // embed SQL SELECT … } (1) If (Click) Then MsgBox . . . End If

5. Before Windows Your code did all of the work. Programmer In complete control. Code ran from top to bottom. With Windows Structure and interface are defined by Windows standards. Event-driven. Code is in small pieces that are called as needed, when some event occurs. Windows Form On_Click MsgBox . . . On_Change UPDATE . . . Windows Environment Programming Appendix Monolithic Code Start here Do this routine If (. . .) Then print . . . End If Wait for input More routines End here Event/Trigger

6. Simple assignment [control] = value value = [control] Naming conventions Is it a variable or a control? Full names Forms![myform]![ctlTax] Null values: IsNull([control]) Form Code Sub ComputeTax Total = … taxRate = ... [ctlTax] = taxRate*Total End Sub After Update 32.50 ctlTax Data on Forms

7. Set properties with code Examples Visible/Invisible Locked/Unlocked Combo box RowSource Methods SetFocus Undo Requery Code examples [control].Visible = False [control].Locked = False [control].SetFocus [combo].RowSource= “SELECT ... FROM . . .” Employee Control Properties and Methods Sub Employee_AfterUpdate() If (Employee = “manager”) Then [cmdSalary].Visible = True [cmdSchedule].Visible = True Else [cmdSalary].Visible = False [cmdSchedule].Visible = False End If End Sub

8. Full names: Forms! [formname1]! [control1] Forms must be open Form1.Visible = True Subforms: Forms! [formname1]! [subform].Form! [control1] Transfer Data Across Forms

9. Forms Record Source Caption Default View Menu/Scroll Bars Navigation/Selection Size/Center Pop Up/Modal/Border Controls Name Control Source Format/Decimal/Default Controls Input Mask Validation Rule/Text Status Bar Auto Tab/Enter Visible Enabled/Locked Tab Stop/Index Size, Position Back/Fore Color/Effects Border Font Common Properties

10. Forms Current Before/After Insert Before/After Update Delete Before/After Del Confirm Open/Close Load/Unload Activate/Deactivate Got/Lost Focus Click/Double Click Error/Timer Controls Before/After Update Change Enter/Exit Got/Lost Focus Click Double Click Mouse Key Down/Up/Press Common Events

11. See help system Common uses FindNext FindRecord GoToControl GoToPage GoToRecord Hourglass Maximize/Minimize OpenForm OpenQuery OpenReport Print Quit RunApp (Shell) RunSQL SetWarnings TransferDatabase TransferSpreadsheet DoCmd: Internal Commands

12. Select Into New table Insert Into Append rows Update Change data Delete Delete rows Operate on sets of data Use String for Where Use DoCmd RunSQL Programming: SQL

13. ADO Update • Changes set of values--based on Where • Syntax • Update table SET col1=val1, col2=val3 Where condition • Examples Dim cnn as ADODB.Connection Dim cmd as ADODB.Command Set cnn = CurrentProject.Connection Set cmd = CreateObject(“ADODB.Command”) cmd.ActiveConnection = cnn cmd.CommandType = adCmdText strSQL = “Update Order Set EstShipDate=OrderDate+3” cmd.CommandText = strSQL cmd.Execute

14. Employee Table Change titles Create a form Old title (combo) New title Command button SQL SQL Example: Employee Titles EID Name Phone Title 2298 Adams 2253 Manager 9983 Cuervo 9973 Supervisor 2736 Dubai 3385 Worker Manager Go Team Leader UPDATE Employee SET Title = " Team Leader " WHERE Title = " Manager ”;

15. SQL Example: New Titles UPDATE Employee SET Title = "Team Leader" WHERE Title = "Manager"; Manager Go Team Leader Sub cmdGo_Click Build Update command in a String, using values from OldTitle and NewTitle. Execute the command. End Sub

16. SQL Update Example SQL Sample UPDATE Employee SET Title = "“Team Leader " WHERE Title = "Manager"; Sub cmdGo_AfterUpdate Dim strSQL As String, qt as String Dim cnn As ADODB.Connection, cmd As ADODB.Command q = “’” ‘ single quotation mark strSQL = "UPDATE Employee SET Title = " & q & [txtNewTitle] & q _ & " WHERE Title = " & q & [cboOldTitle] & q & " ; " Set cnn = CurrentProject.Connection Set cmd = CreateObject(“ADODB.Command”) cmd.ActiveConnection = cnn cmd.CommandType = adCmdText cmd.CommandText = strSQL cmd.Execute End Sub

17. SQL UPDATE (Oracle and SQL Server) UPDATE Employee SET Title = "Team Leader " WHERE Title = "Manager"; Oracle PROCEDURE ChangeTitle(oldTitle IN VARCHAR2(50), newTitle IN VARCHAR2(50)) IS UPDATE Employee SET Title = newTitle WHERE Title = oldTitle; END ChangeTitle; SQL Server CREATE PROCEDURE ChangeTitle(@oldTitle VARCHAR(50), @newTitle VARCHAR(50)) AS UPDATE Employee SET Title = @newTitle WHERE Title = @oldTitle GO END

18. Debug your Query

19. LastName FirstName Masi Jorge Insert Into (1) • Adds rows to a table • Syntax • Insert Into table (col1, col2, …) • Values (value1, value2, … CID Last First Phone … 938 Sosa Javier 8474 Masi Jorge • strSQL = "Insert Into [Customer] (Last, First) " • strSQL = strSQL & " Values ( " & q & [ctlLast] & q • strSQL = strSQL & " , " & q & [ctlFirst] & q & “ )" • cmd.CommandText = strSQL • cmd.Execute

20. Copies data rows to a second table Syntax INSERT INTO {new table, columns} SELECT {any SQL} Example, move all customers who have not placed orders recently. Let users define “recently” by picking the number of days. Insert Into (2) SQL INSERT INTO OldCustomer SELECT * FROM Customer WHERE CustomerID NOT IN (SELECT CustomerID FROM Order WHERE (Odate > Date() - x);

21. Code Example for Insert CustomerID Name Phone 7763 Juarez 9987 3635 Kramer 2285 4456 Ciaro 8474 CustomerID Name Phone 7763 Juarez 9987 … Customers who have not placed an order within some time frame. O# C# Odate 9987 3635 02-15-01 2275 4456 05-23-01 Dim strSQL strSQL = “INSERT INTO OldCustomer” strSQL = strSQL & “ SELECT * FROM Customer WHERE” strSQL = strSQL & “ CustomerID NOT IN” strSQL = strSQL & “ (SELECT CustomerID FROM Order” strSQL = strSQL & “ WHERE (Odate > Date() - “ strSQL = strSQL & [txtDays] cmd.CommandText = strSQL cmd.Execute Time frame (txtDays) is given by user.

22. strWhere = “CustomerID NOT IN (SELECT CustomerID FROM Order” strWhere = strWhere & “ WHERE (ODate > Date() – “ & [txtDays] & “)” strSQL = “INSERT INTO OldCustomer” strSQL = strSQL & “ SELECT * FROM Customer WHERE “ & strWhere cmd.CommandText = strSQL cmd.Execute strSQL = “DELETE FROM Customer WHERE “ & strWhere cmd.CommandText = strSQL cmd.Execute Delete • Delete a set of rows that match a condition • Syntax: Delete From table Where condition • Cascade on Delete! • Example: Move old customer data

23. Syntax D... (expr, domain, criteria) “Column” “Table” “Where Clause” Functions DAvg, DCount, DFirst, DLast, DMin, DMax, DStDev, DStDevP, DSum, DVar, DVarP Dlookup Usually better to use SQL. Or to write DAO code. Data Lookup Commands: D... Column Table V = DSum(“BalanceDue”, “Customer”, “City=‘Chicago’”) Where Clause

24. Purpose Track through table or query one row at a time. Data cursor/pointer to active row. Why? Performance. SQL cannot do everything. Complex calculations. Compare multiple rows. Row-Level Object Programming Year Sales 1998 104,321 1999 145,998 2000 276,004 2001 362,736 1998 104,321 1999 145,998 2000 276,004 2001 362,736 MoveNext MovePrevious MoveFirst MoveLast Move Test for Beginning and End of File

25. ADODB Recordset Initialization Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim strSQL As String Set cnn = CurrentProject.Connection Set rst = CreateObject("ADODB.Recordset") strSQL = "SELECT LastName, FirstName FROM Customer" rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly Do Until rst.EOF ‘ Do something with the data rst.MoveNext Loop rst.Close Access 2000

26. Problems with Multiple Users Original Data Modified Data Name Sales Alice 444,321 Carl 254,998 Donna 652,004 Ed 411,736 Name Sales Alice 444,321 Neal 333,229 Carl 254,998 Donna 652,004 Ed 411,736 New row is added--while code is running. rst.Open “Data”, cnn, , , adCmdTableDirect Value1 = rst(“Sales”) ‘ Alice (444,321) rst.MoveNext ‘ Carl … rst.MovePrevious ‘ ??? Which row

27. Move Commands .MoveNext .MovePrevious .MoveFirst .MoveLast .Move nRows Location tests BOF EOF Bookmarks Dim MyMark As String MyMark = rst.Bookmark (Save position) .Move . . . (Move somewhere else) rst.Bookmark = MyMark (Return to mark) Table Locator Commands Name Sales Alice 444,321 Neal 333,229 Carl 254,998 Donna 652,004 Ed 411,736 Save position bmk = rst.Bookmark rst.MoveNext rst.Bookmark = bmk

28. rst.Find “condition” Inefficient: Sequential search Use SQL instead rst.EOF ‘ True if not found rst.Seek value, adSeekFirstEQ ADO: Recordset Find & Seek Commands rst.Open “Table”,cnn, adOpenDynamic ,adLockReadOnly, _ adCmdTableDirect rst.Index = “PrimaryKey” rst.Seek keyvalue, adSeekFirstEQ If (not rst.EOF) Then ‘ Make changes End If

29. Sample ADO Code to Change Data Dim cnn as ADODB.Connection Dim rst As ADODB.Recordset Set cnn = CurrentProject.Connection Set rst = CreateObject("ADODB.Recordset") rst.Open strSQL, cnn, adOpenDynamic, adLockOptimistic Do Until (rst.EOF) rst(“BalanceDue”) = rst(“BalanceDue”)*(1 + [PctIncrease] ) rst.Update rst.MoveNext Loop rst.Close Add a 10% charge to the BalanceDue for every customer. The 10% value is entered on the form by the user. What happens if the row is already locked? Normally use SQL instead.

30. Errors are events Simple code: Display error and exit MsgBox Err.Description, , “Title” Resume Exit_Label Event Options On Error Goto [label] On Error Goto 0 On Error Resume Next Resume Options Resume Resume Next Resume label Error Handling On Error Goto ErrSub1 Program code ExitSub1: Exit Sub ErrSub1: MsgBox Err.Description,,”Errors” Resume ExitSub1 Error occurs

31. Alternate Error Handling Sub mySubroutine On Error Resume Next … err.Clear ‘ clears error object rst.Open strSQL, cnn If (err.Number <> 0) Then … Handle the error End If Exit Sub Error occurs, skip to next line. Test for error, handle it.

32. Concurrent Access Multiple users or processes changing the same data at the same time. Final data will be wrong! Force sequential Locking Delayed, batch updates Two processes Receive payment ($200) Place new order ($150) Initial balance $800 Result should be $800 -200 + 150 = $750 Interference result is either $600 or $950 Concurrent Access Customers Receive Payment Place New Order ID Balance Jones $800 $600 $950 1) Read balance 800 2) Subtract pmt -200 4) Save new bal. 600 3) Read balance 800 5) Add order 150 6) Write balance 950

33. Deadlock Two (or more) processes have placed locks on data and are waiting for the other’s data. Many solutions Random wait time Global lock manager Two-phase commit - messages Deadlock 1) Lock Data A 3) Wait for Data B Data A Data B 2) Lock Data B 4) Wait for Data A

34. Lock Manager

35. Concurrent Access Customer Balance Adams 152.35 Brown 315.81 Jones 115.67 Using & locked Running the program to add interest charges generates an error message. Do until rst.EOF rst(“Balance”) = rst(“Balance”)*(1.0+rate) rst.Update rst.MoveNext Loop

36. Errors and Locks On Error Goto ErrSub1 … rst(“Balance”) = rst”(Balance”)*1.10 rst.Update … ExitSub1: Exit Sub ErrSub1: If ( MsgBox (Err.Description, vbRetryCancel, _ "Error (RS)” ) = vbRetry ) Then Resume Else Resume ExitSub1 End If If the table is locked, Edit will cause an error. Let the user retry the edit or exit. To do it automatically, wait for a random number of seconds.

37. Optimistic Locks • Assume that collisions are rare • Improved performance, fewer resources • Allow all code to read any data (no locks) • When code tries to write a new value • Check to see if the existing value is different from the one you were given earlier • If it is different, someone changed the database before you finished, so it is a collision--raise an error • Reread the value and try again

38. Optimistic Locks for Simple Update (1) Read the balance (2) Add the new order value (3) Write the new balance (4) Check for errors (5) If there are errors, go back to step (1).

39. Post’s Picky Programming • Use a naming convention. • Use proper indentation. • Comment your work. • Avoid spaces in variable names. • Use Option Explicit. • Recompile constantly. • Use as many parentheses as possible. • Split complex conditions. • Make it easy for the user. • Use the status bar and tool tips. • All code must be subject to error trapping. • Use Retry with rst.Edit sections. • Use subroutines and functions to simplify. • Keep backup copies. • Never use a raw number--use Const. • Remember that databases can be moved. • Test applications on different hardware. • Test all calculations by hand.

40. Comments Weak comments dblSum = 0# ' Initialize the accumulator Do While Not rst.EOF ' Loop through the table dblSum = dblSum + rst(“Balance”) ' Accumulate the balance rst.MoveNext ' Move to the next row Loop ' End the loop ' Need to compute total balance from sales ' Will use a loop instead of SQL ' Because some conditions will be added later dblSum = 0# Do While Not rst.EOF ' Add condition when user provides it, for example ' If this customer has more than three sales past due, ' only count the three most recent (write off the older ones) dblSum = dblSum + rst(“Balance”) rst.MoveNext Loop Useful comments

41. Sally’s Pet Store Main Switchboard Employee logs in. Buttons are presented based on the management level of the employee. Accounting Marketing Employees Purchasing forms are accessible by this employee. Not available to this employee.

42. Sally’s Pet Store: Switchboard Logic Event: EmployeeID AfterUpdate On Error Goto ErrEIDAU Declare variables. Lookup assigned ManagementLevel of employee. Get Management levels for each section. Make two sections of buttons invisible. If (MgtLevel > Level1) Then Make first section of buttons visible. If (MgtLevel > Level2) Then make second section of buttons visible. End If End If ExitEIDAU: Exit Sub ErrEIDAU: MsgBox Resume ExitEIDAU

43. Sally’s Pet Store: Switchboard Code Private Sub EmployeeID_AfterUpdate() On Error GoTo ErrEIDAU Dim varLevel, varMGTLEVEL1, varMGTLEVEL2 If Not IsNull(EmployeeID) Then varLevel = DLookup("EmployeeLevel", "Employee", _ "EmployeeID=" & [EmployeeID]) If Not IsNull(varLevel) Then varMGTLEVEL1 = DLookup("Value", "Preferences", _ "KeyID=" & qt & "MGTLEVEL1" & qt) varMGTLEVEL2 = DLookup("Value", "Preferences", _ "KeyID=" & qt & "MGTLEVEL2" & qt) End If End If cmdAnimalPurchase.Visible = False cmdMerchandisePurchase.Visible = False cmdInventory.Visible = False cmdAccounting.Visible = False cmdMarketing.Visible = False cmdEmployees.Visible = False

44. Sally’s Pet Store: Switchboard Code If (varLevel > Val(varMGTLEVEL1)) Then cmdAnimalPurchase.Visible = True cmdMerchandisePurchase.Visible = True cmdInventory.Visible = True If (varLevel > Val(varMGTLEVEL2)) Then cmdAccounting.Visible = True cmdMarketing.Visible = True cmdEmployees.Visible = True End If End If ExitEIDAU: Exit Sub ErrEIDAU: MsgBox Err.Description, , "Unexpected Error (EIDAU)" Resume ExitEIDAU End Sub

45. Sally’s Pet Store: Employees Enter a ZIP code and the form tries to find a matching city. Choose a city and the ZIP code is entered automatically. Spin buttons can be used to set employee level.

46. Sally’s Pet Store: Employee Spin Button Private Sub SpinLevel_SpinDown() If IsNull(EmployeeLevel) Then EmployeeLevel = 0 Else If (EmployeeLevel > 0) Then EmployeeLevel = EmployeeLevel - 1 End If End Sub Private Sub SpinLevel_SpinUp() If IsNull(EmployeeLevel) Then EmployeeLevel = 1 Else If (EmployeeLevel < 255) Then EmployeeLevel = EmployeeLevel + 1 End If End Sub

47. Sally’s Pet Store: City Private Sub CityID_AfterUpdate() On Error GoTo ErrCIDAU If IsNull([ZipCode]) Then [ZipCode] = DLookup("ZipCode", "City", "CityID=" & [CityID]) End If ExitCIDAU: Exit Sub ErrCIDAU: MsgBox Err.Description, , "Unexpected Error (CIDAU)" Resume ExitCIDAU End Sub ‘ Requires a large city table. ‘ Do not replace an existing ZipCode entry.

48. Sally’s Pet Store: ZipCode Private Sub Zipcode_AfterUpdate() On Error GoTo ErrZCAU Dim strZipShort As Variant, newCityID As Variant strZipShort = Get5DigitZipCode(ZipCode) newCityID = DLookup("CityID", "City", _ "ZipCode=" & qt & strZipShort & qt) If Not IsNull(newCityID) Then [CityID] = newCityID End If ExitZCAU: Exit Sub ErrZCAU: MsgBox Err.Description, , "Unexpected Error (ZCAU)" Resume ExitZCAU End Sub ‘ City table only uses 5 digit codes. ‘ But we need to store 9 digits in ZipCode.

49. Primary Data Types DECIMAL(precision, scale) precision: Number of digits scale: Round-off point DECIMAL(7,4): 123.4567 INTEGER BIT Yes/No CHAR Fixed length string VARCHAR Variable length string IMAGE Binary data DATETIME Appendix: SQL Server: Data Types

50. Appendix: SQL Server Structure CREATE PROCEDURE myProcedure(@oldProjectID integer) AS BEGIN DECLARE myVar decimal SET @myVar = @oldProjectID IF (@myVar > 10) BEGIN END END