[Omaha.pm] DBI odbc question

[Jay Hannah]

Ryan Stille wrote:
> I am working on a script that inserts records into a Progress database.
> The script connects via DBI and odbc.

Since I don't see a DBD::Progress I guess that's the way to go.

http://www.cpan.org/modules/by-module/DBD/

> As I loop through each potential record to import, I query the database
> for some info.  Occasionally I get errors like this:
> 
> DBD::ODBC::st execute failed: [OpenLink][ODBC][Progress Server]Stale
> request handle. Request was not opened or has been closed. (1253)
> (SQL-S1000)(DBD: st_execute/SQLExecute err=-1) at ./siimport.pl line
> 170.

Yikes. I've been doing DBI for years and I've never seen "Stale request handle" before. Sorry. I never use DBD::ODBC.

> On some of the queries I was able to switch from:
> $sth = $dbh->prepare($query);
> $rc = $sth->execute();
> ($si_owner) = $sth->fetchrow_array();
> 
> To:
> ($si_owner) = $dbh->selectrow_array($query);
> 
> Which make the problem go away for some reason.  I was not able to
> change all the queries to use selectrow_array of course.  But after
> googling the "select statement currently executing" I came up with this:

Generally speaking, that should just be a shortcut that does the same thing behind the scenes.

> $dbh->{odbc_exec_direct} = 1;
> 
> Which made the problem go away completely.  But I'd really like to
> understand why.  I hate to see this problem come up again when I put the
> script into production.  The blurb from the documentation was no help:
> 
> odbc_exec_direct
> Force DBD::ODBC to use SQLExecDirect instead of SQLPrepare() then
> SQLExecute. There are drivers that only support SQLExecDirect and the
> DBD::ODBC do() override doesn't allow returning result sets. Therefore,
> the way to do this now is to set the attributed odbc_exec_direct. There
> are currently two ways to get this: $dbh->prepare($sql, {
> odbc_exec_direct => 1}); and $dbh->{odbc_exec_direct} = 1; When
> $dbh->prepare() is called with the attribute "ExecDirect" set to a
> non-zero value dbd_st_prepare do NOT call SQLPrepare, but set the sth
> flag odbc_exec_direct to 1.

No clue. I don't know jack about the internals of ODBC. Many DBI/DBD::* geniuses and authors hang out here:

http://lists.cpan.org/showlist.cgi?name=dbi-users

Good folk. You may get some answers if you join that list and re-post what you posted to this list. (Excellent explanation of the problem, by the way.)

Sorry I couldn't help more,

j