Probolem with ODBC access

[Crewone]

Problem with ODBC access

After a day of debugging we are running into a problem with the easysoft oob bridge in combination with php5, unixodbc on a 64 bit debian etch machine.

We have a progress 10.1A server on windows.

Queries work fine, but after 100 "selects" on a single connection we get the following error:

[unixODBC][DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE] Number of open cursors exceeds limit (7612), SQL state S1000

This happens with any select statement on any database. (We have three)

We have increased the maximum number of cursors to 500.

We tried different cursor types in the ODBC driver.

In the odbc-odbc bridge from easysoft: we tried connection pooling on/off, we tried "Free statements" on/off. (And about every other option.)

A simple PHP script to illustrate my problem:

----------
$db = connect...
for( $i = 0; $i < 500; $i++ )
{
// next line will fail after 100 iterations
$res = my_odbc_sql( "SELECT * FROM PUB.Rela WHERE \"Rela-kn-exact-admnr\" = 709 AND \"Rela-ki\" = 152", $db );

odbc_result_all( $res ); // print HTML
odbc_free_result( $res );
}
--------

Any ideas? Thanks!

 

[Casper]

Does the query without the loop gives the same problem?
If not then there is a bug in 10.1A related to this. I think you should upgrade your SQL client to at least 10.1B.

Casper.

 

[Crewone]

Without the loop (or even less than 100 queries) there are no problems.

Is it possible to upgrade just the SQL client? Or is it better to install the complete 10.1B/C package?

Sander

 

[Casper]

It should be enough to upgrade the SQL client.

Look also at KB P122758: http://progress.atgnow.com/esprogress/Group.jsp?bgroup=progress&id=P122758

Casper

 

[Crewone]

Well,

that got me one step further. We upgraded to a 10.1C client, but now after 100 statements the following error occurs:

--
SQL error: [unixODBC][DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]SQL engine Statement Mgr statement cache overflowed. 0 statements currently in use (chst free_lru cache). (9799), SQL state S1000 in SQLExecDirect in
--

Any more ideas?

 

[Casper]

What does my_odbc_sql() do?
I only know php by using odbc_prepare() and odbc_excute() or odbc_exec() AKA odbc_do().

Does it help if you are using SQL_CUR_USE_ODBC instead of the default cursor?

I thnik the problem is gone if you do the connect and disconnect within the loop. am I right?

Casper.

 

[Crewone]

Casper,

my_odbc_sql is just a wrapper for odbc_exec (we also tried odbc_prepare / odbc_execute).

Here is a rewrite:

---
$db = odbc_connect( "exact", "name", "password" );
for( $i = 0; $i < 300; $i++ )
{
echo $i . " \n";
$stmt = odbc_exec( $db, "SELECT * FROM PUB.debtor WHERE ( \"adm-nr\" = '709' ) AND ( \"debtor\" = '2229034' ) " );
}
---

This will fail after 103 iterations.

If I connect and disconnect for every statement, there are no errors but the performance is down the drain.

---

UPDATE: The plot thickens.

If I use SQL_CUR_USE_ODBC with the connect function, the application will fail as well. But now after 206 iteration instead of 103. The error is also really fun: SQLState 0000 and a bunch of weird characters. This is using the 10.1C driver.

And now for the fun part: If I use the SQL_CUR_USE_ODBC cursor with the 10.1A driver, it works. Which means I have to DOWNGRADE again to get things working? That can't be the purpose of the upgrade!? ARGH!



Sander

 

[tamhas]

Check the Startup Command and Parameter Reference, particularly for those starting with SQL. -SQLStmtCache seems suggestive.