LIS 558: Database Management Systems

1
LIS 558 Database Management Systems
November 16, 2006

• ODBC (Open DataBase Connectivity)
• Margaret Kipp
• mkipp_at_uwo.ca

Faculty of Information Studies, University of
Western Ontario
2

• Introduction to ODBC

3
Outline

• The Client Server Model
• ODBC
• ODBC Examples
• ODBC Exercises

4
The Client/Server Model

• client/server interactions are like calling a big
  company with a phone tree
• you are the client
• the company with the phone tree is the server
• client/server interactions involve the client
  making requests and the server carrying out the
  request and then returning results or messages

5
Client/Server (cont.)

• the Internet is a client/server system
• the web browser is a client
• the server is a machine named by the IP address
  typed into the browser
• e.g. using SFTP
• use the secure file transfer tool
• connect to gcampbell.fims.uwo.ca with your user
  name and password, now you can copy files to the
  server from the lab machine

6
What is ODBC?

• ODBC or Open DataBase Connectivity is a standard
  method (protocol) used to connect to databases
  using a user written application
• ODBC uses SQL to access data in the database
• ODBC is very commonly used and often built into
  DBMS applications
• ODBC is a client server application

7
ODBC Client Server Model
http//www.datadirect.com/developer/odbc/basics/in
dex.ssp
8
ODBC Architecture

• ODBC has four components
• Application - processes and calls ODBC functions
  to submit SQL statements and retrieve results
• Driver Manager- loads drivers for the application
• ODBC Driver- processes ODBC function calls,
  submits SQL requests to a specific data source,
  and returns results to the application
• Data source - consists of the data to access and
  its associated operating system, DBMS, and
  network platform (if any) used to access the DBMS

9
ODBC Architecture (cont.)

• Using ODBC
• the user runs an application (web browser or
  local software)
• the user enters user name, password, database
  name
• the application calls the driver manager to
  select the right ODBC driver
• the ODBC driver connects to the database
• now the user can query the database

10
How ODBC Works (in brief)

• working with ODBC is like working with cursors
• create a connection connect to DB on server
  using a connect string
• create a cursor
• fetch data from the cursor
• do something with the data/display the data
• close the cursor (possibly also the connection)

11

• ODBC with Python

12
Python ODBC Example - MySQL

• This script accesses the MySQL database using
  ODBC
• The script will prompt you for a user name and a
  password
• it will connect to the database and allow you to
  perform queries from a menu
• run at the command line
• python queryDB.py

13
ODBC with Python (Steps)

• Step 1 import the MySQLdb ODBC driver
• import MySQLdb
• Step 2 connect to the database
• conn MySQLdb.connect(host"localhost",
  userusername, passwdpassword, dbdbname)
• Step 3 create a cursor
• conn.cursor()

14
ODBC with Python (cont.)

• Step 4 execute a query
• cursor.execute("SELECT FROM Book")
• Step 5 fetch the data from the cursor (select
  only)
• data cursor.fetchall()
• Step 6 print the data (or do something with it)
  (select only)
• for item in data
• print item

15
ODBC with Python (cont.)

• Step 7 close the cursor and the connection
• cursor.close()
• conn.close()

16
ODBC with Python (cont.)

• Note that steps 5 and 6 were for SELECT
  statements only
• for other statements, you should commit or
  rollback after step 4
• e.g. conn.commit() or conn.rollback()
• It is also possible to select from the table you
  have just modified to make sure updates
  succeeded, simply do steps 4, 5 and 6 for the
  table you've updated

17
ODBC with Python (SELECT)

• import MySQLdb
• conn MySQLdb.connect(host"localhost",
  user"username", passwd"password",
  db"SummerReading")
• cursor conn.cursor()
• cursor.execute("SELECT FROM Book")
• data cursor.fetchall()
• for item in data
• print item

18
ODBC with Python (Modify)

• import MySQLdb
• conn MySQLdb.connect(host"localhost",
  user"username", passwd"password",
  db"SummerReading")
