Transcript No Slide Title - John E. Carter

ODBC
the
MIDDLEWARE
that
CONNECTS!
Presented by
Carter-Davis-Wagner
CSIS 4490 - Spring 2002
Dr. Hoganson
Conceptual Office Layout at Bob’s Bikes
PC at
front
counter
PC at
loading
dock
PC in
Bob’s
office
Server in
back office
LAN
ODBC - Open DataBase Connectivity - from Microsoft
•Provides
interface
to MySQL Database
•Provides
interface
to access the tables for Bob’s Bike
Shop using ActiveX
•Interfaces with Word to create form
letters with MSQuery
How does it connect?
Clients
LAN
•Interfaces with Excel to extract data
to create charts and graphs using VBA
Server(s)
•Also may interface with
•PowerPoint
•Access Database
•Products from other Vendors
Internet
Firewall
ODBC layer
Bob’s Bike Shop Inventory System
Specifications
Server
P100 server
48MB RAM
SCSI Hard Drive
Windows NT 4.0 Workstation
DHCP Addressing
Hub
3Com Model TP4
4 Ports
Cable
Category 5
Client 1
IBM ThinkPad
Windows 95
Client 2
IBM ThinkPad
Windows 2000 Professional
Database
MySQL http://www.mysql.org
MySQL ODBC Driver
Software
Visual Basic
MS Excel
MS Word
ODBC Configuration
MySQL has a click and go setup
program to install ODBC Drivers
Configure the drivers to access
the data
•Data Set Name
•Location (IP address or server
name)
•Database name
•User ID and password (as
required)
Select the MySQL ODBC Driver...
MySQL
Configuration screen
for the ODBC driver
MySQL
Database
AdministrationS
creen
MySQL Manager
Database csis4490 has
2 Tables
•Manufacturer
•Inventory
The Database Interface...
Table Statements
use csis4490;
create table inventory (model char(10) Not Null, color char(10) Not Null, size
int(11) Not Null Default 0,
gender char(1) Not Null, instock int(11) Default 0, onorder int(1) Not Null,
manid int(9) Not Null Default 0, invid int(9) Not Null Auto_Increment,
Primary Key (invid), Unique id (invid),FOREIGN KEY(manid) REFERENCES
manufacturer(manid) )
create table manufacturer (company char(25) Not Null, address1 char(25) Not
Null, address2 char(25),
city char(15) Not Null, state char(12) , country char(12) Not Null, mailcode
char(12), contact char(25),
phone char(12), email char(30),manid int(9) Not Null Auto_Increment, Primary Key
(manid), Unique id (manid) )
MySQL Query
Select * from inventory
Word: Mail Merge using MSQuery to create a form letter
Word: Mail Merge using MSQuery to create a form letter
Word: Fields that are extracted from the database
The fields in << >> are
extracted from the database
for use in the form letter.
Word: Completed letter with name and address!
Excel: Data can be extracted to spreadsheet using MSQuery
Excel: Data can be also be accessed using code (VBA).
‘select DSN
conString = "DSN=csis4490"
‘connect
thisconn = SQLOpen(conString, , 2)
If IsError(thisconn) Then Error 9999 'invalid response from server
mysql$ = "select model, color, size, instock from inventory"
'send query
Sheets("Status").Range("b4") = sqlexecquery(thisconn, StringToArray(mysql$))
‘save error (if any)
Sheets("Status").Range("b17") = sqlerror()
‘define destination cell for each field retrieved
Sheets("Status").Range("b5") = sqlbind(thisconn, 1, Sheets("Data").Range("a6"))
Sheets("Status").Range("b19") = sqlerror()
Sheets("Status").Range("c5") = sqlbind(thisconn, 2, Sheets("Data").Range("b6"))
Sheets("Status").Range("b21") = sqlerror()
Excel: Generate chart from the database.
ODBC Troubleshooting…
Thank you !