|
wabranty has asked for the wisdom of the Perl Monks concerning the following question:
This is my first week with Perl so please excuse the newbie question. I've been through the Win32::ODBC tutorial and Mr. Roth's faqs but I can't seem to find an answer to this (probably) basic question: when I run this script, I get a "Can't run Sql on an undefined value" error.
use Win32::ODBC;
my($db) = new Win32::ODBC("dsn=Map");
$db->Sql("SELECT * FROM States");
while ($db->FetchRow()) {
my(%data) = $db->DataHash();
Print "$data{'ID'}";
print "$data{'State'}";
}
$db->Close();
When I installed Win32::ODBC, I ran the test.pl script and it worked fine. Why can't I set my dsn? Thanks |
DBI!! (was: Win32::ODBC undefined DSN)
by BBQ (Curate) on Jul 07, 2000 at 09:25 UTC
|
wabranty, since you are starting off, I'd seriously give some thought at checking out DBI and the DBD drivers. DBI has become the de facto database interface for all your perl needs. While Win32::ODBC works on Win (doh), DBI will run on virtually all platforms provided you have the requirements to load the correct database driver (read DBD).
On Win, you can use almost the same amount of drivers (DBD::Oracle, DBD::mysql, DBD::CSV, etc) and if you run out of options on DBD's, there is always DBD::ODBC. The nice thing about DBI is works cross-platform, cross-database! You can develop on one database/platform and deliver on another entirely diferent system.
Tim Bunce should probably hire me as a marketing guy for DBI, since I think this is the 3rd time or so that I answer a Win32::ODBC question by pointing towards DBI. Beleive me when I say its well worth the shot!
#!/home/bbq/bin/perl
# Trust no1!
| [reply] |
|
|
I too have answered this question many times, and BBQ is right on the money.
Connecting to databases can be confusing, especially trying to use Win32::ODBC.
If you intend to work on other platforms, then start learning the DBI now, and you
will be greatly rewarded for your effort (not like it's hard to learn) :).
Otherwise, if you just need a quick and dirty way to get at data in an existing
database with an existing DSN connection then look at this node
which states the same thing I just said above and shows how to use ADO from Perl.
Good question though. Good luck!
| [reply] |
(jcwren) Re: Win32::ODBC undefined DSN
by jcwren (Prior) on Jul 07, 2000 at 01:20 UTC
|
Did you go into Start|Settings|Control Panel|Data Sources(ODBC) and setup a DSN to your database? In my experience with ODBC, you have to create a system DSN. I've tried creating user DSNs with less luck, but I haven't pursued the matter in depth. Your DSN will need to have the database connection, username, password, etc, since you're relying completly on the DSN to supply that information (you can override it in the ODBC connection statement, if you like).
If this doesn't help, I can fish up an example of a working ODBC connection, along with a correctly specified DSN, if you like.
Two other points: Preceeding your code with <code> and </code> tags will make it more readable. Also, can you provide the line number the error occurred on? I'm not sure that your subject of the topic and the error you're reporting may necessarily correspond.
--Chris
e-mail jcwren | [reply] |
Re: Win32::ODBC undefined DSN
by Shoeboy (Sexton) on Jul 07, 2000 at 12:45 UTC
|
1. Enclose your code in <code></code>
2. Throw in some error handling when you create your connection. (I snipped this from the FAQ:) $DSN = "My DSN";
if (!($db = new Win32::ODBC($DSN))){
print "Error connecting to $DSN\n";
print "Error: " . Win32::ODBC::Error() . "\n";
exit;
}
3. Tell us what error you're getting.
It's hard to troubleshoot without an error. Like jcwren suggested, check your dsn.
Finally, Win32::ODBC usually isn't the best way to access databases under Win32. The Win32::OLE module lets you use ADO, this is really the optimal database interface on Win32. ADO will really come in handy if a VB programmer has to maintain your code. (Don't laugh, I've had to bring VB guys up to speed on my code. ADO was the only part they understood.)
--Shoeboy
perl -e "do {kill $java, $ada, $cobol, $pascal, $csh;} until die 'Just another perl hacker';" | [reply]
[d/l]
[select] |
RE: Win32::ODBC undefined DSN
by barndoor (Pilgrim) on Jul 07, 2000 at 12:27 UTC
|
BBQ is dead right. DBI is by far the best was to access databases in Perl. The platform independence is so useful.
I recently wrote a program which replicated files between systems using a configuration held in a database. I developed the application using Ingres as the database, but when we went live it was using a Sybase database.
The conversion took 5 minutes and worked first time.
You do get some issues such as connection parameters being different, and there are differences in SQL syntax (e.g. Ingres and Sybase swap the FROM and SET parts of an UPDATE statement). Otherwise it is a good generic interface.
'Programming the Perl DBI' from O'Reilly is a good book to learn all about it and has a section on differences between databases. It even has a chapter on DBI and ODBC interaction talking about both DBD::ODBC in DBI and a comparision to WIN32::ODBC.
Hope this helps. | [reply] |
|
|
