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

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)
   {
     pRst->m_pMyQueryDef->Close();
     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->Requery();
}
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 :

  DAO.Recordset
  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 ?
JP_GobletAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

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.
0
AndyAinscowFreelance programmer / ConsultantCommented:
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)
{
      pDB->m_pWorkspace->BeginTrans();

      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);
 
      rs.Open(&Qdf);

      for(int i = 1; i < 11000; i++)
      {
            var.lVal = i;
            Qdf.SetParamValue("[z]", var);
            rs.Requery();
            rs.Edit();
            rs.SetFieldValue("Beleg ID", var);
            rs.Update();
      }
      rs.Close();
      Qdf.Close();
      pDB->m_pWorkspace->CommitTrans();
}

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JP_GobletAuthor Commented:
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.
0
JP_GobletAuthor Commented:
to be more precise, i expected a performance increase not in the querying process, but in the updating of the table
0
AndyAinscowFreelance programmer / ConsultantCommented:
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).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.