ODBC

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

Here's a simple program that takes a URL parameter, breaks it into an array and then looks up each entry in an MS Access database. Unfortuantely, when the array has more than 14 entries, a perl.exe error window pops up. Could someone please guide me along the path to resolution?

#!/usr/local/bin/bash
use CGI qw(:standard);
use Win32::ODBC;

$qry = new Win32::ODBC('support_agreement_DSN');
@mac = split (/,/,param('MAC'));
foreach $mac_addr (@mac) {
    $sql = "SELECT agreement.Field2 FROM agreement WHERE (((agreement.
+Field1)='$mac_addr'))";
    $qry->Sql($sql);
    $qry->FetchRow();
    $support_status{$mac_addr}= $qry->Data('agreement.Field2');
}
$qry->Close();
[download]

Comment on ODBC Download Code

Replies are listed 'Best First'.
Re: ODBC by BlueBlazerRegular (Friar) on Apr 30, 2002 at 21:16 UTC
Well, without knowing the exact error, this is all guessing, but here goes: Does the 15th entry in the array get translated into a SQL-like piece of code? For example, if the 15th entry was something like: `SELECT agreement.Field2 FRM agreement` then your actual command would end up looking like this: `SELECT agreement.Field2 FROM agreement WHERE (((agreement.Field1)=SELECT agreement.Field2 FRM agreement))` and it would/should blow up on the 'FRM'. Admittedly, this is a unlikely example, but anything in your array entry that looks like SQL might be parsed by Access as SQL, and therefore cause a SQL syntax error. Or worse, actually be executed (imagine putting a `DROP TABLE agreement` in the URL). Or - I don't use Win32::ODBC (I prefer DBI), but "Programming the Perl DBI" mentions that Win32::ODBC doesn't use statement handles, but rather uses the database handle to process the SQL command. It also goes on to state that database handles can be cloned (although it doesn't explain how that happens). In your example you create a database handle via `$qry = new Win32::ODBC('support_agreement_DSN');`. You then use that database handle each time you do a new query. If Win32::ODBC is actually 'cloning' that database handle each time you create a new SQL statement, you could be running out of database handles. Those are my thoughts on the subject.	[reply] [d/l] [select]
Re: ODBC by beamsack (Scribe) on May 01, 2002 at 00:54 UTC
First of all the statement `$qry->FetchRow()` [download] should return true if a row was fetched. This should be checked before the call to `$qry->Data('agreement.Field2')` [download] note that the query just may be failing to find any matches. To check ODBC itself, on Windows boxes go to Control Panel and select the ODBC applet. There you will find the option to turn on tracing. The trace file, sql.log, is usually generated in the root directory of your system drive. Make sure to turn tracing off when finished because it will slow down your machine big time.	[reply] [d/l] [select]
Re: ODBC by dws (Chancellor) on Apr 30, 2002 at 23:28 UTC
Unfortuantely, when the array has more than 14 entries, a perl.exe error window pops up. Lacking info on what your data looks like, I can only provide a few hints: Rather than reusing the connection ($qry), create a new one inside of the loop, use it, and close it. If there's a problem with Win32::ODBC recycling data structures for queries, this should side step it. Beef up your error detection. Print `$qry->Error()` after each operation while debugging.	[reply] [d/l]
Re: ODBC by beebware (Pilgrim) on May 01, 2002 at 13:15 UTC
I would recommend: Use DBI and DBD::ODBC rather than Win32::ODBC. It'll make things a lot easier in the long run (this is from experience) Use `use strict;` Use `use CGI::Carp "fatalsToBrowser";` And after you do `$sql=...` do a `print "SQL=$sql"` bit so you can visually see what exactly is being processed.	[reply] [d/l] [select]