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

Here is the code that is giving me problems
    my $date_file = new IO::File;
    $date_file->open("<start_date.txt") or die "cannot find file!";
    chomp(my $date = $date_file->getline);
    $date_file->close;


    my $query = q{SELECT Count(x) AS numberOfx, y
                   INTO temp_table
                   FROM Referrals
                   WHERE ((x Is Not Null) AND ([Today's Date]> ?))
                   GROUP BY x
                   ORDER BY Count(x), y};
        $sth = $dbh->prepare($query);
        $sth->execute($date);
        $sth->finish();

[download]
This gives the error 
DBD::ODBC::st execute failed: called with 1 bind variables when 0 are 
+needed at
C:\Documents and Settings\xxx\Desktop\distribute.pl line 264.

[download]
To me this was strange....so I decide to oblige the error and remove the bind variable to see if it expects one when I don't provide it. So I take $date out of the call to $sth->execute() and I get this error 
DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver
+] Too few
parameters. Expected 1. (SQL-07002)(DBD: st_execute/SQLExecute err=-1)
+ at C:\Doc
uments and Settings\xxx\Desktop\distribute.pl line 264.

[download]
Why does it expect one when I don't provide it, and not want one when I do? This is an odd error.. any ideas?

Replies are listed 'Best First'.
(jeffa) Re: weird DBI error re: parameter binding
by jeffa (Bishop) on Jul 30, 2003 at 17:06 UTC
    You are indeed telling DBI that you will have one bind var with this line of SQL: 
    WHERE ((x Is Not Null) AND ([Today's Date]> ?))

    [download]
    But it seems that DBI (or the ODBC driver) doesn't like that line. First off, a field name of "Today's Date" is god-awful ... i don't use spaces or any special characters in my tables or my fields, regardless of whether or not it is valid to do so. It most definitely is not portable. Why not name that field today_date or such? Much easier to deal with.

    My recommendation is to simplify your test, maybe something like: 

    my $dbh = (
   'connection string', 'user', 'pass',
   {RaiseError => 1},   # IMPORTANT!!!
);

my $sth = $dbh->prepare(q|
   select * from Referrals
   where [Today's Date] > ?
|);
$sth->execute($date);

    [download]
    And see what happens. If that doesn't reveal anything new, then how about simply: 
    my $sth = $dbh->prepare(q|
   select [Today's Date] from Referrals
|);
$sth->execute();

    [download]
    UPDATE:
    I really am no DBI expert ... but this might just be a bug within DBI ... maybe DBI chokes on elaborate field names and or the square brackets when it tries to bind the vars. A cheap solution is to just interpolate $date instead your SQL string ... but only do this if you trust the contents of $date or really scrub it like skyknight suggested. (skyknight++)

    jeffa

    L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)
[reply]
[d/l]
[select]

      I agree .. I had a Sh*t fit when I saw the table names and field names.. its not my doing.. I'm just workin with what I have.

              my $query = q{SELECT x
                   FROM Referrals
                   WHERE [Today's Date]>?};
        $sth = $dbh->prepare($query);
        $sth->execute($date);

      [download]
      produced the error
      DBD::ODBC::st execute failed: called with 1 bind variables when 0 are 
+needed at
C:\Documents and Settings\xxx\Desktop\distribute.pl line 263.

      [download]
      I have no idea why this is causing problems. If it were a type mismatch the DBD would probably say so.. .as I have had similar errors in the past. The query does work if I hard code in a value and call with no bind variables however. So at least things aren't THAT screwed up.

      edit:I took jeffa's suggestion and just interpolated it like I did before the grand days of discovering parameter binding.. this worked just fine... still bugs me though.
[reply]
[d/l]
[select]

        Sounds like your DBD doesn't handle [ and ] and so sees the ' as the start of a string literal and so doesn't consider the ? to be a placeholder.

                        - tye
[reply]
Re: weird DBI error re: parameter binding
by skyknight (Hermit) on Jul 30, 2003 at 16:56 UTC

    Try adding some actual error handling to your code... You really ought to have some regex or other verifying the content of $date before you pass it off to execute. You've done nothing to prove to me that $date has a valid value in it.

    Update: Bah! Why on earth would people mod down my comment? We know absolutely nothing from his post what is contained in $data, and his assumptions about it may very well be the root of his problems.

[reply]
      The file is one line... I made the file. It contains 06/25/2003 I also tried it as 2003-06-25 I printed it out from the script as well.. 
      *06/25/2003*

      [download]
      was the output generated when I added print "*".$date."*";after the file read. Any more questions?
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom