Re: Evaluated "require $database" not found.
From: Hardy Merrill (HMerrill_at_dhcr.state.ny.us)
Date: 12/28/04
- Previous message: Ron Wingfield: "Re: Evaluated "require $database" not found."
- Maybe in reply to: Ron Wingfield: "Evaluated "require $database" not found."
- Next in thread: Ron Wingfield: "Re: selectrow_array functions seems to die???"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Tue, 28 Dec 2004 07:59:37 -0500 To: <rtwingfield@archaxis.net>,<dbi-users@perl.org>
Ron, my comments below as HM>> Sorry for the ridiculous (lack of)
quoting - old version of Groupwise at work :-(
Hardy Merrill
>>> "Ron Wingfield" <rtwingfield@archaxis.net> 12/27/04 07:41PM >>>
Hello everyone,
I do appreciate all of your suggestions. Actually, now I do have a
good handle on the problems. I was in a "couldn't see the forest for
the trees" scenario. I've gone through my three proof-of-concept Perl
programs and they are working very well. There is still a lot of
functionality to add to the programs (such as the important function of
returning the MySQL query results back to the "middle" program, i.e.,
the appication user interface program) , but basically I have the
following in place:
DB_Select.pl calls Contacts_01.pl calls DBI_API_01.pl
The DBI_API_01.pl program is totally generic and handles all database
maintenance I/O (Inquire, Update, Insert, and Delete) for any
database/table. As it turns out, the parameter passing problem was a
non-issue once I realized that I was using a technique of Mr. Paul
DuBois that incorporated a custom Perl library module to handle
connections to (some of) his Cookbook Recipe examples.
Also very frustrating is this little nuance:
$dsn="DBI:mysql:host=$host_name;database=$db_name"; # CORRECT
SYNTAX!
$dbh=DBI->connect($dsn, $userid, $passwd,{PrintError=>0,
RaiseError=>0});
DBI_error("Could not connect to $dsn."); # Test/exit on error.
HM>> I've never done error handling like this before. Looks like you
are *always* calling the DBI_error subroutine - I'm curious to know what
DBI_error contains for error handling code. As you may know, typical
error handling involves an "or" (or "||"), something like this:
$dbh=DBI->connect($dsn,
$userid,
$passwd,
{PrintError=>0,
RaiseError=>0}
) || die "Could not connect to $dsn: $DBI::errstr";
??? I'm familiar with error trapping as described in "perldoc DBI" -
here's a snippet from the paragraph titled "Transactions":
=========================================================
Transactions
Transactions are a fundamental part of any robust database system.
They
protect against errors and database corruption by ensuring that
sets of
related changes to the database take place in atomic (indivisible,
all-or-nothing) units.
This section applies to databases that support transactions and
where
"AutoCommit" is off. See "AutoCommit" for details of using
"AutoCommit"
with various types of databases.
The recommended way to implement robust transactions in Perl
applications is to use "RaiseError" and "eval { ... }" (which is
very
fast, unlike "eval "...""). For example:
$dbh->{AutoCommit} = 0; # enable transactions, if possible
$dbh->{RaiseError} = 1;
eval {
foo(...) # do lots of work here
bar(...) # including inserts
baz(...) # and updates
$dbh->commit; # commit the changes if we get this far
};
if ($@) {
warn "Transaction aborted because $@";
# now rollback to undo the incomplete changes
# but do it in an eval{} as it may also fail
eval { $dbh->rollback };
# add other application on-error-clean-up code here
}
=========================================================
A lot of good info there, but the main point about error trapping is
this:
$dbh->{RaiseError} = 1;
eval {
### dbi statements here ###
};
if ($@) {
### if any dbi statements in an eval fail with a "die"
when
### RaiseError is on(1), then the die message gets put
### into $@, and control passes to the next statement
### after the eval.
}
Not that any of this will affect your errant connect statement, but I
thought I would include it here just in case you weren't aware of it
;-)
Hardy Merrill
. . .if the construct of the $dsn variable includes a space as in
$dsn="DBI:mysql:host=$host_name; database=$db_name"; # DO NOT USE
SPACE!
then the connect will fail, but the DBI_error( ) handler will not trap
the error and report the failed attempt to connect. The program will
continue processing and the DBI_error( ) handler will report errors if
subsequent SELECTS, are attempted on a valid table in the database, for
example:
DBI Error 1046: No database selected
Query execution to select from states failed.
Process terminated.
Notice that this was a failure of an operation to load a pop-up
selection list of states from the "states" table.
What I have found is that if the space(s) are removed as per the
correct syntax, then the DBI_error( ) handler will trap and report a
connection failure if a non-existent database is specified, as per this
example:
DBI Error 1044: Access denied for user 'cbuser'@'localhost' to
database 'xookbook'
Could not connect to DBI:mysql:host=localhost;database=xookbook.
Process terminated.
Notice that I intentionally mispelled the name of the "cookbook"
database as "xookbook" to force the error. Finally, the DBI_error( )
handler subroutine function accepts a custom string text for display in
addition to the actual DBI error number and string issued by MySQL.
Thanks again to everyone 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
--------------------------------------------------------------------------------
----- Original Message -----
From: Moosmann, James
To: 'Ron Wingfield'
Sent: Monday, December 27, 2004 2:09 PM
Subject: RE: Evaluated "require $database" not found.
What you missed is fundamental to understanding Perl vs
understanding
anything about the DBI module, and I would really recommend you
picking up a
good beginners book on Perl.
The values for $db_name, $host_name, $user_name, and $password all
have to
be changed to your specific database.
This requires that you first have an installation of mysql setup on a
host
someplace, you will also likely need the port( 3306 default but not
necessarily the one your ISP is using, if he provides mysql as part
of your
webhosting package. )
You would have to have a table(schema) set up that had a database
called
'cookbook' also. Which would have to be manually created by you.
That is NOT a working example as it is all by itself. It is simply a
shell
of how you do a simple DBI connection. There is much more to learn.
DBI
has interfaces to many different types of DB's, and you need a
specific
module for the specific RDBMS you want to communicate with.
-----Original Message-----
From: Ron Wingfield [mailto:rtwingfield@archaxis.net]
Sent: Monday, December 27, 2004 2:02 PM
To: Jeff Zucker
Cc: dbi-users@perl.org
Subject: Re: Evaluated "require $database" not found.
Yes, it does help. I really appreciate the time you've devoted to
this
discussion! I agree, from one environment to another, words mean
different
things. On top of everything else, I tend to want to understand
MySQL in
terms of DB2 (or DB/400) which is actually the original un-named
imbedded,
native databse of IBM's S/38 (OS/38) which was renamed AS/400
(OS/400) (. .
.interesting to note that IBM does not manufacture anything known as
an
AS/400, but still sell OS/400.)
Regardless, I have discovered a package object called "Cookbook.pm"
that I
was not aware of. If (some of) you recall, I've mentioned that I'm a
noobee
at MySQL and I'm using Paul DuBois' "MySQL Cookbook, O'Reilly &
Associates,
2003. The book is good and Mr. DuBois explains most topics very well
(I
recommend the book.). Unfortunately, and probably because I think
I'm smart
enough to skip around in the book, I have missed the point in the
created
package, Cookbook.pm, that is a library file for connecting to
MySQL.
Accompanying the book is an online repository of Recipes, i.e., code
that
accompanies the examples that Mr. DuBois uses in his book
(http://www.kitebird.com/mysql-cookbook/). Regardless, I have been
using
these techniques and the Cookbook.pm is a Perl "script" (hope I'm
using the
term script correctly) that contains the following code:
package Cookbook;
# Cookbook.pm - library file with utility routine for connecting to
MySQL
use strict;
use DBI;
# Establish a connection to the cookbook database, returning a
database
# handle. Dies with a message if the connection cannot be
established.
sub connect
{
my $db_name = "cookbook";
my $host_name = "localhost";
my $user_name = "cbuser";
my $password = "cbpass";
my $dsn = "DBI:mysql:host=$host_name;database=$db_name";
return (DBI->connect ($dsn, $user_name, $password,
{ PrintError => 0, RaiseError =>
1}));
}
1; # return tru
In my noobee'ness, I have not appreciated the significance of the
construct:
use Cookbook;
my $dbh = Cookbook::connect( );
You see, I have been looking at the "connect" instruction as if
something
magical was happening here (within the mystry code of Perl), when
indeed,
the results of the execution of the subroutine, ./Cookbook/connect .
(Stupid, Stupid, Stupid ME!) This explains why the simple construct,
"my
$dbh = Cookbook::connect( );" only works if the spelling is Cookbook,
rather
than the actual name of the database, "cookbook". This is what I
have been
incorrectly calling a "wholesale" connection without regard for
user-id and
password, etc. Also, this explains (to me) why my attempts to
externalize
the call by passing parameters are always "over-ridden". Notice all
the
hard-code in the Cookbook.pm file.
I think I have crossed the Rubicon. When I get it put together,
I'll let
everyone know.
Film at eleven,
Ron W.
----------------------------------------------------------------------------
----
----- Original Message -----
From: Jeff Zucker
To: Ron Wingfield
Cc: dbi-users@perl.org
Sent: Monday, December 27, 2004 11:55 AM
Subject: Re: Evaluated "require $database" not found.
Ron Wingfield wrote:
> A practical example of a business application might be
illustrated by a
> manufacturing operation spread over multiple cities. In such a
> scenario, a common server (i.e., the actual physical box and
all),
> located in Little Rock also serves the Ft. Smith and Memphis
locations,
> too; therefore, we have separate DATABASES for each of the three
> locations. ... That is to say, " use whatever DATABASE that is
designated
> by the variable, "$database".
Ok, I think I understand now. There are only two problems with
your
"use database" - #1, the definition of "use" and #2, the definition
of
"database" :-).
"Use" is a both a perl command and in some implementations of SQL,
a SQL
command. It means two completely different things in the two
languages.
In perl, it means to load and import a piece of software into the
operating environment of the script. In some dialects of SQL (but
not
all, it isn't part of the SQL standard), it means to qualify
requests
for tables and other information as belonging to a certain grouping
of
tables which are related to each other. The two "use"s are
utilized
differently in perl scripts. Perl "use" stands in the script by
itself,
while SQL "use" must be part of DBI method call such as do(). For
example:
#!perl -w
use strict; # perl USE
use DBI; # perl USE
my $dbh = DBI->connect(...);
$dbh->do("USE foo"); # SQL USE
The word "database" has even more meanings. There is no definition
for
it in the SQL standard, because it is not used in the SQL standard.
The
SQL standard specifies that tables can be grouped with related
tables
into a grouping called a schema which can be grouped into larger
groupings called catalogs (which can belong to clusters, but lets
forget
about them for now). Thus a fully qualified table name in the
standard
is in this form:
ANSI : catalog.schema.table
Different implementations of SQL substitute the word "database"
into
this setup, but in completely different ways. Thus in postgreSQL,
the
above would be written as
PostgreSQL : database.schema.table
I'm not too familiar with MySQL, but I believe it has only two of
those
three levels and writes them as
MySQL : database.table
In other words, what MySQL calls a database, the ANSI standard and
postgreSQL call a schema. And what postgreSQL calls a database,
the
ANSI standard calls a catalog.
Add to this the common practice of refering to the database
management
system (e.g. MySQL or PostgreSQL) as the "database" - informally
people
say "What database do you use? I use MySQL" - and the entire thing
becomes even more messy.
Usually the "database" (in the MySQL sense of a grouping of tables)
is
specified in the DBI connection string rather than with USE
statements
so you really don't need the SQL USE statement unless your script
is
going to be utilizing different databases in a single run.
Anyway, I think what you want is a dynamic connection (which can
utilize
different schemas ("databases") in different runs of the script)
along
the lines of:
#!perl -wT
use strict;
use DBI;
use CGI;
my $cgi = new CGI();
my $database = $cgi->param('database');
my $user = $cgi->param('user');
my $auth = $cgi->param('auth');
my $host = $cgi->param('host');
my $port = $cgi->param('port');
#
# code to validate the user-supplied data
#
my $dsn = "dbi:mysql:database=$database;host=$host;port=$port";
my $dbh = DBI->connect($dsn,$user,$auth) or die $DBI->errstr;
#
# You can now access tables in the $database "database"
Hope this helps.
--
Jeff
- Previous message: Ron Wingfield: "Re: Evaluated "require $database" not found."
- Maybe in reply to: Ron Wingfield: "Evaluated "require $database" not found."
- Next in thread: Ron Wingfield: "Re: selectrow_array functions seems to die???"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
