CDaoRecordSet->Requery() not allowed in a transaction ?

Posted on 2004-11-13
Last Modified: 2013-11-20
i'm using parameterized queries, that's the fastest way i have found when i have to call very often the same query with just a parameter value changing. Here is how i open such a recordset :

if (! pRst->IsOpen())
    strSQL.Format("PARAMETERS [Param] Long; SELECT * FROM POINTS WHERE tagPoint = [Param]");
    if (pRst->m_pMyQueryDef)
     delete pRst->m_pMyQueryDef;
     pRst->m_pMyQueryDef = NULL;
   pRst->m_pMyQueryDef = new CDaoQueryDef(m_pDatabase);
   pRst->m_pMyQueryDef->Create("", strSQL);
   COleVariant object = param; // value to be searched
   pRst->m_pMyQueryDef->SetParamValue("[Param]", object);
   pRst->Open(pRst->m_pMyQueryDef, dbOpenDynaset);
else // recordset already opened : just requery with the new parameter value :
  COleVariant object = param; // value to be searched
  pRst->m_pMyQueryDef->SetParamValue("[Param]", object);
pRst->m_bFound = ! pRst->IsEOF();

That works fine and fast.
Now i try to accelerate some part of my code where i have to update some values for a big numbers of records; a SQL UPDATE cannot be used because the values have to computed  by one of my functions for each record. I thought that doing the update batch within a transaction could have a positive effect on speed. Opening the recordset as done above still works, but when the recordset  is already opened, pRst->Requery()  causes this exception :

  Operation not supported in transactions.
  ErrorCode DAO : 3246

"Operation not supported' : that seems to put a stop on my expectations. But i find it curious that opening the recordset still works : isn't a (re)query implicitly done during this opening ?
So my question is : is there a way to use parameterized queries during transactions ?
Question by:JP_Goblet
    LVL 43

    Expert Comment

    You could try to explicitly close the recordset before the requery  (Not tried it so it may not have any effect)
      pRst->m_pMyQueryDef->SetParamValue("[Param]", object);

    However you state you are in a transaction for performance reasons.  I might be wrong in this but I don't think it would make any difference (and possibly even result in poorer performance).  My understanding of transactions is to provide an 'all or none' mechanism to preserve data integrity.
    Roughly if you perform three actions (A then B then C).  Without a transaction if C fails your data has an Action A and B performed upon it but no C - consequences could be catastrophic.  In a transaction if C fails you rollback and your data is as if nothing was performed.
    LVL 43

    Accepted Solution

    I've done some testing here (I hadn't thought of your approach myself before).

    For my small test.
    This requery is vastly faster than closing/opening the recordset. (15 times faster)
    Wrapping the close/open in a transaction improves the performance by about 70%, but that is still lousy compared to the requery.
    Wrapping the requery in a transaction showed no change in performance.

    Note the final statement.
    The following code works.  It is a paramterised query inside a transaction.

    void CXyzDlg::PerfomQuery(CDaoDatabase* pDB)

          CDaoRecordset rs(pDB);
          CDaoQueryDef Qdf(pDB);

          Qdf.Create(_T(""), _T("PARAMETERS z Long; SELECT testTbl.* FROM testTbl WHERE (((testTbl.testID)=[z]));"));

          COleVariant var((long)0);
          Qdf.SetParamValue("[z]", var);

          for(int i = 1; i < 11000; i++)
                var.lVal = i;
                Qdf.SetParamValue("[z]", var);
                rs.SetFieldValue("Beleg ID", var);


    Author Comment

    I was surprised that your code works as it does the same than mine : open once and then just requery for all records. After some tests i have discovered why my code did not work : my recordset was already opened before the BeginTrans (because, again for performance reasons, i keep the same recordset for all my application, and i reopen it only when necessary, for example if the query filter has changed). Closing the recordset before BeginTrans and then reopening it solves the problem.
    But as you said there is no performance increase.

    I supposed the transaction would speed things because it writes all the data on disk in one shot; without transaction there are a lot of access to disk (although the windows HD cache is supposed to avoid that ?). That's what i had noticed some time ago when i made tests with ADO.

    Your conclusion is right :  a parameterised query inside a transaction can be done but do not increase performance.

    Author Comment

    to be more precise, i expected a performance increase not in the querying process, but in the updating of the table
    LVL 43

    Expert Comment

    The transaction is still useful for the data integrity for bulk processing.
    Interesting, I'll have to remember this approach (parameter requery instead of close/open).

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Suggested Solutions

    Title # Comments Views Activity
    Motorola device cold boot 3 331
    Replacement selected text 2 45
    WinWaitActive parameters 12 30
    sumNumber challenge 16 78
    Introduction: Load and Save to file, Document-View interaction inside the SDI. Continuing from the second article about sudoku.   Open the project in visual studio. From the class view select CSudokuDoc and double click to open the header …
    Introduction: Hints for the grid button.  Nested classes, templated collections.  Squash that darned bug! Continuing from the sixth article about sudoku.   Open the project in visual studio. First we will finish with the SUD_SETVALUE messa…
    This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now