FAQ
Hi,

I'm using DBI for a "proof of concept" project that I thought should be rather
quick.

But I see DBI is rather quirky to the novice :)

I'm on FreeBSD 5.4 using perl 5.8.8 and DBI 1.52

Using the connection string in the Subject line, I created a table as follows:

Create Table Folders (Name TEXT, FolderID INTEGER, Parent INTEGER);

Then I added a couple of test rows:

insert into Folders (Name,FolderID,Parent) values ('foo',0,0);
insert into Folders (Name,FolderID,Parent) values ('bar',1,0);


Next I attempted to change the Name column on the first record:

update Folders set Name='foo too' where FolderID=0;

Instead of updating the existing record, a new record was added.

So I figure it is an issue with Name being the Key value, and I change my
sequence to this:

Create Table Folders (FolderID INTEGER, Name TEXT, Parent INTEGER);

But now I can't even add a record:

insert into Folders (FolderID,Name,Parent) values (0,'foo',0);
insert into Folders (FolderID,Name,Parent) values (1,'bar',0);

Both Insert statements execute, and no warnings are produced. But when I
query the table it is empty.

I recognize that the issue is more likely a "feature" of the underlying
database, but I'm not even sure what that is when I just

use DBI;
my $dbh = DBI->connect('dbi:DBM:mldbm=Storable;f_dir=/foo/bar');
$dbh->{RaiseError} = 1;


I just subscribed and I haven't figured out yet how to view the archives
(haven't located them yet) so don't blow up at me if this is really really
simple. Just ignore me, instead. Keep your blood pressure in check, ok? :)

Thanks for getting this far!

lane

