Win32::ODBC - A Tutorial

I was always told, "Look to your hobbies to start a business." About three years ago, when I was interested in doing something that combined my hobbies, Perl and the Internet, I thought about starting a business. I remember the previous summer having found a store in Dewey Beach, Delaware, that sold thousands of hot sauces, another of my passions, and a great idea came to me. I could sell the hot sauces for the store on the Internet. I would put the product data into a database, sell via the web, using Perl to generate the HMTL and keep track of sales.

One thing was missing though. How would I connect to a database? Outside of work, I only had access to a Win95 machine. On it, I was running Perl for Win32, from hip Communications (now called ActiveState). OraPerl, DBI, and other solutions were not yet ported to the Win32 platform. For a long time I was stuck. I thought of different solutions, but never tried any.

Ideally I was looking for an ODBC solution. ODBC (Open Database Connectivity) was Microsoft's attempt to standardize database access from within an application. If a single program needed to connect to an Oracle database, an Informix database or an Access database (determined at run time), it would need 3 versions of the database code because each of those databases have their own proprietary data access schemes. With ODBC, you simply write to the ODBC API and let the ODBC Manager and Driver take care of the database language specifics. (Initially just a Windows solution, ODBC is now finding its way onto many Unix boxes).

Finally, along came Dave Roth's Win32::ODBC module. In it, Dave married the ODBC toolkit with Perl, finally giving me (and countless others) access to databases from Perl on Win32 machines. I quickly started to put together the pieces necessary to run a store, starting with the product database, extracting data and creating HTML files dynamically via Perl.

But, in using the ODBC module I found that the documentation was a little sparse. After talking to Dave about it, I set out to document the package more thoroughly. So began my association with the Win32::ODBC module. Since then I have gotten many emails asking for help and examples. This article is an attempt to satisfy some of those requests.

Tutorial
This tutorial will walk you through the basics of Win32::ODBC programming. A number of examples will use a CGI app to illustrate their points, using Lincoln Stein's CGI module (CGI.pm). Because the purpose of this tutorial is to introduce you to Win32::ODBC, I will assume a basic understanding of Perl, SQL (see Demystifying SQL for more info), CGI concepts and HTML. I will also assume that the web server, scripts, ODBC data sources, and everything else are already set up and working.

The following two tables will be used. They are pulled directly from MS-Access, a relational database running under Win95 or WinNT. But the joy of ODBC is that this database could be any database running under any OS, as long as ODBC drivers for the setup exist. The tables, and indeed the SQL itself, are very simple/simplistic, in order to keep the focus on the module.

Table 1: Sauces
Field NameTypeLengthNotes
ProductIDAutoNumbern/aSequence / Primary key
ProductNameText25 
QuantityNumberDoublein ounces
PriceNumberDouble 
PepperTypeText20main type of pepper in sauce
ScovilleTextLongrating of heat content; Jalapeño pepper = 3-4,000 units, Scotch Bonnets (Habañero) = 2-300,000 units!

Table 2: State
Field NameTypeLengthNotes
PIDNumberIntegerProcess ID - Multi-key Primary key
ProductIDNumberLongForeign Key into Sauces table - Multi-key Primary key
SequenceNumberIntegerMulti-key Primary key

Installing Win32::ODBC
To install the module, you need to determine which version of Perl you are using and what build it is. There are now two versions of Perl that run on Win32 systems, one from ActiveState (formerly ActiveWare, formerly hip), and one that is ported from the Perl core distribution. As of this writing, Win32::ODBC only works with the ActiveState et. al version. To determine the build number, type the following at a command prompt:

	C:\> perl -version

You also need to know where the Perl installation directory is (referred to as $PERL below), and you need a temporary directory ($TEMP below). When you have this info, follow these installation steps:

Getting Started
To get started, let's write a simple script that extracts all the data from the database and writes them out in a table. The first thing to do, as when using any Perl module, is to tell the script that we want to use the module.

	use Win32::ODBC;
Next we tell it to create a new ODBC object:

	my($db) = new Win32::ODBC("Article");
This creates an object ($db) which connects to the data source named 'Article'. If we had a user name and password associated with this data source, we could supply them all using an ODBC connect string:

	my($db) = new Win32::ODBC("dsn=Article; uid=foo; pwd=bar");
where 'Article' is the Data Source Name, 'foo' is the User ID, and 'bar' is the Password.

