none
How can I go back to the prior resluts set after SQLMoreResluts or SQLFetchScroll return SQL_NO_DATA

    Question

  • ODBC --    How can I go back to the prior resluts set after SQLMoreResluts or SQLFetchScroll return SQL_NO_DATA without re-execut the statement?
    Tuesday, August 20, 2013 3:46 AM

All replies

  • Can anybody do me a favor ? Thank you very much
    Tuesday, August 20, 2013 3:47 AM
  • ODBC --    How can I go back to the prior resluts set after SQLMoreResluts or SQLFetchScroll return SQL_NO_DATA without re-execut the statement?

    Depending on the values are specified for SQLSetStmtAttr, the cursor may be closed once the last row is fetched with SQLFetchScroll (unlike SQLFetch).  It will not be possible to navigate back once the cursor is closed.  But If you have a scrollable cursor that is still open, you should be able to specify SQL_FETCH_FIRST to navigate back to the start of the result set.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Tuesday, August 20, 2013 12:22 PM
  • In my code, I first invoke SQLSetStmtAttr(SQL_SCROLLABLE) after SQLAlloc(SQL_HANDLE_STMT) .I used SQLGetStmtAttr to check the stament,and it work(return SQL_SCROLLABLE_ON ), then i used SQLExecDirective to exec an statement which will return a result set.However when  i used SQLGetStmtAttr to recheck the with the same statement handle ,the return code changeed to  SQL_SCROLLABLE_OFF, which suprise me. So the fllowing call  SQLFetchScroll with SQL_PRIOR  failed(success with SQL_NEXT) .

    I don't why the statement's attribute change to SQL_SCROLLABLE_OFF?

    For ODBC  return stroed procedure's return value and output parameter only when SQLMoreResluts or SQLFetchScroll return SQL_NO_DATA, in which situtation, the cursor is closed?  If i want to navigate the rowset , Should i build my own RecordSet class and cache all data? 

    the underlayer driver is {SQL Native Server}

    Tks

    Wednesday, August 21, 2013 7:17 AM
  • I don't why the statement's attribute change to SQL_SCROLLABLE_OFF?

    For ODBC  return stroed procedure's return value and output parameter only when SQLMoreResluts or SQLFetchScroll return SQL_NO_DATA, in which situtation, the cursor is closed?  If i want to navigate the rowset , Should i build my own RecordSet class and cache all data? 

    the underlayer driver is {SQL Native Server}

    I assume you mean the "SQL Server Native Client" (or "SQL Server Native Client 10" or ""SQL Server Native Client 11") ODBC driver?

    Are there any statements in the stored procedure other than a single SELECT statement?


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Wednesday, August 21, 2013 11:52 AM
  • Yes, i mean  SQL Native Client 9.0 ODBC Driver(for SQL server 2005), and whose connection string uses "driver ={SQL Native Server}", for i am not sure whether these drivers matter, i put it here.

    No, i only have two simple SELECT statements in my stroed procuedure. which will return two different result sets and i have it tested with SQLMoreResults.  

    I am so confused why the statment's  attribute change to SQL_SCROLLABLE_OFF just after invoking SQLExecDirective, making  my SQLFetchScroll operation with only  forward-only function.                                                                                                                                                                                                            Tks.

    Thursday, August 22, 2013 6:03 AM
  • Is it possibily relative to the settings on SQL Server DataBase ?

    I found this reference 

    http://technet.microsoft.com/zh-cn/library/ms131453(v=sql.90).aspx


    Thursday, August 22, 2013 7:13 AM
  • Is it possibily relative to the settings on SQL Server DataBase ?

    I found this reference 

    http://technet.microsoft.com/zh-cn/library/ms131453(v=sql.90).aspx


    Implicit cursor type conversion is the culprit.  This is why I asked about multiple statements in the proc; multiple result sets are not allowed with server cursors: http://technet.microsoft.com/en-US/library/ms131331(v=sql.90).aspx

    Do you have the same problem with a single result set?  You might try using the ODBC cursor library (client-side cursors) rather than those provided by SQL Server Native Client (server-side cursors).  I can't speak to multiple result sets with ODBC cursors but it's worth a try if you must have scroll capability with multiple result sets.  Alternatively, you could roll your own in code by fetching all the results into client structures and scrolling over the local copy.

    FWIW, the ODBC driver name for the SQL Server 2005 Native Client ODBC driver is "SQL Native Client" (not "SQL Native Server"). The actual driver is important here because driver capabilities do affect implicit cursor conversion.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, August 22, 2013 12:32 PM
  • It's so careless of me to typed "SQL Native Client" again, thanks for your patient edification.

    So, i try ODBC cursor library. and I get scrollable cursor in the first result set, However another questions came up.By making comparation with server cursor, I got the following results.
    
    Is there a way to obtain both scrolling and fetch in other result set functions. I consider making a local copy the last choice, for which is much more complex and i don't have enough experience in memory management. I think i have missed to find related solutions in MSDN but i can' t find where it is. 

    And http://msdn.microsoft.com/en-us/library/windows/desktop/ms713601(v=vs.85).aspx  

    said advoid using ODBC cursor library.  But it does'nt mention when and why it do this. How should i make the trade off. What i am working on is writing an easy ODBC wrapper for accessing SQL Server.(2005,2008 or 2012 and later)

    Tks!

    Monday, August 26, 2013 3:05 AM