Search Discussions

  • Jeff Zucker at Sep 17, 2006 at 1:20 am

    Lane wrote:
    my $dbh = DBI->connect('dbi:DBM:mldbm=Storable;f_dir=/foo/bar');
    $dbh->{RaiseError} = 1;
    I can't reproduce your problem. Please run the following script and
    send the output back to the list.

    #!/usr/bin/perl -w
    use strict;
    use DBI;
    my $dbh = DBI->connect('dbi:DBM:mldbm=Storable;f_dir=/foo/bar');
    print $dbh->dbm_versions;
    __END__


    --
    Jeff
  • Lane at Sep 17, 2006 at 2:21 am

    On Saturday 16 September 2006 20:23, Jeff Zucker wrote:
    Lane wrote:
    my $dbh = DBI->connect('dbi:DBM:mldbm=Storable;f_dir=/foo/bar');
    $dbh->{RaiseError} = 1;
    I can't reproduce your problem. Please run the following script and
    send the output back to the list.

    #!/usr/bin/perl -w
    use strict;
    use DBI;
    my $dbh = DBI->connect('dbi:DBM:mldbm=Storable;f_dir=/foo/bar');
    print $dbh->dbm_versions;
    __END__
    Thanks, Jeff.

    Here's the output:

    DBD::DBM 0.03 using SDBM_File + MLDBM + Storable
    DBD::File 0.35
    DBI::SQL::Nano 0.03
    SQL::Statement 1.15
    DBI 1.52
    OS freebsd (5.4-release-p14)
    Perl 5.008008 (i386-freebsd-64int)
  • Lane at Sep 17, 2006 at 2:54 am

    On Saturday 16 September 2006 20:23, Jeff Zucker wrote:
    Lane wrote:
    my $dbh = DBI->connect('dbi:DBM:mldbm=Storable;f_dir=/foo/bar');
    $dbh->{RaiseError} = 1;
    I can't reproduce your problem. Please run the following script and
    send the output back to the list.

    #!/usr/bin/perl -w
    use strict;
    use DBI;
    my $dbh = DBI->connect('dbi:DBM:mldbm=Storable;f_dir=/foo/bar');
    print $dbh->dbm_versions;
    __END__
    Here is a script to demonstrate the two issues. I should have done this
    first, I guess. On my system the second "show" sub lists the two original
    records plus one new record having Name=foo too,FolderID=0,Parent=0.

    There is no output from the third or fourth call to "show." The output is
    after the __END__ marker.

    #!/usr/bin/perl -w
    use strict;
    use DBI;
    my $dbh = DBI->connect('dbi:DBM:mldbm=Storable;f_dir=./foo');
    print $dbh->dbm_versions;

    sub show() {
    my $sth=$dbh->prepare("select * from Folders");
    $sth->execute();
    while (my $ref = $sth->fetchrow_hashref()) {
    while ((my $key,my $value) = each %$ref) {
    print "$key=$value,";
    }
    print "\n";
    }
    }
    $dbh->do("Drop table if exists Folders");
    print "Create Table Folders (Name TEXT, FolderID INTEGER, Parent INTEGER)\n";
    $dbh->do("Create Table Folders (Name TEXT, FolderID INTEGER, Parent
    INTEGER)");
    $dbh->do("insert into Folders (Name,FolderID,Parent) values ('foo',0,0)");
    $dbh->do("insert into Folders (Name,FolderID,Parent) values ('bar',1,0)");
    print "This is the table after initial creating and population\n";
    show;
    print "Changing one Name value ...\n";
    $dbh->do("update Folders set Name='foo too' where FolderID=0");
    print "Now the table looks like this:\n";
    show;
    print "Dropping and recreating ...\n";
    $dbh->do("Drop table if exists Folders");
    print "Create Table Folders (FolderID INTEGER, Name TEXT, Parent Integer)\n";
    $dbh->do("Create Table Folders (FolderID INTEGER, Name TEXT, Parent
    Integer)");
    $dbh->do("insert into Folders (FolderID,Name,Parent) values (0,'foo',0)");
    $dbh->do("insert into Folders (FolderID,Name,Parent) values (1,'bar',0)");
    print "This is the table after initial creation and population\n";
    show;
    print "\nUpdating Folders ...\n";
    $dbh->do("update Folders set Name='foo too' where FolderID=0");
    print "\nNow the table after Name was changed on one record...\n";
    show;
    __END__

    DBD::DBM 0.03 using SDBM_File + MLDBM + Storable
    DBD::File 0.35
    DBI::SQL::Nano 0.03
    SQL::Statement 1.15
    DBI 1.52
    OS freebsd (5.4-release-p14)
    Perl 5.008008 (i386-freebsd-64int)

    Create Table Folders (Name TEXT, FolderID INTEGER, Parent INTEGER)
    This is the table after initial creating and population
    FolderID=0,Name=foo,Parent=0,
    FolderID=1,Name=bar,Parent=0,
    Changing one Name value ...
    Now the table looks like this:
    FolderID=0,Name=foo,Parent=0,
    FolderID=0,Name=foo too,Parent=0,
    FolderID=1,Name=bar,Parent=0,
    Dropping and recreating ...
    Create Table Folders (FolderID INTEGER, Name TEXT, Parent Integer)
    This is the table after initial creation and population

    Updating Folders ...

    Now the table after Name was changed on one record...
  • Jeff Zucker at Sep 18, 2006 at 4:06 am

    Lane wrote:
    my $dbh = DBI->connect('dbi:DBM:mldbm=Storable;f_dir=./foo');
    Yes, there does appear to be a bug in DBD::DBM update when the key field
    is updated. I'll look into it. In the meantime, since you said that
    you are only prototyping, you can use Temporary tables which are not
    effected by this bug. Leave your test exactly as-is except put the word
    TEMP after the word CREATE in your create table SQL statements like so:

    $dbh->do("
    Create TEMP Table Folders ( Name TEXT, FolderID INT, Parent INT)
    ");

    --
    Jeff
  • Lane at Sep 18, 2006 at 4:21 am

    On Sunday 17 September 2006 23:09, Jeff Zucker wrote:
    Lane wrote:
    my $dbh = DBI->connect('dbi:DBM:mldbm=Storable;f_dir=./foo');
    Yes, there does appear to be a bug in DBD::DBM update when the key field
    is updated. I'll look into it. In the meantime, since you said that
    you are only prototyping, you can use Temporary tables which are not
    effected by this bug. Leave your test exactly as-is except put the word
    TEMP after the word CREATE in your create table SQL statements like so:

    $dbh->do("
    Create TEMP Table Folders ( Name TEXT, FolderID INT, Parent INT)
    ");
    Thanks,

    I'll try that.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedSep 17, '06 at 12:34a
activeSep 18, '06 at 4:21a
posts6
users2
websitedbi.perl.org

2 users in discussion

Lane: 4 posts Jeff Zucker: 2 posts

People

Translate

site design / logo © 2022 Grokbase