DBD ODBC question - $dbh->{odbc_exec_direct}

I am working on a script that inserts records into a Progress database.
The script connects via DBI and odbc.

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=3D-1) at ./siimport.pl line
170.
DBD::ODBC::st fetchrow_array failed: (DBD: no select statement currently
executing err=3D-1) at ./siimport.pl line 171.
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=3D-1) at ./siimport.pl line
170.
DBD::ODBC::st fetchrow_array failed: (DBD: no select statement currently
executing err=3D-1) at ./siimport.pl line 171.
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=3D-1) at ./siimport.pl line
170.
DBD::ODBC::st fetchrow_array failed: (DBD: no select statement currently
executing err=3D-1) at ./siimport.pl line 171.

On some of the queries I was able to switch from:
$sth =3D $dbh->prepare($query);
$rc =3D $sth->execute();
($si_owner) =3D $sth->fetchrow_array();

To:
($si_owner) =3D $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:

$dbh->{odbc_exec_direct} =3D 1;

Which made the problem go away completely. But I'd really like to
understand why. I'd 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 =3D> 1}); and $dbh->{odbc_exec_direct} =3D 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.


Thanks for any help.
-Ryan

Re: DBD ODBC question - $dbh->{odbc_exec_direct}

Try
sth->finish()
after the
sth->execute()

i see you're catching the return values too, as in:

$rc = sth->execute()

are you actually using them for anyting? if not, i'd suggest not
capturing them, because they're doing nothing but executing more
instructions.



statements. perhaps the stale sth


On Thu, 15 Dec 2005, Ryan Stille wrote:

> I am working on a script that inserts records into a Progress database.
> The script connects via DBI and odbc.
>
> 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.
> DBD::ODBC::st fetchrow_array failed: (DBD: no select statement currently
> executing err=-1) at ./siimport.pl line 171.
> 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.
> DBD::ODBC::st fetchrow_array failed: (DBD: no select statement currently
> executing err=-1) at ./siimport.pl line 171.
> 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.
> DBD::ODBC::st fetchrow_array failed: (DBD: no select statement currently
> executing err=-1) at ./siimport.pl line 171.
>
> 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:
>
> $dbh->{odbc_exec_direct} = 1;
>
> Which made the problem go away completely. But I'd really like to
> understand why. I'd 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.
>
>
> Thanks for any help.
> -Ryan
>

--
LLL OOOO UUU UUU IIII SSSSS
LLL OOO OOO UUU UUU IIII SSSS
LLL OOO OOO UUU UUU IIII SSS
LLL OOO OOO UUU UUU IIII SSS
LLL OOOO OOOO UUU UUU IIII SSSSS
LLL OOOO OOOO UUU UUU IIII SSSSS
LLL OOOO OOOO UUUUUUUUUUU IIII SSSSS
LLL OOOO OOOO UUUUUUUUU IIII SSSS
LLLL OOOOOOOO UUUUU IIII SSSSSS
LLLLLLLL OOOOOO UUU IIII SSSSS
LLLLLLLL

RE: DBD ODBC question - $dbh->{odbc_exec_direct}

louis gonzales wrote:
> Try
> sth->finish()
> after the
> sth->execute()

I tried this and it did indeed stop the errors, even without the
"$dbh->{odbc_exec_direct} =3D 1" line. Actually I added it after the
fetch, I wasn't sure if I should clean up $sth before I fetch() from it.

What was happening by not calling finish()? My recollection was that
this is not usually necessary.

Thanks,
-Ryan

RE: DBD ODBC question - $dbh->{odbc_exec_direct}

Hey Ryan,
Actually, I noticed the stale handle error you were getting and with a
little intuition, without digging in too deep, figured it had to do with
the method being called and not closing "finishing" before the next
iteration of the same method call.

Glad it solved the issue!

Regards,

On Thu, 15 Dec 2005, Ryan Stille wrote:

> louis gonzales wrote:
>> Try
>> sth->finish()
>> after the
>> sth->execute()
>
> I tried this and it did indeed stop the errors, even without the
> "$dbh->{odbc_exec_direct} = 1" line. Actually I added it after the
> fetch, I wasn't sure if I should clean up $sth before I fetch() from it.
>
> What was happening by not calling finish()? My recollection was that
> this is not usually necessary.
>
> Thanks,
> -Ryan
>

--
LLL OOOO UUU UUU IIII SSSSS
LLL OOO OOO UUU UUU IIII SSSS
LLL OOO OOO UUU UUU IIII SSS
LLL OOO OOO UUU UUU IIII SSS
LLL OOOO OOOO UUU UUU IIII SSSSS
LLL OOOO OOOO UUU UUU IIII SSSSS
LLL OOOO OOOO UUUUUUUUUUU IIII SSSSS
LLL OOOO OOOO UUUUUUUUU IIII SSSS
LLLL OOOOOOOO UUUUU IIII SSSSSS
LLLLLLLL OOOOOO UUU IIII SSSSS
LLLLLLLL

RE: DBD ODBC question - $dbh->{odbc_exec_direct}

Without looking at your code, I can guess:
Even if you only expect one row, you need to select until no more
results are returned, which triggers the finish() for you. If you
don't, you need finish().

Jeff

-----Original Message-----
From: Ryan Stille [mailto:rps@willcomminc.com]=20
Sent: Thursday, December 15, 2005 3:42 PM
To: dbi-users@perl.org
Subject: DBD ODBC question - $dbh->{odbc_exec_direct}

I am working on a script that inserts records into a Progress database.
The script connects via DBI and odbc.

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=3D-1) at ./siimport.pl line
170.
DBD::ODBC::st fetchrow_array failed: (DBD: no select statement currently
executing err=3D-1) at ./siimport.pl line 171.
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=3D-1) at ./siimport.pl line
170.
DBD::ODBC::st fetchrow_array failed: (DBD: no select statement currently
executing err=3D-1) at ./siimport.pl line 171.
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=3D-1) at ./siimport.pl line
170.
DBD::ODBC::st fetchrow_array failed: (DBD: no select statement currently
executing err=3D-1) at ./siimport.pl line 171.

On some of the queries I was able to switch from:
$sth =3D $dbh->prepare($query);
$rc =3D $sth->execute();
($si_owner) =3D $sth->fetchrow_array();

To:
($si_owner) =3D $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:

$dbh->{odbc_exec_direct} =3D 1;

Which made the problem go away completely. But I'd really like to
understand why. I'd 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 =3D> 1}); and $dbh->{odbc_exec_direct} =3D 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.


Thanks for any help.
-Ryan