17.3 Database programming with ODBC

17.3 Database programming with ODBC

17.3.1 The structure of ODBC

Open Database Connectivity (ODBC) is a product from Microsoft to provide a unified interface for different database types. ODBC has existed since the early days of Windows and become the de facto standard for database applications across different vendors and platforms. The unique feature of ODBC is that none of its functions are vendor specific. Once you have registered your database with ODBC, you can access your remote data with browsers. Whether you have an Access, dBase, FoxPro, Oracle, or MySQL database, you can control it with the same type of programming and coding.

The first step to use ODBC is to register your databases with the ODBC administrator. This administrator is an interface between your Web applications and the actual databases. Your Web pages can open a connection to the desired database via the DSN of ODBC and perform your SQL query.

The ODBC interface works by providing different drivers for different types of database. Depending on your system, some popular ODBC drivers for Windows systems are: dBase, Access, Excel, FoxPro, and Oracle. ODBC drivers are usually provided by vendors and configured automatically when you install the corresponding database products. For example, if you install Microsoft Office, you will install the ODBC drivers for Access and Excel at the same time. A diagram describing ODBC in action is shown in Fig. 17.3.

ODBC drivers are controlled by the ODBC driver manager and administrated by the ODBC administrator. For Windows systems, the driver manager is a dynamic link library (DLL) provided by Microsoft to determine which driver to load based on the DSN. For other systems such as UNIX/LINUX, ODBC managers are also available.

To use MySQL on the Web, you need to register MySQL databases with ODBC first.

17.3.2 Registering MySQL databases on the Web with ODBC

The driver to handle MySQL databases is called MyODBC and available from the official site of MySQL. Basically, there are two versions of MyODBC: one is for Windows and the other is for UNIX/LINUX operating systems.

For example, to install MyODBC on Windows NT/2000/XP, you can use the file

myodbc-2.50.39-nt.zip

Unzip the file into an empty directory and run the set-up program setup.exe. You will see a Setup dialog window as in Fig. 17.4. If you press the Continue button, the Install Drivers dialog box will appear with MySQL driver (see Fig. 17.5). Highlight this driver and click the OK button. Once MyODBC is installed, you are ready to register your remote MySQL databases with ODBC.

Assume that you have MySQL database personnel located on a server called www.pwt-ex.com. The procedure to register this database on a client machine running Windows XP is as follows:

• Activate the Start | Control Panel and click on "Administrative Tools" from XP (Fig. 17.6).
  
  

  Figure 17.6. MyODBC installation

  

  
  
• Click the "Data Source (ODBC)" icon to open the "ODBC Data Source Administrator" window (see Fig. 17.7).
  
  

  Figure 17.7. System DSN

  

  
  
• Click on the "System DSN" Menu and press the Add button.
  
  
• The "Create New Data Source" window is shown. Highlight the "MySQL" option and click the Finish button (Fig. 17.8).
  
  

  Figure 17.8. Create a new data source

  

  
  
• The "MySQL Driver" default configuration screen appears (Fig. 17.9). For a basic configuration with hostname, username and password, fill in the following fields and click the "OK" button (Fig. 17.9):
  
  
  
  

  Windows DSN name:          personnel
  MySQL host(name or IP):    www.pwt-ex.com
  MySql database name:       personnel
  User:                      johnsmith
  Password:                  *********
  

  
  

  Figure 17.9. MySQL driver configuration

  

  
  

The values in these fields will be used as the defaults when you attempt to make a connection. Registering a database in system DSN would allow your colleagues to use it. If you want some privacy, you can register your database in user DSN.

You now have a remote MySQL database called personnel registered in your local machine. One way to test your MyODBC driver and MySQL system is to develop a Web page to access the remote database with browsers.

17.3.3 A page to access ODBC databases

At the end of Chapter 15, we used Perl script to develop a Web page to get information from a Microsoft Access database. The example ex15-15.pl uses the Win32::ODBC Perl module to gain access to the remote database registered on your local machine. With some simple modifications, this page can be used to establish a more general page to access database on the Web. In order to accept multiple line input for SQL statements, the first modification is to use textarea for user input. The XHTML coding for this page is listed below:

