SQL err in WIN32

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

Hi all, This is the error I'm getting, that I can't seem to overcome, nor determine what's causing it.
Sql() ERROR
$SqlStatement: SELECT * FROM Mailing List
$err: 1 1 "-1305"

I'm trying to pull data out of an MS Access database. There was no PERL on the machine until the other day so everything is current, as of Tuesday, and the PC is running W2K. Figured out the ODBC manager so I'm able to connect to the database (I think), but err when I do the first SELECT. And I've quintiple checked, Mailing List is the table in the Lake Alpine mdb. In trying to track down an answer, examples I've seen on the net, show a real error message rather than the -1305, So I'm wondering if I've really got everything I need installed. Aside from the win32::odbc package, I can't find an authoritative list of packages that should be installed, if you want to access, Access. I'm a victim of not knowing what I don't know as Windows is not my native environment. Any clues or hint's would be greatly appreciated, especially if you can tell me how to make the error messages appear. My humble thanks in advance.

#!/usr/bin/perl -w
use strict;
use lib(".");
use DBI;
use DB_File;
use Win32::ODBC;

my ($db,%Data,$SqlStatement,$err );

my($DSN) = "Lake Alpine";
if (!($db = new Win32::ODBC($DSN))){
    print "Error connecting to $DSN\n";
    print "Error: " . Win32::ODBC::Error() . "\n";
    exit;
}
 
($SqlStatement) = "SELECT * FROM Mailing List";
if ( $db->Sql($SqlStatement)){
    
   $err = $db->Error; 
   warn "Sql() ERROR\n"; 
   warn "\t\$SqlStatement: $SqlStatement\n"; 
   warn "\t\$err: $err\n";
   $db->Close();
   exit;
}

while( $db->FetchRow() ) {
    undef %Data;
    %Data = $db->DataHash();
    print "Read a record.\n";
}

$db->Close();
[download]

