New here. dbi:DBM:mldbm=Storable;f_dir=/foo/bar. Inserts when asked to update and other weirdness

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

Re: New here. dbi:DBM:mldbm=Storable;f_dir=/foo/bar. Inserts whenasked to update and other weirdness

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

Re: New here. dbi:DBM:mldbm=Storable;f_dir=/foo/bar. Inserts when asked to update and other weirdnes

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)

Re: New here. dbi:DBM:mldbm=Storable;f_dir=/foo/bar. Inserts when asked to update and other weirdnes

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...

Re: New here. dbi:DBM:mldbm=Storable;f_dir=/foo/bar. Inserts whenasked to update and other weirdness

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

Re: New here. dbi:DBM:mldbm=Storable;f_dir=/foo/bar. Inserts when asked to update and other weirdnes

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.