Next, we write the SQL to extract the data (the capitalization is for stylistic reasons):

	$db->Sql("SELECT * FROM Sauces");
and then the loop to fetch the data, one row at a time:

	while ($db->FetchRow()) {
		...
	}
Inside the while loop we actually get the data. In Win32::ODBC, there are two actions, one to fetch the row, and one to get the data that was fetched. There are also two ways to get the fetched data, Data() and DataHash(). The former returns the data into an array, in the order it was selected in the SQL statement. The latter returns the data into a hash, in the form '$hash{FieldName} = DataValue'. If you select all fields using the "*" wildcard, as we did, then the field order that the data comes back in is undefined, so using Data() might not make sense. We'll use DataHash(). Finally, we close the ODBC handle before the end of the program.

Putting it all together, the script should look something like the following:

	use Win32::ODBC;
	my($db) = new Win32::ODBC('Article');
	$db->Sql("SELECT * FROM Sauces");
	while ($db->FetchRow()) {
		my(%data) = $db->DataHash();
		print HTML "<TR><TD>$data{'ProductName'}";
		print HTML "    <TD>$data{'Quantity'} ounces";
		print HTML "    <TD>\$$data{'Price'}";
		print HTML "    <TD>$data{'PepperType'}";
		print HTML "    <TD>$data{'Scoville'} Scoville Units";
	}
	$db->Close();

Debugging
Notice that we did no error checking after calling any of the Win32::ODBC methods. This is not a good programming practice, and I only do it here because column space is precious; you should check religiously for errors. It's actually quite easy to get error messages out of the module.

When we called the Sql() method, we were "compiling" the SQL statement. If we had asked for a column or table that did not exist, we would have generated an error, which we should have checked for. If we do get an error, we should display the error text and die gracefully:

	my($stmt) = "SELECT HasGarlic FROM Sauces";
	if ($db->Sql($stmt)) {
		my($err) = $db->Error;
		warn "Sql() ERROR\n";
		warn "\t\$stmt: $stmt\n";
		warn "\t\$err:  $err\n";
		exit;
	}
The Error() method will return a string with the following format in a scalar context:

	"ErrorNumber ConnectionNumber ErrorText"
In the example above, we asked for a column that does not exist. For me, this generated the following message:

	Sql() ERROR
			$stmt: SELECT HasGarlic FROM Sauces
			$err: [-3010] [1] [0] "[Microsoft][ODBC Microsoft Access 97 Driver] Too few parameters. Expected 1."
The value of $err is generated by the ODBC Manager, not the Win32::ODBC module. As you can see from the message, they can be a little cryptic. But hopefully you will be able to determine the reason for the error.

Two other functions may be helpful in diagnosing why you are not getting back the data you thought you should. FieldNames() returns an array of the names of the fields that are in the current result set. For example, suppose you were trying to figure out why "$data{'scoville'}" kept coming back empty. Using FieldNames() you might find that the hash key is actually capitalized - "$data{'Scoville'}".

The second method to try is DumpData(). This will dump all of the field names and the data for all of the rows in the current result set to the currently selected output filehandle.

If you are convinced that you have found a bug, or are too stumped to go on, and wish to write for help, the most important information to include is the version of both Perl and Win32::ODBC. Simply type:

	perl -version
at the command prompt for Perl's version (copy all of the output down), and write a small script like this to determine the Win32::ODBC version:

	my($pm, $pll) = $db->Version();
	die "PM: $pm, PLL: $pll\n";

CRUD - Create, Read, Update, & Delete
The standard functions to implement in any database or database language are Create (insert), Read (select), Update, and Delete. SQL is no exception, and if SQL can do it, so can ODBC (well, almost).

Suppose that, instead of always displaying all records in the Sauces database, we only wanted to display records whose last name began with a certain letter. After all, the database may have hundreds of records. Our previous SELECT statement would now look like this:

	$db->Sql("SELECT * FROM Sauces WHERE PepperType LIKE '%chile%'");
or put into a CGI context:

	$pattern = $query->param('pattern');
	$db->Sql("SELECT * FROM Sauces WHERE PepperType LIKE '$pattern'");
Both of these statements use the LIKE conditional, which takes a string as a parameter. This string, like all SQL strings, needs to be enclosed in single-quotes. Also note that we are using the SQL wildcard syntax, not regex's (see SQL Wildcards vs. Perl Regular Expressions).

