This document has mistakes a-plenty, which Tim ( amongst others ) have commented on. They'll be incorporated into a better document at a later date, but for now.....
> I don't suppose you have a snippet of code to show how to do a
> submit with mSQL and DBD?
I'm going to assume you mean through a WWW form? Anyway, it's much of a
muchness. Try this ( off the top of my head! ):
-----------------8<----------------------------8<----------------------
#!/usr/bin/perl -w
#
use DBI; # Load the DBI Switch
$drh = DBI->install_driver( 'mSQL' );
# Load the mSQL driver. Returns a driver
# - handle in $drh
$dbh = $drh->connect( '', 'rubbish' );
# Connects via the mSQL driver ( as designated
# - by the driver handle $drh ) to a database
# - on the local machine called 'rubbish'.
die "Cannot connect: $DBI::errstr\n"
unless $dbh;
# Check we've connected. Die if not.
&ReadParse; # A routine to parse a URL. See CGI.pm
foreach $arg ( keys %in ) { # The args are returned in a hash %in
print "$arg: $in{$arg}\n";
}
# Assume that we've submitted a form with the following fields:
#
# o Name
# o Email address
#
# Assume we have a table in the 'rubbish' database called 'cgistuff' with the
# - following fields:
#
# o name CHAR(64)
# o email CHAR(128)
$cursor = $dbh->do( " # Insert a row of data into the table
INSERT INTO cgistuff VALUES # - via the database handle returned
( $in{'name'}, $in{'email'} )" ); # - above
# A $dbh->do doesn't require an execute or finish phase. It just happens
# - atomically.
$dbh->disconnect; # Disconnect the database handle. This actually
# - disconnects you from the database specified
# - by $dbh
exit 0;
--------------------------8<--------------------------8<---------------------
> I am confused by the code. Where do I find a complete list of the
> functions that are available to me.
The 'test.pl' script in 0.60pl7 exercises all the functions in the driver. A
basic problem to using drivers is understanding the architecture, which
I'll take a moment to try and explain:
$drh--------------------- Driver handles
| |
$dbh---$dbh---$dbh---$dbh Database handles
| | | |
$sth---$sth--+ $sth-+ $sth-+ $sth-+-$sth Statement handles
( Apologies for the bad drawing! ). What is says though is:
o Before you do operations with a certain type of database you load
the database driver, eg, if you want to use mSQL you call:
$drh = DBI->install_driver( 'mSQL' );
If we thern wished to load the Oracle driver, another line:
$oradrh = DBI->install_driver( 'Oracle' );
Notice they are assigned to different variables. The driver for each
database *type* ( Oracle, mSQL ) is loaded only ONCE per script.
Hence the one $drh above. All operations on that type of database
are invoked indirectly through the $drh ( or DRIVER HANDLE ).
o Now that we've got a driver loaded, we can start doing things with
databases of that type, eg, connect! Obviously you have to connect to
a specific database before you can start doing stuff, so these
functions are called against the DRIVER HANDLE, eg:
$dbh = $drh->connect( '', 'rubbish' );
This connects you to an mSQL database ( as defined by the driver
handle you use to connect ), identified by the arguments passed to
the 'connect' call. If successfull, it returns a variable $dbh
( DATABASE HANDLE ).
The differences between a DRIVER HANDLE and a DATABASE HANDLE is
that the driver handle is independent of actual databases. It just
tells the DBI Switch which set of lower-level database routines to
call, eg, the Switch thinks "I've just had a connect request with
these arguments, and it's via the $drh driver handle. Now, the $drh
driver handle is an mSQL one, so we'll call the mSQL connect primitive to do the actual work."
Previously, we only had *one* driver handle per database type.
Database handles are much more plentiful. You can have as many
database handles as you need within one driver handle, eg:
$dbh1 = $drh->connect( '', 'rubbish1' );
$dbh2 = $drh->connect( '', 'rubbish2' );
$dbh3 = $drh->connect( 'someotherhost', 'pants1' );
$dbh4 = $drh->connect( 'someotherhost2', 'pants2' );
Connects us simultaneously to 4 different databases ( all mSQL, using
the *one* driver handle ). These database are located on 3 different
physical machines. This can be done within one script.
o Statement handles are another level down. Think of the whole
structure as a tree. You can one DRIVER HANDLE, but many DATABASE
HANDLES from that, and many STATEMENT HANDLES from *each* DATABASE
HANDLE. Yes?
Good! Now, a statement handle is a handle created on operations within the driver which act on statements! No, really! A statement is an
operation on actual data within tables ( usually ), eg,
$sth = $dbh1->do( "DELETE FROM table1" );
This executes the statement "DELETE FROM table1" in the database
indicated previously by $dbh1 ( from above, the one on 'localhost'
called 'rubbish1'. We could now do:
$sth = $dbh4->do( "UPDATE cgistuff SET email = ''" );
In our CGI example, which would clear all the email fields.
As in the relationship between DATABASE HANDLES and DRIVER HANDLES
of the many to one, it exists between STATEMENT HANDLES and DATABASE
HANDLES.
Another aspect of STATEMENT HANDLES is the 'cursor', eg:
$sth = $dbh1->prepare( "SELECT id, name, email
FROM madeuptable" );
$sth->execute;
while ( @row = $sth->fetchrow ) {
print "Row: @row\n";
}
$sth->finish;
This chunk of code prepares a STATEMENT HANDLE $sth with regard to
the statement "SELECT .....". If the database allows you to prepare
it ( $sth is returned defined and not undef ), ( a successfull
prepare implies the field name are correct, the database is
connectable still and the table exists ), then you can continue
working with that statement.
The second part of using a cursor is to EXECUTE the statement. This
sends it to the database engine to return the rows into a cache. At
this point, you specifiy bind variables ( if any ), but these don't
exist in DBD::mSQL, so don't worry!
After a successful execution, the data is ready to fetch from the
database. We can now execute a 'while' loop calling 'fetchrow'
against the STATEMENT HANDLE. If the fetchrow returns 'undef', the
loop will terminate. The 'fetchrow' will return undef when there's
no more data to be fetched.
A LIST is returned from fetchrow. In the example above, we're
fetching all the fields into a LIST, but you could have done:
while ( ( $id, $name, $email ) = $sth->fetchrow )
and fetched the fields directly into separate variables.
The final call is: 'finish' which closes a cursor and is called
usually after all the rows have been fetched. This frees up the
memory used by the cursor.
> Thanks for your help
Hope this helps!
Again, with this information in mind, re-read the 'test.pl' script with
DBD::mSQL-0.60pl7. It'll show which functions are callable against which sort
of HANDLE. Although here's another off the top of my head list. I may have
missed one or two.....
$drh is a DRIVER HANDLE, $dbh a DATABASE HANDLE and $sth a STATEMENT HANDLE.
@ary is a LIST, $scalar is an ordinary scalar, %hash is a hash. $rv is a
return value which is ignorable.
(*) means non-standard and mSQL specific
Invoking through Returns
---------------- -------
DBI->install_driver $drh
$drh->connect $dbh
$drh->func( '_ListDBs' ) @ary *
$drh->disconnect_all ?
$dbh->func( '_ListTables' ) @ary *
$dbh->disconnect $rv
$dbh->do $rv
$dbh->prepare $sth
$sth->execute $rv
$sth->fetchrow @ary
$sth->finish $rv