in reply to DBI Select results confussion

however I can only get at the results if I call "fetchrow_array" on the results?

There are several ways (besides fetchrow_array()) to retrieve results from a query. Which others have you tried using, and what happened?

A suggestion: For a number of reasons that you'll find covered if you search for "bind", it's a good idea to use parameter binding in queries. This looks like: 

  my $sql = <<SQL;
    SELECT count(*)
    FROM files
    WHERE geo=? AND lob="is" AND country=?
  SQL
  my $sth = $dbh->prepare($sql);
  $sth->execute($geo, $country);
  my($count) = $sth->fetchrow_array();
  $sth->finish();

[download]
Since your count(*) query returns a single row of data, you don't need to call it in a loop. It's good form, however, to do   $sth->finish(); when you're done.

(Edited to correct a typo in the code.)

(Eeek. Edited again to correct a worse typo.)

Replies are listed 'Best First'.
Re: Re: DBI Select results confussion
by heezy (Monk) on Nov 10, 2002 at 21:02 UTC

    I thought I knew of two separate ways for retriving data from a query..

    1.Use bind, for example: 

    $query_is->bind_columns(\$count_is);

    [download]

    2.Use fetchrow_array(), for example: 

    @row = $query_is->fetchrow_array

    [download]

    .. could you (or someone else) show me how to use "bind" regarding the example in my first posting.

    When I do bind (same code as in my original posting) but without the "fetchrow_array" line it does not output any data? i.e it only outputs data if I do the fetchrow_array command.

    Thanks

    M

[reply]
[d/l]
[select]
      When I do bind (same code as in my original posting) but without the "fetchrow_array" line it does not output any data? i.e it only outputs data if I do the fetchrow_array command.

      O.K., I think I see where the misunderstanding is. You're trying to arrange to have the result of the query show up in a variable. There are two ways to do that: you can do the appropriate binding to cause the result of a fetch to get stuffed into a variable, or you can fetch a row (either as an array or as a hash) and extract the value manually. You're trying to do the former; my snippet does the latter.

      In both cases, unless a row is fetched, there's no value there to get. Hence you need to force a fetch.

      I avoid using bind_column() et al. because I find the resulting code is harder to follow. Readers can easily follow code that manually fetches a row and extracts values, but using column binding forces readers to remember that there's a magic wormhole in that region of space that causes values to automagically appear elsewhere.

[reply]
[d/l]

        Thanks for bearing with me whilst I explained my posting! And thanks for replying and making it clear!

        M

[reply]

In Section Seekers of Perl Wisdom