Grokbase › Groups › Perl › dbi-users › April 2011
FAQ
Dear List,

The short version of my question is: Using DBD::DBM, is there a simple
way to tell whether a table exists? (Other than just trying to query it
and handling failure.)

I've tried "$dbh->{'dbm_tables'}" which seemed promising, except that
it's always empty until I actually execute a query.

I've tried "$dbh->func('list_tables')" but that just lists all the files
in the directory. I'd rather not use that because I'd rather not have
to remember how the particular DBM handles extensions.


The longer version of the question, with the rest of the story, is below.

Thanks for any advice,

dave

--

I have a little application that tracks state of tickets. I wrote a
proof-of-concept using DBI and DBD::SQLite. A colleague suggested I try
a different backing store, like BerkeleyDB or GDBM. "No problem," said
I, "I'll just switch to DBD::DBM."

Of course, I had cheated. In the SQLite version, I had the statement

CREATE TABLE IF NOT EXISTS tickets (...)

at the very beginning. For the DBM version, I have to remove the "IF
NOT EXISTS" phrase. The code "works", but its output is peppered with
warnings like


DBD::DBM::st execute failed: Cannot CREATE './tickets.pag' because it
already exists at
/usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBD/DBM.pm
line 338.

Those are just warnings and everything else seems to work. At least
with SDBM.

