1Tie::DBI(3)           User Contributed Perl Documentation          Tie::DBI(3)
2
3
4

NAME

6       Tie::DBI - Tie hashes to DBI relational databases
7

SYNOPSIS

9         use Tie::DBI;
10         tie %h,'Tie::DBI','mysql:test','test','id',{CLOBBER=>1};
11
12         tie %h,'Tie::DBI',{db       => 'mysql:test',
13                          table    => 'test',
14                          key      => 'id',
15                          user     => 'nobody',
16                          password => 'ghost',
17                          CLOBBER  => 1};
18
19         # fetching keys and values
20         @keys = keys %h;
21         @fields = keys %{$h{$keys[0]}};
22         print $h{'id1'}->{'field1'};
23         while (($key,$value) = each %h) {
24           print "Key = $key:\n";
25           foreach (sort keys %$value) {
26               print "\t$_ => $value->{$_}\n";
27           }
28         }
29
30         # changing data
31         $h{'id1'}->{'field1'} = 'new value';
32         $h{'id1'} = { field1 => 'newer value',
33                       field2 => 'even newer value',
34                       field3 => "so new it's squeaky clean" };
35
36         # other functions
37         tied(%h)->commit;
38         tied(%h)->rollback;
39         tied(%h)->select_where('price > 1.20');
40         @fieldnames = tied(%h)->fields;
41         $dbh = tied(%h)->dbh;
42

DESCRIPTION

44       This module allows you to tie Perl associative arrays (hashes) to SQL
45       databases using the DBI interface.  The tied hash is associated with a
46       table in a local or networked database.  One column becomes the hash
47       key.  Each row of the table becomes an associative array, from which
48       individual fields can be set or retrieved.
49

USING THE MODULE

51       To use this module, you must have the DBI interface and at least one
52       DBD (database driver) installed.  Make sure that your database is up
53       and running, and that you can connect to it and execute queries using
54       DBI.
55
56   Creating the tie
57          tie %var,'Tie::DBI',[database,table,keycolumn] [,\%options]
58
59       Tie a variable to a database by providing the variable name, the tie
60       interface (always "Tie::DBI"), the data source name, the table to tie
61       to, and the column to use as the hash key.  You may also pass various
62       flags to the interface in an associative array.
63
64       database
65           The database may either be a valid DBI-style data source string of
66           the form "dbi:driver:database_name[:other information]", or a
67           database handle that has previously been opened.  See the
68           documentation for DBI and your DBD driver for details.  Because the
69           initial "dbi" is always present in the data source, Tie::DBI will
70           add it for you if necessary.
71
72           Note that some drivers (Oracle in particular) have an irritating
73           habit of appending blanks to the end of fixed-length fields.  This
74           will screw up Tie::DBI's routines for getting key names.  To avoid
75           this you should create the database handle with a ChopBlanks option
76           of TRUE.  You should also use a PrintError option of true to avoid
77           complaints during STORE and LISTFIELD calls.
78
79       table
80           The table in the database to bind to.  The table must previously
81           have been created with a SQL CREATE statement.  This module will
82           not create tables for you or modify the schema of the database.
83
84       key The column to use as the hash key.  This column must prevoiusly
85           have been defined when the table was created.  In order for this
86           module to work correctly, the key column must be declared unique
87           and not nullable.  For best performance, the column should be also
88           be declared a key.  These three requirements are automatically
89           satisfied for primary keys.
90
91       It is possible to omit the database, table and keycolumn arguments, in
92       which case the module tries to retrieve the values from the options
93       array.  The options array contains a set of option/value pairs.  If not
94       provided, defaults are assumed.  The options are:
95
96       user
97           Account name to use for database authentication, if necessary.
98           Default is an empty string (no authentication necessary).
99
100       password
101           Password to use for database authentication, if necessary.  Default
102           is an empty string (no authentication necessary).
103
104       db  The database to bind to the hash, if not provided in the argument
105           list.  It may be a DBI-style data source string, or a previously-
106           opened database handle.
107
108       table
109           The name of the table to bind to the hash, if not provided in the
110           argument list.
111
112       key The name of the column to use as the hash key, if not provided in
113           the argument list.
114
115       CLOBBER (default 0)
116           This controls whether the database is writable via the bound hash.
117           A zero value (the default) makes the database essentially read
118           only.  An attempt to store to the hash will result in a fatal
119           error.  A CLOBBER value of 1 will allow you to change individual
120           fields in the database, and to insert new records, but not to
121           delete entire records.  A CLOBBER value of 2 allows you to delete
122           records, but not to erase the entire table.  A CLOBBER value of 3
123           or higher will allow you to erase the entire table.
124
125               Operation                       Clobber      Comment
126
127               $i = $h{strawberries}->{price}     0       All read operations
128               $h{strawberries}->{price} += 5     1       Update fields
129               $h{bananas}={price=>23,quant=>3}   1       Add records
130               delete $h{strawberries}            2       Delete records
131               %h = ()                            3       Clear entire table
132               undef %h                           3       Another clear operation
133
134           All database operations are contingent upon your access privileges.
135           If your account does not have write permission to the database,
136           hash store operations will fail despite the setting of CLOBBER.
137
138       AUTOCOMMIT (default 1)
139           If set to a true value, the "autocommit" option causes the database
140           driver to commit after every store statement.  If set to a false
141           value, this option will not commit to the database until you
142           explicitly call the Tie::DBI commit() method.
143
144           The autocommit option defaults to true.
145
146       DEBUG (default 0)
147           When the DEBUG option is set to a non-zero value the module will
148           echo the contents of SQL statements and other debugging information
149           to standard error.  Higher values of DEBUG result in more verbose
150           (and annoying) output.
151
152       WARN (default 1)
153           If set to a non-zero value, warns of illegal operations, such as
154           attempting to delete the value of the key column.  If set to a zero
155           value, these errors will be ignored silently.
156
157       CASESENSITIV (default 0)
158           If set to a non-zero value, all Fieldnames are casesensitiv. Keep
159           in mind, that your database has to support casesensitiv Fields if
160           you want to use it.
161

