random bind_col error using DBI

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

here is the code i have for connecting to a MS SQL server database:

          eval{
          
            my $dbh = DBI->connect($connectionInfo,$userid,$passwd,{Ra
+iseError => 1});
            my $sth = $dbh->prepare($query);

            $sth->execute();


            @dsetcols=[];
            $xi=0;
            @fieldnames = split(/,/, $fnames);
            foreach $fieldname (@fieldnames) {
              $ds{$dsetname}{$fieldname}=[];
              push @dsetcols,"";
              $sth->bind_col($xi+1,\$dsetcols[$xi]);
       
              $xi++;
            }
            
            while($sth->fetch()) {
              $xi=0;
              foreach $fieldname (@fieldnames) {

                push @{$ds{$dsetname}{$fieldname}},safedecode($dsetcol
+s[$xi]);

                $xi++;
              }
            }
            
            push @{$ds{rowcount}{$dsetname}},$sth->rows();
            
            $sth->finish();
            

            $dbh->disconnect; 
            
            print "$query <p>";

          };
          
          if($@){
            print "<a href=\"";
            print $ENV{'HTTP_REFERER'};
            print "\">Back</a><p>";
            print "<p>";
            print "<b>Query</b> <i>\"";    
            print $query;
            print "\" </i> <b>failed.</b><p>";
            print "<b>Database Error Message: </b><i>\"";
            print $@;
            print "\"</i><p>";
            $badquery = 1;
          }
        }
        $x++;
      }
[download]

but at random times i would receive this error message: bind_col: column 2 is not a valid column (1..1) which points to the line where bind_col is being called.. i'm not exactly sure what the error message means.. any idea why this is happening? thanks

Comment on random bind_col error using DBI Download Code

Replies are listed 'Best First'.
Re: random bind_col error using DBI by Tanktalus (Canon) on May 23, 2005 at 17:54 UTC
It means that your $query sometimes only has a single placeholder ('?') in it. So I'd concentrate on how you set up the $query at this point in debugging this application.	[reply]
Re^2: random bind_col error using DBI by chauzer (Novice) on May 23, 2005 at 18:13 UTC
Can you explain what you mean by a query having only a single placeholder? I'm kind of confused by that when debugging, i have it print out the actual query as well so i can see which query caused the error... an example of one query that caused the bind_col error was this: SELECT user_id, username FROM users WHERE purch_perm_id != 1 AND deleted != 1 ORDER BY username the weird thing is, if i hit refresh, the page will load fine and there won't be any errors	[reply]
Re^3: random bind_col error using DBI by Tanktalus (Canon) on May 24, 2005 at 23:40 UTC
I didn't say "query", I said "$query" - as in the variable you're passing in to $dbh->prepare(). The query you posted, "`SELECT user_id, username FROM users WHERE purch_perm_id != 1 AND deleted != 1 ORDER BY username`" has no placeholders, so you shouldn't be binding anything. Then the next place to look is where you're doing your binding. `@dsetcols=[];` [download] I don't think this is doing what you think it's doing. It might be, I'm not entirely sure. This is setting @dsetcols to be an array with one element: an arrayref. You probably want: `@dsetcols=();` [download] which sets @dsetcols to an empty array. Next you do: `@fieldnames = split(/,/, $fnames);` [download] So, when your code fails, what is $fnames? Print that out in your error message, too. I would expect that your example query doesn't have a blank $fnames, even though it should.	[reply] [d/l] [select]