Search Discussions

  • David Nicol at Apr 1, 2011 at 6:04 pm ⇧

    I've tried "$dbh->func('list_tables')" but that just lists all the files in
    the directory. I'd rather not use that because I'd rather not have to
    remember how the particular DBM handles extensions.

    if you make a directory and keep nothing in it but database files, you can
    use that list after cropping the extensions and removing duplicates, for the
    two-file variants


    --
    the kansas city go club thursday night meeting has moved to Borders at 119th
    and Strang Line. http://www.borders.com/online/store/StoreDetailView_373
  • Jens Rehsack at Apr 3, 2011 at 11:19 am ⇧

    On 04/01/11 17:15, David McMath wrote:
    Dear List,
    Hi David,

    I quick reply now without taking a look into the details of the
    implementation to prevent that I forget you (I'm currently busy
    with other tasks ...).
    The short version of my question is: Using DBD::DBM, is there a simple
    way to tell whether a table exists? (Other than just trying to query it
    and handling failure.)

    I've tried "$dbh->{'dbm_tables'}" which seemed promising, except that
    it's always empty until I actually execute a query.
    That's true, the attribute dbm_tables contains only those tables which
    had been "touched" in this session.
    I've tried "$dbh->func('list_tables')" but that just lists all the files
    in the directory. I'd rather not use that because I'd rather not have to
    remember how the particular DBM handles extensions.
    This shouldn't happen with a proper configured DBD::DBM session. If I
    forgot to dig deeper here within next 4 business days, please feel free
    to remind me.

    You should include the version numbers of DBI and SQL::Statement
    you use in your environment and they should better match the latest
    released versions ;)
    Further, you should add the OS(s) you're using (just to be sure).
    The longer version of the question, with the rest of the story, is below.

    Thanks for any advice,

    dave

    --

    I have a little application that tracks state of tickets. I wrote a
    proof-of-concept using DBI and DBD::SQLite. A colleague suggested I try
    a different backing store, like BerkeleyDB or GDBM. "No problem," said
    I, "I'll just switch to DBD::DBM."

    Of course, I had cheated. In the SQLite version, I had the statement

    CREATE TABLE IF NOT EXISTS tickets (...)

    at the very beginning. For the DBM version, I have to remove the "IF NOT
    EXISTS" phrase. The code "works", but its output is peppered with
    warnings like


    DBD::DBM::st execute failed: Cannot CREATE './tickets.pag' because it
    already exists at
    /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBD/DBM.pm
    line 338.

    Those are just warnings and everything else seems to work. At least with
    SDBM.
    When you're able to create a small test case to demonstrate it, you would
    save me some work. This seems to be a bug, because "CREATE TABLE IF NOT
    EXISTS ..." should work fine without noise.

    Best regards,
    Jens
  • David McMath at Apr 4, 2011 at 4:25 pm ⇧

    On 04/03/2011 06:18 AM, Jens Rehsack wrote:
    On 04/01/11 17:15, David McMath wrote:
    Dear List,
    Hi David,

    I quick reply now without taking a look into the details of the
    implementation to prevent that I forget you (I'm currently busy
    with other tasks ...).
    ...
    I've tried "$dbh->func('list_tables')" but that just lists all the files
    in the directory. I'd rather not use that because I'd rather not have to
    remember how the particular DBM handles extensions.
    This shouldn't happen with a proper configured DBD::DBM session. If I
    forgot to dig deeper here within next 4 business days, please feel free
    to remind me.

    You should include the version numbers of DBI and SQL::Statement
    you use in your environment and they should better match the latest
    released versions ;)
    Further, you should add the OS(s) you're using (just to be sure).
    Thanks for your time. Including version numbers is always good advice.
    When I wrote the original, DBI was something very old and DBD::DBM was
    v0.03. After updating, I understand that list_tables should work.

    The attached script almost does the right thing except for a bug in
    SqlEngine (?). I described that at

    https://rt.cpan.org/Public/Bug/Display.html?id=67223

    I don't think "CREATE TABLE IF NOT EXISTS" parses, even after I updated
    everything I could think of. The attached script gives the output
    below. But the list_tables trick is good enough for me.

    Thanks again for your help,

    dave

    --

    [mcdave@epgy-dsk-999 ticketServer]$ uname -a
    Linux epgy-dsk-999.Stanford.EDU 2.6.18-164.11.1.el5 #1 SMP Wed Jan 6
    13:26:04 EST 2010 x86_64 x86_64 x86_64 GNU/Linux
    [mcdave@epgy-dsk-999 ticketServer]$ perl gdb_test_list.pl
    Version information:
    Use of uninitialized value in concatenation (.) or string at
    /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBD/DBM.pm
    line 178.
    Use of uninitialized value in concatenation (.) or string at
    /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBD/DBM.pm
    line 185.
    DBD::DBM 0.06 using SDBM_File () + MLDBM () +
    MLDBM::Serializer::Storable
    DBD::File 0.40 using IO::File (1.14)
    DBI::DBD::SqlEngine 0.03 using SQL::Statement 1.33
    DBI 1.616
    OS linux (2.6.18-128.1.10.el5)
    Perl 5.008008 (x86_64-linux-thread-multi)
    List of tables:
    Can't use string ("tickets") as an ARRAY ref while "strict refs" in use
    at
    /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBI/DBD/SqlEngine.pm
    line 688.
    Can't find column definitions! at
    /usr/lib/perl5/site_perl/5.8.8/SQL/Statement.pm line 88
    DBD::DBM::db do failed: Can't find column definitions! [for Statement
    "CREATE TABLE IF NOT EXISTS tickets
    (tid TEXT PRIMARY KEY
    , user_id INTEGER
    , started DATE
    , status_f VARCHAR(16)
    , value_f VARCHAR(255)
    )
    "] at gdb_test_list.pl line 19.
    Can't find column definitions! at gdb_test_list.pl line 18.
  • Jens Rehsack at Apr 4, 2011 at 5:30 pm ⇧

    2011/4/4 David McMath <mcdave@stanford.edu>:
    On 04/03/2011 06:18 AM, Jens Rehsack wrote:
    On 04/01/11 17:15, David McMath wrote:

    Dear List,
    Hi David,

    I quick reply now without taking a look into the details of the
    implementation to prevent that I forget you (I'm currently busy
    with other tasks ...).
    ...
    I've tried "$dbh->func('list_tables')" but that just lists all the files
    in the directory. I'd rather not use that because I'd rather not have to
    remember how the particular DBM handles extensions.
    This shouldn't happen with a proper configured DBD::DBM session. If I
    forgot to dig deeper here within next 4 business days, please feel free
    to remind me.

    You should include the version numbers of DBI and SQL::Statement
    you use in your environment and they should better match the latest
    released versions ;)
    Further, you should add the OS(s) you're using (just to be sure).
    Thanks for your time.  Including version numbers is always good advice.
    When I wrote the original, DBI was something very old and DBD::DBM was
    v0.03.  After updating, I understand that list_tables should work.

    The attached script almost does the right thing except for a bug in
    SqlEngine (?).  I described that at

    https://rt.cpan.org/Public/Bug/Display.html?id=67223
    I'll see when I find the time to care (might not be soon). Thanks for reporting.
    I don't think "CREATE TABLE IF NOT EXISTS" parses, even after I updated
    everything I could think of.  The attached script gives the output below.
    But the list_tables trick is good enough for me.
    You're right and I'm sorry - "CREATE TABLE IF NOT EXISTS" is neither
    supported by DBI::SQL::Nano nor by SQL::Statement. I'll try to remind
    myself when hacking on RT#67223.
    Thanks again for your help,
    You're welcome,
    Jens
    dave

    --

    [mcdave@epgy-dsk-999 ticketServer]$ uname -a
    Linux epgy-dsk-999.Stanford.EDU 2.6.18-164.11.1.el5 #1 SMP Wed Jan 6
    13:26:04 EST 2010 x86_64 x86_64 x86_64 GNU/Linux
    [mcdave@epgy-dsk-999 ticketServer]$ perl gdb_test_list.pl
    Version information:
    Use of uninitialized value in concatenation (.) or string at
    /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBD/DBM.pm line
    178.
    Use of uninitialized value in concatenation (.) or string at
    /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBD/DBM.pm line
    185.
    DBD::DBM                 0.06 using SDBM_File () + MLDBM () +
    MLDBM::Serializer::Storable
    DBD::File              0.40 using IO::File (1.14)
    DBI::DBD::SqlEngine  0.03 using SQL::Statement 1.33
    DBI                      1.616
    OS                       linux (2.6.18-128.1.10.el5)
    Perl                     5.008008 (x86_64-linux-thread-multi)
    List of tables:
    Can't use string ("tickets") as an ARRAY ref while "strict refs" in use at
    /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBI/DBD/SqlEngine.pm
    line 688.
    Can't find column definitions! at
    /usr/lib/perl5/site_perl/5.8.8/SQL/Statement.pm line 88
    DBD::DBM::db do failed: Can't find column definitions! [for Statement
    "CREATE TABLE IF NOT EXISTS tickets
    (tid TEXT PRIMARY KEY
    , user_id INTEGER
    , started DATE
    , status_f VARCHAR(16)
    , value_f VARCHAR(255)
    )
    "] at gdb_test_list.pl line 19.
    Can't find column definitions! at gdb_test_list.pl line 18.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedApr 1, '11 at 5:16p
activeApr 4, '11 at 5:30p
posts5
users3
websitedbi.perl.org

3 users in discussion

David McMath: 2 posts Jens Rehsack: 2 posts David Nicol: 1 post

Content

People

Support

Translate

site design / logo © 2022 Grokbase