ppm> query *
Querying target 1 (ActivePerl 5.8.4.810)
1. ActivePerl-DocTools 0.04 Perl extension for Documentation TOC Gene~
2. ActiveState-Relocat~ 0.03 Relocate a Perl installation
3. ActiveState-Rx 0.60 Regular Expression Debugger
4. Archive-Tar 1.07 Manipulates TAR archives
5. Compress-Zlib 1.22 Interface to zlib compression library
6. Data-Dump 1.01 Pretty printing of data structures
7. DB_File 1.809 Perl5 access to Berkeley DB version 1.x
8. DBD-ODBC 1.07 ODBC Driver for DBI
9. DBI 1.43 Database independent interface for Perl
10. Digest-HMAC 1.01 Keyed-Hashing for Message Authentication
11. Digest-MD2 2.03 Perl interface to the MD2 Algorithm
12. Digest-MD4 1.1 Perl interface to the MD4 Algorithm
13. Digest-SHA1 2.06 Perl interface to the SHA-1 Algorithm
14. File-CounterFile 1.01 Persistent counter class
15. Font-AFM 1.18 Interface to Adobe Font Metrics files
16. HTML-Parser 3.34 HTML parser class
17. HTML-Tagset 3.03 Data tables useful in parsing HTML
18. HTML-Tree 3.18 build and scan parse-trees of HTML
19. IO-Zlib 1.01 IO:: style interface to Compress::Zlib
20. libwin32 0.21 A collection of extensions that aims to p~
21. libwww-perl 5.75 Library for WWW access in Perl
22. MD5 2.02 Perl interface to the MD5 Algorithm (obso~
23. PPM3 3.1 Perl Package Manager: locate, install, up~
24. SOAP-Lite 0.55 Library for Simple Object Access Protocol~
25. Tk 800.024 A Graphical User Interface Toolkit
26. URI 1.27 Uniform Resource Identifiers (absolute an~
27. Win32-AuthenticateU~ 0.02 Win32 User authentication for domains
28. XML-Parser 2.34 A Perl module for parsing XML documents
29. XML-Simple 2.09 Easy API to read/write XML (esp config fi~
ppm> q

Comment on SQL err in WIN32 Download Code

Replies are listed 'Best First'.
Re: SQL err in WIN32 by EdwardG (Vicar) on Jul 30, 2004 at 08:57 UTC
Have you tried enclosing the table name in square brackets, like this: `$SqlStatement = "SELECT * FROM [Mailing List]";` [download]	[reply] [d/l]
Re^2: SQL err in WIN32 by Robertn (Hermit) on Jul 31, 2004 at 02:18 UTC
Thank you, thank you, thank you. That seems to have done it. Where perchance did you come across that piece of information? I've not seen it in any of my reference books, and I've gone back to look again now that I know what to look for, and I still didn't find anything. Thanx again.	[reply]
Re: SQL err in WIN32 by WhiteBird (Hermit) on Jul 30, 2004 at 20:27 UTC
I think you have everything you need to make the connection. Your syntax looks a little unusual to me, however, and I think the calls to your error routine is what might be giving you a problem. I changed your error statements to something that I'm more familiar with and was able to get past that part of the code. Try something like this for error checking: `my($DSN) = "Lake Alpine"; if (!($db = new Win32::ODBC($DSN))){ die "Error connecting: " . Win32::ODBC::Error() . "\n";` [download] `($SqlStatement) = "SELECT * FROM Mailing List"; if ( $db->Sql($SqlStatement)){ { print "error submitting SQL statment " . $db->Error(). "\n"; }` [download] When I run the code with those statements, I get to the next part of the code which executes and it prints out "Read a record" over and over again. If you're going to actually want to read your data, you'll have some work to do on that part of the code. If you find you need help with that part, just come on back and ask more questions.	[reply] [d/l] [select]
Re^2: SQL err in WIN32 by Robertn (Hermit) on Jul 31, 2004 at 02:30 UTC
Hi, I did a cut and paste of your code into the program, and while it will now fall through, I still get this error. error submitting SQL statment 1 1 "-1305" If your getting a message rather than -1305, then I still have a problem, probably with my setup. If your getting -1305 then I don't feel so bad, but still suspect I'm missing the error file, or still not accessing it properly. I've not yet written the get data part, what's there is mostly from the win32::odbc fact, as I wanted to make sure I was accessing the file, before I got heavy into code, but thanks for the offer. I'll certainly come back if I get stuck. Thanks ever so much for taking your time to help out.	[reply]
Re^3: SQL err in WIN32 by WhiteBird (Hermit) on Jul 31, 2004 at 15:27 UTC
I'm glad to see that EdwardG nailed the problem for you. I wasn't pulling any errors at all, but I didn't have a space in my table name. I avoid spaces in names at all costs. Just out of curiosity I renamed my table with a space in the name and I pulled up the 1305 error. In detail it reads: `error submitting SQL statment [-1305] [1] [0] "[Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot find the input table or query 'CODE'. Make sure it exists and that its name is spelled correctly."` Which points directly to my table which is named "CODE CODE". Just FYI for future reference.	[reply] [d/l]
Re^4: SQL err in WIN32 by Robertn (Hermit) on Aug 01, 2004 at 20:45 UTC
Re^5: SQL err in WIN32 by WhiteBird (Hermit) on Aug 02, 2004 at 13:31 UTC
Re: SQL err in WIN32 by EdwardG (Vicar) on Jul 31, 2004 at 11:47 UTC
Names with embedded spaces are best avoided in the Windows environment. I've learned this from experience. Access and SQL Server have problems, and the square brackets trick is something I've used for many years, I forget where I learned it.	[reply]
Re: SQL err in WIN32 by runrig (Abbot) on Aug 01, 2004 at 21:11 UTC
Your problem seems to be solved, so I'll just comment on your use of the modules. Win32::ODBC is completely unrelated to DBI, so you would rarely if ever need to use both modules. Unless you need some special feature(s) of Win32::ODBC, I would just use DBI and DBD::ODBC, as it is more portable, you can use placeholders and bind values, and you are likely to get more help with it around here. But since you already have it working with Win32::ODBC, you can ignore that advice for now unless you want to do more work just for the sake of doing more work :) Also, you seem to be including DB_File for no apparent reason, (so you should be able to remove all references to it and DBI AFAICT)	[reply]