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 :)

In reply to DBI madness by rsilvergun

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.