This WHERE clause would certainly cut down on the number of rows displayed, but let's assume that we have a really slow link, and we only want to display 10 rows at a time. In CGI this is not necessarily an easy task, given its statelessness. We'll use the State table to help us retain the query results between accesses.

The first time we access the database, instead of displaying the results of the query, we'll store them in the State table. Inside of the while loop we could do one of two things: store the results in an array and then insert them later into the State table, or insert them into the State table while we fetch them. The former uses one ODBC object:

	#***** SQL statement to fetch the data *****
	$db->Sql("SELECT ProductID FROM Sauces " .
		"WHERE Scoville > 10000 " .
		"ORDER BY Scoville, ProductName");
	#***** fetch each row *****
	my($cnt, $id, @ids) = 0;
	while ($db->FetchRow) {
		#***** store for later use *****
		push(@ids, $db->Data);
	}
	#***** use stored IDs to populate State table *****
	foreach $id (@ids) {
		$db->Sql("INSERT INTO State (PID, ProductID, Sequence) " .
			"VALUES ($$, $id, " . $cnt++ . ")");
	}
while the latter uses a second ODBC object (one for each SQL statement):

	#***** new statement, same connection *****
	my($ins) = new Win32::ODBC($db);
	#***** SQL statement to fetch the data *****
	$db->Sql("SELECT ProductID FROM Sauces " .
		"WHERE Scoville > 10000 " .
		"ORDER BY Scoville, ProductName");
	#***** fetch each row *****
	my($cnt, $id) = 0;
	while ($db->FetchRow) {
		($id) = $db->Data;
		#***** immediately store in State table *****
		$ins->Sql("INSERT INTO State (PID, ProductID, Sequence) " .
			"VALUES ($$, $id, " . $cnt++ . ")");
	}
As soon as the $ins->Sql(...) method is called, the Insert takes place. And as soon as the Insert takes place, it is committed (unless you specify otherwise; see Transactions below).

After filling in the State table, we would then pull rows out of it in sets of 10, again using 2 ODBC objects:

	#***** get CGI parameters *****
	$pid = $query->param('pid'};
	$start = $query->param('start'};
	#***** fetch data out of State table *****
	$dbstate->Sql("SELECT ProductID FROM State " .
		"WHERE PID = $pid AND Sequence >= $start " .
		"AND Sequence < " . ($start + 10) . " " .
		"ORDER BY Sequence");
	#***** fetch each row from State *****
	my($id);
	while ($dbstate->FetchRow) {
		$id = $dbstate->Data;
		#***** fetch actual data from Sauces *****
		$dbsauce->Sql("SELECT * FROM Sauces WHERE ProductID = $id");
		#***** output HTML *****
	}
That will work, but it seems a little inefficient. We could accomplish it all in one SQL statement by using a join:

	#***** get CGI parameters *****
	$pid = $query->param('pid'};
	$start = $query->param('start'};
	#***** fetch Sauces data based on State table data *****
	$db->Sql("SELECT sa.* FROM State st " .
		"INNER JOIN Sauces sa ON st.ProductID = sa.ProductID " .
		"WHERE st.PID = $pid " .
			"AND st.Sequence >= $start " .
			"AND st.Sequence < " . ($start + 10) . " " .
		"ORDER BY st.Sequence");
	#***** fetch each row *****
	while ($db->FetchRow) {
		my(%data) = $db->DataHash;
		#***** output HTML ****
	}
As you can see, the complexity is all in the SQL, not in the use of the ODBC module. We used the basic calls to Sql() and FetchRow() just like before.

Updates and Deletes are similar to Inserts. Given that we have this State table, we would need some process to clean it up. We could clean it up all at once:

	$db->Sql("DELETE FROM State");
or only when a particular user is done with the data:

	$pid = $query->param('pid'};
	$db->Sql("DELETE FROM State WHERE PID = $pid");
Note: none of this is very effective or efficient in terms of real-world CGI. It is merely a convenient analogy to illustrate basic Win32::ODBC concepts. For a more complete (though still simplistic) example, see PepperSearch.pl.

Transactions
Some ODBC drivers support transactions, with commits and rollbacks (see Data Sources to find out how to determine if your driver supports this). Sometime after connecting (usually immediately after, but it depends on your logic flow) you can set the AutoCommit option off, and then rollback or commit depending upon program flow:

	$db->SetConnectOption('SQL_AUTOCOMMIT', 'SQL_AUTOCOMMIT_OFF');
	...
	if ($fatal_error) {
		$db->Transact('SQL_ROLLBACK');
		die "Error - rolling back\n";
	}
	...
	$db->Transact('SQL_COMMIT');

