• do you use strict?
• do you use warnings?
• are all variable initialized?
• what about using placeholders or $dbh->quote()?

1. You could use the DBI->trace(2) method, to find out where exactly the flow of info between client and server is broken, and what query was passed.
2. You could reduce your script to the minimum instructions that reproduce the error, (using strict and -w). If you can do it without quoting problems, i.e. by hardcoding the SQL string, then we could rule out some more things and start considering the possibility of a bug.
3. Have a look at some tutorials that could help you to find out what could be wrong: Reading from a database, Tricks with DBI and The fine art of database programming.

 _  _ _  _  
(_|| | |(_|><
 _|   

I've debugged the SQL statement by printing it to a text file and it is correct.

I'm not trying to make DBI look bad, even because I think it's impossible, since it's one of the best nodes ever written, in my opinion.

I'm just saying that there's a bug somewhere, maybe not in DBI, maybe in MS Access? All I know is that it should work and for some weird reason it doesn't...

I'll try to clean the code to minimize the "noise" between the necessary code to run the query and the point it's generating the error, but I'm still pretty sure that is not a code error.

my ($author_nickname, $author_email) = ("DaWolf","erabbott\@terra.com.br") if ($author_name eq "Er Galvão Abbott");

$sthg = $dbh->prepare_cached("SELECT DISTINCT login FROM usuarios WHER
+E filial=? AND auth=? AND login<>?");
$sthg->execute($filial, 4, $filial);

#...

$sthn = $dbh->prepare_cached("SELECT DISTINCT nome FROM usuarios WHERE
+ login=? AND filial=?") or die "ERRO!"; 
$sthn->execute($gerente, $filial);
# ...
[download]

I've tried with placeholders and still doesn't works (I've foreseen this, since I'm not using placeholders in the other dozens of scripts that do similar things in the app).

I've thought about the 'cached' part too, since the 'Alloc' part of the error msg really indicates a problem caching the query. Well, it's not this. I've tryied without caching and the result is the same.

I've Googled for this particular error msg and there's only one result that really matters and the guy who posted the same question that I did received no response, so I think this is a very serious problem.

I can only think on blaming Access, since the whole script where the snippet that I've posted came from is really big. Maybe MS Access can't handle it? I'm really perlplexed...

Thanks anyway for your reply.

my ($author_nickname, $author_email) = ("DaWolf","erabbott\@terra.com.br") if ($author_name eq "Er Galvão Abbott");

Have you tried what gmax had suggested?
Enable tracing right before the prepare_cached() and see what you get;e.g.,


DBI->trace(2, 'dbitrace.txt');
$sthn = $dbh->prepare_cached("SELECT DISTINCT nome FROM usuarios WHERE
+ login=? AND filial=?") or die "ERRO!"; 
$sthn->execute($gerente, $filial);
[download]

Run your program and see what you get in 'dbitrace.txt'

--perlplexer

Unless your variables are coming from trusted sources or you're doing some serious sanitization prior to using them in your SQL, you should be using placeholders anyway. Don't let the user stick a single-quote in your input and let them inject their own SQL into your statement. Placeholders/bind variables are secure, and allow your SQL to be re-used, so it's faster and more efficient. Plus, it makes your code more readable. Just some tips, good luck.

I don't have an answer but a shot in the dark. Let's assume that the SQLAllocStmt error is just that - an inablility to allocate memory for the statement. Where in the loop is the error occuring (after X number of loop iterations?). Is it possible to move the prepare_cached (with placeholders) outside the loop so you're not preparing it n times? Also, is the handles' finish being called before the next execute? (Probably). Some drivers have problems executing a still active handle.

-derby

I did find the following in the DBI doc....good luck!

              This caching can be useful in some applications, but it
              can also cause problems and should be used with care. If
              the cached "$sth" being returned is active (i.e., is a
              "SELECT" that may still have data to be fetched) then a
              warning will be generated and "finish" will be called
              for you.  The warning can be suppressed by setting
              "$allow_active" to true.  The cache can be accessed (and
              cleared) via the the CachedKids entry elsewhere in this
[download]