problem with bind_param()

Hi,

I want to use bind_param(). Therefor I search for the datatype of a
column and do the binding.

This piece of code works:

#! /usr/bin/perl

use strict;
use warnings;
use DBI ':sql_types';

my $dbh = DBI->connect('DBI:ODBC:driver=Microsoft Access-Treiber
(*.mdb);dbq=db1.mdb',user,pass);

my $col = 'Testcol';

my $statement = "INSERT INTO table (`$col`) VALUES(?)";

my $coltype = get_type($col,$dbh);
$coltype = 'SQL_'.$coltype;
my $sth = $dbh->prepare($statement);

for(2..1000){
my $var = ;
chomp $var;
no strict 'refs';
$sth->bind_param(1,$var,&{"DBI::$coltype"});
use strict 'refs';
$sth->execute($var) or die $dbh->errstr();
}

sub get_type{
my ($name,$dbh) = @_;
my ($sthcolinfo) = $dbh->column_info(undef,undef,undef,$name);
my $hashref = $sthcolinfo->fetchrow_hashref();
return $hashref->{TYPE_NAME};
}


But when I split the code in "script" and "module" it does not work.
I've tried this:

#! /usr/bin/perl

use strict;
use warnings;
use lib qw(.);
use DbiTest;
use DBI ':sql_types';

my $col = 'Testcol';
DbiTest->new($col);


module:

package DbiTest;

use strict;
use warnings;
use DBI ':sql_types';

sub new{
my ($class,$col) = @_;
my $dbh = DBI->connect('DBI:ODBC:driver=Microsoft Access-Treiber
(*.mdb);dbq=db1.mdb',user,pass);

my $statement = "INSERT INTO table (`$col`) VALUES(?)";

my $coltype = get_type($col,$dbh);
$coltype = 'SQL_'.$coltype;
my $sth = $dbh->prepare($statement);

for(2..1000){
my $var = ;
chomp $var;
no strict 'refs';
$sth->bind_param(1,$var,&{"DBI::$coltype"});
use strict 'refs';
$sth->execute($var) or die $dbh->errstr();
}
}

sub get_type{
my ($name,$dbh) = @_;
my ($sthcolinfo) = $dbh->column_info(undef,undef,undef,$name);
my $hashref = $sthcolinfo->fetchrow_hashref();
return $hashref->{TYPE_NAME};
}

1;

With that code the script dies with the following errormessage:
Usage: SQL_LONGVARCHAR() at DbiTest line 21

Regards,
Renee Baecker

Re: problem with bind_param()

The solution is quite simple:
$sth->bind_param(1,$var,&{"DBI::$coltype"}());

But the question is why my version (&{"DBI::$coltype"} ) runs in a
single script and why it doesn't work after splitting into script and
module...

Regards,
Renee


Am 14.09.2005 um 10:00 Uhr haben Sie geschrieben:
> Hi,
>
> I want to use bind_param(). Therefor I search for the datatype of a
> column and do the binding.
>
> This piece of code works:
>
> #! /usr/bin/perl
>
> use strict;
> use warnings;
> use DBI ':sql_types';
>
> my $dbh = DBI->connect('DBI:ODBC:driver=Microsoft Access-Treiber
> (*.mdb);dbq=db1.mdb',user,pass);
>
> my $col = 'Testcol';
>
> my $statement = "INSERT INTO table (`$col`) VALUES(?)";
>
> my $coltype = get_type($col,$dbh);
> $coltype = 'SQL_'.$coltype;
> my $sth = $dbh->prepare($statement);
>
> for(2..1000){
> my $var = ;
> chomp $var;
> no strict 'refs';
> $sth->bind_param(1,$var,&{"DBI::$coltype"});
> use strict 'refs';
> $sth->execute($var) or die $dbh->errstr();
> }
>
> sub get_type{
> my ($name,$dbh) = @_;
> my ($sthcolinfo) = $dbh->column_info(undef,undef,undef,$name);
> my $hashref = $sthcolinfo->fetchrow_hashref();
> return $hashref->{TYPE_NAME};
> }
>
>
> But when I split the code in "script" and "module" it does not work.
> I've tried this:
>
> #! /usr/bin/perl
>
> use strict;
> use warnings;
> use lib qw(.);
> use DbiTest;
> use DBI ':sql_types';
>
> my $col = 'Testcol';
> DbiTest->new($col);
>
>
> module:
>
> package DbiTest;
>
> use strict;
> use warnings;
> use DBI ':sql_types';
>
> sub new{
> my ($class,$col) = @_;
> my $dbh = DBI->connect('DBI:ODBC:driver=Microsoft Access-Treiber
> (*.mdb);dbq=db1.mdb',user,pass);
>
> my $statement = "INSERT INTO table (`$col`) VALUES(?)";
>
> my $coltype = get_type($col,$dbh);
> $coltype = 'SQL_'.$coltype;
> my $sth = $dbh->prepare($statement);
>
> for(2..1000){
> my $var = ;
> chomp $var;
> no strict 'refs';
> $sth->bind_param(1,$var,&{"DBI::$coltype"});
> use strict 'refs';
> $sth->execute($var) or die $dbh->errstr();
> }
> }
>
> sub get_type{
> my ($name,$dbh) = @_;
> my ($sthcolinfo) = $dbh->column_info(undef,undef,undef,$name);
> my $hashref = $sthcolinfo->fetchrow_hashref();
> return $hashref->{TYPE_NAME};
> }
>
> 1;
>
> With that code the script dies with the following errormessage:
> Usage: SQL_LONGVARCHAR() at DbiTest line 21
>
> Regards,
> Renee Baecker
>
>
>
>

RE: problem with bind_param()

perl@renee-baecker.de [mailto:perl@renee-baecker.de] wrote:

> The solution is quite simple:
> $sth->bind_param(1,$var,&{"DBI::$coltype"}());
>
> But the question is why my version (&{"DBI::$coltype"} ) runs in a
> single script and why it doesn't work after splitting into script and
> module...

perldoc perlsub:

To call subroutines:

NAME(LIST); # & is optional with parentheses.
NAME LIST; # Parentheses optional if
predeclared/imported.
&NAME(LIST); # Circumvent prototypes.
&NAME; # Makes current @_ visible to called
subroutine.

In other words, &NAME is functionally equivalent to &NAME(@_).

In your original code, when you called &{"DBI::$coltype"}, @_ was empty,
so it worked. Once you moved the code into a subroutine, @_ was no
longer empty, so you needed to add the parens to make sure the SQL_...
sub was called without any arguments.

Ronald