So I could run your sample code I added a little code at the start:

...
use DBI;

unlink 'file_01.csv';
unlink 'file_02.csv';
unlink 'new.csv';
open my $oFile, '>', 'file_01.csv';
print $oFile "Prefix,NumberRange,ChargeBand\n";
close $oFile;
open $oFile, '>', 'file_02.csv';
print $oFile "Termination,Service,ChargeBand\n";
close $oFile;

my $dir = '.';
...
    ) or die "Cannot connect: " . $DBI::errstr;

print "DBI            version: $DBI::VERSION\n";
print "DBD::CSV       version: $DBD::CSV::VERSION\n";
print "SQL::Statement version: $SQL::Statement::VERSION\n";

my $sth_match = $dbh_match->prepare(
...
[download]

and got the following:

DBI            version: 1.607
DBD::CSV       version: 0.22
SQL::Statement version: 1.15
Use of uninitialized value in substitution iterator at C:/Perl/lib/SQL
+/Parser.pm line 1596.
    DBI 1.607-ithread default trace level set to 0x0/1 (pid 4280) at n
+oname1.pl line 48
    <- disconnect= 1 at noname1.pl line 50
    <- DESTROY(DBI::st=HASH(3c13f34))= ''
    <- DESTROY(DBI::db=HASH(3c12a54))= ''
    <- disconnect_all= undef at DBI.pm line 717
!   <- DESTROY(DBI::dr=HASH(3c124c4))= undef during global destruction
[download]

I'm using Perl 5.10.1 from ActiveState and after updating to the 'current' versions of the mentioned modules obtained:

DBI            version: 1.616
DBD::CSV       version: 0.31
SQL::Statement version: 1.33
DBD::CSV::st execute failed: You passed 0 parameters where 1 required 
+[for Statement "
        CREATE TABLE new AS SELECT file_01.Prefix, file_01.NumberRange
+, 
                file_02.Termination, file_02.Service, file_02.ChargeBa
+nd
        FROM file_01 INNER JOIN file_02
        ON file_01.Chargeband = file_02.ChargeBand
        WHERE file_02.Termination LIKE '%something%'
      "] at C:\Users\Peter\Delme~~\PerlScratch\noname1.pl line 46.
DBD::CSV::st execute failed: You passed 0 parameters where 1 required 
+[for Statement "
        CREATE TABLE new AS SELECT file_01.Prefix, file_01.NumberRange
+, 
                file_02.Termination, file_02.Service, file_02.ChargeBa
+nd
        FROM file_01 INNER JOIN file_02
        ON file_01.Chargeband = file_02.ChargeBand
        WHERE file_02.Termination LIKE '%something%'
      "] at C:\Users\Peter\Delme~~\PerlScratch\noname1.pl line 46.
[download]

which starts to look like a bug introduced or revealed in the process of fixing another bug.

Interestingly switching to use a place holder in the like:

...
        WHERE file_02.Termination LIKE ?
      |
    );
$sth_match->execute('%something%')
    or die "Cannot execute: " . $sth_match->errstr();

DBI->trace(1);
...
[download]

generates the same error!

Here is the (correct) fix. I'll discuss with TimBo if I can submit this:

You'd have to change (in sub execute) .../DBI/DBD/SqlEngine.pm yourself for now.

    unless ($sth->{sql_params_checked}++) {
        # bug in SQL::Statement 1.20 and below causes breakage
        # on all but the first call
        my @req_prm = $stmt->params ();
        my $n_req = @req_prm == 1 && ref $req_prm[0] ? $req_prm[0]->nu
+m : scalar @req_prm;
        unless ($n_req == (my $nparm = @$params)) {
            my $msg = "You passed $nparm parameters where $n_req requi
+red";
            $sth->set_err ($DBI::stderr, $msg);
            return;
            }
        }
[download]

YES! This did the trick! (Well, I also needed to change the WHERE to AND when combined with INNER JOIN ... ON ... I think I read that somewhere... ;-) )

Thanks, Tux, you ROCK!

HTH,

planetscape

Actually, there seems to be a problem with this fix.

If I run the code from Re: DumpArrayToExcel (for example):

use DBI;
my $dbh=DBI->connect('dbi:CSV:');
my $AoA =[ ['number','name','sex','age'],
           ['0','Jack','M','28'],
           ['1',"Marry",'F','29'] ];
$dbh->do("CREATE TABLE worksheet AS IMPORT(?)",{},$AoA);
__END__
[download]

I get the following error on Strawberry Perl (where I installed your patch), but not on Cygwin Perl, which was unchanged:

DBD::CSV::db do failed: You passed 1 parameters where 0 required [for 
+Statement
"CREATE TABLE worksheet AS IMPORT(?)"] at 443517.pl line 6.
[download]

planetscape

This quite obviously is a bug in SQL::Statement, as when I display the requireed parameters (r) and the parameters actually passed (p), I see this:

{   p                => [
        [
            [   'number',
                'name',
                'sex',
                'age'
                ],
            [   0,
                'Jack',
                'M',
                28
                ],
            [   1,
                'Marry',
                'F',
                29
                ]
            ]
        ],
    r                => [
        bless ({
            num              => 0
            }, 'SQL::Statement::Param' )
        ]
    }
[download]

You could disable the complete parameter count match test if the required params is a blessed SQL::Statement::Param object, but I'm afraid that is sweeping problems under the carpet.

Update: the this statement passed by accident before my fix, as the required list of parameters consisted of only one (wrong) item: an unchecked blessed object. The number of items in the parameters passed was also just one single item: a list reference. The old test just check the lowest level where one happens to be one and the test passed.


Enjoy, Have FUN! H.Merijn

Digging deeper, I remembered that this message has had some code changes around it. Most notable this, which was done by me.

The chang log tells me:

=head2 Changes in DBI 1.609 (svn r12816) 8th June 2009

  Fixes to DBD::File (H.Merijn Brand)
    added f_schema attribute
    table names case sensitive when quoted, insensitive when unquoted
    workaround a bug in SQL::Statement (temporary fix) related
      to the "You passed x parameters where y required" error
[download]

That sure sounds related!

It is however nice to note the intertwined relations here. That change was submitted to DBD::File, but the code seems to have been moved to DBI::DBD::SqlEngine (see sub execute in that file) in more recent DBI versions. If you replace

  unless ((my $req_prm = $stmt->params ()) == (my $nparm = @$params)) 
+{
      my $msg = "You passed $nparm parameters where $req_prm required"
+;
      $sth->set_err ($DBI::stderr, $msg);
      return;
      }
[download]

with

  unless ((my $req_prm = $stmt->params ()) == (my $nparm = @$params)) 
+{
      my $msg = "You passed $nparm parameters where $req_prm required"
+;
      warn $msg;
      }
[download]

you essentially revert to what it was before 2009. Still wrong, but you might find a fix.

/me digs on. If I find something, I'll add that here.

I have no solution, but for all bleading edge versions, it is still the same:

Using DBI            version 1.616
Using DBD::File      version 0.40
Using SQL::Statement version 1.33
Using Text::CSV_XS   version 0.83
DBD::CSV::st execute failed: You passed 0 parameters where 1 required 
+[for Statement "
    CREATE TABLE new AS SELECT file_01.Prefix, file_01.NumberRange,
    file_02.Termination, file_02.Service, file_02.ChargeBand
    FROM file_01 INNER JOIN file_02
    ON file_01.Chargeband = file_02.ChargeBand
    WHERE file_02.Termination LIKE '%something%'"] at inner.pl line 32
+.
DBD::CSV::st execute failed: You passed 0 parameters where 1 required 
+[for Statement "
    CREATE TABLE new AS SELECT file_01.Prefix, file_01.NumberRange,
    file_02.Termination, file_02.Service, file_02.ChargeBand
    FROM file_01 INNER JOIN file_02
    ON file_01.Chargeband = file_02.ChargeBand
    WHERE file_02.Termination LIKE '%something%'"] at inner.pl line 32
+.
Exit 22
[download]

With DBD::CSV problems, in 99% of the cases, the real problem is in DBD::File (part of DBI) or SQL::Statement, which makes it so hard to pinpoint the source of the failures. DBD::CSV is just a very very thin interface layer that links Text::CSV_XS to DBD::File which uses SQL::Statement to do the SQL "stuff".

mje suggested on IRC that % might do something weird in there, so I browsed the SQL::Statement .pm's to see if % was dealt with special, but I saw nothing out of the ordinary.

Then I created file_01.csv and file_02.csv just to make sure that them missing would not cause the failure and I put some data in there:

$ cat file_01.csv
Prefix,NumberRange,Chargeband
pfx,1,4
$ qs f file_01
SCHEMA: merijn, TABLE: file_01
[/pro/3gl/CPAN/DBD-CSV/sandbox]
select    prefix, numberrange, chargeband
from      file_01
prefix|numberrange|chargeband
------------------------------
pfx   |1          |4
$ cat file_02.csv
Termination,Service,ChargeBand
end,yes,4
$ qs f file_02
SCHEMA: merijn, TABLE: file_02
[/pro/3gl/CPAN/DBD-CSV/sandbox]
select    termination, service, chargeband
from      file_02
termination|service|chargeband
-------------------------------
end        |yes    |4
$
[download]

and I removed the %-like stuff (the fact that the where doesn't match doesn't matter now):

$ cat inner.pl
#!/pro/bin/perl

use strict;
use warnings;
use DBI;

my $dir = ".";
my $eol = "\n";
my $sep = ",";

my $dbh_match = DBI->connect ("dbi:CSV:", undef, undef, {
    f_dir        => $dir,
    f_ext        => ".csv/r",
    csv_eol      => $eol,
    csv_sep_char => $sep,
    RaiseError   => 1,
    PrintError   => 1,
    }) or die "Cannot connect: " . $DBI::errstr;

print STDERR "Using perl           version $]\n";
print STDERR "Using DBI            version $DBI::VERSION\n";
print STDERR "Using DBD::File      version $DBD::File::VERSION\n";
print STDERR "Using SQL::Statement version $SQL::Statement::VERSION\n"
+;
print STDERR "Using Text::CSV_XS   version $Text::CSV_XS::VERSION\n";

my $sth_match = $dbh_match->prepare (qq;
    CREATE TABLE new AS SELECT file_01.Prefix, file_01.NumberRange,
    file_02.Termination, file_02.Service, file_02.ChargeBand
    FROM file_01 INNER JOIN file_02
    ON file_01.Chargeband = file_02.ChargeBand
    WHERE file_02.Termination LIKE 'something';
    );
$sth_match->execute or die "Cannot execute: " . $sth_match->errstr ();

DBI->trace (1);

$dbh_match->disconnect;
[download]

$ perl inner.pl
Using perl           version 5.014001
Using DBI            version 1.616
Using DBD::File      version 0.40
Using SQL::Statement version 1.33
Using Text::CSV_XS   version 0.82
DBD::CSV::st execute failed: You passed 0 parameters where 1 required 
+[for Statement "
    CREATE TABLE new AS SELECT file_01.Prefix, file_01.NumberRange,
    file_02.Termination, file_02.Service, file_02.ChargeBand
    FROM file_01 INNER JOIN file_02
    ON file_01.Chargeband = file_02.ChargeBand
    WHERE file_02.Termination LIKE 'something'"] at inner.pl line 33.
DBD::CSV::st execute failed: You passed 0 parameters where 1 required 
+[for Statement "
    CREATE TABLE new AS SELECT file_01.Prefix, file_01.NumberRange,
    file_02.Termination, file_02.Service, file_02.ChargeBand
    FROM file_01 INNER JOIN file_02
    ON file_01.Chargeband = file_02.ChargeBand
    WHERE file_02.Termination LIKE 'something'"] at inner.pl line 33.
Exit 22
$
[download]

FWIW WHERE file_02.Termination = 'end'; yields the same, so LIKE can be ruled out as possible cause.

Arg. Yes, I should have mentioned I tried a few permutations of WHERE clauses, involving =, LIKE, etc., etc., until the WHERE 1=1 was almost a whim. Then, of course, my head exploded. ;-/

HTH,

planetscape