USING THE TIED ARRAY

163       The tied array represents the database table.  Each entry in the hash
164       is a record, keyed on the column chosen in the tie() statement.
165       Ordinarily this will be the table's primary key, although any unique
166       column will do.
167
168       Fetching an individual record returns a reference to a hash of field
169       names and values.  This hash reference is itself a tied object, so that
170       operations on it directly affect the database.
171
172   Fetching information
173       In the following examples, we will assume a database table structured
174       like this one:
175
176                           -produce-
177           produce_id    price   quantity   description
178
179           strawberries  1.20    8          Fresh Maine strawberries
180           apricots      0.85    2          Ripe Norwegian apricots
181           bananas       1.30    28         Sweet Alaskan bananas
182           kiwis         1.50    9          Juicy New York kiwi fruits
183           eggs          1.00   12          Farm-fresh Atlantic eggs
184
185       We tie the variable %produce to the table in this way:
186
187           tie %produce,'Tie::DBI',{db    => 'mysql:stock',
188                                  table => 'produce',
189                                  key   => 'produce_id',
190                                  CLOBBER => 2 # allow most updates
191                                  };
192
193       We can get the list of keys this way:
194
195           print join(",",keys %produce);
196              => strawberries,apricots,bananas,kiwis
197
198       Or get the price of eggs thusly:
199
200           $price = $produce{eggs}->{price};
201           print "The price of eggs = $price";
202               => The price of eggs = 1.2
203
204       String interpolation works as you would expect:
205
206           print "The price of eggs is still $produce{eggs}->{price}"
207               => The price of eggs is still 1.2
208
209       Various types of syntactic sugar are allowed.  For example, you can
210       refer to $produce{eggs}{price} rather than $produce{eggs}->{price}.
211       Array slices are fully supported as well:
212
213           ($apricots,$kiwis) = @produce{apricots,kiwis};
214           print "Kiwis are $kiwis->{description};
215               => Kiwis are Juicy New York kiwi fruits
216
217           ($price,$description) = @{$produce{eggs}}{price,description};
218               => (2.4,'Farm-fresh Atlantic eggs')
219
220       If you provide the tied hash with a comma-delimited set of record
221       names, and you are not requesting an array slice, then the module does
222       something interesting.  It generates a single SQL statement that
223       fetches the records from the database in a single pass (rather than the
224       multiple passes required for an array slice) and returns the result as
225       a reference to an array.  For many records, this can be much faster.
226       For example:
227
228            $result = $produce{apricots,bananas};
229                => ARRAY(0x828a8ac)
230
231            ($apricots,$bananas) = @$result;
232            print "The price of apricots is $apricots->{price}";
233                => The price of apricots is 0.85
234
235       Field names work in much the same way:
236
237            ($price,$quantity) = @{$produce{apricots}{price,quantity}};
238            print "There are $quantity apricots at $price each";
239                => There are 2 apricots at 0.85 each";
240
241       Note that this takes advantage of a bit of Perl syntactic sugar which
242       automagically treats $h{'a','b','c'} as if the keys were packed
243       together with the $; pack character.  Be careful not to fall into this
244       trap:
245
246            $result = $h{join( ',', 'apricots', 'bananas' )};
247                => undefined
248
249       What you really want is this:
250
251            $result = $h{join( $;, 'apricots', 'bananas' )};
252                => ARRAY(0x828a8ac)
253
254   Updating information
255       If CLOBBER is set to a non-zero value (and the underlying database
256       privileges allow it), you can update the database with new values.  You
257       can operate on entire records at once or on individual fields within a
258       record.
259
260       To insert a new record or update an existing one, assign a hash
261       reference to the record.  For example, you can create a new record in
262       %produce with the key "avocados" in this manner:
263
264          $produce{avocados} = { price       => 2.00,
265                                 quantity    => 8,
266                                 description => 'Choice Irish avocados' };
267
268       This will work with any type of hash reference, including records
269       extracted from another table or database.
270
271       Only keys that correspond to valid fields in the table will be
272       accepted.  You will be warned if you attempt to set a field that
273       doesn't exist, but the other fields will be correctly set.  Likewise,
274       you will be warned if you attempt to set the key field.  These warnings
275       can be turned off by setting the WARN option to a zero value.  It is
276       not currently possible to add new columns to the table.  You must do
277       this manually with the appropriate SQL commands.
278
279       The same syntax can be used to update an existing record.  The fields
280       given in the hash reference replace those in the record.  Fields that
281       aren't explicitly listed in the hash retain their previous values.  In
282       the following example, the price and quantity of the "kiwis" record are
283       updated, but the description remains the same:
284
285           $produce{kiwis} = { price=>1.25,quantity=>20 };
286
287       You may update existing records on a field-by-field manner in the
288       natural way:
289
290           $produce{eggs}{price} = 1.30;
291           $produce{eggs}{price} *= 2;
292           print "The price of eggs is now $produce{eggs}{price}";
293               => The price of eggs is now 2.6.
294
295       Obligingly enough, you can use this syntax to insert new records too,
296       as in $produce{mangoes}{description}="Sun-ripened Idaho mangoes".
297       However, this type of update is inefficient because a separate SQL
298       statement is generated for each field.  If you need to update more than
299       one field at a time, use the record-oriented syntax shown earlier.
300       It's much more efficient because it gets the work done with a single
301       SQL command.
302
303       Insertions and updates may fail for any of a number of reasons, most
304       commonly:
305
306       1. You do not have sufficient privileges to update the database
307       2. The update would violate an integrity constraint, such as making a
308       non-nullable field null, overflowing a numeric field, storing a string
309       value in a numeric field, or violating a uniqueness constraint.
310
311       The module dies with an error message when it encounters an error
312       during an update.  To trap these erorrs and continue processing, wrap
313       the update an eval().
314
315   Other functions
316       The tie object supports several useful methods.  In order to call these
317       methods, you must either save the function result from the tie() call
318       (which returns the object), or call tied() on the tie variable to
319       recover the object.
320
321       connect(), error(), errstr()
322           These are low-level class methods.  Connect() is responsible for
323           establishing the connection with the DBI database.  Errstr() and
324           error() return $DBI::errstr and $DBI::error respectively.  You may
325           may override these methods in subclasses if you wish.  For example,
326           replace connect() with this code in order to use persistent
327           database connections in Apache modules:
328
329            use Apache::DBI;  # somewhere in the declarations
330            sub connect {
331            my ($class,$dsn,$user,$password,$options) = @_;
332               return Apache::DBI->connect($dsn,$user,
333                                           $password,$options);
334            }
335
336       commit()
337              (tied %produce)->commit();
338
339           When using a database with the autocommit option turned off, values
340           that are stored into the hash will not become permanent until
341           commit() is called.  Otherwise they are lost when the application
342           terminates or the hash is untied.
343
344           Some SQL databases don't support transactions, in which case you
345           will see a warning message if you attempt to use this function.
346
347       rollback()
348              (tied %produce)->rollback();
349
350           When using a database with the autocommit option turned off, this
351           function will roll back changes to the database to the state they
352           were in at the last commit().  This function has no effect on
353           database that don't support transactions.
354
355       select_where()
356              @keys=(tied %produce)->select_where('price > 1.00 and quantity < 10');
357
358           This executes a limited form of select statement on the tied table
359           and returns a list of records that satisfy the conditions.  The
360           argument you provide should be the contents of a SQL WHERE clause,
361           minus the keyword "WHERE" and everything that ordinarily precedes
362           it.  Anything that is legal in the WHERE clause is allowed,
363           including function calls, ordering specifications, and sub-selects.
364           The keys to those records that meet the specified conditions are
365           returned as an array, in the order in which the select statement
366           returned them.
367
368           Don't expect too much from this function.  If you want to execute a
369           complex query, you're better off using the database handle (see
370           below) to make the SQL query yourself with the DBI interface.
371
372       dbh()
373              $dbh = (tied %produce)->dbh();
374
375           This returns the tied hash's underlying database handle.  You can
376           use this handle to create and execute your own SQL queries.
377
378       CLOBBER, DEBUG, WARN
379           You can get and set the values of CLOBBER, DEBUG and WARN by
380           directly accessing the object's hash:
381
382               (tied %produce)->{DEBUG}++;
383
384           This lets you change the behavior of the tied hash on the fly, such
385           as temporarily granting your program write permission.
386
387           There are other variables there too, such as the name of the key
388           column and database table.  Change them at your own risk!
389