Example: ex17-09.htm - A Page To Access ODBC Databases

 1: <?xml version="1.0" encoding="iso-88591"?>
 2: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
 3:    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 4: <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
 5: <head><title> Access ODBC Databases  ex1709.htm</title></head>
 6: <style>
 7:  .txt{font-family:arial;font-size:14pt;color:#000088;font-weight:bold}
 8:  .butSt{background-color:#aaffaa;font-family:arial;font-weight:bold;
 9:     font-size:14pt;color:#008800;width:520px;height:30px}
10:  .textareaSt{background-color:#aaffaa;font-family:arial;font-weight:bold;
11:     font-size:14pt;color:#008800;width:580px;height:200px}
12: </style>
13:
14: <body style="font-size:18pt;background:#bbbbff;
15:    text-align:center" class="txt">
16:  Accessing MySQL Database(s) via ODBC<br />with Perl Script<br />
17:
18:  <img src="line1.gif" width="550" height="6" />
19:  <table class="txt" align="center" cellspacing="10" width="550" >
20:   <tr><td colspan="2">Remote Database Information:</td></tr>
21:   <tr valign="top"><td><img src="bullet1.gif" vspace ="3" /></td>
22:     <td>The remote database type is MySQL</td></tr>
23:   <tr><td valign="top"><img src="bullet1.gif" vspace ="3" /></td>
24:     <td>The name of the database is called personnel</td></tr>
25:   <tr><td valign="top"><img src="bullet1.gif" vspace ="3" /></td>
26:     <td>The database contains a table called people<br /></td></tr>
27:  </table>
28:  <img src="line1.gif" width="550" height="6" /><br />
29:
30:  <form action = "ex17-09.pl" method="post">
31:     Enter Your SQL Query String<br />
32:   <textarea rows="5" cols="30" name = "querySt" class="textareaSt">
33:     SELECT * FROM people;</textarea><br /><br />
34:   <input type = "submit" value = "Send Query" class="butSt"
35:      style="width:180px;background:#bbbbbb">
36:  </form>
37: </body>
38: </html>

This is a simple page to get the SQL query string such as SELECT * FROM people;. After the introductory text, the main part of this page is the XHTML form declared in lines 3036. The textarea element inside the form (lines 3233) generates a text box for the user to enter an SQL query. Once the Send Query button is pressed, the following form action (line 30)

<form action="ex1709.pl" method="post">

will activate the CGI program ex17-09.pl in the same directory as the Web page ex17-09.htm. The purpose of this Perl program is to process the SQL query in the text area and return results to the user. A screen shot of this page is shown in Fig. 17.10.

The CGI program ex17-09.pl is a Perl implementation to access databases via ODBC. As long as you have a remote database somewhere on the Internet and registered on your local machine with ODBC, you can use this example to gain access to it. The first part of the program is listed as follows:

Example: ex17-09.pl - ODBC Database With Perl (Part One)

 1: #! /usr/bin/perl
 2:
 3: use Win32::ODBC;
 4: use CGI qw (:standard);
 5: print "Content-type:text/html\n\n";
 6:
 7: my $querySt = param(querySt);
 8: $querySt =~ s/\n/ /g;
 9: ($querySt,$tmp1) = split(/;/,$querySt);
10: $querySt = $querySt . ";";
11:
12: $dsnName = "personnel";
13:
14: if (!($odbcData = new Win32::ODBC($dsnName)))
15: {
16:    print "ODBC Connection .. Error..\n";
17:   exit();
18: }
19:
20: if ($odbcData->Sql($querySt))
21: {
22:    print "Error.. SQL failed..\n";
23:    $odbcData->Close();
24:    exit();
25: }

This is an ODBC application and therefore you need to include the Win32::ODBC module in the program (line 3) to make it work. The SQL query string is captured by the usual Perl function param() and stored in the variable $querySt.

Since the query string $querySt represents the user input from textarea, it may contain non-printable characters to prevent the execution of the SQL query. For example, if you spread the SQL statement over multiple lines, each line will have a newline character attached at the end. In this case, the SQL query may stop after the first line. To prevent this situation and accept multiple line input, we use the following three simple treatments (see lines 810):

$querySt =~ s/\n/ /g;
($querySt,$tmp1) = split(/;/,$querySt);
$querySt = $querySt . ";";

The first line is to replace all newline characters by a space so that the multiple line SQL query can be executed. Since all SQL statements are terminated by a semi-colon, the second line is used to extract the query string up to the first appearance of a semi-colon. The SQL statement is stored in $querySt and the remaining part in $tmp1. All characters after the first semi-colon will be ignored. After the second statement, the query string will have no semi-colon. The third statement is to add the semi-colon at the end of the query string. This will guarantee that the program will still work if you forget to put a semi-colon at the end of your query.

This program is hardwiring an ODBC connection to a MySQL database called personnel. If you want to use other databases or have more than one database, you can modify the data source name $dsnName in line 12 to suit your needs.

The command below (line 14) is to make the connection between your browser and the database:

if (!($odbcData = new Win32::ODBC($dsnName)))

If you have a successful connection, the variable $odbcData object can be used to execute the SQL query by the statement (see line 20)

if ($odbcData->Sql($querySt))

If this SQL query is successful, the variable $odbcData will contain the query data. Next, you need to develop a Web page to display the query result in XHTML format. Consider the second half of the program:

Listing: Continuation Of Perl Script ex1709.pl (Part Two)

26:
27: print << "mypage";
28:  <?xml version="1.0" encoding="iso-88591"?>
29:  <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
30:     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
31:  <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
32:  <head><title>Example ex1709.pl</title></head>
33:  <style>
34:   .bodySt {font-family:arial;font-weight:bold;background:#000088;
35:      font-size:18pt;color:#ffff00;text-align:center}
36:   .txtSt{font-family:arial;font-weight:bold;font-size:13pt;color:#ffff00}
37:  </style>
38:  <body class="bodySt" >
39:  Based On Your SQL Query String:<br />
40:
41:  <div style="font-size:14pt" align="center">
42:   <table><tr><td class="txtSt" style="color:#ffffff;width:500px;
43:           text-align:center;font-size:14pt"><br />
44:      $querySt </br><br /></td></tr>
45:   </table>
46:    Query O.K. And The Returned Results Are As Follows:<br />
47:  </div>
48: <img src="line1.gif" width="600" height="6" alt="pic" />
49:   <br /><br />
50:   <table cellspacing="5" class="txtSt" align="center" width="570">
51: mypage
52:
53: $count = 0;
54: while($odbcData->FetchRow())
55: {
56:    %odbcData = $odbcData->DataHash();
57:     @keys = keys(%odbcData);
58:
59:   if ($count ==0)
60:   {
61:     print "<tr>";
62:     for ($ii = 0; $ii < @keys; $ii++)
63:     {
64:       print "<td style=\"color:#00ff00;font-size:16pt\">$keys[$ii]</td>";
65:     }
66:     print "</tr>";
67:   }
68:
69:   print "<tr>";
70:   for ($ii = 0; $ii < @keys; $ii++)
71:   {
72:     print "<td>$odbcData{$keys[$ii]}</td>";
73:   }
74:   print "</tr>";
75:    $count++;
76: }
77: $odbcData->Close();
78:
79: print << "endpage";
80:   </table>
81:  <img src="line1.gif" width="600" height="6" alt="pic" />
82:  <br /><br />Total Entries = $count<br /><br />
83: </body>
84: </html>
85: endpage

This part is a Web page returned by the Perl program when the SQL query is successful. In this case, the variable $odbcData contains the SQL query results. In order to display the contents in $odbcData effectively, an XHTML table is used. The while-loop in lines 5476 fetches the database and processes the records one by one. A hash function in lines 5657 is used to convert each row (or record) of the database to members of a hash element called @keys. Inside @keys, the array $keys[] stores the field names of the database table and $odbcData($keys[]) stores the values of the associated field. The first member of @keys looks like this:

$keys[0] = location    $odbcData($keys[0]) = London
$keys[1] = salary      $odbcData($keys[1]) = 30000
     ...        ...    ...      ...
$keys[5] = name        $odbcData($keys[5]) = Michael

These are the field name/value pairs. Therefore the first for-loop in lines 6265 displays the field names $keys[n]. This for-loop only needs to be executed once since we only want to display the names once. The second for-loop in lines 7073 outputs the elements $odbcData($keys[n]). For each n, the variable $odbcData($keys[n]) stores the field data of the table. When we run through all the rows, the entire SQL query data are returned to the browser. At this point, a Close statement in line 77 is needed to close the ODBC connection.

If you enter the SQL query SELECT * FROM people; as shown in Fig. 17.10, all records of the table people are displayed on the browser screen. A screen shot is shown in Fig. 17.11.

17.3.4 A page to access multiple database types

Suppose you are running a successful business with four offices located in New York, London, Paris, and Tokyo. The sales records for each office are stored in databases as in Table 17.3.

Table 17.3. Multiple database types

Location	Database type	Database name	Table name
New York	MySQL	sale_usa	sales
London	MySQL	sale_uk	sales
Paris	MS Access	sale_fr	sales
Tokyo	MS Access	sale_jp	sales

The first thing in developing a Web page to access all these databases is to register them on your local machine with ODBC. After registration, a screen shot of the "Data Source Administrator" window on a Windows XP machine will appear as in Fig. 17.12.

Note that the database type and name from Table 17.3 are shown in the name and driver fields inside the ODBC administrator.

One of the efficient ways to implement this example is to use a select box. With a select box, you can change the database and perform an SQL query easily. Consider the following page:

Example: ex17-10.htm - Access Multiple Database Types With ODBC

 1:  <?xml version="1.0" encoding="iso-88591"?>
 2:  <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
 3:     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 4:  <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
 5: <head><title> Access Multiple Database  ex1710.htm</title></head>
 6:  <style>
 7:   .txt{font-family:arial;font-size:16pt;color:#000088;font-weight:bold}
 8:   .butSt{background-color:#aaffaa;font-family:arial;font-weight:bold;
 9:      font-size:14pt;color:#008800;width:520px;height:30px}
10:  .textareaSt{background-color:#aaffaa;font-family:arial;font-weight:bold;
11:       font-size:14pt;color:#008800;width:580px;height:150px}
12:   </style>
13:   <body style="font-size:18pt;background:#bbbbff;
14:      text-align:center" class="txt">
15:     Multiple Database Types with ODBC<br /><br />
16:    <img src="line1.gif" width="550" height="6" /><br />
17:
18:   <form action = "ex17-10.pl" method="post">
19:    <div class="txt" style="width:550px;height:150px" >
20:      Remote Databases Information:<br /><br />
21:     <select class="butSt" style="width:440px" name="database" >
22:      <option value="sale_usa">Sales In
23:         New York: Table: sales (MySQL)</option>
24:      <option value="sale_uk">Sales In
25:         London: Table: sales (MySQL)</option>
26:      <option value="sale_fr">Sales In
27:         Paris: Table: sales (MS Access)</option>
28:      <option value="sale_jp">Sales In
29:         Tokyo: Table: sales (MS Access)</option>
30:     </select>
31:    </div><br />
32:    <img src="line1.gif" width="550" height="6" /><br />
33:       Enter Your SQL Query String<br />
34:     <textarea rows="8" cols="30" name = "querySt" class="textareaSt">
35:       SELECT * FROM sales;</textarea><br /><br />
36:     <input type = "submit" value = "Send Query" class="butSt"
37:        style="width:180px;background:#bbbbbb">
38:    </form>
39:  </body>
40: </html>

This is a form application and the main part of this example is the select box defined in lines 2130. Consider the first option value of the select box:

<option value="sale_usa">Sales In New York: Table: sales (MySQL)</option>

The message in the middle tells us that the database is a MySQL type representing company sales in New York. The table inside the database is called sales. Once this option is picked and submitted to the Perl script ex17-10.pl, the string sale_usa is returned as the value of the select box. This sale_usa is one of the DSNs registered on ODBC so that it can be used to access the corresponding database. A screen shot of this example is shown in Fig. 17.13.

Once you have the information on the selected database, the script program ex17-10.pl becomes very similar to ex17-09.pl. Basically, all you have to do is to change the DSN (i.e., $dsnName) to the value returned by the select box. That means you only need to change lines 125 of ex17-09.pl and call it ex17-10.pl. The first 25 lines of ex17-10.pl are listed below:

Example: ex17-10.pl - The Perl Script For ex17-10.htm

 1: #!o:/perl/bin/perl
 2:
 3: use Win32::ODBC;
 4: use CGI qw (:standard);
 5: print "Content-type:text/html\n\n";
 6:
 7: my $dataB = param("database");
 8: my $querySt = param(querySt);
 9: $querySt =~ s/\n/ /g;
10: ($querySt,$tmp1) = split(/;/,$querySt);
11: $querySt = $querySt . ";";
12:
13: $dsnName = $dataB;
14: if (!($odbcData = new Win32::ODBC($dsnName)))
15: {
16:    print "ODBC Connection .. Error..\n";
17:    exit();
18: }
19:
20: if ($odbcData->Sql($querySt))
21: {
22:    print "Error.. SQL failed..\n";
23:    $odbcData->Close();
24:    exit();
25: }

Every time the Send Query button is pressed, the value of the select box is passed to the variable $dataB defined in line 7. This variable is then assigned to the DSN variable $dsnName in line 13. Once this $dsnName is fixed, we can consider that the entire application involves only one database. All the ODBC connections, SQL processing, and displays are the same as in ex17-09.pl. Thus the remaining program codes of this example are the same as in ex17-09.pl. Some screen shots of this example in action are shown in Figs 17.14 and 17.15.

From the viewpoint of ODBC, databases from MySQL and Access are the same.

ODBC does provide a good tool to access different databases on the Web. However, it also relies heavily on the Windows coding. From line 3 of ex17-09.pl and ex17-10.pl, you can see that it includes the module

use Win32::ODBC;

in the Perl processor; Win32::ODBC only works on Win32 systems. As a Web programmer, you may be asked to write a page or a program to access some databases in an environment other than Windows or without ODBC. Can we access databases without ODBC?

For a truly platform-independent database interface, the so-called Database Interface is a popular candidate on the Web.