20 Persistent data storage relational databases and ADO
20 – Persistent data storage: relational databases and ADO Mark Dixon Page 1
Questions: Session variables • Write a line of VB code to put 74 into a session variable called score. Session("score") = 74 • Write VB code that adds 1 to a variable called g, when a session variable called i is over 25. If Session("i") > 25 Then g = g + 1 End If Mark Dixon Page 2
Question: Self-Contained • Are the following routines self contained? Dim g As Double Dim w As Double Sub Square(By. Ref res As Double, By. Val n 1 As Double) res = n 1 * n 1 End Sub Function u(num As Double) As Double Return num * (w + g) End Function Mark Dixon Page 3
Admin: SQL Book Gennick J (2006) SQL Pocket Guide (2 nd edition). O'Reilly. ISBN: 0 -596 -52688 -1 Mark Dixon Page 4
Session Aims & Objectives • Aims – To introduce the fundamental ideas involved in using relational databases for persistent data storage • Objectives, by end of this week’s sessions, you should be able to: – create a relational database table – create a web page (ASP) that displays data from a single table in a database • using Active. X Data Objects (ADO) – use SQL in your programs to create more complex record-sets Mark Dixon Page 5
Persistent Data Storage • So far – programs (web-pages) lose data when closed • Not realistic – typically data stored to persistent storage device (e. g. hard disk, key drive, floppy disk, CD-RW) • Use either – flat files – database (relational, or object oriented) Mark Dixon Page 6
Example: People (analysis) SPECIFICATION • User Requirements – need to have access to people's details • Software Requirements – Functional: – Display list of people from a database – Non-functional should be viewable anywhere in the world Mark Dixon Page 7
Example: People (Database) • Information organised into – tables (e. g. person) – fields (e. g. phone) – records (e. g. 1 Dixon Mark 01752 232556 …) Person ID 1 2 3 Mark Dixon Surname Dixon Smith Jones Field Record Forenames Mark John Sally Phone 01752 232556 01752 111111 01752 888888 Gender Male Female Page 8
Questions: Music (Database) • How many fields? • How many records? 3 9 Track Mark Dixon Track Title Artist Name Country Paranoid Black Sabbath UK Falling in Love Aerosmith US Pink Aerosmith US Love in an Elevator Aerosmith US Smooth Criminal Alien Ant Farm US Meaning of Life Disturbed US The Game Disturbed US Voices Disturbed US Down with the Sickness Disturbed US Page 9
DBMS • Database Management Systems (DBMS) provide facilities for: – creating and changing databases • add/remove records • add/remove fields • add/remove data – For example: • • • Mark Dixon Microsoft Access d. Base Borland Paradox My. SQL Microsoft SQL Server Oracle home/small business large scale Page 10
MS Access Mark Dixon Music database Page 11
Active. X Data Objects • Active. X Data Objects (ADO) – common database interface • allow you to write code for any DBMS MS Access VB or VB Script code DB front end Mark Dixon ADO MS SQL Server … … Page 12
Using Record Sets Connection string – identify database Const cs = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=D: People. mdb; " Dim rs As Object rs = Create. Object("ADODB. Recordset") Open record set with table rs. Open("Person", cs) Do Until rs. EOF() … rs. Move. Next() Loop rs. Close() rs = Nothing Mark Dixon Move to next record Close record set Page 13
ADO Record Set Object • Used to interact with tables • Properties – BOF: true if at start of record set (before first record) – EOF: true if at end of record set (after last record) – Fields: used to get and set data values • Methods – – – Mark Dixon Open: used to open record set Move. First: moves focus to first record Move. Previous: moves focus to previous record Move. Next: moves focus to next record Move. Last: moves focus to last record Close: closes record set Page 14
Connection Strings: UDL files • Generate connection strings – Right click on desktop – Select New, Text Document – Rename to *. UDL (Yes to warning message) – Double click – Select provider (MS Jet 4. 0 OLE DB Provider) – Click Next – Select or enter DB name – Click Test Connection button – Click OK – Open with Notepad, cut & paste text Mark Dixon Page 15
Example: People (rs) People. as px rs Sub Page_Load() Const cs = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=D: People. mdb; " Dim rs As Object Dim s As String rs = Create. Object("ADODB. Recordset") rs. Open("Person", cs) s = "" Do Until rs. EOF() s = s & rs. Fields("Surname"). Value & " " rs. Move. Next() Loop par. Data. Inner. Html = s rs. Close() rs = Nothing End Sub Mark Dixon Page 16
Example: People (s) People. as px rs Sub Page_Load() Const cs = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=D: People. mdb; " Dim rs As Object Dim s As String s rs = Create. Object("ADODB. Recordset") rs. Open("Person", cs) s = "" Do Until rs. EOF() s = s & rs. Fields("Surname"). Value & " " rs. Move. Next() Loop par. Data. Inner. Html = s rs. Close() rs = Nothing End Sub Mark Dixon Page 17
Example: People (recordset) People. as px rs Sub Page_Load() Const cs = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=D: People. mdb; " Dim rs As Object Dim s As String s rs = Create. Object("ADODB. Recordset") rs. Open("Person", cs) s = "" Do Until rs. EOF() s = s & rs. Fields("Surname"). Value & " " rs. Move. Next() Loop par. Data. Inner. Html = s rs. Close() rs = Nothing End Sub Mark Dixon Page 18
Example: People (Open) People. as px rs Sub Page_Load() Const cs = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=D: People. mdb; " Dim rs As Object Dim s As String s rs = Create. Object("ADODB. Recordset") rs. Open("Person", cs) s = "" Do Until rs. EOF() s = s & rs. Fields("Surname"). Value & " " rs. Move. Next() Loop par. Data. Inner. Html = s rs. Close() rs = Nothing End Sub Mark Dixon Page 19
Example: People. as px rs Sub Page_Load() Const cs = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=D: People. mdb; " Dim rs As Object Dim s As String s rs = Create. Object("ADODB. Recordset") rs. Open("Person", cs) s = "" Do Until rs. EOF() s = s & rs. Fields("Surname"). Value & " " rs. Move. Next() Loop par. Data. Inner. Html = s rs. Close() rs = Nothing End Sub Mark Dixon Page 20
Example: People (EOF) People. as px rs Sub Page_Load() Const cs = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=D: People. mdb; " Dim rs As Object Dim s As String s rs = Create. Object("ADODB. Recordset") rs. Open("Person", cs) s = "" Do Until rs. EOF() s = s & rs. Fields("Surname"). Value & " " rs. Move. Next() Loop par. Data. Inner. Html = s rs. Close() rs = Nothing End Sub Mark Dixon Page 21
Example: People. as px rs Sub Page_Load() Const cs = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=D: People. mdb; " Dim rs As Object Dim s As String s rs = Create. Object("ADODB. Recordset") Dixon rs. Open("Person", cs) s = "" Do Until rs. EOF() s = s & rs. Fields("Surname"). Value & " " rs. Move. Next() Loop par. Data. Inner. Html = s rs. Close() rs = Nothing End Sub Mark Dixon Page 22
Example: People (Move. Next) People. as px rs Sub Page_Load() Const cs = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=D: People. mdb; " Dim rs As Object Dim s As String s rs = Create. Object("ADODB. Recordset") Dixon rs. Open("Person", cs) s = "" Do Until rs. EOF() s = s & rs. Fields("Surname"). Value & " " rs. Move. Next() Loop par. Data. Inner. Html = s rs. Close() rs = Nothing End Sub Mark Dixon Page 23
Example: People (Loop) People. as px rs Sub Page_Load() Const cs = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=D: People. mdb; " Dim rs As Object Dim s As String s rs = Create. Object("ADODB. Recordset") Dixon rs. Open("Person", cs) s = "" Do Until rs. EOF() s = s & rs. Fields("Surname"). Value & " " rs. Move. Next() Loop par. Data. Inner. Html = s rs. Close() rs = Nothing End Sub Mark Dixon Page 24
Example: People (EOF) People. as px rs Sub Page_Load() Const cs = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=D: People. mdb; " Dim rs As Object Dim s As String s rs = Create. Object("ADODB. Recordset") Dixon rs. Open("Person", cs) s = "" Do Until rs. EOF() s = s & rs. Fields("Surname"). Value & " " rs. Move. Next() Loop par. Data. Inner. Html = s rs. Close() rs = Nothing End Sub Mark Dixon Page 25
Example: People. as px rs Sub Page_Load() Const cs = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=D: People. mdb; " Dim rs As Object Dim s As String s rs = Create. Object("ADODB. Recordset") Dixon />Smith rs. Open("Person", cs) s = "" Do Until rs. EOF() s = s & rs. Fields("Surname"). Value & " " rs. Move. Next() Loop par. Data. Inner. Html = s rs. Close() rs = Nothing End Sub Mark Dixon Page 26
Example: People (Move. Next) People. as px rs Sub Page_Load() Const cs = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=D: People. mdb; " Dim rs As Object Dim s As String s rs = Create. Object("ADODB. Recordset") Dixon Smith rs. Open("Person", cs) s = "" Do Until rs. EOF() s = s & rs. Fields("Surname"). Value & " " rs. Move. Next() Loop par. Data. Inner. Html = s rs. Close() rs = Nothing End Sub Mark Dixon Page 27
Example: People (Loop) People. as px rs Sub Page_Load() Const cs = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=D: People. mdb; " Dim rs As Object Dim s As String s rs = Create. Object("ADODB. Recordset") Dixon Smith rs. Open("Person", cs) s = "" Do Until rs. EOF() s = s & rs. Fields("Surname"). Value & " " rs. Move. Next() Loop par. Data. Inner. Html = s rs. Close() rs = Nothing End Sub Mark Dixon Page 28
Example: People (EOF) People. as px rs Sub Page_Load() Const cs = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=D: People. mdb; " Dim rs As Object Dim s As String s rs = Create. Object("ADODB. Recordset") Dixon Smith rs. Open("Person", cs) s = "" Do Until rs. EOF() s = s & rs. Fields("Surname"). Value & " " rs. Move. Next() Loop par. Data. Inner. Html = s rs. Close() rs = Nothing End Sub Mark Dixon Page 29
Example: People. as px rs Sub Page_Load() Const cs = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=D: People. mdb; " Dim rs As Object Dim s As String s rs = Create. Object("ADODB. Recordset") Dixon Smith />Jones rs. Open("Person", cs) s = "" Do Until rs. EOF() s = s & rs. Fields("Surname"). Value & " " rs. Move. Next() Loop par. Data. Inner. Html = s rs. Close() rs = Nothing End Sub Mark Dixon Page 30
Example: People (Move. Next) People. as px rs Sub Page_Load() Const cs = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=D: People. mdb; " Dim rs As Object Dim s As String s rs = Create. Object("ADODB. Recordset") Dixon Smith Jones rs. Open("Person", cs) s = "" Do Until rs. EOF() s = s & rs. Fields("Surname"). Value & " " rs. Move. Next() Loop par. Data. Inner. Html = s rs. Close() rs = Nothing End Sub Mark Dixon Page 31
Example: People (Loop) People. as px rs Sub Page_Load() Const cs = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=D: People. mdb; " Dim rs As Object Dim s As String s rs = Create. Object("ADODB. Recordset") Dixon Smith Jones rs. Open("Person", cs) s = "" Do Until rs. EOF() s = s & rs. Fields("Surname"). Value & " " rs. Move. Next() Loop par. Data. Inner. Html = s rs. Close() rs = Nothing End Sub Mark Dixon Page 32
Example: People (EOF) People. as px rs Sub Page_Load() Const cs = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=D: People. mdb; " Dim rs As Object Dim s As String s rs = Create. Object("ADODB. Recordset") Dixon Smith Jones rs. Open("Person", cs) s = "" Do Until rs. EOF() s = s & rs. Fields("Surname"). Value & " " rs. Move. Next() Loop par. Data. Inner. Html = s rs. Close() rs = Nothing End Sub Mark Dixon Page 33
Example: People. as px rs Sub Page_Load() Const cs = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=D: People. mdb; " Dim rs As Object Dim s As String s rs = Create. Object("ADODB. Recordset") Dixon Smith Jones rs. Open("Person", cs) s = "" Do Until rs. EOF() s = s & rs. Fields("Surname"). Value & " " rs. Move. Next() Loop par. Data. Inner. Html = s rs. Close() rs = Nothing End Sub Mark Dixon Page 34
Example: People. as px rs Sub Page_Load() Const cs = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=D: People. mdb; " Dim rs As Object Dim s As String s rs = Create. Object("ADODB. Recordset") Dixon Smith Jones rs. Open("Person", cs) s = "" Do Until rs. EOF() s = s & rs. Fields("Surname"). Value & " " rs. Move. Next() Loop par. Data. Inner. Html = s rs. Close() rs = Nothing End Sub Mark Dixon Page 35
Example: People. as px rs Sub Page_Load() Const cs = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=D: People. mdb; " Dim rs As Object Dim s As String s rs = Create. Object("ADODB. Recordset") Dixon Smith Jones rs. Open("Person", cs) s = "" Do Until rs. EOF() s = s & rs. Fields("Surname"). Value & " " rs. Move. Next() Loop par. Data. Inner. Html = s rs. Close() rs = Nothing End Sub Mark Dixon Page 36
Example: Countries. as px Sub Page_Load() Const cs As String = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=D: Countries. mdb; " html by hand Dim rs As Object put br tags between data Dim s As String rs = Create. Object("ADODB. Recordset") rs. Open("Country", cs) s = "" Do Until rs. EOF s = s + rs. Fields("Name"). value rs. Move. Next Loop par. Data. Inner. Html = s rs. Close() rs = Nothing End Sub Need data on separate lines Mark Dixon Page 37
Example: Countries (error) Countries. as px Sub Page_Load() Const cs As String = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=D: Countries. mdb; " Try putting br tag here Dim rs As Object Dim s As String rs = Create. Object("ADODB. Recordset") rs. Open("Country", cs) s = Do Until rs. EOF s = s + rs. Fields("Name"). value rs. Move. Next Loop par. Data. Inner. Html = s rs. Close() VB does not understand html rs = Nothing End Sub Mark Dixon Page 38
Example: Countries. as px • Need double Sub Page_Load() quotes around Const cs As String = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=D: Countries. mdb; " Dim rs As Object tag Dim s As String • (VB sees html as rs = Create. Object("ADODB. Recordset") literal string) rs. Open("Country", cs) s = " " Do Until rs. EOF s = s + rs. Fields("Name"). value rs. Move. Next Loop par. Data. Inner. Html = s rs. Close() rs = Nothing End Sub runs, but br in wrong place Mark Dixon Page 39
Example: Countries. as px Sub Page_Load() Const cs As String = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=D: Countries. mdb; Move "br tag inside loop. Dim rs As Object Which bit of code pulls data from database? Dim s As String rs = Create. Object("ADODB. Recordset") rs. Open("Country", cs) s = "" Do Until rs. EOF s=s+ rs. Fields("Name"). value rs. Move. Next Loop par. Data. Inner. Html = s rs. Close() rs = Nothing End Sub Mark Dixon Page 40
Example: Countries. as px Sub Page_Load() Const cs As String = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=D: Countries. mdb; Move "br tag inside loop. Dim rs As Object after field data Dim s As String rs = Create. Object("ADODB. Recordset") rs. Open("Country", cs) s = "" Do Until rs. EOF s = s + rs. Fields("Name"). value + " " rs. Move. Next Loop par. Data. Inner. Html = s rs. Close() rs = Nothing End Sub View Source Mark Dixon Page 41
Embedding html in VB • html must be string (inside double quotes) • follows normal pattern for expressions: data s = s + "<b>" + rs. Fields("Name"). value + "</b>" operator Mark Dixon operator Page 42
Embedding html in VB (errors) s = s + "<i>" rs. Fields("Gender"). value + "</i>" missing operator s = s + rs. Fields("Height"). value + </i>" missing double quote s = s + <ul> + rs. Fields("Height"). value html tag must be inside double quotes s = s + "<ul>" + rs. Fields("<b>Height"). value looks for field in database called <b>Height Mark Dixon Page 43
Questions: HTML in VB • Are these correct (assume variables and fields exist)? g = g + rs. Fields("Surname "). value h = h + "<ol>" rs. Fields("Width"). value a = "<p>" + a + "</p>" html = html + "<img src=face. gif />" h = <table> + h + "</table>" Mark Dixon Page 44
Example: People v 2 • Display Surname of Male people: Sub Page_Load() Const cs = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=D: People. mdb; Dim rs As Object Dim s As String rs = Create. Object("ADODB. Recordset") rs. Open("Person", cs) s = "" Do Until rs. EOF() If rs. Fields("Gender"). value = "Male" Then s = s & rs. Fields("Surname"). Value & " " End If rs. Move. Next() Loop par. Data. Inner. Html = s rs. Close() rs = Nothing End Sub Mark Dixon Page 45
Example: People v 3 • Display Surname of Male people: Sub Page_Load() Const cs = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=D: Peo Dim rs As Object Dim s As String Dim sql As String rs = Create. Object("ADODB. Recordset") sql = "SELECT * FROM [Person] WHERE [Gender] = rs. Open(sql, cs) s = "" Do Until rs. EOF() s = s & rs. Fields("Surname"). Value & " " rs. Move. Next() Loop par. Data. Inner. Html = s rs. Close() SQL statement rs = Nothing End Sub Mark Dixon Page 46
SQL: Queries • main purpose of databases: – get information back out: searching • Structured Query Language – dedicated to interacting with databases • 3 rd Generation Language (such as VB, C++) – code describes how to do task • 4 th Generation Language (such as SQL) – code describes what to do (not how to do it) Mark Dixon Page 47
SQL: SELECT statement • SELECT statement – used to get data – can be embedded in VB, via rs. Open: rs. Open("Person", cs) rs. Open("SELECT * FROM [Person]", cs) all fields Mark Dixon Page 48
SQL: WHERE & ORDER BY • WHERE clause – used to restrict data SELECT * FROM [People] WHERE [age]>=18; • ORDER BY clause – used to change order of data SELECT * FROM [People] ORDER BY [Surname]; Mark Dixon Page 49
SQL: strings (text data) • Possible confusion: SELECT * FROM Person WHERE Surname = Smith this will look for field called Smith - gives error need single (SQL) quotes to signify literal text SELECT * FROM Person WHERE Surname = 'Smith' Mark Dixon Page 50
SQL & MS access queries • MS Access – Queries: select data from database – really SQL select statements – can use queries to test SQL code MS Access: People. mdb Mark Dixon Page 51
Questions: SQL • Create an SQL statement to extract Track Title of records by Aerosmith Track Title Artist Name Country Paranoid Black Sabbath UK Falling in Love Aerosmith US Pink Aerosmith US Love in an Elevator Aerosmith US Smooth Criminal Alien Ant Farm US Meaning of Life Disturbed US The Game Disturbed US Voices Disturbed US Down with the Sickness Disturbed US SELECT [Track Title] FROM Track WHERE [Artist Name] = 'Aerosmith'; MS Access: Music. mdb Mark Dixon Page 52
Questions: SQL • Create an SQL statement to extract all fields of songs by Disturbed, ordered by track name Track Title Artist Name Country Paranoid Black Sabbath UK Falling in Love Aerosmith US Pink Aerosmith US Love in an Elevator Aerosmith US Smooth Criminal Alien Ant Farm US Meaning of Life Disturbed US The Game Disturbed US Voices Disturbed US Down with the Sickness Disturbed US SELECT * FROM Track WHERE [Artist Name] = 'Disturbed' ORDER BY [Track Title]; MS Access: Music. mdb Mark Dixon Page 53
Example: People v 4 • User controls what is displayed: Mark Dixon Page 54
SQL: DISTINCT records Artist Name SELECT [Artist Name] FROM [Track]; Black Sabbath Aerosmith Alien Ant Farm Disturbed SELECT DISTINCT [Artist Name] FROM [Track]; Artist Name Black Sabbath Aerosmith Alien Ant Farm Disturbed Mark Dixon Page 55
Tutorial Exercise: People • • • Task 1: Create your own People database: – Open MS Access – Create a new database file – Create a new table – Create fields – Enter data Task 2: Get the People v 1 example (from the lecture) working. Task 3: Modify your page so that it displays phone number as well as the person's name. Task 4: Modify your page so that it displays the data in an html table. Task 5: Modify your page so that records for males are displayed in blue, and records for females are displayed in red. Task 6: Modify your page as per version 4. You will need to: – Add a form to the page, and three submit buttons – In your code, detect when a button has been pressed (have a look at previous weeks) Task 7: Modify your page so that the user can type a letter, and only names starting with that letter are displayed. Task 8: Modify your page so that the user can type a series of numerical digits and only phone numbers containing those digits are displayed. Task 9: Modify your code so that the user can order the data by surname, or email address (You may want to use a Query String) Mark Dixon Page 56
- Slides: 56