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

I use stuff like $sth->prepare($SQL_STATEMENT) all the time. However that doesn't seem to work if I have places for bind varibles in the query. example: 
$SQL_STATEMENT = qq!SELECT * FROM FOO WHERE THIS = ?!;
$sth = $dbh->prepare($SQL_STATEMENT);
$sth->execute($value_for_this);

[download]
this results in: "execute called with 5 bind variables when 0 are needed at ...". If I do it like this: 
$sth = $dbh->prepare('SELECT * FROM FOO WHERE THIS = ?');
$sth->execute($value_for_this);

[download]
It works. Is it me?

Replies are listed 'Best First'.
Re: DBI not letting bind varibles in if you use a varible for the statement.
by ncw (Friar) on Sep 06, 2000 at 19:57 UTC
    I would say its you - I use this syntax every day and it works fine!

    Examine carefully what you wrote - on the first example you used qq{...} and on the second you used '...'. That is the first example does variable interpolation (equivalent to "...") and the second one doesn't.

    I can't see how this would matter in the code you've posted but I suspect if you look at the real code you'll see a difference.

[reply]
      nail on the head. thanks. ...jptxs thinks about writing a proofreading script for himself :)
[reply]
(jeffa) Re: DBI not letting bind varibles in if you use a varible for the statement.
by jeffa (Bishop) on Sep 06, 2000 at 19:59 UTC
    It looks like the question mark in your first example is being interpolated. Use single quotes instead, like the second example.

    UPDATE: oops, ignore my first sentance. Just to save my butt for giving a bad answer, I will say that the second example (using a ? in the SQL statement and then passing arguments to the execute funtion) is better than using double quotes and interpolated any arguments in the SQL statement. The reason is because using the ? in single quotes will allow the SQL statement to be cached once, resulting in a slight performance increase. Example: 

    'SELECT * FROM FOO WHERE BAR=?'     #this gets used many times

#whereas
"SELECT * FROM FOO WHERE BAR=$val"  #might only get used once
                                    #because different values
                                    #of $val will cause many
                                    #different versions to get
                                    #cached

    [download]
    Just in case someone reading this didn't know . . .
    Jeff
[reply]
[d/l]
(Ovid) Re: DBI not letting bind varibles in if you use a varible for the statement.
by Ovid (Cardinal) on Sep 06, 2000 at 20:13 UTC
    I'm looking at your code and I can only see one problem: you didn't post the code causing the error. I don't think jeffa's suggestion about using single instead of double quotes is relevant (sorry jeffa) as I use both single and double quotes all the time, depending upon the situation.

    I would suggest posting the actual code snippet causing the error. Also, print out your SQL statement to verify that you have the syntax correct. However, make sure you get down to the bottom of this rather than just go the second route. I build SQL statements on the fly all the time and if I was forced to use your second example, I couldn't do that.

    Random thoughts: your SQL statement was all caps. Is it that was in your program also? If so, are you getting some weird problem with field names being case-sensitive (I don't think that's it as that generates and "invalid field" message or something similar). Also, are you working with multiple fields and perhaps have your commas wrong?

    Cheers,
    Ovid

    Update: I am totally stumped as to why single quotes would work here (as opposed to double quotes). The only things I could think could cause issues would be if you were to have unusual characters in field name (such as $, \, or @) which could cause interpolation within double quotes. Both with MySQL and MS SQL Server I've had no problem using double quotes.

    Oracle's fields names are not case-sensitive? I suppose that's what they call a "feature" ala MS-Speak. Hmm... I'll have to start investigating this. I just assumed they were case-sensitive... :(

[reply]
      that is the code, except for the exceedingly long SQL statement - which was built for me by a tool and works fine. The suggestion about using single quotes actually did fix the issue - was that just a side effect?

      Also, I don't know about other DBs, but Oracle's column names are not case-sensative. The all caps thing is how I learned SQL from the get-go (been writing SQL long before Perl :)

[reply]
        That is the code, except for the exceedingly long SQL statement - which was built for me by a tool and works fine. The suggestion about using single quotes actually did fix the issue - was that just a side effect?
        The "exceedingly long SQL statement" was the problem, if changing from double quotes to single quotes fixed it. You must have some @, $, or \ somewhere in that string.

        When people post "this breaks" but don't post the code that actually breaks, all we can do is guess. On Usenet, it's a common practice to simply make up a nonsense answer like "the problem is on line 48, or near there". {grin}

        But yes, changing the stuff you posted from double to single quote wouldn't have changed a thing, so whatever your secret SQL is, that's where the problem was. Please keep that in mind next time. Most of the people answering questions here are not asking for things idly. {grin}

        -- Randal L. Schwartz, Perl hacker

[reply]
Re: DBI not letting bind varibles in if you use a varible for the statement.
by btrott (Parson) on Sep 06, 2000 at 20:02 UTC
    Do you have the RaiseError attribute set to 1? If not, it could be possible that you previously assigned $sth to a *different* statement handle; then you tried to prepare this one, and there was an error, but you didn't see it, because you're not checking for errors. Could it be possible that $sth is still referencing the old statement handle, then? And that that old statement handle has 5 bind variables?

    It sounds kind of farfetched, I know. :)

    Anyway... try checking for errors on your prepare: 

        $sth = $dbh->prepare($SQL_STATEMENT)
        or die "Can't prepare $SQL_STATEMENT: ", $dbh->errstr;

    [download]
    Or turn on RaiseError, if you don't already have it on.

    I've used this construct many, many times, so I'd say it's definitely something weird in your situation.

[reply]
[d/l]

Back to Seekers of Perl Wisdom