Fetching over 400 000 records from database via odbc php. - Forum - OpenEdge General - Progress Community

Fetching over 400 000 records from database via odbc php.

 Forum

Fetching over 400 000 records from database via odbc php.

This question is answered

Hello, I'm trying to fetch 22.5 millions records from DataBase via ODBC and PHP, but odbc_fetch returns only 400000 records. What I noticed also. The simple query

SELECT COUNT(*) FROM PUB.SaleGood 

doesn't return any value via ODBC, but correct returns value via SQLExp.
Help me please, to resolve this issue.

OpenEdge Server - Linux Ubuntu, OpenEdge version - 10.1B02

Verified Answer
  • On 6/23/2014 8:24 AM, esemashko wrote:
    Reply by esemashko

    I tried to increase ArraySize in DSN, but server stopped with message:

    [2014/06/23@03:03:30.562954248389133+0400] P-18855      T-1749755648 I SRV     2: (915)   Lock table overflow, increase -L on server L = 16000

    Stop receiving emails on this subject.

    Flag this post as spam/abuse.



    You may be running with a Transaction Isolation Level of Repeatable Read, if you get 16000 locks.
    Repeatable Read will lock every record read until end of transaction. You may not need this behavior.

    You might try using Transaction Isolation Level of Read Committed.

    The isolation level can be set in the ODBC Data Admin for the DSN. This assumes that your app and PHP do not over-ride the isolation level defined for the DSN.

    If  your applications is also writing to the db via Insert/Update/Delete while reading, then the isolation level has no effect on locking for writes, and these will not be affected.

    Hope this helps,        ......sjp


All Replies
  • Hello esemashko,

    I think that this article will help you:

    "Is there a way in SQL-92 to limit the number of records in the result set that the query returns"

    knowledgebase.progress.com/.../P26439

    "...When programming an ODBC client using the OpenEdge 10.1A or later ODBC driver, use the ArraySize attribute in a DSN-less connection string..."

    Regards,

    Valeriy

  • I read the article, but increasing of ArraySize doesn't help me. Moreover, increasing ArraySize value more than 16000 led to the fact that server throw critical exception and stopped.

  • Related topic community.progress.com/.../10923.aspx,

    but the answer was not found yet.

    Maybe someone faced a similar problem?

    Regards,

    Valeriy

  • Can anybody of technical support can help?

  • Hi,

    There is no limitation in the Connect for ODBC drivers regarding number of rows returned. I was also unable to find any related articles in out Knowledgebase. This limitation is either PHP- or application related.

    To further investigate, I suggest to open a Support Ticket, see Article Number 000047073 how to do this.

    Regards,

    Pim

  • Hi,

    There is no limitation in the Connect for ODBC drivers regarding number of rows returned. I was also unable to find any related articles in out Knowledgebase. This limitation is either PHP- or application related.

    To further investigate, I suggest to open a Support Ticket, see Article Number 000047073 how to do this.

    Regards,

    Pim

  • Why a new thread on the same topic.  Lacking a response which solves the problem here, I recommend opening a support case as suggested on the original thread.  The other thing you could try is to do the select with a starting key supplied that will put you higher in the stack.  If that gets you the same number of records, it will be very clear that the problem is a property of the connection.

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • Hello, I'm trying to fetch 22.5 millions records from DataBase via ODBC and PHP, but odbc_fetch returns only 400000 records. What I noticed also. The simple query SELECT COUNT(*) FROM PUB.SaleGood doesn't return any value via ODBC, but correct returns value via SQLExp. Help me please, to resolve this issue. OpenEdge Server - Linux Ubuntu, OpenEdge version - 10.1B02

  • I don't think it is a PHP limitation. I can get a million rows to return with PHP without any problem using DataDirect SQL Server Wire Protocol driver. Which driver do you use? How big is a row in your table? I think it is either your app or your system memory limitation.

    Good luck,

    Eugene

  • I'm using ODBC driver, odbcinst.ini contains such string:

    Driver=/usr/dlc/odbc/lib/pgoe1022.so

    And row consists of 11 columns:

       10 KeySaleCheck               inte      
       20 KeyDepartment             inte
       30 Code                               deci-0
       40 KeyMaterialEntity          inte
       50 KeyRange                      inte
       60 QtyCash                         inte
       70 QtyBank                          inte
       80 DiscountBank               deci-2
       90 DiscountCash              deci-2
      100 PayCash                      deci-2
      110 PayBank                       deci-2

    How I can check, has the system some memory limitations or no?
    And why ODBC driver not returns result for COUNT(*) request, but JDBC returns?

    Thanks.

  • I tried to increase ArraySize in DSN, but server stopped with message:

    [2014/06/23@03:03:30.562954248389133+0400] P-18855      T-1749755648 I SRV     2: (915)   Lock table overflow, increase -L on server L = 16000

  • On 6/23/2014 8:24 AM, esemashko wrote:
    Reply by esemashko

    I tried to increase ArraySize in DSN, but server stopped with message:

    [2014/06/23@03:03:30.562954248389133+0400] P-18855      T-1749755648 I SRV     2: (915)   Lock table overflow, increase -L on server L = 16000

    Stop receiving emails on this subject.

    Flag this post as spam/abuse.



    You may be running with a Transaction Isolation Level of Repeatable Read, if you get 16000 locks.
    Repeatable Read will lock every record read until end of transaction. You may not need this behavior.

    You might try using Transaction Isolation Level of Read Committed.

    The isolation level can be set in the ODBC Data Admin for the DSN. This assumes that your app and PHP do not over-ride the isolation level defined for the DSN.

    If  your applications is also writing to the db via Insert/Update/Delete while reading, then the isolation level has no effect on locking for writes, and these will not be affected.

    Hope this helps,        ......sjp


  • The sql "select COUNT(*)  from table" with huge number of rows could be just slow for the server to return. You may file a performance request to OpenEdge about this issue. Not sure how your app handles that many rows, I suggest you fetch what you need in memory, examine/analyze it, reuse the memory and fetch the next set of rows.

    Best regards,

    Eugene

  • and:

    the OpenEdge RDBMS uses a memory-resident lock table for row and table locks. The -L configuration parameter referred to in the error message is used to specify the size of the lock table.

  • Thank you very much for help.