DBI bind_param problems

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

Os: Linux
DB: MS SQL Server
DBD: Sybase w/Free tns

Hi All, I have a very peculiar bind_param problem. Below is the (leaned down code snippet) of a routine in a module i'm writing.

Below doesnt work

sub load {
    my $class = shift;
    my $id = shift;

    my $dbh = Some::Class->get_dbh();

    my $sth = $dbh->prepare(<<"ENDSQL");
SELECT  field1, field2
FROM    table
WHERE   id = ?
ENDSQL

    my ($field1, $field2);

    eval {
#       need to figure out why this bind_param isnt working
        $sth->bind_param(1, $id);
        $sth->execute();
        $sth->bind_col(1, \$field1);
        $sth->bind_col(2, \$field2);
    };

    if($@){
        print "There was a error";
    }

    $sth->fetch();
    $sth->finish();
}
[download]

The bind_param above does not seem to be binding the parameter.

Below does work

sub load {
    my $class = shift;
    my $id = shift;

    my $dbh = Some::Class->get_dbh();

    my $sth = $dbh->prepare(<<"ENDSQL");
SELECT  field1, field2
FROM    table
WHERE   id = $id
ENDSQL

    my ($field1, $field2);

    eval {
        $sth->execute();
        $sth->bind_col(1, \$field1);
        $sth->bind_col(2, \$field2);
    };

    if($@){
        print "There was a error";
    }

    $sth->fetch();
    $sth->finish();
}
[download]

If i dont bind the param it works fine. Has anyone experienced this? Is this at the dbi or dbd level.

Comment on DBI bind_param problems Select or Download Code

Replies are listed 'Best First'.
Re: DBI bind_param problems by mpeppler (Vicar) on Dec 18, 2002 at 19:23 UTC
This is a FreeTDS problem - placeholders aren't supported yet, which means that bind_param() can't work either. The FreeTDS developers are hard at work trying to add placeholder support. Michael	[reply]
Re: Re: DBI bind_param problems by Anonymous Monk on Dec 18, 2002 at 19:28 UTC
Thanks Michael. I figured as much since all my other code that uses bind_param works with dbd oracle and mysql. Thanks for the confirmation.	[reply]
Re: DBI bind_param problems by JamesNC (Chaplain) on Dec 18, 2002 at 19:20 UTC
Looks like you omitted the variable in your execute statement for your ? placeholder. Try this... `sub load { my $class = shift; my $id = shift; my $dbh = Some::Class->get_dbh(); my $sth = $dbh->prepare(<<"ENDSQL"); SELECT field1, field2 FROM table WHERE id = ? ENDSQL my ($field1, $field2); eval { # need to figure out why this bind_param isnt working $sth->bind_param(1, $id); $sth->execute($id); # ? is a placeholder, you have to supply + execute with the var :) $sth->bind_col(1, \$field1); $sth->bind_col(2, \$field2); };` [download]	[reply] [d/l]
Re: DBI bind_param problems by runrig (Abbot) on Dec 18, 2002 at 19:02 UTC
`if($@){ print "There was a error"; }` [download] This is not a very informative error message. Do you have RaiseError set (see the DBI docs)? Set it and include $@ in your error message. One wild guess is that '$id' has embedded newline(s) or other characters in it.	[reply] [d/l]
Re: DBI bind_param problems by Fletch (Bishop) on Dec 18, 2002 at 20:42 UTC
Consider enabling tracing on your handle (setting it to `2' is usually sufficient for getting what's getting sent to and from the underlying database libraries). Seeing just what's getting passed back and forth to be executed can clear up whether the problem is in your code or in what you're passing. See `perldoc DBI` for more on the `trace()` method.	[reply] [d/l] [select]
Re: DBI bind_param problems by benn (Vicar) on Dec 18, 2002 at 19:31 UTC
The DBI docs mention that not all drivers support placeholders, while the DBD::Sybase docs talk about placeholders only as part of an execute statement...ie `$sth->execute($id);` [download] Maybe this is the problem - bind_param may not be fully implemented in DBD::Sybase.	[reply] [d/l]