1 / 21

ODBC the MIDDLEWARE that CONNECTS!

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. Server in back office. PC at loading dock. LAN. PC in Bob’s office.

tierra
Download Presentation

ODBC the MIDDLEWARE that CONNECTS!

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ODBC the MIDDLEWARE that CONNECTS! Presented by Carter-Davis-Wagner CSIS 4490 - Spring 2002 Dr. Hoganson

  2. Conceptual Office Layout at Bob’s Bikes PC at front counter Server in back office PC at loading dock LAN PC in Bob’s office

  3. ODBC - Open DataBase Connectivity - from Microsoft • Provides interface to MySQL Database to access the tables for Bob’s Bike Shop using ActiveX • Interfaces with Word to create form letters with MSQuery • Interfaces with Excel to extract data to create charts and graphs using VBA • Also may interface with • PowerPoint • Access Database • Products from other Vendors • Provides interface

  4. Bob’s Bike Shop Inventory System Specifications ServerClient 1 P100 server IBM ThinkPad 48MB RAM Windows 95 SCSI Hard Drive Windows NT 4.0 Workstation Client 2 DHCP Addressing IBM ThinkPad Windows 2000 Professional Hub 3Com Model TP4 Database 4 Ports MySQL http://www.mysql.org MySQL ODBC Driver Cable Category 5 Software Visual Basic MS Excel MS Word

  5. ODBC Configuration • Data Set Name • Location (IP address or server name) • Database name • User ID and password (as required) MySQL has a click and go setup program to install ODBC Drivers Configure the drivers to access the data

  6. Select the MySQL ODBC Driver...

  7. MySQL Configuration screen for the ODBC driver

  8. MySQL Database AdministrationScreen

  9. MySQL Manager • Database csis4490 has 2 Tables • Manufacturer • Inventory

  10. The Database Interface...

  11. 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) )

  12. MySQL Query Select * from inventory

  13. Word: Mail Merge using MSQuery to create a form letter

  14. Word: Mail Merge using MSQuery to create a form letter

  15. Word: Fields that are extracted from the database The fields in << >> are extracted from the database for use in the form letter.

  16. Word: Completed letter with name and address!

  17. Excel: Data can be extracted to spreadsheet using MSQuery

  18. 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()

  19. Excel: Generate chart from the database.

  20. ODBC Troubleshooting…

  21. Thank you !

More Related