• cursor conn.cursor()
• cursor.execute("INSERT INTO Child (Name,
  ReadingLevel, PlaceOfRegistration) VALUES ('Jacob
  Two Two', 'Early Readers', 3)")
• cursor.commit()

19
ODBC with Python (Modify) 2

• import MySQLdb
• conn MySQLdb.connect(host"localhost",
  user"username", passwd"password",
  db"SummerReading")
• cursor conn.cursor()
• cursor.execute("INSERT INTO Child (Name,
  ReadingLevel, PlaceOfRegistration) VALUES ('Jacob
  Two Two', 'Early Readers', 3)")
• cursor.execute("SELECT FROM Child")
• data cursor.fetchall()
• for item in data print item

20
User Input

• Example
• tablename raw_input("Please enter the table
  name ")
• cursor.execute("SELECT FROM s" (tablename))
• this code can be used to replace the
  cursor.execuse command in the existing select
  script
• asks the user for a table name

21
ODBC with Python and Access

• for Access with Python, just use a different
  connect string and import different drivers
• import dbi
• import odbc
• conn odbc.odbc('DriverMicrosoft Access Driver
  (.mdb)DbqSummerReading.mdb')
• note there is a space between the word Driver
  and the parenthesis
• all other steps are the same!

22
ODBC Exercise

• write a python script to access the database and
  print out everything from the child table
• use the sample script for select statements and
  modify the SQL statement

23
ODBC Exercise

• write a python script to access the database and
  print out everything from a user specified table
• see queryDB.py and take a look at how this script
  gets user input and adds it to the SQL statement
• tablename raw_input("Please enter the table
  name ")
• cursor.execute("SELECT ID FROM s" (tablename))

24

• ODBC with VBA

25
ODBC with Access (Steps)

• Step 1 declare variables
• Dim conConnector As ADODB.Connection
• Dim strConnection As String
• Step 2 connect to the database
• Set conConnector New ADODB.Connection
• strConnection "Provider'Microsoft.JET.OLEDB.4.0
  'Data Source'SummerReading.mdb'"
• conConnector.Open strConnection

26
ODBC with Access (cont.)

• For modifications...
• Step 3 execute the SQL statement
• conConnector.Execute "INSERT INTO Child (Name,
  ReadingLevel, PlaceOfRegistration) VALUES ('Jacob
  Two Two', 'Early Readers', 3)"
• Step 4 close the connection
• conConnector.Close

27
ODBC with Access (cont.)

• For selecting...
• Step 3 declare additional variables
• Dim rstRecordSet As ADODB.Recordset
• Dim fldFields As ADODB.Field
• Step 4 create the record set (cursor)
• Set rstRecordSet New ADODB.Recordset
• rstRecordSet.Source "SELECT FROM Child",
  conConnector, adOpenStatic, adLockOptimistic,
  adCmdText
• rstRecordSet.Open

28
ODBC with Access (cont.)

• For selecting...
• Step 5 loop through the rows
• For Each fldFields In rstRecordSet.Fields
• MsgBox fldFields.Name, fldFields.Value
• Next
• Step 6 close the connection
• conConnector.Close
• Set conConnector Nothing

29
ODBC with Access (SELECT)

• Private Sub cmdConnector_Click()
• Dim conConnector As ADODB.Connection
• Dim strConnection As String
• Dim rstRecordSet As ADODB.Recordset
• Dim fldFields As ADODB.Field
• Set conConnector New ADODB.Connection
• strConnection "Provider'Microsoft.JET.OLEDB
  .4.0'Data Source'SummerReading.mdb'"
• conConnector.Open strConnection

30
ODBC with Access (SELECT) 2

• Set rstRecordSet New ADODB.Recordset
• rstRecordSet.Source "SELECT FROM Child",
  conConnector, adOpenStatic, adLockOptimistic,
  adCmdText
• rstRecordSet.Open
• For Each fldFields In rstRecordSet.Fields
• MsgBox fldFields.Name fldFields.Value
• Next
• conConnector.Close
• Set conConnector Nothing
• End Sub

31
ODBC with Access (Modify)

• Private Sub cmdConnector_Click()
• Dim conConnector As ADODB.Connection
• Set conConnector New ADODB.Connection
• strConnection "Provider'Microsoft.JET.OLEDB
  .4.0'Data Source'SummerReading.mdb'"
• conConnector.Open strConnection
• conConnector.Execute "INSERT INTO Child
  (Name, ReadingLevel, PlaceOfRegistration) VALUES
  ('Jacob Two Two', 'Early Readers', 3)"
• conConnector.Close
• End Sub

32
More Information

• This is a tutorial on using Access with VBA
• http//www.functionx.com/vbaccess/
• Python is installed in the labs (look for Active
  Python)
• Can also be downloaded for free
• http//www.activestate.com/Products/ActivePython/