A question about DBI

"Curtis Leach" <[email protected]> Tue, 10 Sep 2013 19:45:59 +0000

Newsgroups	gmane.comp.lang.perl.modules.dbi.general
Message-ID	<[email protected]>

Hello,

I'm having issues trying to query flat files using DBI & DBD::CSV.  I'm currently upgrading from v5.8.8 to v5.16.3 and with the latest Perl platform my code no longer works.  I'm running on various Windows platforms.

I'm going to Strawberry Perl v5.16.3  (32-bit)
DBI 1.628    (upgraded core module)
DBD-CSV 0.41
SQL-Statement 1.405
Text-CSV_XS 1.01

From:  Active State Perl v 5.8.8
DBI  1.49
DBD-CSV 0.22
SQL-Statement 1.14
Text-CSV_XS 0.23

Here is a code sample that reproduces my issue:

my %attr = (PrintError => 0, RaiseError => 1, AutoCommit => 1);
my $dbh = DBI->connect ("DBI:CSV:csv_sep_char=|;csv_eol=\n", "", "", \%attr);
$dbh->{csv_tables}->{logs} = { 'file' => basename ($src_file) };
$dbh->{f_dir} = dirname ($src_file);

my $sql = "SELECT File, Extension, Directory, Message from logs where Status = " .
                $dbh->quote ("ERROR") . " ORDER BY Time";
my $sth = $dbh->prepare ( $sql );
$sth->execute ();

I turned on tracing (level 2) and according to the error message the file I'm trying to find isn't being found.

It looks like the query is ignoring the setting of "f_dir".  It's instead looking for the file in the current directory, which isn't what "f_dir" points to!  (The trace acknowledges f_dir is pointing to the correct location)

So if I manually run the code from the location where the log file is located, everything works.

So I see one of two things being true:

1)      "f_dir" is being ignored.

2)      There is a problem with how I initialized things.

Can anyone comment about which is the real issue?  With the files I'm querying all over the place, putting chdir's all over my code isn't really a good option.

Curtis Leach
Lead Engineer
RMS  IT & ERM IT
Phone: (702) 494-4562

[cid:[email protected]]

image001.jpg (image/jpeg, 8.6 KB) - not displayed

