Re: Problem with "while/fetchrow_array"

From: Ron Wingfield (rtwingfield_at_archaxis.net)
Date: 12/29/04 

To: "Hardy Merrill" <HMerrill@dhcr.state.ny.us>, <dbi-users@perl.org>
Date: Wed, 29 Dec 2004 13:20:09 -0600

Thanks Hardy, I suspect that you are correct, sir!

I'm going to go pickup a copy of "Programming the Perl DBI" book, today. I believe that's an O'Reilly publication, isn't it? (I can "Google" it.)

By the way, I've just executed the following code. Notice that there is no "while" loop, but rather three simple calls to fetchrow_array( ).

  a.. The first returns the last database in the known world as a row.
  b.. The second call as expected, returns nothing in $db_name; subsequently, a NULL (or blank value) is pushed onto the array. It's interesting to note that no DBI error is reported, and this is understandable because hitting the end of a list is not considered a DBI error, . . .just that the list is exhausted.
  c.. The third call pushes a hard-coded value, "foobar" onto the array.

The displayed popup menu will display the list as:

  test

  foobar

Here's the test code: (BTY Hardy, this is also an example of how I use the DBI_error( ) as a test and debug tool.)
  $db_name = $sth->fetchrow_array();
  push(@db_name_values, $db_name); # Save each value in array.
  DBI_error("GOT HERE! db_name=$db_name_values[0]<br>","1"); #### TEMP!

  $db_name = $sth->fetchrow_array();
  DBI_error("Any DBI Error will be printed above.<br>","7"); #### TEMP!
  push(@db_name_values, $db_name); # Save each value in array.
  DBI_error("GOT HERE! db_name=$db_name_values[1]<br>","1"); #### TEMP!

  push(@db_name_values, "foobar"); # Save each value in array.
  DBI_error("GOT HERE! db_name=$db_name_values[2]<br>","1"); #### TEMP!

OTTF,
Ron Wingfield

FreeBSD 4.8 -- Apache http 2.0.28 -- MySQL client/server 4.1.7
Perl 5.8.5 -- p5-DBD-mysql-2.9004 driver -- p5-DBI-1.46

--------------------------------------------------------------------------------

  ----- Original Message -----
  From: Hardy Merrill
  To: rtwingfield@archaxis.net ; dbi-users@perl.org
  Sent: Wednesday, December 29, 2004 12:23 PM
  Subject: Re: Problem with "while/fetchrow_array"

  Ron, someone else will correct me if I'm wrong, but long ago this bit me
  also. I believe I remember the answer being that "SHOW" is a mysql
  client command - that is not a valid SQL command.

  I'm not sure if mysql has a mysql-only sql command you can give to get
  the names of the databases - or maybe they have some system table that
  contains the names of the databases and you can SELECT from that. Check
  the mysql docs to see if there's maybe a mysql specific SQL command you
  can give - similar to SHOW but not specific to the mysql client - to
  list databases.

  If you have the "Programming the Perl DBI" book, I believe there's a
  place in there that describes how to get the names of available drivers
  and datasources (which may contain the database names). I'd also read
  the perldocs for DBI and DBD::mysql to see what $dbh and $sth methods
  are available - maybe one of them will give you a list of database
  names.

  Sorry I can't be more help - I'm not using DBI nor MySQL regularly :-(
  Not that I don't want to...

  Hardy Merrill

>>> "Ron Wingfield" <rtwingfield@archaxis.net> 12/29/04 01:07PM >>>
  Hello everyone,

  I'm having difficulty solving this code problem. I can load a popup
  list of states from a "states" table, but I can't seem to get-it-right
  when I execute a SHOW, rather than a SELECT query. Actually, I want to
  create a popup list of all DATABASES through the CGI. From the command
  line, this works:

    mysql> SHOW DATABASES;
    +-----------+
    | Database |
    +-----------+
    | cookbook |
    | test |
    +-----------+
    2 rows in set (0.00 sec)

  As you can see, there are two databases on my server, "cookbook" and
  "test". I prepare the query request, and execute it without any error
  complaints. The while/fetchrow_array returns only one iteration or row,
  . . .apparently the last row which contains the "test" database, and
  ultimately my popup list only contains the "test" database row. I'm not
  getting any error from the fetchrow_array function; however I only see
  one diagnostic display of "GOT HERE! db_name=test". Apparently, the
  while loop has only looped one time. Regarding the following code, why
  is "cookbook" missed?

    $sth=$dbh->prepare("SHOW DATABASES;");
    $sth->execute( );
    DBI_error("Query execution to SHOW DATABASES failed.","7");
    while($db_name = $sth->fetchrow_array( ))
    {
            DBI_error("Fetchrow_array failed.","7"); # Test/no
  exit-on-error.
            push(@db_name_values, $db_name); # Save each value in
  array.
            DBI_error("GOT HERE! db_name=$db_name<br>","1"); #### TEMP!
                                             # When used with option "1",
  ----^
                                             # this always prints a
  diagnostic "trace" message.
    }
    print # Start of print block
    $cgi-> popup_menu
    (
                    -name=>'db_name',
                    -values=>\@db_name_values
    ),

  Thanks and OTTF,
  Ron Wingfield

  FreeBSD 4.8 -- Apache http 2.0.28 -- MySQL client/server 4.1.7
  Perl 5.8.5 -- p5-DBD-mysql-2.9004 driver -- p5-DBI-1.46

  --------------------------------------------------------------------------------

Relevant Pages

  • Re: How to protect Python source from modification
    ... They can then execute any arbitrary SQL command. ... >>SQL commands on their own database, who are you to tell them they ...
    (comp.lang.python)
  • Re: INSERT statements not INSERTING when using mysql from python
    ... Well, I've checked the SQL log, and my insert statements are certainly ... But then again when I execute the problematic command manually nothing ... and after that the original tables and database is used. ... It occured to me that if I could access the mysql query log that might ...
    (comp.lang.python)
  • Re: INSERT statements not INSERTING when using mysql from python
    ... One partial explanation might be that for some reason it is recreating ... a PSP script that makes many calls to populate a database. ... Then I execute many insert statements in various different loops on ... It occured to me that if I could access the mysql query log that might ...
    (comp.lang.python)
  • Newbie: Statement handle inactive after successful execute of SELECT
    ... certain operations fail. ... an iterator which walks a set of rows in a MySQL 3.23.58 DB using DBI 1.21. ... or the execute would have failed, and I've verified that @$bind is correct ... database instance in the same MySQL process. ...
    (perl.dbi.users)
  • Re: error in inserting a longblob data
    ... 'abc'xyz' - a string with an embedded '. ... > gets stored in the database but for other files it gives a MySql ... > in _execute ...
    (comp.lang.python)