ODBC

1. ODBC • Darin Baish • Michael Diluzio • Eric Lindsay • Ryan Stroup

2. History of ODBC • Microsoft releases first version of ODBC in 1992 • Based on the SAG CLI, which was created by the SQL Access Group

3. History of ODBC • ODBC 2.0 SDK was released in 1994 • ODBC 3.0 SDK was release in 1996

4. Typical ODBC Architecture

5. How ODBC Works? • ODBC inserts a middle layer called a Client Driver • Purpose of the Client Driver is to translate the applications queries into commands that the DBMS understands

6. Other Parts of the Architecture • Application – calls functions defined in the ODBC API to access a data source • Driver Manager – implements the ODBC API and provides information to the application • Database – contains all the data

7. Setting Up a Data Source in Windows • A data source is just a database that ODBC connects to • This allows the person to change database types without any changes to the program • Step 1. Get a database. • Using access for this example because it’s on this computer

8. Setting Up a Data Source in Windows • Step 2. Open Windows ODBC Manager • Control Panel / Administrative Tools / Data Sources (ODBC). • Step 3. Click Add, Select the Microsoft Access Driver (.mdb) and click Finish • Step 4. Type a name, Select a database or create one and click OK. You’re done. Celebrate.

9. Setting Up a Data Source in Windows • Obviously, all of this can be done in UNIX or Linux but how to do it differs. • ODBC is developed by third parties for UNIX and creating a data source differs for each.

10. An Example in ASP

11. An Example in Perl • First, import the packages needed to use ODBC. This is so Perl knows what you’re talking about later • use Win32::ODBC; • Next, we need to connect to the database. Here is where you need to know the name of the data source. • $db = new Win32::ODBC(“YOUR_FRIGGIN_DATA_SOURCE_ NAME_HERE“);

12. An Example in Perl • Now you can make SQL statements. • $stmt = “INSERT INTO diskdrives (food) VALUES (‘pudding’ {‘food’}); • To make the statement execute in the database, do this • $db->Sql($stmt); • It’s easy to insert pudding into diskdrives.

13. An Example in Perl • But, lets say you want to get data from the database. You cant. Just kidding. Do this: • $stmt = “SELECT * FROM interesting_computer_books”; • $db->Sql(stmt); • If for some strange reason there’s actually items in the table interesting_computer_books, do the following to get them.

14. An Example in Perl • Loop through the results • while ($db->FetchRow()) { ($title, $author) = $db->Data(“title”, “author”) // Do what you want with the data here $bookBurner->Burn($title, $author); }

15. An Example in Perl • Once you’re all done with the database, close the connection • $db->Close(); • So, that’s it. Imagine how long this would have taken without ODBC.

16. Conclusion Open Database Connectivity Based on SQL Access Group’s Call Level Interface specification Invented by Microsoft in the early nineties Microsoft realized a problem and decided to do something about it People who wrote programs that interacted with databases had to write different code for each database

17. Conclusion • ODBC gives programmer the ability to write single source code in ODBC standards. • ODBC then does the work to gain access to different types of databases if necessary. • There are essentially four parts to ODBC:

18. Conclusion • Application • Coded by the programmer. Communicates with ODBC • Driver Manager • Provides the ODBC interface to the application • Also responsible for loading the correct database driver that is desired by the application • Database Driver • Handles translation between ODBC’s API, and each database that it connects to • Database • The database in which ODBC communicates with (Access, Oracle, etc.)

19. Conclusion The four parts that make up ODBC:

20. Conclusion • Advantages: • Allows access to different types of databases • Uniform way of retrieving information • Highly efficient • Low memory requirements

21. Conclusion • Disadvantages • Complex and steep learning curve • All data in database must look like a relational database

22. Conclusion • ODBC changed the way people code their programs that have to interact with databases. • The efficiency of programmers in the business world has increased due to ODBC because they no longer are wasting time to create multiple copies of a program. • ODBC has vastly improved the way programmers deal with databases.