f_dir:root => f_dir=root

I've tried:

my $dbh = DBI->connect ("dbi:CSV:f_dir=/root", undef, undef, {
    f_ext      => ".csv",
    RaiseError => 1,
}
);
[download]

and

my $dbh = DBI->connect ("dbi:CSV:f_dir=root", undef, undef, {
    f_ext      => ".csv",
    RaiseError => 1,
}
);
[download]

and

my $dbh = DBI->connect ("dbi:CSV:", undef, undef, {
    f_ext      => ".csv",
    RaiseError => 1,
    f_dir      => "/root",
}
);
[download]

and:

my $dbh = DBI->connect ("dbi:CSV:", undef, undef, {
    f_ext      => ".csv",
    RaiseError => 1,
    f_dir      => "root",
}
);
[download]

But none of these work. Can you please clarify what you meant?

Your original post said "dbi:CSV:f_dir:/root, which was obviously wrong, and thus most likely the cause of failure.

Assuming you have recent enough versions of the modules you use, what is the output of:

use strict;
use warnings;

use DBI;

my $dbh = DBI->connect ("dbi:CSV:", undef, undef, {
    f_dir              => "/root",
    f_ext              => ".csv/r",
    RaiseError         => 1,
    PrintError         => 1,
    ShowErrorStatement => 1,
    });

print "DBI-$DBI::VERSION\n";
print "DBD::CSV-$DBD::CSV::VERSION\n";
print "Text::CSV_XS-$Text::CSV_XS::VERSION\n";
print "SQL::Statement-$SQL::Statement::VERSION\n";
print "\n";
print "$_\n" for $dbh->tables (undef, undef, undef, undef);
[download]


Enjoy, Have FUN! H.Merijn

what's the end-of-line convention in export.csv and are you properly passing that to DBI->connect?

e.g., if export.csv does not actually have any proper end-of-line sequences (because DBD::CSV with the parameters you're give it is expecting Something Else), then the entire file is one line, which then gets interpreted as a header, and there are no actual rows, so...

Humbug. When no eol is passed, like the op did, the default is to recognize all of \r, \n, and \r\n. 97% chance it gets it right.


Enjoy, Have FUN! H.Merijn

...Humbug...

You made me laugh loud... :) (Just the word.)

Hello paulski82,

I am not an SQL user, because I am working with MySQL so maybe my observations are wrong. But as far as I know SQL and MySQL are really close to each other so the syntax is the same 99% of the time.

A few things that I noticed on your code:

my $sql_query = "SELECT * FROM export";

my $dbh = DBI->connect ("dbi:CSV:f_dir:/root", undef, undef, {
    f_ext      => ".csv",
    RaiseError => 1,
}
);
[download]

You are preparing a my $sql_query = "SELECT * FROM export"; before connecting.

Because you are executing the command after through:

my $sth = $dbh->prepare($sql_query);
$sth->execute();
[download]

Your code will compile and execute without any problem, but it is a bit confusing in a human readable form and maybe also confuses you on the process.

I would suggest you modify your code like this:

$dbh = DBI->connect ("dbi:CSV:f_dir:/root", undef, undef, {
        f_ext      => ".csv/r",
        RaiseError => 1,
        }) or die "Cannot connect: $DBI::errstr";

my $sql_query = "SELECT * FROM export";

my $sth = $dbh->prepare($sql_query);
$sth->execute();
[download]

Observe the line that I have added or die "Cannot connect: $DBI::errstr", it will help you see a possible error if for any reason can not connect.

I would also suggest you modify the line:

my $sql_query = "SELECT * FROM export";
my $sth = $dbh->prepare($sql_query);
$sth->execute();
[download]

To:

my $sth = $dbh->prepare ("SELECT * from export");
$sth->execute();
[download]

It is more clear what you are doing and also less coding lines make your code more efficient and faster.

At the end of your code when you finish, I would suggest to disconnect from your database.

Sample of code:

$sth->finish();
$dbh->disconnect();
[download]

From the book Programming the Perl DBI written by Alligator Descartes & Tim Bunce, I have taken the following line:

It is important to free up both your local machine resources and, more importantly, valuable database resources.

As the rest of the monks suggested I think you are missing something small in the:

my $dbh = DBI->connect ("dbi:CSV:f_dir:/root", undef, undef, {
    f_ext      => ".csv",
    RaiseError => 1,
}
);
[download]

I would suggest you to read the DBD::CSV - DBI driver for CSV files it contains code analysis and samples of code. I am sure by experimenting you will find your error.

Hope this helps.

Though we value the time and effort you have taken to answer to this question, I'd advice to read the whole thread before posting next time.

In your elaborate answers, you have left in all the fundamental errors of the OP and only commented on possible improvements on the sequence of coding that do not solve the question at hand, thus adding distraction to the readers however well you meant, this does not help.

Declaring a SQL statement is not the same thing as preparing it. If you have read the book you point to, you should know that :)

The OP has nothing to do with declaring or preparing the SQL statement, but with using the correct syntax in being able to access a table. Quoting might be more an issue here than shuffling the code around.

Personally, I would not put the statement in a variable at all, but pass it directly to the prepare method, but TIMTOWTDI, and neither is wrong.

$sth->finish is only to be executed on non-select handles. The book is a bit outdated/obsoleted in that subject.


Enjoy, Have FUN! H.Merijn