Purdy has asked for the wisdom of the Perl Monks concerning the following question:

I created a central script (jdb.cgi for lack of a snazzy name ;)) that simply uses the caller() function and based on the filepath (of the script that requires this), sets up the DSN, connects and declares a global variable ($dbh) that the script requiring this file will use for db stuff.

So then I create a script like so: 

$database = "foo";
require '/home/share/jdb.cgi';
# now I can do whatever with $dbh..

[download]

This may not seem like the best way to do it (and may not be ;)), but I have two web servers & two different directories and I don't want to work on CGI scripts in one, test and get it ready and then move it over to production and have to remember to switch something (such as if I had a subroutine to change a parameter).

I hope this makes sense so far. Ok, so the problem is whenever my script on the development server updates the database, it's updating the production database server (not the DSN it was given, which would be port 3308, a development db server that has a different datadir, etc). I debugged & found that the DSN for the development server was being provided correctly for a script that was within the "site-dev" path ... boggles me as to what could be wrong.

Thanks in advance for any help! :)

Jason

Here's the script: 

use DBI;
 
my ($package, $filename) = caller;
#my ($dsn);
 
# if it's the dev site, return the Dev DSN, otherwise, return
# the production DSN.
if ($filename =~ /sites-dev/) {
        # it's the dev site
        $dsn = "DBI:mysql:database=$database;port=3308";
} else {
        # it's the production site
        $dsn = "DBI:mysql:database=$database;port=3306";
}
 
$dbh = DBI->connect($dsn, "perlscript", "passboy");
 
1;

[download]

Replies are listed 'Best First'.
Re: Centralized DSN Switch Script (MySQL)
by chromatic (Archbishop) on Nov 02, 2001 at 02:56 UTC
    Print out the filename for debugging purposes. If your webserver is chroot()ed, maybe the filename isn't what you think it is.

    I was originally going to suggest making the file into a module proper and writing your own import subroutine. Doing that would allow you to say: 

    use JDB::DSN 'dev';
my $dbh = connect();

    [download]
    On second thought, that doesn't seem too helpful. If you want to be able to move a development script to a production server without having to edit anything, it won't work. It's a little more robust, though, for values of robust that include packages and avoiding global variables.
[reply]
[d/l]
Re: Centralized DSN Switch Script (MySQL)
by Purdy (Hermit) on Nov 02, 2001 at 03:33 UTC
    I put together this test script and also put in a print in the central script to print the FILENAME. Here's the test script, located in "/home/sites-dev/home/dbtest.pl": 
    #!/usr/bin/perl
 
use DBI;
 
$database = "qsrsubs";
require '/home/sites/jdb.cgi';
 
print "DBH = $dbh\n";
print "DSN = $dsn\n";
 
$cmd = "show variables";
$sth = $dbh->prepare($cmd);
$sth->execute();
 
while (@ary = $sth->fetchrow_array) {
        print "PORT = $ary[1]\n" if grep(/port/, @ary);
}

    [download]
    And here's the output: 

    {jason@www home}$ /home/sites-dev/home/dbtest.pl
FILENAME = /home/sites-dev/home/dbtest.pl
DBH = DBI::db=HASH(0x816603c)
DSN = DBI:mysql:database=qsrsubs;port=3308
PORT = 3306

    arg - this baffles me!

    Jason

[reply]
[d/l]
      Baffling indeed. But searching Google Groups revealed the following: http://groups.google.com/groups?hl=en&selm=87s85a%2419c5%241%40FreeBSD.csie.NCTU.edu.tw

      So maybe $dsn = "DBI:mysql:database=$database;host=127.0.0.1;port=3308"; will bring you luck...

[reply]
[d/l]
[select]
        Oops, I forgot to log in to post that previous reply -- If the proposed solution works, I'll gladly take karma points on this post ;-)
[reply]
Re: Centralized DSN Switch Script (MySQL)
by lucs (Sexton) on Nov 02, 2001 at 02:16 UTC
    'site-dev' or 'sites-dev'?
[reply]
      sites-dev - the full path would be something like "/home/sites-dev/site3/web/cgi-bin/script.pl" - that would be a script that runs on the development server.

      A production server script would be in the path "/home/sites/site3/web/cgi-bin/script.pl".

      It looks like the DSN is coming through fine (I commented out that 'my ($dsn)' line and in the script that required this central script, printed out $dsn.

      I'm sure there are other configuration questions, too, but don't know where to start.

      Thanks!

[reply]
        Okay. Thought it might have been a typo :-) So, your tests show that in the development environment, $dsn expands to DBI:mysql:database=foo;port=3308 (which is what you seem to want), yet the wrong database is being updated. If that is correct, I don't know what's wrong; the perl up to here is fine. Sorry.
[reply]
[d/l]
[select]
Re: Centralized DSN Switch Script (MySQL)
by Purdy (Hermit) on Nov 02, 2001 at 18:56 UTC
    Oh that worked!!!! YEEE-HAW!! God bless, lucs! And I finally got voting rights, too! Just in time! :)

    Now I have to go get the right username/passwd to work, but I think I can handle that one. ;)

    Jason

[reply]

Back to Seekers of Perl Wisdom