dbitrace.log (application/octet-stream, 4 KB)

    DBI 1.628-ithread default trace level set to 0x0/2 (pid 924 pi 3a8ff4) at cfops_feed.pl line 805 via cfops_feed.pl line 217
    -> DBI->connect(DBI:CSV:csv_sep_char=|;csv_eol=
, , ****, HASH(0x36fefbc))
    -> DBI->install_driver(CSV) for MSWin32 perl=5.016003 pid=924 ruid=0 euid=0
       install_driver: DBD::CSV version 0.41 loaded from C:/strawberry/perl/site/lib/DBD/CSV.pm
    -> STORE in DBD::_::common for DBD::CSV::dr (DBI::dr=HASH(0x405b9f4)~0x405ba84 'ShowErrorStatement' 1) thr#3a8ff4
       warn: 0 '' (err#0)
    <- STORE= ( 1 ) [1 items] at SqlEngine.pm line 77
    <- install_driver= DBI::dr=HASH(0x405b9f4)
    !! warn: 0 CLEARED by call to connect method
    -> connect for DBD::CSV::dr (DBI::dr=HASH(0x405b9f4)~0x405ba84 'csv_sep_char=|;csv_eol=
' '' **** HASH(0x31c041c)) thr#3a8ff4
    <- connect= ( DBI::db=HASH(0x405c6b4) ) [1 items] at DBI.pm line 669
    -> connected in DBD::_::db for DBD::CSV::db (DBI::db=HASH(0x405c6b4)~0x405c624 'DBI:CSV:csv_sep_char=|;csv_eol=
' '' **** HASH(0x36fefbc)) thr#3a8ff4
    <- connected= ( undef ) [1 items] at DBI.pm line 731
    <- connect= DBI::db=HASH(0x405c6b4)
    -> STORE in DBI::DBD::SqlEngine::db for DBD::CSV::db (DBI::db=HASH(0x405c624)~INNER 'dbi_connect_closure' CODE(0x37f34e4)) thr#3a8ff4
    <- STORE= ( 1 ) [1 items] at DBI.pm line 740
    <> FETCH= ( HASH(0x3803994)0keys ) [1 items] ('csv_tables' from cache) at cfops_feed.pl line 816
    -> STORE in DBI::DBD::SqlEngine::db for DBD::CSV::db (DBI::db=HASH(0x405c624)~INNER 'f_dir' 'C:\Users\cleach\Desktop\My-Old-XP-PC-2013\spool_data\heidev\erm\logs') thr#3a8ff4
    <- STORE= ( 1 ) [1 items] at cfops_feed.pl line 817
    -> quote in DBI::DBD::SqlEngine::db for DBD::CSV::db (DBI::db=HASH(0x405c6b4)~0x405c624 'ERROR') thr#3a8ff4
    <- quote= ( ''ERROR'' ) [1 items] at cfops_feed.pl line 822
    -> prepare in DBI::DBD::SqlEngine::db for DBD::CSV::db (DBI::db=HASH(0x405c6b4)~0x405c624 'SELECT File, Extension, Directory, Message from logs where Status = 'ERROR' ORDER BY Time') thr#3a8ff4
    <- prepare= ( DBI::st=HASH(0x405f47c) ) [1 items] at cfops_feed.pl line 824
    -> execute in DBI::DBD::SqlEngine::st for DBD::CSV::st (DBI::st=HASH(0x405f47c)~0x405f7b4) thr#3a8ff4
    !! ERROR: 2000000000 '
Execution ERROR: Cannot open C:\Users\cleach\Desktop\My-Old-XP-PC-2013\spool_data\heidev\erm\exec\cfops_feed.130910.log.txt: No such file or directory (2) at C:/strawberry/perl/site/lib/DBI/DBD/SqlEngine.pm line 1589.
 called from C:\Users\cleach\Desktop\My-Old-XP-PC-2013\spool_data\heidev\erm\exec\cfops_feed.pl at 217.

 at C:/strawberry/perl/site/lib/DBI/DBD/SqlEngine.pm line 1239.
' (err#1)
    <- execute= ( undef ) [1 items] at cfops_feed.pl line 825
    -> DESTROY in DBI::DBD::SqlEngine::st for DBD::CSV::st (DBI::st=HASH(0x405f7b4)~INNER) thr#3a8ff4
       ERROR: 2000000000 '
Execution ERROR: Cannot open C:\Users\cleach\Desktop\My-Old-XP-PC-2013\spool_data\heidev\erm\exec\cfops_feed.130910.log.txt: No such file or directory (2) at C:/strawberry/perl/site/lib/DBI/DBD/SqlEngine.pm line 1589.
 called from C:\Users\cleach\Desktop\My-Old-XP-PC-2013\spool_data\heidev\erm\exec\cfops_feed.pl at 217.

 at C:/strawberry/perl/site/lib/DBI/DBD/SqlEngine.pm line 1239.
' (err#1)
    <- DESTROY= ( undef ) [1 items] at cfops_feed.pl line 825
    -- DBI::END ($@: , $!: )
    -> disconnect_all in DBD::File::dr for DBD::CSV::dr (DBI::dr=HASH(0x405b9f4)~0x405ba84) thr#3a8ff4
    <- disconnect_all= ( undef ) [1 items] at DBI.pm line 748
!   -> DESTROY in DBI::DBD::SqlEngine::db for DBD::CSV::db (DBI::db=HASH(0x405c624)~INNER) thr#3a8ff4
    !! ERROR: 2000000000 CLEARED by call to disconnect method
!   -> disconnect in DBI::DBD::SqlEngine::db for DBD::CSV::db (DBI::db=HASH(0x405c624)~INNER) thr#3a8ff4
!   <- disconnect= ( 1 ) [1 items] at SqlEngine.pm line 860 during global destruction
!   <- DESTROY= ( undef ) [1 items] during global destruction
!   -> DESTROY in DBD::File::dr for DBD::CSV::dr (DBI::dr=HASH(0x405ba84)~INNER) thr#3a8ff4
!   <- DESTROY= ( undef ) [1 items] during global destruction