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

Hello Wise ones, this one's pretty esoteric, so I'm not sure if there's any hope...

I'm trying to open up a vanilla Access DB and get at the goodies inside. I run this command:

$dbh = DBI->connect("dbi:ODBC:pecmsdb") or die 'Cannot Connect to database';
and so long as I run my script directly from within Windows, all is well, the DB is opened and I can run sql statements off it. I loaded the DB manually in Control Panel ( Windows XP ) using the regular Microsoft Access (*.mdb) driver.

Here's where things get crazy. What I'm trying to do us us a program called redmon (it's a printer port redirector, it lets you redirect the output of a printer driver to a program) to capture a report as it prints, clean up the report (using values I pull from the database) and output the report. Everything works fine, except the database refuses to open when redmon calls my script.

I know redmon does weird stuff to standard input, and as such pipes and redirectors get kind of wonky when I run it, but I wasn't expecting it to keep me from opening the database. I also know that for several parts of my script, I've had to specify full paths to get things to work when redmon call my script, when if I was just calling the script from the command line full paths weren't necessary.

I can't think of what would keep me from opening the database. Is there more I could be specifying in my call to open to make things more explicit? Right now I can't even imagine how it would be possible that I can open the DB with the same script fine from a command line and it all falls apart when I try and have another program call it. Maybe redmon has it's on set of environment variables, and the DBI interface needs the default environment variables...

Failing all that, is there some way for me to open the database more directly? i.e. using a path (c:\mydata\pecmsdb.mdb)?

Oh, here's the full Error messege:

DBI connect('pecmsdb','',...) failed: MicrosoftODBC Driver Manager Data source name not found and no default driver specified (SQL-IM002)(DBD: db_login/SQLConnect err=-1) at c:\FixReportBase\querry.pl line 7 REDMON EndDocPort: process finished after 2 seconds

Near as I can tell, it's the generic cannot connect error...

Oh, if I use Win32::ODBC, it seems to work. At least I can open the database :). Here's the code I used: 
#! c:\perl\bin\perl

use Win32::ODBC;

$DriverType = "Microsoft Access Driver (*.mdb)";
$DSN = "Win32 ODBC --MAOmaoMAOmaoMAO--";
$Dir = "c:\\Windows\\system32";
$DBase = "cmsdb.mdb";


Win32::ODBC::ConfigDSN(ODBC_ADD_DSN, $DriverType,("DSN=$DSN", "Descrip
+tion=MAO Win32 ODBC Test DSN for Perl", "DBQ=$Dir\\$DBase", "DEFAULTD
+IR=$Dir", "UID=", "PWD=")) or die "ConfigDSN(): Could not add tempora
+ry DSN" . Win32::ODBC::Error();

$db=new Win32::ODBC($DSN) or die "couldn't ODBC $DSN because ", Win32:
+:ODBC::Error(), "\n";

if ($InvoiceNumber eq ""){ $InvoiceNumber = "I111999-0024-A"; }

# Use invoice # to index into the Quotes table and get the company ID.
+ I092000-1770-A (I111999-0024-A has a PO)

$sqlstatement = "SELECT companyID, quantity1,workordernumber,ponumber,
+jobname,jobdescription from Quotes where invoicenumber LIKE '$Invoice
+Number'";


!$db->Sql($sqlstatement) or die "couldn't do $query because ", $db->Er
+ror(), "\n";

while($db->FetchRow())
{
 my %Data = $db->DataHash();
 foreach my $key(keys(%Data)){
   print $key," -> ",$Data{$key};
 }  
}


Win32::ODBC::ConfigDSN(ODBC_REMOVE_DSN, $DriverType, "DSN=$DSN") or di
+e "ConfigDSN(): Could not remove temporary DSN because ", Win32::ODBC
+::Error();

[download]
Anyway, forgive me for rambling, it's late and I've been banging my head against this for hours. I'll keep at it though, wish me luck :)

Replies are listed 'Best First'.
Re: DBI madness
by graff (Chancellor) on Jun 17, 2007 at 04:18 UTC
    Can this "redmon" program be run in a command line shell, and does it allow you to redirect printer driver output to its own stdout? (Alternatively, can it simply save the printer driver output to a file?)

    The point would be to avoid using redmon's own internal method for invoking your perl program. Have it write or save its output in some "normal" way so that your perl program (running on its own in a command line shell) can read it.

    Update: To check whether its an environment issue, you could add this to your perl script, before the DBI connect call: 

    open( E, ">", "check-env-$$.txt" ); # adjust path to suit your taste
print E "$_ => $ENV{$_}\n" for ( sort keys %ENV );
close E;

    [download]
    Then compare the two versions of the check-env file (the one when run from the command line, the other when run from redmon).

    Another update: If redmon can be run as a command-line tool, and can write its output to stdout, you could turn the tables: have your perl script run redmon, rather than the other way around: 

    open( RED, "-|", "redmon arg1 ..." ) or die "redmon failed: $!";
while (<RED>) {
    # do stuff...
    print;
}

    [download]
[reply]
[d/l]
[select]
      figures as soon as I give up hope, I find a way to make it work. This line will open the Database with a path instead of from the DSN ->

      $dbh = DBI->connect('dbi:ODBC:driver=microsoft access driver (*.mdb);dbq=C:\Windows\system32\cmsdb.mdb');

      Thanks for the ENV trick, I didn't realize you could get to the environment variables that easily :D. Man, I love perl.
[reply]
[d/l]

Back to Seekers of Perl Wisdom