SPONSORS:






User Tag List

Results 1 to 3 of 3
  1. #1
    Junior Member
    Join Date
    Mar 2001
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Possible ODBC Limitation ?

    Hi,

    I am trying to get data from a Sybase or MSSQL databse and can actually get the data just fine.
    However, the Database at times can be very large, ie.. 2.5 million rows of data. ( It takes forever for the query to return )

    I want to 'SET ROWCOUNT 500' and only get the first 500 hits to work with in my scripts.

    Here is what I have so far:
    [-] LIST OF STRING get_DB_Data_String (SQL_Data sql, BOOLEAN bSave optional) // returns single column values from SQL Query
    [ ] LIST OF STRING lsDataList
    [ ] STRING sDataString
    [ ] INT i = 0
    [ ] HDATABASE hdbc
    [ ] HSQL hstmnt
    [ ] hdbc = DB_Connect ( getConnectionString () )
    [ ]
    [ ] hstmnt = DB_ExecuteSql (hdbc, "SET ROWCOUNT 50")
    [ ] hstmnt = DB_ExecuteSql (hdbc, "{sql.SQL_Query}")
    [ ]
    [-] while (DB_FetchNext (hstmnt, sDataString) ) && i < 500
    [ ] ListAppend (lsDataList, sDataString)
    [ ] i++
    [ ]
    [ ]
    [-] if (bSave == true)
    [ ]
    [ ] ListWrite ( lsDataList, "{dataPath}\{sql.sFileName}" )
    [ ] print (" Data List recorded to {dataPath}\{sql.sFileName}")
    [ ] print (" ** {i} records Retrieved")
    [-] if i == 0
    [ ] LogWarning (" ** ** No Data Loaded")
    [-] print("")
    [ ]
    [ ]
    [ ] DB_FinishSQL (hstmnt)
    [ ] DB_Disconnect (hdbc)
    [ ] return ( lsDataList )

    It runs OK, but takes forever, because it still tries to load the entire result set, and then process the DB_FetchNext statement.
    I can limit my results within the while loop, but I would prefer to do it as the Query is processed to save time..

    I have a hunch this is an ODBC thing that I probably can't get around.

    any ideas?

    Jason


    ------------------
    Jason B. Archibald
    Jason B. Archibald

  2. #2
    Member
    Join Date
    Jun 2001
    Location
    Bedford, MA, USA
    Posts
    85
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Possible ODBC Limitation ?

    Jason,
    This doesn't help you directly, but for Oracle you can specify a row limitation in the where clause. The following query will return no more than 99 rows.
    There may be keywords for Sybase and MSSQL that are similar to Oracle's ROWNUM.

    Good luck.
    - Jim


    ------------------

  3. #3
    Junior Member
    Join Date
    Mar 2001
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Possible ODBC Limitation ?

    Thanks for your input,

    After further investigation I found my code works great against a MSSQL database, but Sybase seems to ignore the SET ROWCOUNT in this situation.

    I found to I can use SELECT TOP 99 and this also works great for MSSQL but Sybase wants nothing to do with it. Sybase also dos not have a LIMIT or ROWNUM options like Oracle and others.

    I think Sybase has some work to do if they really want to stay even remotly as a competitor in the DBMS market..


    ------------------
    Jason B. Archibald
    Jason B. Archibald

 

 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

vBulletin Optimisation provided by vB Optimise v2.6.0 Beta 4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.0.9 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Questions / Answers Form provided by vBAnswers (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominatevBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 12:47 PM.

Copyright BetaSoft Inc.