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

Hi! First of all sorry for my bad english =) I've just started learning DBI, and faced a problem with bind_columns usage. So..here is my code 
BEGIN { $ENV{DBI_SQL_NANO} = 1 }
use DBI;
use DBI::SQL::Nano;
my $dbh = DBI->connect('DBI:DBM(RaiseError=1):');
my $query = "CREATE TABLE socks (id TINYINT NOT_NULL AUTO_INCREMENT, i
+p VARCHAR(12), port INT,country CHAR(2))" and sql_exec(0,$query);
sub sql_exec(0,$query) {....}
#sql_exec is my subroutine, which takes 2 args first #boolean, which d
+efines 0 non-select query, or 1 select #query respectively, and a que
+ry itself, and makes #a standard $sql = $dbh->prepare($query) || die(
+$dbh->errstr);
# $sql->execute || die($sql->errstr); etc..
$query = "INSERT INTO socks (id,ip,port,country) VALUES (1,180.118.232
+,8080,US)" and sql_exec(0,$query);#no problem
#and on binding a select query result we got stuck..for #example
$query = "SELECT (id,ip,port,country) FROM socks where id = 1";
$sql = $dbh->prepare($query) || die($dbh->errstr);
my ($id,$ip,$country);
$sql->bind_columns(\($id,$ip,$port,$country));
$sql->execute || die($sql->errstr);
while($sql->fetch)
{
    print "id=>$id, ip=>$ip, port=>$port, country=>$country\n";
}

And on a DBI->trace(4); i've got smth like

<- fetch= [ '1' '180.118.232' undef ]
and on insert i've got
-> prepare in DBD::File::db for DBD::DBM::db (DBI::db=HASH(0x80106e440
+)~0x80106e3c0 'INSERT INTO socks (id,ip,port,country) VALUES (1,180.1
+18.232,8080,US)') thr#80100b000
    <> FETCH= 'DBD::DBM::st' ('ImplementorClass' from cache) at /usr/l
+ocal/lib/perl5/site_perl/5.8.9/mach/DBD/File.pm line 179 via  at clic
+k.pl line 25
    -> STORE in DBD::File::st for DBD::DBM::st (DBI::st=HASH(0x8019a33
+10)~0x80235dcd0 'f_stmt' DBD::DBM::Statement=HASH(0x80235dc20)) thr#8
+0100b000
    <- STORE= 1 at /usr/local/lib/perl5/site_perl/5.8.9/mach/DBD/File.
+pm line 206 via  at click.pl line 25
    -> STORE for DBD::DBM::st (DBI::st=HASH(0x8019a3310)~0x80235dcd0 '
+f_params' ARRAY(0x80235ddd0)) thr#80100b000
    <- STORE= 1 at /usr/local/lib/perl5/site_perl/5.8.9/mach/DBD/File.
+pm line 207 via  at click.pl line 25
    -> STORE for DBD::DBM::st (DBI::st=HASH(0x8019a3310)~0x80235dcd0 '
+NUM_OF_PARAMS' 0) thr#80100b000

[download]
I just cant understand where the problem is.. Thanks a lot in advance!

Replies are listed 'Best First'.
Re: DBI and bind_columns again
by CountZero (Bishop) on May 01, 2009 at 07:42 UTC
    If your were running this code under use strict; you would have gotten an error for this part of the code:
    my ($id,$ip,$country);
$sql->bind_columns(\($id,$ip,$port,$country));

    [download]
    which should be 
    my ($id,$ip,$port,$country);
$sql->bind_columns(\($id,$ip,$port,$country));

    [download]
    This may or may not have anything to do with your problem, whatever your problem is: "and on binding a select query result we got stuck.." isn't the most clear description of an error. What kind of error are you experiencing?

    Also, the idiom 

    my $query = "CREATE ..." and sql_exec(0,$query);

    [download]
    is unnecessarily obfuscated. Why don't you just do:
    my $query = "CREATE ...";
sql_exec(0,$query);

    [download]
    or even
    sql_exec(0,"CREATE ...");

    [download]
    ?

    CountZero

    A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

[reply]
[d/l]
[select]
      Read more... Quote (573 Bytes)
      You are right..but it is right in my code, i've just misscopypasted some how o_O =)
      Read more... Quote (227 Bytes)
      Description of a problem is not in "and on binding a select query result we got stuck.." it is in a DBI debug report >> "<- fetch= '1' '180.118.232' undef undef row1" So two fields are defined, but others are now..i just cant understand how it could be, while it was clearly INSERT'ed.
      Read more... Quote (326 Bytes)
      It is reasonable, thank you!
[reply]
[d/l]
[select]
        Are you sure all values were indeed correctly inserted? My first guess would be that the port and country fields are empty in your database.

        CountZero

        A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

[reply]
Re: DBI and bind_columns again
by almut (Canon) on May 01, 2009 at 09:40 UTC 
    $sql->bind_columns(\($id,$ip,$port,$country));
$sql->execute || die($sql->errstr);

    [download]

    Have you tried putting the bind_columns after the execute, as the documentation recommends:

    "For maximum portability between drivers, bind_columns() should be called after execute() and not before."
[reply]
[d/l]
[select]
      Yes, i have, but that doesn't solve solve the problem
[reply]

Back to Seekers of Perl Wisdom