PERFORMANCE

391       What is the performance hit when you use this module rather than the
392       direct DBI interface?  It can be significant.  To measure the overhead,
393       I used a simple benchmark in which Perl parsed a 6180 word text file
394       into individual words and stored them into a database, incrementing the
395       word count with each store.  The benchmark then read out the words and
396       their counts in an each() loop.  The database driver was mySQL, running
397       on a 133 MHz Pentium laptop with Linux 2.0.30.  I compared Tie::RDBM,
398       to DB_File, and to the same task using vanilla DBI SQL statements.  The
399       results are shown below:
400
401                     UPDATE         FETCH
402         Tie::DBI      70 s        6.1  s
403         Vanilla DBI   14 s        2.0  s
404         DB_File        3 s        1.06 s
405
406       There is about a five-fold penalty for updates, and a three-fold
407       penalty for fetches when using this interface.  Some of the penalty is
408       due to the overhead for creating sub-objects to handle individual
409       fields, and some of it is due to the inefficient way the store and
410       fetch operations are implemented.  For example, using the tie
411       interface, a statement like $h{record}{field}++ requires as much as
412       four trips to the database: one to verify that the record exists, one
413       to fetch the field, and one to store the incremented field back.  If
414       the record doesn't already exist, an additional statement is required
415       to perform the insertion.  I have experimented with cacheing schemes to
416       reduce the number of trips to the database, but the overhead of
417       maintaining the cache is nearly equal to the performance improvement,
418       and cacheing raises a number of potential concurrency problems.
419
420       Clearly you would not want to use this interface for applications that
421       require a large number of updates to be processed rapidly.
422

BUGS

BUGS

425       The each() call produces a fatal error when used with the Sybase driver
426       to access Microsoft SQL server. This is because this server only allows
427       one query to be active at a given time.  A workaround is to use keys()
428       to fetch all the keys yourself.  It is not known whether real Sybase
429       databases suffer from the same problem.
430
431       The delete() operator will not work correctly for setting field values
432       to null with DBD::CSV or with DBD::Pg.  CSV files do not have a good
433       conception of database nulls.  Instead you will set the field to an
434       empty string.  DBD::Pg just seems to be broken in this regard.
435

AUTHOR

437       Lincoln Stein, lstein@cshl.org
438
440         Copyright (c) 1998, Lincoln D. Stein
441
442       This library is free software; you can redistribute it and/or modify it
443       under the same terms as Perl itself.
444

AVAILABILITY

446       The latest version can be obtained from:
447
448          http://www.genome.wi.mit.edu/~lstein/Tie-DBI/
449

SEE ALSO

451       perl(1), DBI(3), Tie::RDBM(3)
452
453
454
455perl v5.38.0                      2023-07-21                       Tie::DBI(3)
Impressum