Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

"You passed 0 parameters where 1 required" error using DBD::CSV, an INNER JOIN, and a WHERE clause

by planetscape (Chancellor)
on Jun 21, 2011 at 18:53 UTC ( #910793=perlquestion: print w/replies, xml ) Need Help??

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

Greeting Monks. I have a conundrum. This code causes the error "You passed 0 parameters where 1 required":

#!/usr/bin/perl -w 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; 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;

Output:

DBD::CSV::st execute failed: You passed 0 parameters where 1 required +[for State ment " 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 test_DBI__CSV_read_for_PM.pl line 32. DBD::CSV::st execute failed: You passed 0 parameters where 1 required +[for State ment " 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 test_DBI__CSV_read_for_PM.pl line 32.

However, change the WHERE clause to an entirely useless WHERE 1=1, and it doesn't error!

perl -V:

Summary of my perl5 (revision 5 version 10 subversion 0) configuration +: Platform: osname=MSWin32, osvers=5.1, archname=MSWin32-x86-multi-thread uname='' config_args='undef' hint=recommended, useposix=true, d_sigaction=undef useithreads=define, usemultiplicity=define useperlio=define, d_sfio=undef, uselargefiles=define, usesocks=und +ef use64bitint=undef, use64bitall=undef, uselongdouble=undef usemymalloc=n, bincompat5005=undef Compiler: cc='gcc', ccflags =' -s -O2 -DWIN32 -DHAVE_DES_FCRYPT -DPERL_IMPL +ICIT_CONTE XT -DPERL_IMPLICIT_SYS -fno-strict-aliasing -DPERL_MSVCRT_READFIX', optimize='-s -O2', cppflags='-DWIN32' ccversion='', gccversion='3.4.5', gccosandvers='' intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=1234 d_longlong=undef, longlongsize=8, d_longdbl=define, longdblsize=12 ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='long lo +ng', lseek size=8 alignbytes=8, prototype=define Linker and Libraries: ld='g++', ldflags ='-s -L"C:\strawberry\perl\lib\CORE" -L"C:\straw +berry\c\li b"' libpth=C:\strawberry\c\lib libs= -lmsvcrt -lmoldname -lkernel32 -luser32 -lgdi32 -lwinspool - +lcomdlg32 -ladvapi32 -lshell32 -lole32 -loleaut32 -lnetapi32 -luuid -lws2_32 -lm +pr -lwinmm -lversion -lodbc32 -lodbccp32 perllibs= -lmsvcrt -lmoldname -lkernel32 -luser32 -lgdi32 -lwinspo +ol -lcomdl g32 -ladvapi32 -lshell32 -lole32 -loleaut32 -lnetapi32 -luuid -lws2_32 + -lmpr -lw inmm -lversion -lodbc32 -lodbccp32 libc=-lmsvcrt, so=dll, useshrplib=true, libperl=libperl510.a gnulibc_version='' Dynamic Linking: dlsrc=dl_win32.xs, dlext=dll, d_dlsymun=undef, ccdlflags=' ' cccdlflags=' ', lddlflags='-mdll -s -L"C:\strawberry\perl\lib\CORE +" -L"C:\st rawberry\c\lib"' Characteristics of this binary (from libperl): Compile-time options: MULTIPLICITY PERL_DONT_CREATE_GVSV PERL_IMPLICIT_CONTEXT PERL_IMPLICIT_SYS PERL_MALLOC_WRAP PL_OP_SLAB_ALLOC USE_ITHREADS USE_LARGE_FILES USE_PERLIO Built under MSWin32 Compiled at Apr 5 2008 10:33:57 %ENV: PERL5LIB="/lib/perl5/site_perl/5.10" @INC: /lib/perl5/site_perl/5.10 C:/strawberry/perl/lib C:/strawberry/perl/site/lib .

DBI version: 1.615

DBD::CSV version: 0.31

I've gone blind looking at this. Please, someone, lead me out of this cave!


Update: SQL::Statement version: 1.33

Update 2011-06-24: Jens Rehsack quite correctly pointed out on irc.perl.org/6667#dbi today that SQL::Statement does NOT support combining JOIN ... ON and WHERE. I had originally tried AND, but when several variations failed with the You passed 0 parameters where 1 required error, I tried experimenting with WHERE, just to see if that made a difference, and then eventually hit upon WHERE 1=1, whence the error disappeared, and I knew something was wrong. That's when I posted here, although by that time the restriction had slipped my mind. I alluded to this in these posts, but evidently did not make myself very clear at all. ;-)

HTH,

planetscape
  • Comment on "You passed 0 parameters where 1 required" error using DBD::CSV, an INNER JOIN, and a WHERE clause
  • Select or Download Code

Replies are listed 'Best First'.
Re: "You passed 0 parameters where 1 required" error using DBD::CSV, an INNER JOIN, and a WHERE clause
by GrandFather (Saint) on Jun 21, 2011 at 22:10 UTC

    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( ...

    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

    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.

    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); ...

    generates the same error!

    True laziness is hard work
Re: "You passed 0 parameters where 1 required" error using DBD::CSV, an INNER JOIN, and a WHERE clause
by Tux (Canon) on Jun 22, 2011 at 12:57 UTC

    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; } }

    Enjoy, Have FUN! H.Merijn

      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__

      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.
      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' ) ] }

        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
Re: "You passed 0 parameters where 1 required" error using DBD::CSV, an INNER JOIN, and a WHERE clause
by Tux (Canon) on Jun 22, 2011 at 12:05 UTC

    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

    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; }

    with

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

    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.


    Enjoy, Have FUN! H.Merijn
Re: "You passed 0 parameters where 1 required" error using DBD::CSV an INNER JOIN, and a WHERE clause
by Tux (Canon) on Jun 22, 2011 at 07:59 UTC

    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

    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".


    Enjoy, Have FUN! H.Merijn
Re: "You passed 0 parameters where 1 required" error using DBD::CSV, an INNER JOIN, and a WHERE clause
by Tux (Canon) on Jun 22, 2011 at 10:24 UTC

    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 $

    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;
    $ 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 $

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


    Enjoy, Have FUN! H.Merijn

      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
A reply falls below the community's threshold of quality. You may see it by logging in.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://910793]
Approved by BrowserUk
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (4)
As of 2022-12-04 17:55 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?