Re: Mysql::Simple?

• Previous message: Ron Savage: "ANNOUNCE: CGI::TabPane V 1.02"
• In reply to: kz: "Re: Mysql::Simple?"
• Next in thread: Brian McCauley: "Re: Mysql::Simple?"
• Reply: Brian McCauley: "Re: Mysql::Simple?"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Date: Thu, 29 Jan 2004 21:41:35 +0000
To: kz <notpublic@restricted.com>

On Thu, 29 Jan 2004, kz wrote:

> "Dan Bolser" <dmb@mail.mrc-dunn.cam.ac.uk> wrote in message
> news:Pine.LNX.4.21.0401291309300.24595-100000@mail.mrc-dunn.cam.ac.uk...
> >
> > Hello, I have a pet module called Mysql::Simple, would anyone be
> > interested in making it a CPAN module? It doesn't do much (mostly
> > concerned with connection), and I am lazy,but I guess CPAN is the best
> > home for my pet?
>
> What extra features would your module offer what you can't (simply) do with
> DBI? I wouldn't reinvent the wheel...
>
> $dbh = DBI->connect("DBI:mysql:database=$DBname;host=$DBserver",
> "$DBuser", "$DBpassword",{'RaiseError' => 1});
> $sth = $dbh->prepare($cmd);
> $sth->execute();
> while ($ref = $sth->fetchrow_hashref()) {
> # do whatever with $ref->{col1},$ref->{col2} etc
> }
> $dbh->do($cmd2,..);
> $sth->finish();
> $dbh->disconnect();
>
> In 95% of the cases you would use these statements anyway, but I might be
> missing something...

OK, I know this of course.

My (simple) way you say...

use Mysql::Simple 'doQuery';

doQuery("anything you like");

__END__

The module reads config files from standard places, and also does a
GetOptions passthrough to interpret standard mysql options, finally it
does Term::ReadPassword if it can't find a password. I still need to make
it work with standard DBI environment variables, but it is no bigie.

doQuery returns a results handle from select statements, which is why I
want my results in the form of an array of psudohashes, this way the user
can say...

use Mysql::Simple 'doQuery';

$x = doQuery("select * from bla");

for(@$x){
  print $_->[1]. "\n";
  print $_->{'bla_column'}. "\n";
}

__END__

Quite simple you agree?

However, I don't want to start using psudohashes if

A) DBI already does the above
B) They are about to be removed
C) There is a huge overhead - simple things can be somewhat slower, but
big penalties are prohibitive.

Thanks for the details below.

I welcome all feedback - I have found this module to be useful in our
unstable research environment, where mysql servers hop from machine to
machine. This way I find it easy to set up and wangle connection details.

Cheers,
Dan.

>
> > I would like to be able to access my results by numeric column index
> > (starting at 1) or hash key (column name). So I think pseudohash, but this
> > will be changed in new perl version, and I am also worried about the
> > efficiency of creating an array of psudo hashes (one per row returned).
>
> You can have both.
> perldoc dbi says, among others:
>
> "fetchrow_arrayref"
> $ary_ref = $sth->fetchrow_arrayref;
> Fetches the next row of data and returns a reference to an array holding the
> field values....This is the fastest way to fetch data, particularly if used
> with "$sth->bind_columns".
>
> "fetchrow_array"
> @ary = $sth->fetchrow_array;
> Fetches the next row of data and returns it as a list containing the field
> values.
>
> -- This is your "numeric column index" criteria. --
>
> "fetchrow_hashref"
> $hash_ref = $sth->fetchrow_hashref;
> Fetches the next row of data and returns it as a reference to a hash
> containing field name and field value pairs....Because of the extra work
> "fetchrow_hashref" and Perl have to perform, it is not as efficient as
> "fetchrow_arrayref" or "fetchrow_array".
>
> -- This is your "column name" criteria. --
>
> I personally prefer this one because I don't have to remember that the
> column "invoice_due_date" of my "invoices" MySQL database is the 4th
> column...
> It's also easier to say "two teaspoons of sugar" than "two measures of the
> substance from the leftmost big blue round can ($can[0]) taken with the
> small instrument located rightmost in the kitchen drawer
> ($instrument[$#instrument])".
>
> Other ideas?
>
> Regards,
>
> Zoltan
>
>
>

• Previous message: Ron Savage: "ANNOUNCE: CGI::TabPane V 1.02"
• In reply to: kz: "Re: Mysql::Simple?"
• Next in thread: Brian McCauley: "Re: Mysql::Simple?"
• Reply: Brian McCauley: "Re: Mysql::Simple?"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]