use Tie::DBI;
tie %h,Tie::DBI,'mysql:test','test','id',{CLOBBER=>1};
tie %h,Tie::DBI,{db => 'mysql:test',
table => 'test',
key => 'id',
user => 'nobody',
password => 'ghost',
CLOBBER => 1};
# fetching keys and values
@keys = keys %h;
@fields = keys %{$h{$keys[0]}};
print $h{'id1'}->{'field1'};
while (($key,$value) = each %h) {
print "Key = $key:\n";
foreach (sort keys %$value) {
print "\t$_ => $value->{$_}\n";
}
}
# changing data
$h{'id1'}->{'field1'} = 'new value';
$h{'id1'} = { field1 => 'newer value',
field2 => 'even newer value',
field3 => "so new it's squeaky clean" };
# other functions
tied(%h)->commit;
tied(%h)->rollback;
tied(%h)->select_where('price > 1.20');
@fieldnames = tied(%h)->fields;
$dbh = tied(%h)->dbh;
tie %var,Tie::DBI,[database,table,keycolumn] [,\%options]
Tie a variable to a database by providing the variable name, the tie interface (always ``Tie::DBI''), the data source name, the table to tie to, and the column to use as the hash key. You may also pass various flags to the interface in an associative array.
Note that some drivers (Oracle in particular) have an irritating habit of appending blanks to the end of fixed-length fields. This will screw up Tie::DBI's routines for getting key names. To avoid this you should create the database handle with a ChopBlanks option of TRUE. You should also use a PrintError option of true to avoid complaints during STORE and LISTFIELD calls.
Operation Clobber Comment
$i = $h{strawberries}->{price} 0 All read operations
$h{strawberries}->{price} += 5 1 Update fields
$h{bananas}={price=>23,quant=>3} 1 Add records
delete $h{strawberries} 2 Delete records
%h = () 3 Clear entire table
undef %h 3 Another clear operation
All database operations are contingent upon your access privileges. If your account does not have write permission to the database, hash store operations will fail despite the setting of CLOBBER.
commit() method.
The autocommit option defaults to true.
tie() statement.
Ordinarily this will be the table's primary key, although any unique column
will do.
Fetching an individual record returns a reference to a hash of field names and values. This hash reference is itself a tied object, so that operations on it directly affect the database.
-produce-
produce_id price quantity description
strawberries 1.20 8 Fresh Maine strawberries
apricots 0.85 2 Ripe Norwegian apricots
bananas 1.30 28 Sweet Alaskan bananas
kiwis 1.50 9 Juicy New York kiwi fruits
eggs 1.00 12 Farm-fresh Atlantic eggs
We tie the variable %produce to the table in this way:
tie %produce,Tie::DBI,{db => 'mysql:stock',
table => 'produce',
key => 'produce_id',
CLOBBER => 2 # allow most updates
};
We can get the list of keys this way:
print join(",",keys %produce);
=> strawberries,apricots,bananas,kiwis
Or get the price of eggs thusly:
$price = $produce{eggs}->{price};
print "The price of eggs = $price";
=> The price of eggs = 1.2
String interpolation works as you would expect:
print "The price of eggs is still $produce{eggs}->{price}"
=> The price of eggs is still 1.2
Various types of syntactic sugar are allowed. For example, you can refer to $produce{eggs}{price} rather than $produce{eggs}->{price}. Array slices are fully supported as well:
($apricots,$kiwis) = @produce{apricots,kiwis};
print "Kiwis are $kiwis->{description};
=> Kiwis are Juicy New York kiwi fruits
($price,$description) = @{$produce{eggs}}{price,description};
=> (2.4,'Farm-fresh Atlantic eggs')
If you provide the tied hash with a comma-delimited set of record names, and you are not requesting an array slice, then the module does something interesting. It generates a single SQL statement that fetches the records from the database in a single pass (rather than the multiple passes required for an array slice) and returns the result as a reference to an array. For many records, this can be much faster. For example:
$result = $produce{apricots,bananas};
=> ARRAY(0x828a8ac)
($apricots,$bananas) = @$result;
print "The price of apricots is $apricots->{price}";
=> The price of apricots is 0.85
Field names work in much the same way:
($price,$quantity) = @{$produce{apricots}{price,quantity}};
print "There are $quantity apricots at $price each";
=> There are 2 apricots at 0.85 each";
To insert a new record or update an existing one, assign a hash reference
to the record. For example, you can create a new record in
%produce with the key ``avocados'' in this manner:
$produce{avocados} = { price => 2.00,
quantity => 8,
description => 'Choice Irish avocados' };
This will work with any type of hash reference, including records extracted from another table or database.
Only keys that correspond to valid fields in the table will be accepted. You will be warned if you attempt to set a field that doesn't exist, but the other fields will be correctly set. Likewise, you will be warned if you attempt to set the key field. These warnings can be turned off by setting the WARN option to a zero value. It is not currently possible to add new columns to the table. You must do this manually with the appropriate SQL commands.
The same syntax can be used to update an existing record. The fields given in the hash reference replace those in the record. Fields that aren't explicitly listed in the hash retain their previous values. In the following example, the price and quantity of the ``kiwis'' record are updated, but the description remains the same:
$produce{kiwis} = { price=>1.25,quantity=>20 };
You may update existing records on a field-by-field manner in the natural way:
$produce{eggs}{price} = 1.30;
$produce{eggs}{price} *= 2;
print "The price of eggs is now $produce{eggs}{price}";
=> The price of eggs is now 2.6.
Obligingly enough, you can use this syntax to insert new records too, as in $produce{mangoes}{description}=``Sun-ripened Idaho mangoes''. However, this type of update is inefficient because a separate SQL statement is generated for each field. If you need to update more than one field at a time, use the record-oriented syntax shown earlier. It's much more efficient because it gets the work done with a single SQL command.
Insertions and updates may fail for any of a number of reasons, most commonly:
eval().
tie() call (which returns the object), or call
tied() on the tie variable to recover the object.
Connect() is responsible
for establishing the connection with the DBI database.
Errstr() and error() return $DBI::errstr and
$DBI::error respectively. You may may override these methods in subclasses
if you wish. For example, replace connect() with this code in
order to use persistent database connections in Apache modules: use
Apache::DBI; # somewhere in the declarations sub connect { my
($class,$dsn,$user,$password,$options) = @_; return
Apache::DBI->connect($dsn,$user, $password,$options); }
(tied %produce)->commit();
When using a database with the autocommit option turned off, values that
are stored into the hash will not become permanent until
commit() is called. Otherwise they are lost when the
application terminates or the hash is untied.
Some SQL databases don't support transactions, in which case you will see a warning message if you attempt to use this function.
(tied %produce)->rollback();
When using a database with the autocommit option turned off, this function
will roll back changes to the database to the state they were in at the
last commit(). This function has no effect on database that
don't support transactions.
Don't expect too much from this function. If you want to execute a complex query, you're better off using the database handle (see below) to make the SQL query yourself with the DBI interface.
$dbh = (tied %produce)->dbh(); This returns the tied hash's underlying database handle. You can use this handle to create and execute your own SQL queries.
(tied %produce)->{DEBUG}++;
This lets you change the behavior of the tied hash on the fly, such as temporarily granting your program write permission.
There are other variables there too, such as the name of the key column and database table. Change them at your own risk!
each() loop. The database driver was mySQL, running on a 133
MHz Pentium laptop with Linux 2.0.30. I compared Tie::RDBM, to DB_File, and
to the same task using vanilla DBI SQL statements. The results are shown
below:
UPDATE FETCH Tie::DBI 70 s 6.1 s Vanilla DBI 14 s 2.0 s DB_File 3 s 1.06 s
There is about a five-fold penalty for updates, and a three-fold penalty for fetches when using this interface. Some of the penalty is due to the overhead for creating sub-objects to handle individual fields, and some of it is due to the inefficient way the store and fetch operations are implemented. For example, using the tie interface, a statement like $h{record}{field}++ requires as much as four trips to the database: one to verify that the record exists, one to fetch the field, and one to store the incremented field back. If the record doesn't already exist, an additional statement is required to perform the insertion. I have experimented with cacheing schemes to reduce the number of trips to the database, but the overhead of maintaining the cache is nearly equal to the performance improvement, and cacheing raises a number of potential concurrency problems.
Clearly you would not want to use this interface for applications that require a large number of updates to be processed rapidly.
each() call produces a fatal error when used with the
Sybase driver to access Microsoft SQL server. This is because this server
only allows one query to be active at a given time. A workaround is to use
keys() to fetch all the keys yourself. It is not known whether
real Sybase databases suffer from the same problem.
The delete() operator will not work correctly for setting
field values to null with DBD::CSV or with DBD::Pg. CSV files do not have a
good conception of database nulls. Instead you will set the field to an
empty string. DBD::Pg just seems to be broken in this regard.
Copyright (c) 1998, Lincoln D. Stein
This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
perl(1), DBI(3), Tie::RDBM(3)