Data Sources
Sometimes you don't know ahead of time which database you need to connect to. You can query the ODBC Manager and get a list:

	print HTML "<SELECT NAME="DataSources" SIZE=1>\n";
	my(%dsn, $key) = Win32::ODBC::DataSources();
	foreach $key (sort keys %dsn) {
		print HTML "<OPTION>$key\n";
	}
	print HTML "</SELECT>\n";
This will produce a combo box with all of the data sources listed. But suppose you knew you only wanted Oracle databases. The value of the returned hash contains the name of the driver, so you could filter them:

	my(%dsn, $key) = Win32::ODBC::DataSources();
	foreach $key (sort keys %dsn) {
		print HTML "<OPTION>$key\n" if $dsn{$key} =~ /Oracle/;
	}
Once you have connected to a driver, you can query it to see if it can handle a specific ODBC API function, such as transactions:

	my(%data) = $db->GetFunctions($db->SQL_API_SQLTRANSACT);
	my($transOK) = $data{$db->SQL_API_SQLTRANSAC};
	$db->SetConnectOption('SQL_AUTOCOMMIT', 'SQL_AUTOCOMMIT_OFF')
		if ($transOK);
or you could query the connection for things like the database name and version:

	my($db_ver) = $db->GetInfo($db->SQL_DBMS_VER);
	my($db_name) = $db->GetInfo($db->SQL_DBMS_NAME);
You can also configure new data sources by using ConfigDSN():

	$db->ConfigDSN($db->ODBC_ADD_DSN, "Microsoft Access Driver (*.mdb)",
							  "DSN=foo", "DBQ=c:\\temp\\foo.mdb");
The second argument, the driver name, has to be specified exactly right, and the subsequent arguments are all driver specific. One way to determine the name and the arguments is to set up a dummy data source of the correct type, connect to it, and then use GetDSN() to find out what it expects when configuring it:

	my(%data, $key) = $db->GetDSN();
	foreach $key (keys %data) {
		print "$key: <$data{$key}>\n";
	}
Then, specify these same elements when adding a new data source, like we did above.

Data Dictionary
After connecting to a data source, you can find out what tables are in it:

	my(@tables) = $db->TableList;
This will return all tables regardless of who owns them. You can use the more versatile Catalog() if you wish to find only specific tables or table types:

	#***** table-type == VIEW *****
	my(@views) = $db->Catalog("", "", "", 'VIEW');
	#***** owner == UBER, type == SYSTEM TABLE *****
	my(@uber) = $db->Catalog("", 'UBER', "", 'SYSTEM TABLE');
Unfortunately, the corresponding Field function, SQLColumns(), was not implemented in Win32::ODBC, so you cannot determine which fields are in a particular table. You can, however, find out what fields or columns are in a specific result set and get their column attributes:

	$db->Sql("SELECT * FROM State");
	my(@fields) = $db->FieldNames;
	my(%type) = $db->ColAttributes($db->SQL_COLUMN_TYPE);
	my(%len) = $db->ColAttributes($db->SQL_COLUMN_LENGTH);
	my(%null) = $db->ColAttributes($db->SQL_COLUMN_NULLABLE);
	foreach $field (sort @fields) {
		print HTML "<TR><TD>$field<TD>$type{$field}",
				"<TD>$len{$field}<TD><BR>",
	$null{$field} == $db->SQL_NO_NULLS ? "NOT NULL" : "",
	"\n";
	}
which for our State table would produce:

	<TR><TD>PID<TD>4<TD>4<TD><BR>
	<TR><TD>ProductID<TD>4<TD>4<TD><BR>
	<TR><TD>Sequence<TD>5<TD>2<TD><BR>

Closing
You know, I never did start that hot sauce business. Once I had all the pieces together and working, I found that solving the problem was much more interesting than running a business. I now use Perl and Win32::ODBC to help with my web site, generating hundreds of pages from several different databases. It's fast, it's easy, and because it's in Perl, it's fun!

So give Win32::ODBC a try. Unleash your data with the power of Perl!


Copyright © Joseph L. Casadonte Jr. 1997-8. All rights reserved.
Win32::ODBC - A Tutorial / 17 January 1998 / joc@netaxs.com