DBD::DBM - a DBI driver for DBM & MLDBM files


        use DBI;
        $dbh = DBI->connect('dbi:DBM:');                # defaults to SDBM_File
        $dbh = DBI->connect('DBI:DBM(RaiseError=1):');  # defaults to SDBM_File
        $dbh = DBI->connect('dbi:DBM:type=GDBM_File');  # defaults to GDBM_File
        $dbh = DBI->connect('dbi:DBM:mldbm=Storable');  # MLDBM with SDBM_File
                                                        # and Storable


        $dbh = DBI->connect('dbi:DBM:', undef, undef);
        $dbh = DBI->connect('dbi:DBM:', undef, undef, { dbm_type => 'ODBM_File' });

       and other variations on connect() as shown in the DBI docs and with the
       dbm_ attributes shown below

       ... and then use standard DBI prepare, execute, fetch, placeholders,
       etc., see "QUICK START" for an example


       DBD::DBM is a database management sytem that can work right out of the
       box.  If you have a standard installation of Perl and a standard
       installation of DBI, you can begin creating, accessing, and modifying
       database tables without any further installation.  You can also add
       some other modules to it for more robust capabilities if you wish.

       The module uses a DBM file storage layer.  DBM file storage is common
       on many platforms and files can be created with it in many languges.
       That means that, in addition to creating files with DBI/SQL, you can
       also use DBI/SQL to access and modify files created by other DBM mod-
       ules and programs.  You can also use those programs to access files
       created with DBD::DBM.

       DBM files are stored in binary format optimized for quick retrieval
       when using a key field.  That optimization can be used advantageously
       to make DBD::DBM SQL operations that use key fields very fast.  There
       are several different "flavors" of DBM - different storage formats sup-
       ported by different sorts of perl modules such as SDBM_File and MLDBM.
       This module supports all of the flavors that perl supports and, when
       used with MLDBM, supports tables with any number of columns and inser-
       tion of Perl objects into tables.

       DBD::DBM has been tested with the following DBM types: SDBM_File,
       NDBM_File, ODBM_File, GDBM_File, DB_File, BerekeleyDB.  Each type was
       tested both with and without MLDBM.


       DBD::DBM operates like all other DBD drivers - it's basic syntax and
       operation is specified by DBI.  If you're not familiar with DBI, you
       should start by reading DBI and the documents it points to and then
       come back and read this file.  If you are familiar with DBI, you
       already know most of what you need to know to operate this module.
       Just jump in and create a test script something like the one shown

       You should be aware that there are several options for the SQL engine
       underlying DBD::DBM, see "Supported SQL syntax".  There are also many
       options for DBM support, see especially the section on "Adding multi-
       column support with MLDBM".

       But here's a sample to get you started.

        use DBI;
        my $dbh = DBI->connect('dbi:DBM:');
        $dbh->{RaiseError} = 1;
        for my $sql( split /;\n+/,"
            CREATE TABLE user ( user_name TEXT, phone TEXT );
            INSERT INTO user VALUES ('Fred Bloggs','233-7777');
            INSERT INTO user VALUES ('Sanjay Patel','777-3333');
            INSERT INTO user VALUES ('Junk','xxx-xxxx');
            DELETE FROM user WHERE user_name = 'Junk';
            UPDATE user SET phone = '999-4444' WHERE user_name = 'Sanjay Patel';
            SELECT * FROM user
            my $sth = $dbh->prepare($sql);
            $sth->dump_results if $sth->{NUM_OF_FIELDS};


       Specifiying Files and Directories

       DBD::DBM will automatically supply an appropriate file extension for
       the type of DBM you are using.  For example, if you use SDBM_File, a
       table called "fruit" will be stored in two files called "fruit.pag" and
       "fruit.dir".  You should never specify the file extensions in your SQL

       However, I am not aware (and therefore DBD::DBM is not aware) of all
       possible extensions for various DBM types.  If your DBM type uses an
       extension other than .pag and .dir, you should set the dbm_ext
       attribute to the extension. And you should write me with the name of
       the implementation and extension so I can add it to DBD::DBM!  Thanks
       in advance for that :-).

           $dbh = DBI->connect('dbi:DBM:ext=.db');  # .db extension is used
           $dbh = DBI->connect('dbi:DBM:ext=');     # no extension is used


           $dbh->{dbm_ext}='.db';                      # global setting
           $dbh->{dbm_tables}->{'qux'}->{ext}='.db';   # setting for table 'qux'

       By default files are assumed to be in the current working directory.
       To have the module look in a different directory, specify the f_dir
       attribute in either the connect string or by setting the database han-
       dle attribute.

       For example, this will look for the file /foo/bar/fruit (or
       /foo/bar/fruit.pag for DBM types that use that extension)

          my $dbh = DBI->connect('dbi:DBM:f_dir=/foo/bar');
          my $ary = $dbh->selectall_arrayref(q{ SELECT * FROM fruit });

       And this will too:

          my $dbh = DBI->connect('dbi:DBM:');
          $dbh->{f_dir} = '/foo/bar';
          my $ary = $dbh->selectall_arrayref(q{ SELECT x FROM fruit });

       You can also use delimited identifiers to specify paths directly in SQL
       statements.  This looks in the same place as the two examples above but
       without setting f_dir:

          my $dbh = DBI->connect('dbi:DBM:');
          my $ary = $dbh->selectall_arrayref(q{
              SELECT x FROM "/foo/bar/fruit"

       If you have SQL::Statement installed, you can use table aliases:

          my $dbh = DBI->connect('dbi:DBM:');
          my $ary = $dbh->selectall_arrayref(q{
              SELECT f.x FROM "/foo/bar/fruit" AS f

       See the "GOTCHAS AND WARNINGS" for using DROP on tables.

       Table locking and flock()

       Table locking is accomplished using a lockfile which has the same name
       as the table's file but with the file extension '.lck' (or a lockfile
       extension that you suppy, see belwo).  This file is created along with
       the table during a CREATE and removed during a DROP.  Every time the
       table itself is opened, the lockfile is flocked().  For SELECT, this is
       an shared lock.  For all other operations, it is an exclusive lock.

       Since the locking depends on flock(), it only works on operating sys-
       tems that support flock().  In cases where flock() is not implemented,
       DBD::DBM will not complain, it will simply behave as if the flock() had
       occurred although no actual locking will happen.  Read the documenta-
       tion for flock() if you need to understand this.

       Even on those systems that do support flock(), the locking is only
       advisory - as is allways the case with flock().  This means that if
       some other program tries to access the table while DBD::DBM has the ta-
       ble locked, that other program will *succeed* at opening the table.
       DBD::DBM's locking only applies to DBD::DBM.  An exception to this
       would be the situation in which you use a lockfile with the other pro-
       gram that has the same name as the lockfile used in DBD::DBM and that
       program also uses flock() on that lockfile.  In that case, DBD::DBM and
       your other program will respect each other's locks.

       If you wish to use a lockfile extension other than '.lck', simply spec-
       ify the dbm_lockfile attribute:

         $dbh = DBI->connect('');
         $dbh->{dbm_lockfile} = '.foo';
         $dbh->{dbm_tables}->{qux}->{lockfile} = '.foo';

       If you wish to disable locking, set the dbm_lockfile equal to 0.

         $dbh = DBI->connect('dbi:DBM:lockfile=0');
         $dbh->{dbm_lockfile} = 0;
         $dbh->{dbm_tables}->{qux}->{lockfile} = 0;

       Specifying the DBM type

       Each "flavor" of DBM stores its files in a different format and has
       different capabilities and different limitations.  See AnyDBM_File for
       a comparison of DBM types.

       By default, DBD::DBM uses the SDBM_File type of storage since SDBM_File
       comes with Perl itself.  But if you have other types of DBM storage
       available, you can use any of them with DBD::DBM also.

       You can specify the DBM type using the "dbm_type" attribute which can
       be set in the connection string or with the $dbh->{dbm_type} attribute
       for global settings or with the $dbh->{dbm_tables}->{$ta-
       ble_name}->{type} attribute for per-table settings in cases where a
       single script is accessing more than one kind of DBM file.

       In the connection string, just set type=TYPENAME where TYPENAME is any
       DBM type such as GDBM_File, DB_File, etc.  Do not use MLDBM as your
       dbm_type, that is set differently, see below.

        my $dbh=DBI->connect('dbi:DBM:');               # uses the default SDBM_File
        my $dbh=DBI->connect('dbi:DBM:type=GDBM_File'); # uses the GDBM_File

       You can also use $dbh->{dbm_type} to set global DBM type:

        $dbh->{dbm_type} = 'GDBM_File';  # set the global DBM type
        print $dbh->{dbm_type};          # display the global DBM type

       If you are going to have several tables in your script that come from
       different DBM types, you can use the $dbh->{dbm_tables} hash to store
       different settings for the various tables.  You can even use this to
       perform joins on files that have completely different storage mecha-

        my $dbh->('dbi:DBM:type=GDBM_File');
        # sets global default of GDBM_File

        my $dbh->{dbm_tables}->{foo}->{type} = 'DB_File';
        # over-rides the global setting, but only for the table called "foo"

        print $dbh->{dbm_tables}->{foo}->{type};
        # prints the dbm_type for the table "foo"

       Adding multi-column support with MLDBM

       Most of the DBM types only support two columns.  However a CPAN module
       called MLDBM overcomes this limitation by allowing more than two col-
       umns.  It does this by serializing the data - basically it puts a ref-
       erence to an array into the second column.  It can also put almost any
       kind of Perl object or even Perl coderefs into columns.

       If you want more than two columns, you must install MLDBM.  It's avail-
       able for many platforms and is easy to install.

       MLDBM can use three different modules to serialize the column -
       Data::Dumper, Storable, and FreezeThaw.  Data::Dumper is the default,
       Storable is the fastest.  MLDBM can also make use of user-defined seri-
       alization methods.  All of this is available to you through DBD::DBM
       with just one attribute setting.

       To use MLDBM with DBD::DBM, you need to set the dbm_mldbm attribute to
       the name of the serialization module.

       Some examples:

        $dbh=DBI->connect('dbi:DBM:mldbm=Storable');  # use MLDBM with Storable
           'dbi:DBM:mldbm=MySerializer'           # use MLDBM with a user defined module
        $dbh->{dbm_mldbm} = 'MySerializer';       # same as above
        print $dbh->{dbm_mldbm}                   # show the MLDBM serializer
        $dbh->{dbm_tables}->{foo}->{mldbm}='Data::Dumper';   # set Data::Dumper for table "foo"
        print $dbh->{dbm_tables}->{foo}->{mldbm}; # show serializer for table "foo"

       MLDBM works on top of other DBM modules so you can also set a DBM type
       along with setting dbm_mldbm.  The examples above would default to
       using SDBM_File with MLDBM.  If you wanted GDBM_File instead, here's

        $dbh = DBI->connect('dbi:DBM:type=GDBM_File;mldbm=Storable');
        # uses GDBM_File with MLDBM and Storable

       SDBM_File, the default file type is quite limited, so if you are going
       to use MLDBM, you should probably use a different type, see Any-

       See below for some "GOTCHAS AND WARNINGS" about MLDBM.

       Support for Berkeley DB

       The Berkeley DB storage type is supported through two different Perl
       modules - DB_File (which supports only features in old versions of
       Berkeley DB) and BerkeleyDB (which supports all versions).  DBD::DBM
       supports specifying either "DB_File" or "BerkeleyDB" as a dbm_type,
       with or without MLDBM support.

       The "BerkeleyDB" dbm_type is experimental and its interface is likely
       to chagne.  It currently defaults to BerkeleyDB::Hash and does not cur-
       rently support ::Btree or ::Recno.

       With BerkeleyDB, you can specify initialization flags by setting them
       in your script like this:

        my $dbh = DBI->connect('dbi:DBM:type=BerkeleyDB;mldbm=Storable');
        use BerkeleyDB;
        my $env = new BerkeleyDB::Env -Home => $dir;  # and/or other Env flags
        $dbh->{dbm_berkeley_flags} = {
             'DB_CREATE'  => DB_CREATE  # pass in constants
           , 'DB_RDONLY'  => DB_RDONLY  # pass in constants
           , '-Cachesize' => 1000       # set a ::Hash flag
           , '-Env'       => $env       # pass in an environment

       Do not set the -Flags or -Filename flags, those are determined by the
       SQL (e.g. -Flags => DB_RDONLY is set automatically when you issue a
       SELECT statement).

       Time has not permitted me to provide support in this release of
       DBD::DBM for further Berkeley DB features such as transactions, concur-
       rency, locking, etc.  I will be working on these in the future and
       would value suggestions, patches, etc.

       See DB_File and BerkeleyDB for further details.

       Supported SQL syntax

       DBD::DBM uses a subset of SQL.  The robustness of that subset depends
       on what other modules you have installed. Both options support basic
       SQL operations including CREATE TABLE, DROP TABLE, INSERT, DELETE,
       UPDATE, and SELECT.

       Option #1: By default, this module inherits its SQL support from
       DBI::SQL::Nano that comes with DBI.  Nano is, as its name implies, a
       *very* small SQL engine.  Although limited in scope, it is faster than
       option #2 for some operations.  See DBI::SQL::Nano for a description of
       the SQL it supports and comparisons of it with option #2.

       Option #2: If you install the pure Perl CPAN module SQL::Statement,
       DBD::DBM will use it instead of Nano.  This adds support for table
       aliases, for functions, for joins, and much more.  If you're going to
       use DBD::DBM for anything other than very simple tables and queries,
       you should install SQL::Statement.  You don't have to change DBD::DBM
       or your scripts in any way, simply installing SQL::Statement will give
       you the more robust SQL capabilities without breaking scripts written
       for DBI::SQL::Nano.  See SQL::Statement for a description of the SQL it

       To find out which SQL module is working in a given script, you can use
       the dbm_versions() method or, if you don't need the full output and
       version numbers, just do this:

        print $dbh->{sql_handler};

       That will print out either "SQL::Statement" or "DBI::SQL::Nano".

       Optimizing use of key fields

       Most "flavors" of DBM have only two physical columns (but can contain
       multiple logical columns as explained below).  They work similarly to a
       Perl hash with the first column serving as the key.  Like a Perl hash,
       DBM files permit you to do quick lookups by specifying the key and thus
       avoid looping through all records.  Also like a Perl hash, the keys
       must be unique.  It is impossible to create two records with the same
       key.  To put this all more simply and in SQL terms, the key column
       functions as the PRIMARY KEY.

       In DBD::DBM, you can take advantage of the speed of keyed lookups by
       using a WHERE clause with a single equal comparison on the key field.
       For example, the following SQL statements are optimized for keyed

        CREATE TABLE user ( user_name TEXT, phone TEXT);
        INSERT INTO user VALUES ('Fred Bloggs','233-7777');
        # ... many more inserts
        SELECT phone FROM user WHERE user_name='Fred Bloggs';

       The "user_name" column is the key column since it is the first column.
       The SELECT statement uses the key column in a single equal comparision
       - "user_name='Fred Bloggs' - so the search will find it very quickly
       without having to loop through however many names were inserted into
       the table.

       In contrast, thes searches on the same table are not optimized:

        1. SELECT phone FROM user WHERE user_name < 'Fred';
        2. SELECT user_name FROM user WHERE phone = '233-7777';

       In #1, the operation uses a less-than (<) comparison rather than an
       equals comparison, so it will not be optimized for key searching.  In
       #2, the key field "user_name" is not specified in the WHERE clause, and
       therefore the search will need to loop through all rows to find the
       desired result.

       Specifying Column Names

       DBM files don't have a standard way to store column names.   DBD::DBM
       gets around this issue with a DBD::DBM specific way of storing the col-
       umn names.  If you are working only with DBD::DBM and not using files
       created by or accessed with other DBM programs, you can ignore this

       DBD::DBM stores column names as a row in the file with the key _meta-
       data \0.  So this code

        my $dbh = DBI->connect('dbi:DBM:');
        $dbh->do("CREATE TABLE baz (foo CHAR(10), bar INTEGER)");
        $dbh->do("INSERT INTO baz (foo,bar) VALUES ('zippy',1)");

       Will create a file that has a structure something like this:

         _metadata \0 | foo,bar
         zippy        | 1

       The next time you access this table with DBD::DBM, it will treat the
       _metadata row as a header rather than as data and will pull the column
       names from there.  However, if you access the file with something other
       than DBD::DBM, the row will be treated as a regular data row.

       If you do not want the column names stored as a data row in the table
       you can set the dbm_store_metadata attribute to 0.

        my $dbh = DBI->connect('dbi:DBM:store_metadata=0');


        $dbh->{dbm_store_metadata} = 0;

       or, for per-table setting

        $dbh->{dbm_tables}->{qux}->{store_metadata} = 0;

       By default, DBD::DBM assumes that you have two columns named "k" and
       "v" (short for "key" and "value").  So if you have dbm_store_metadata
       set to 1 and you want to use alternate column names, you need to spec-
       ify the column names like this:

        my $dbh = DBI->connect('dbi:DBM:store_metadata=0;cols=foo,bar');


        $dbh->{dbm_store_metadata} = 0;
        $dbh->{dbm_cols}           = 'foo,bar';

       To set the column names on per-table basis, do this:

        $dbh->{dbm_tables}->{qux}->{store_metadata} = 0;
        $dbh->{dbm_tables}->{qux}->{cols}           = 'foo,bar';
        # sets the column names only for table "qux"

       If you have a file that was created by another DBM program or created
       with dbm_store_metadata set to zero and you want to convert it to using
       DBD::DBM's column name storage, just use one of the methods above to
       name the columns but *without* specifying dbm_store_metadata as zero.
       You only have to do that once - thereafter you can get by without set-
       ting either dbm_store_metadata or setting dbm_cols because the names
       will be stored in the file.

       Statement handle ($sth) attributes and methods

       Most statement handle attributes such as NAME, NUM_OF_FIELDS, etc. are
       available only after an execute.  The same is true of $sth->rows which
       is available after the execute but does not require a fetch.

       The $dbh->dbm_versions() method

       The private method dbm_versions() presents a summary of what other mod-
       ules are being used at any given time.  DBD::DBM can work with or with-
       out many other modules - it can use either SQL::Statement or
       DBI::SQL::Nano as its SQL engine, it can be run with DBI or
       DBI::PurePerl, it can use many kinds of DBM modules, and many kinds of
       serializers when run with MLDBM.  The dbm_versions() method reports on
       all of that and more.

         print $dbh->dbm_versions;               # displays global settings
         print $dbh->dbm_versions($table_name);  # displays per table settings

       An important thing to note about this method is that when called with
       no arguments, it displays the *global* settings.  If you over-ride
       these by setting per-table attributes, these will not be shown unless
       you specifiy a table name as an argument to the method call.

       Storing Objects

       If you are using MLDBM, you can use DBD::DBM to take advantage of its
       serializing abilities to serialize any Perl object that MLDBM can han-
       dle.  To store objects in columns, you should (but don't absolutely
       need to) declare it as a column of type BLOB (the type is *currently*
       ignored by the SQL engine, but heh, it's good form).

       You *must* use placeholders to insert or refer to the data.


       Using the SQL DROP command will remove any file that has the name spec-
       ified in the command with either '.pag' or '.dir' or your {dbm_ext}
       appended to it.  So this be dangerous if you aren't sure what file it
       refers to:

        $dbh->do(qq{DROP TABLE "/path/to/any/file"});

       Each DBM type has limitations.  SDBM_File, for example, can only store
       values of less than 1,000 characters.  *You* as the script author must
       ensure that you don't exceed those bounds.  If you try to insert a
       value that is bigger than the DBM can store, the results will be unpre-
       dictable.  See the documentation for whatever DBM you are using for

       Different DBM implementations return records in different orders.  That
       means that you can not depend on the order of records unless you use an
       ORDER BY statement.  DBI::SQL::Nano does not currently support ORDER BY
       (though it may soon) so if you need ordering, you'll have to install

       DBM data files are platform-specific.  To move them from one platform
       to another, you'll need to do something along the lines of dumping your
       data to CSV on platform #1 and then dumping from CSV to DBM on platform
       #2.  DBD::AnyData and DBD::CSV can help with that.  There may also be
       DBM conversion tools for your platforms which would probably be quick-

       When using MLDBM, there is a very powerful serializer - it will allow
       you to store Perl code or objects in database columns.  When these get
       de-serialized, they may be evaled - in other words MLDBM (or actually
       Data::Dumper when used by MLDBM) may take the values and try to execute
       them in Perl.  Obviously, this can present dangers, so if you don't
       know what's in a file, be careful before you access it with MLDBM
       turned on!

       See the entire section on "Table locking and flock()" for gotchas and
       warnings about the use of flock().


       If you need help installing or using DBD::DBM, please write to the DBI
       users mailing list at or to the comp.lang.perl.mod-
       ules newsgroup on usenet.  I'm afraid I can't always answer these kinds
       of questions quickly and there are many on the mailing list or in the
       newsgroup who can.

       If you have suggestions, ideas for improvements, or bugs to report,
       please write me directly at the email shown below.

       When reporting bugs, please send the output of $dbh->dbm_versions($ta-
       ble) for a table that exhibits the bug and, if possible, as small a
       sample as you can make of the code that produces the bug.  And of
       course, patches are welcome too :-).


       Many, many thanks to Tim Bunce for prodding me to write this, and for
       copious, wise, and patient suggestions all along the way.


       This module is written and maintained by

       Jeff Zucker < jzucker AT >

       Copyright (c) 2004 by Jeff Zucker, all rights reserved.

       You may freely distribute and/or modify this module under the terms of
       either the GNU General Public License (GPL) or the Artistic License, as
       specified in the Perl README file.


       DBI, SQL::Statement, DBI::SQL::Nano, AnyDBM_File, MLDBM

perl v5.8.8                       2007-05-13                       DBD::DBM(3)
See also DBM::Any(3)

Man(1) output converted with man2html