Re: [MacPerl] DBI Question

[Thomas Wegner <toms_email@gmx.de>]

># DBD::File::db do failed: # Can't locate object method "new" via package
>"IO::File".
>File 'Primus 8.5GB:Applications:MacPerl Ÿ:site_perl:DBD:File.pm'; Line 461
>
>the ONLY change I made to this script was changing dbi:RAM to dbi:File
>
>#!/usr/local/bin/perl -w
>use strict;
>use DBI;
>my $dbh   = DBI->connect('dbi:File(RaiseError=>1):')
>             || die $DBI::errstr;
>my $table = 'testTable';
>$dbh->do("CREATE TABLE $table ( name TEXT, sex TEXT, state TEXT )");
>$dbh->do("INSERT INTO $table VALUES ('Joe','m','US')");
>$dbh->do("INSERT INTO $table VALUES ('Sue','f','US')");
>$dbh->do("INSERT INTO $table VALUES ('Bob','m','CA')");
>$dbh->do("INSERT INTO $table VALUES ('Bev','f','US')");
>$dbh->do("DELETE FROM $table WHERE name = 'Bev'");
>$dbh->do("UPDATE $table SET state = 'CA' WHERE name = 'Sue'");
>my $sth = $dbh->prepare(qq/
>     SELECT * FROM $table WHERE name LIKE "%e%" ORDER BY name
>/);
>$sth->execute;
>while (my $row = $sth->fetch) {
>     foreach (@$row) {print "[$_] "};
>     print "\n";
>}
>#$dbh->do("DROP TABLE $table");
>__END__
>
>Does this mean I need to update IO::File, and if so, to which version?
>
>
>
>--
>Scott R. Godin            | e-mail : webmaster@webdragon.net
>Laughing Dragon Services  |    web : http://www.webdragon.net/

Hi Scott,

first, your call to the connect method is a bit odd. The correct syntax is

    $dbh = DBI->connect($data_source, $username, $password, \%attr);

For the DBI RAM  driver, you should better use

    $dbh = DBI->connect('dbi:RAM:','' ,'', {RaiseError=>1}); ,

although it doesn't complain about your version (and that's curious).

Second,  the DBD::File module is not a true DBI driver, but an 
abstract base class for deriving concrete DBI drivers from it. 
DBD::CSV is derived from DBD::File, thus you should use the CSV 
driver for (comma separated values) files. Change the connect method 
call to

     $dbh = DBI->connect('dbi:CSV:','' ,'', {RaiseError=>1});

(The testTable file will be created in the current working directory.)


Unfortunately, running your script on my Mac results in the following:

[Joe] [m] [US]
[Joe] [m] [US]
[Joe] [m] [US]
[Joe] [m] [US]
[Sue] [f] [CA]
[Sue] [f] [US]
[Sue] [f] [CA]
[Sue] [f] [US]

which is really not what I expected. Obviously, lines are duplicated 
(take a look at the testTable file). I figured out that there is a 
problem with the DELETE and UPDATE SQL-statements. The following 
solution works for me:

(1) Open the module SQL::Statement (in your site_perl folder)

(2) Locate *BOTH* the UPDATE and the DELETE subs, which both contain 
the following lines (at the end):
       ...
       $table->seek($data, 0, 0);
       foreach $array (@rows) {
           $table->push_row($data, $array);
       }
       $table->truncate($data);
       ($affected, 0);
       ...

(3) Change these lines to
       ...
       $table->seek($data, 0, 0);
       $table->truncate($data);   # <===
       foreach $array (@rows) {
             $table->push_row($data, $array);
       }
       ($affected, 0);
       ...
i.e. change the position of the truncate method call.

Now, your script returns

[Joe] [m] [US]
[Sue] [f] [CA]

as one would expect. Please let me know, if you encountered the same 
error and if the solution works for you.

This is a very odd error and I currently have no satisfying 
explanation for it.  Obviously, it's related to the file system 
(seek, tell, truncate). I've written a test script, that reproduces 
the oddity:

To run the script, first create the test file "Untitled" (don't 
forget the CR after the last line):
____
name,sex,state
Joe,m,US
Sue,f,US
Bob,m,CA
Bev,f,US

____

Now, the script:

#! perl -w

my @rows;
open (FH, "+<Untitled");

while (<FH>) {
        push (@rows, $_);
        print $_;
}

shift @rows; # shift header line ('name,sex,state ')

#
# the following block is what actually happens in Statement.pm
#
{
        # seek FILEHANDLE, POSITION, WHENCE
        seek(FH, 15, 0); # position FH after header row at pos 15 (byte count)

        foreach $array (@rows) {
               ### seek(FH, tell(FH), 0); # add this, and everything is ok
               print FH ('new' . $array);
        }

        # truncate FILEHANDLE,LENGTH
        truncate(FH, tell(FH));
}

close (FH);

__END__

Output, not what expected:

name,sex,state
Joe,m,US
Sue,f,US
Bob,m,CA
Bev,f,US

newSue,f,US
newBob,m,CA
newBev,f,US

Now, add the call to seek in the foreach loop, and everything is ok. 
The output, as one might expect, will be:

name,sex,state
newJoe,m,US
newSue,f,US
newBob,m,CA


I don't know if that is a limitation in MacOS, the MacPerl 
implementation of seek/truncate/tell, the documentation,  or my brain.
Hopefully, someone can clearify things.


Best regards,

--Thomas





# ===== Want to unsubscribe from this list?
# ===== Send mail with body "unsubscribe" to macperl-request@macperl.org