locked
VS 2012: Snapshot Recordset no longer updatable

    Question

  • Hi,

    this code has worked since Visual Studio 6 up until 2010. Compiled with Visual Studio 2012 it throws an exception as "ws.Edit()":

     

    	TRY
    	{
    		CDatabase db;
    		if(db.OpenEx("DSN=xxxx", CDatabase::useCursorLib))
    		{
    			CWORKSTATION ws(&db);
    			if(ws.Open(CRecordset::snapshot))
    			{
    				ws.Edit();
    				ws.Update();
    				ws.Close();
    			}
    			db.Close();
    		}
    	}
    	CATCH_ALL(e)
    	{
    		e->ReportError();
    	}
    	END_CATCH_ALL

    Here are the tracing outputs during debugging. Any ideas what's wrong?

    Warning: ODBC Success With Info, Database Context changet to 'xxxx'.
    State:01000,Native:5701,Origin:[Microsoft][SQL Server Native Client 11.0][SQL Server]

    Language Settions changed to German.

    State:01000,Native:5703,Origin:[Microsoft][SQL Server Native Client 11.0][SQL Server]

    DBMS: Microsoft SQL Server
    Version: 11.00.2100
    ODBC Driver Manager Version: 03.80.0000
    Optional Function not implemented
    State:S1C00,Native:0,Origin:[Microsoft][SQL Server Native Client 11.0]

    Warning: Driver does not support requested concurrency.
    Optional Function not implemented

    State:S1C00,Native:0,Origin:[Microsoft][SQL Server Native Client 11.0]

    Warning: Driver does not support requested concurrency.
    Warning: Setting recordset read only.
    First-chance exception at 0x7502277C in db1.exe: Microsoft C++ exception: CDBException at memory location 0x00B9E990.

    Monday, August 27, 2012 8:31 AM

All replies

  • Is the CDatabase/CRecordset Support in Visual Studio 2012 completely broken?

    If I use a wrong Password for "db.OpenEx" this call in line 451 of "dbcore.cpp" throws an exception:

    RETCODE nRetCodeEnv = ::SQLFreeEnv(pDbState->m_henvAllConnections);

    Unhandled exception at 0x7766C2A4 (ntdll.dll) in db1.exe: A LIST_ENTRY was damaged (i. e. double freeing).

    Monday, August 27, 2012 9:12 AM
  • You mean you can not update the snapshot in Visual C++ 2012?

    Please not forget mark your answer, and unmark your disagreed point.

    Tuesday, August 28, 2012 6:15 AM
  • Exactly; because Edit() throws an exception.

    This happens because m_bUpdatable gets set to false in CRecordset::PrepareAndExecute() after unsuccessfully trying to set the cursor concurrency (dbcore.cpp).

    Tuesday, August 28, 2012 6:37 AM
  • I have found the reason for the first problem:

    Microsoft has changed the following call in CDatabase::AllocConnect():

    	// Turn on cursor lib support
    	if (dwOptions & useCursorLib)
    	{
    		AFX_SQL_SYNC(::SQLSetConnectAttr(m_hdbc, SQL_ATTR_ODBC_CURSORS, (SQLPOINTER)SQL_CUR_USE_DRIVER, 0));
    		// With cursor library added records immediately in result set
    		m_bIncRecordCountOnAdd = TRUE;
    	}
    

    In previous Versions of MFC the attribute was set to "SQL_CUR_USE_ODBC".

    Now the question is: Was this done on purpose (then the documentation for "useCursorLib" should be changed) or is it an error???

    Tuesday, August 28, 2012 9:44 AM
  • This is now broken for us as well.  And yes, this is definitely the change that creates the problem - SQL_CUR_USE_DRIVER in 2012, vs. SQL_CUR_USE_ODBC in prior versions. :(

    Does anyone know why this came to be?!

    This breaks our code, since Access/Jet claims to support sql positional operations but claims to not support sql positional updates.  Yet, when MFC requests that an actual update occur, the Jet driver returns failure for both of the concurrency modes it previously claimed to support, as follows:

    CDatabase queries ODBC/Jet for SQL_POS_OPERATIONS in CDatabase::GetConnectInfo(), determines that it supports SQL_POS_UPDATE|SQL_POS_DELETE|SQL_POS_ADD, and records m_dwUpdateOptions |= AFX_SQL_SETPOSUPDATES (unlike <= VS2010 where it would have determined none of those were supported).  It then checks SQL_POSITIONED_STATEMENTS, and finds that nothing is supported (unlike VS2010 where the answer would have been SQL_PS_POSITIONED_DELETE|SQL_PS_POSITIONED_UPDATE).

    CRecordset::PrepareAndExecute() first tries SQL_CONCUR_VALUES, which the Jet/Access driver errors on, then it tries SQL_CONCUR_LOCK, which again the Jet/Access driver errors on, so it finally drops down to SQL_CONCUR_READ_ONLY and flags itself as being m_bUpdatable = m_bAppendable = FALSE, and this time ::SQLPrepare() succeeds (but now we've got a read-only connection, relatively useless to us).

    I'm off to try forcing our own version of CDatabase::AllocConnect() which does it the VS2010 way, to see if that works (I imagine it will).  WRONG!

    Update: Attempting to force VS 2012 to load the older, ODBC cursor library results in:
    "Cursor library not used.  Load failed"


    • Edited by Steve Wolf Wednesday, October 03, 2012 6:41 PM
    Wednesday, October 03, 2012 5:47 PM
  • Hello Steve,

    I've posted the problem on Microsoft Connect:

    http://connect.microsoft.com/VisualStudio/feedback/details/760374/cdatabase-usecursorlib-broken-in-mfc11

    Maybe you would like to add your findings there?

    Thursday, October 11, 2012 6:47 AM