Extracting data using WIN32::ODBC
Hello Monks,
I'm trying to automate a process but I'm geting stuck because I don't have enough experience with DBI. The code below reads a directory into an array then it passes the file names (numbers) to the other subroutine which opens a session with SQL Server 2000 to extract data. the issue is that I keep getting "Can't call method "SQL" on an undefined value at line 46". I would like the output of the select statement to be dump into a file which is created before it fetches data. Could someone that have experience connecting to databases with perl help me? or point me in the right direction to fix this program. I have not seen many samples of reference points in DBI. I appreciate your help.
#! perl -w
use strict;
use Win32::ODBC;
my $dir = 'K:\\reports';
my $count = 0;
opendir DH, $dir or die "Cannot open $dir: $!";
while (my $file = readdir DH) {
next unless $file =~ /\.pdf$/;
my %newfile = substr($file,2,6);
process_records($newfile);
}
closedir DH;
sub process_records {
my ($newfile) = @_;
my $dbname = 'DB_Name';
my $username = 'Username';
my $password = 'Password';
my ($db);
if (!ref($db = new Win32::ODBC("DSN=$dbname;UID=$username;PWD=$password"))) {
my ($ErrNum, $ErrText, $ErrConn) = Win32::ODBC::Error();
#...IT FAILED - DO SOMETHING...
print "couldn't connect";
}
my $sql = ("SELECT field1,field2,field3,field4,field5,field6,lastname,firstname
FROM db_name.table
WHERE field1 = '029622'");
if($db->Sql($sql)){
#...FAILED...
my ($ErrNum, $ErrText, $ErrConn) = $db->Error();
}
my ( $yr, $mo, $dy ) = (localtime)[5,4,3];
my $outfile = sprintf( "C:\\Directory\\%04d%02d%02d.txt",$yr+1900,$mo+1,$dy );
open OUT,">$outfile" or die "Couldn't open $outfile, $!";
while($db->FetchRow()){
my %data = $db->DataHash();
#...Processing Data...
print OUT "%data\n";
}
$db->Close();
# }
Re: Extracting data using WIN32::ODBC
created: 2004-06-14 18:02:44
Re: Extracting data using WIN32::ODBC
created: 2004-06-14 18:19:18
You appear to be under the mistaken impression that you are using DBI in your code. You are not. A DBI script would start out something like this
#!perl -w
use strict;
use DBI;
my $dsn = "some system DSN or DSN-less connection";
my $dbh = DBI->connect(
"dbi:ODBC:$dsn",'me','mypass',{RaiseError=>1}
);
# ...
Unless you have a very specific reason to use Win32::ODBC, use DBI instead - there is more documentation and a larger community to provide support.
Re: Extracting data using WIN32::ODBC
created: 2004-06-14 18:20:48
I'm geting stuck because I don't have enough experience with DBI. ... I have not seen many samples of reference points in DBI
To fix that lack of knowledge, have a look at the Tutorials, see the Working with Files and Databases section especially:
I have heard many monks sing in praise of the node 284436.
Cheers, Sören
Re: Extracting data using WIN32::ODBC
created: 2004-06-14 23:53:06
I must speak in defense of ODBC, even though I haven't gotten it to work yet. With the help I've received in this thread from PostgreSQL's email list, I hope to install the necessary driver and get things working soon. I've also received help from one of Cygwin's newsgroups, and I'm sure there is a support community (in addition to Perl Monks) that could help you with SQL Server problems. The standard instructions sucked, but I have no problem with the support communities.
I rejected DBD::Pg and DBD::PgPP, which I think means that I rejected DBI (which is described an alternative to ODBC though DBD::ODBC requires the DBI module...I'm just accepting that stuff for now). DBD::Pg was rejected because I heard of a monk who couldn't get the current version working on Windows, and I heard elsewhere that an old version of MSVC is required, and I don't want to be limited to an old version of anything. DBD::PgPP was suggested, but I don't want to use a non-core .05 version of a module, especially when it has so many non-core dependencies. That's when ODBC was suggested.
I'm going to (attempt to) use DBD::ODBC instead of Win32::ODBC because I want more portability.
Semi-off-topic observation: When software doesn't have a simple installer, it's because it's not known how to install it, and expect the documentation to suck more than usual.
Fully off-topic observation: Much of the Perl documentation sucks (details available upon request). I heard that there is excellent Python Documentation. Nuff said.
Re: Extracting data using WIN32::ODBC
created: 2004-06-15 08:42:12
It is difficult to say. I could not duplicate your problem. Unfortunately, I had to change your code a fair amount to get it to run so it is possible that I modded out your problem. I'll make some general comments on the off chance that they help.
The error you describe refers, at least the times I have received it, to the fact that the variable $db has been undefined by the time you reach the statement if($db->Sql($sql)){ ... } Since the code seems OK, my first guess would be a scoping problem. That is, $db is going out of scope before your attempt to call Sql(). I note that your last right curly brace is commented out in your code. I needed to uncomment it to get the code to run at all.
You only reference $db twice before the Sql() call so I would next check there next.
A second guess would be the declaration or assignment to $db. You declare $db using a list context but don't actually assign to it so it remains undefined until you get to the new call. After that, it should contain a reference to the ODBC structure. I generally declare $db as my $db = ''; although I don't see how that matters here.
Ok, in looking back, I don't think I've offered much. With regard to ODBC, I've used it on Win32 successfully for several years. It does have some limitations though. I've never been able to get cursors to work, for example. Nor have I been able to fetch more than a single row at a time. I tried DBD::ODBC a few years ago without much success although I have heard of others who have not had a problem. In a recent node, Extracting data from an Ms ACCESS query, McMahon posted a message from David Roth that the Win32::ODBC module is no longer actively supported which is dissapointing. I checked CPAN and it does appear that DBD::ODBC is under active development. I will probably be experimenting with it a bit more now myself.
Good luck, I hope someone else can help :o)
PJ
We are drowning in information and starving for knowledge - Rutherford D. Rogers
The error you describe refers, at least the times I have received it, to the fact that the variable $db has been undefined by the time you reach the statement if($db->Sql($sql)){ ... } Since the code seems OK, my first guess would be a scoping problem. That is, $db is going out of scope before your attempt to call Sql(). I note that your last right curly brace is commented out in your code. I needed to uncomment it to get the code to run at all.
You only reference $db twice before the Sql() call so I would next check there next.
A second guess would be the declaration or assignment to $db. You declare $db using a list context but don't actually assign to it so it remains undefined until you get to the new call. After that, it should contain a reference to the ODBC structure. I generally declare $db as my $db = ''; although I don't see how that matters here.
Ok, in looking back, I don't think I've offered much. With regard to ODBC, I've used it on Win32 successfully for several years. It does have some limitations though. I've never been able to get cursors to work, for example. Nor have I been able to fetch more than a single row at a time. I tried DBD::ODBC a few years ago without much success although I have heard of others who have not had a problem. In a recent node, Extracting data from an Ms ACCESS query, McMahon posted a message from David Roth that the Win32::ODBC module is no longer actively supported which is dissapointing. I checked CPAN and it does appear that DBD::ODBC is under active development. I will probably be experimenting with it a bit more now myself.
Good luck, I hope someone else can help :o)
PJ