jey has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,

I am experiencing some problems with DBD::CSV. I have installed the modules and all the dependencies (Text::CSV_XS, SQL::Statement ... )

I even manage to make everything work the way I wanted there is just one problem at runtime when the code is excecuted.

With the following code (linux red hat 9, perl 5.8.0)

use strict;
use warnings;
use DBI;

my $value = 45;

my $dbh = DBI->connect("DBI:CSV:f_dir=.;csv_sep_char=\\;");

$dbh->{'csv_tables'}->{'my_table'} = {
        'eol' => "\n",
        'sep_char' => ";",
        'quote_char' => undef,
        'escape_char' => undef,
        'file' => 'my_table.csv',
        'col_names' => ["id", "value"]
    };

my $sth = $dbh->prepare(<<SQL);
SELECT * FROM my_table WHERE value = ?
SQL
$sth->execute($value) or die "Cannot execute: " . $sth->errstr();
$sth->bind_columns( \my $ret_1, \my $ret_2 );
while ( $sth->fetch() )
{
   print "key($ret_1) value($ret_2)\n";
}
$sth->finish();

$dbh->disconnect();

exit;

[download]

the file my_table.csv is as follow...

key;value
1;654
2;45
3;354
4;357
5;464
6;654
7;45
8;654
9;45
10;6546

[download]

Here is the output I get

Argument "value" isn't numeric in numeric eq (==) at /jey/.perl-lib/SQ
+L/Statement.pm line 1120, <GEN0> line 1.
key(2) value(45)
key(7) value(45)
key(9) value(45)

[download]

From what I understand, Perl is just complaining about the first line of my CSV file because the data is no integers, hence the <GEN0> line 1.

Does Anyone know how to get rid of this warning?

Furthermore I tried to do SQL commands like

SELECT * FROM table WHERE value = (SELECT * FROM table2 WHERE value2 =
+ ?)

[download]

This doesn't work, does anybody know why and if there is a way of getting round of this

--
jey

Replies are listed 'Best First'.
Re: Help with DBD::CSV
by jZed (Prior) on Apr 30, 2004 at 21:56 UTC
    Hi, In DBD::CSV you should *either* specify the column names in your csv_tables hash like this:

    'col_names' => "id,value"

    *or* in your data file like this: 

    id;value
1;foo
2;bar
    BUT DON'T SPECIFY BOTH! And, IIRC, both "key" and "value" are SQL reserved words so for portability, avoid them as column names.

    SELECT * FROM table WHERE value = (SELECT * ...
    That is called a subselect and it is available in some but by no means all databases. It is not yet available in DBD::CSV. See SQL::Parser for a list of the SQL syntax supported by DBD::CSV.

    If those tips don't solve your problem, let me know (I'm the maintainer of DBD:CSV and SQL::Statement).

[reply]

      Thanks to both sacked and jZed for Answering the post.

      jZed, you were right! When the field's names are not specified in both the file and the csv_table hash, Perl does not complain anymore. In fact, I choose to specify them in the file(s), but is there a reason for prefering one method to the other?

      For the SQL command, I also take note that this is not supported yet and I will find a different way of doing this subselect. (key and value were field name I only used in the example for illustrating my problem.

      --
      jey
[reply]
        In fact, I choose to specify them in the file(s), but is there a reason for prefering one method to the other?
        No, either is fine. I tend to prefer keeping the columns in a file because *) other programs like Lotus or MS-Access that read CSV files can use the field names *) if you forget what the names in the file are, you can either use SQL to query for them or just do a 'head' on the file.
[reply]
Re: Help with DBD::CSV
by sacked (Hermit) on Apr 30, 2004 at 19:14 UTC
    You may need to upgrade the modules used in this program. When running your program with the latest module versions, I do not get the error message described above.

    As for your second question, I'm not sure I understand what you are trying to do with that SQL statement:
    SELECT * FROM table WHERE value = (SELECT * FROM table2 WHERE value2 = ?)

    As far as I know, it is not valid SQL syntax to have field = (LIST). You probably want field IN (LIST).

    In addition, you probably should only be selecting a single column in your subquery:
    SELECT * FROM table WHERE value = (SELECT value2 FROM table2 WHERE value2 = ?)

    If the statement above matches your intent, perhaps you need a join rather than a subquery:
    SELECT * FROM table t, table2 t2 WHERE t.value = t2.value

    If you can explain the desired effect of your SQL statement, I may be able to offer some other suggestions.

    --sacked
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom