Perl ODBC Sample-Connecting to an NT Datasource

#!/usr/bin/perl

#here is a DSN-> change it in control panel->ODBC->system dsn
#it can be _any_ datasource!
	$DSN="FOXTEST";

#This loads the ODBC stuff
	use Win32::ODBC;

#Connect to datasource
#the only interesting/essential line is this:
#	$db=new Win32::ODBC($DSN)
# that is, create a thing called a $db that is a 'handle' to an object 
#that has a connection to the database

#this code just checks, if it doesn't connect (! means not), then print
#some error messages
	if (!($db=new Win32::ODBC($DSN))) {
		print "Error connecting to $DSN\n";
	    print "Error: " . Win32::ODBC::Error() . "\n";
	} else {
	   print "Database opened...\n";
	}

#here is a statement to create a new table
	$SqlStatement = "create table foo (f1 text, f2 text)";	

#this is just like the above statement.  One interesting line, followed
#by error stuff.  in this case we have the function Sql  which returns
#nothing if it works.  The interesting bit of code is:
#	$db->Sql($SqlStatement)
#it says 'run the function Sql with our statement.  You could also skip 
#the variable and write
#	$db->Sql("create table foo (f1 text, f2 text)")
#

	if ($db->Sql($SqlStatement)) {
		print "SQL failed.\n";
	    print "Error: " . $db->Error() . "\n";
	} else  {
		print "Data inserted\n"
	}

#this is getting boring-same thing, except that we insert data, same old
#same old as in Cold Fusion, or any of our SQL adventures
	$SqlStatement = "insert into foo (f1, f2) values ('field 1', 'field 2')";	
	if ($db->Sql($SqlStatement)) {
		print "SQL failed.\n";
	    print "Error: " . $db->Error() . "\n";
	} else  {
		print "Data inserted\n"
	}

#and again, here we select data.  But we have a twist, here we actually
#get something back!

	$SqlStatement = "select * from survey, answer where survey.sid=answer.sid";	
	if ($db->Sql($SqlStatement)) {
		print "SQL failed.\n";
	        print "Error: " . $db->Error() . "\n";
	} else  {

	#thing of  while($db->FetchRow() { } as being like  
	#we say, while FetchRow returns more data, we keep looping.
	#FetchRow gets a row of data from the database and puts it into
	#'limbo'  We get it out of limbo with $db->DataHash()
	#(that is, there is a function called 'DataHash' in the code in
	#the file odbc.pm with is under the win32 directory, somewhere in 
	#the bowels of your perl installation.  If you find that file, you
	#will see other clever ways of getting to the data, but DataHash is
	#probably good enough

		while($db->FetchRow()) {
			$rowcnt++;

			#so now this row of data from the database lives in the
			#'hash' %Data.  If you have the fieldname 'foo' then its
			#contents live in $Data{"foo"}
			#That syntax is a strange perlism, but you get used to it.
			#Rather than get to the data one field at a time, and
			#because I don't always know all of the field names, I do this foreach
			#loop...write me if you want to know more.

			%Data = $db->DataHash();
			foreach $k (keys %Data){
				print "$k $Data{$k}  ";
			}
			print "\n"

		} #the while loop to walk through the data

	} #the else, the statement worked

#Perl is good.  Very very good.
#Rich Gibson, Rich@chilidog.com