Re: Evaluated "require $database" not found.

• Previous message: Ron Wingfield: "Re: Evaluated "require $database" not found."
• Maybe in reply to: Ron Wingfield: "Evaluated "require $database" not found."
• Next in thread: Hardy Merrill: "Re: Evaluated "require $database" not found."
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

To: <dbi-users@perl.org>
Date: Mon, 27 Dec 2004 18:41:16 -0600

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.

. . .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: Hardy Merrill: "Re: Evaluated "require $database" not found."
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]