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

I am having a problem trying to select more than 8 columns from a table using the DBI module. It works fine if I use any one less column and the query works when entered into the database. Is there a simple way to fix this query or will I need to use a different method? 
$query = 'select date, run_time, run_dis, num_push, num_sit, abs, comm
+ent, workout_id, scored from "cshaws_Schema"."log2" where pid = ' . $
+pid . ' order by date';

$sth = $dbh->prepare($query);
$sth->execute($query);
$sth->bind_columns(\$dates, \$run_time, \$run_dis, \$num_push, \$num_s
+it, \$abs, \$comment, \$workout_id, \$scored );
while($sth->fetch())
{
processes
}

[download]

Replies are listed 'Best First'.
Re: DBI Bind Columns
by dragonchild (Archbishop) on Apr 22, 2004 at 17:40 UTC
    Lemme guess, you're on Oracle and the column you keep removing to make it work is "date", right? :-) (You can't have a column called "date" in Oracle. Or "count", or "abs", etc.)

    Another thing, you might want to add "use strict" at the top of your script.

    Another thing, you'll want to look up "placeholders" in the DBI documentation.

    Another thing, you'll want to look at using the following notation: 

    $sth->bind_columns(\my(
    $dates,
    $run_time,
    $run_die,
    # ...
));

    [download]

    (I'm starting to sound like an old Chinese uncle. *grins*)

    ------
    We are the carpenters and bricklayers of the Information Age.

    Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

[reply]
[d/l]
Re: DBI Bind Columns
by DamnDirtyApe (Curate) on Apr 22, 2004 at 17:34 UTC

    Not sure about the bind variables, but the first bit should look like this:

    $query = <<'END';
select date, run_time, run_dis, num_push,
       num_sit, abs, comment, workout_id, scored
from "cshaws_Schema"."log2"
where pid = ? order by date
END

$sth = $dbh->prepare($query);
$sth->execute($pid);

    [download]

    _______________
    DamnDirtyApe 
    Those who know that they are profound strive for clarity. Those who
would like to seem profound to the crowd strive for obscurity.
            --Friedrich Nietzsche
[reply]
[d/l]
Re: DBI Bind Columns
by disciple (Pilgrim) on Apr 22, 2004 at 18:14 UTC

    Many monks are willing to help you with just the information you posted, but you will probably get the correct answer faster if you tell us "how it isn't working". I.E. Is there an error message? If so, what is it? Are there unexpected results? If so, what are they?

    Just trying to get some more info so we provide better help.

[reply]
Re: DBI Bind Columns
by Anonymous Monk on Apr 22, 2004 at 17:49 UTC
    Sorry, should have mentioned that I am using a PostgreSQL database. Also, the it will function proplery with using the date and abs column names, removing on any one of the columns and it will work. Thanks for the help
[reply]
Re: DBI Bind Columns
by asdfgroup (Beadle) on Apr 23, 2004 at 00:03 UTC
    Hi, Maybe you provide more detailed info ? :)
    Or use this as other method : 
    $sth = $dbh->prepare($query);
$sth->execute($query);
while (my $row = $sth->fetchrow_hashref()) {
   #use $row->{date} etc here.
}

    [download]
    This is a bit slowly than bind_column but more straight-forward and lazy :)
[reply]
[d/l]
Re: DBI Bind Columns
by Anonymous Monk on Feb 23, 2016 at 02:58 UTC
    Howdy all, one thing that I would like to do with bind_columns() is to have it dynamically adjust the variable list. I'd like to use the same prepare/execute/bind_columns in a loop but insert different select strings for the prepare statement. With this there may be a different number of variables returned depending on the select statement. One could just make an if statement to detect that but it would be far better if the one could just say that the var names are of the form $Col# and let it fill in the number value up to the number of values returned. Thus dynamically preparing the proper number of vars. If throws an error if you alocate too many ahead of time rather than just NULL filling them or leaving them undefined. Is this even possible? Alan D.
[reply]

      What you need to do is generate your SQL to match your variable list. One method for doing that is to use DBIx::Class. While I generally generate my own SQL, I have had a fair bit of experience with DBIC lately.

[reply]

Back to Seekers of Perl Wisdom