0

I am working on refactoring some data-handling code in a MFC application that uses the CRecordset API (actually, a derived class from it, but the failures are coming out of CRecordset itself AFAICT) to talk to an ODBC data source backed by an Oracle database, but have encountered a sequence of operations that the CRecordset API, at least as of the version shipped with Visual Studio 2012 (which I know is old, but am hog-tied to for the time being) seems to be unable to perform.

In particular, in the following sequence of events, intended to flush changes to the record to the DB so that other queries performed during this sequence can see them:

CRecordset aRecordset(myDatabase);
aRecordset.Open(CRecordset::snapshot, "<some query that yields no records>"); // using CRecordset::dynaset doesn't change things
aRecordset.AddNew();
// set some values on aRecordset...
aRecordset.Update();
aRecordset.Requery();   // removing the Requery calls changes the failure mode
aRecordset.Edit();   // This call fails if the Requery is present
// perform query that needs to pick up on the values set on aRecordset above
// set some more values on aRecordset...
aRecordset.Update();    // This call fails if the Requery is not present
aRecordset.Requery();
aRecordset.Edit();
// perform query that needs to pick up on the values set on aRecordset above
// set yet more values on aRecordset...
aRecordset.Update();
aRecordset.Close();

I get two different failure modes, depending on whether the Requery calls are present or not.

With the Requery calls present, I get the following error from the first call to Edit in the sequence:

Error: Edit attempt failed - not on a record.
Operation failed, no current record.

while with them absent, I get a different error, this time from the second call to Update in the sequence, as follows:

Error: failure updating record.
Invalid cursor state
State:24000,Native:0,Origin:[Microsoft][ODBC Driver Manager]

Am I completely off my rocker in expecting CRecordset to be capable of flushing a newly added record to the database then going back to update the row further? Or is this a simple case of API operator error, and if so, what am I missing here? Is my Visual Studio/MFC too old for this fancy footwork?

Furthermore, it turns out that doing a .Requery() is not an option due to a requirement that I be able to .Open() a recordset with multiple rows, then do an .Edit()/.Update()/.Edit()/.Update() sequence on each row. Using the .Requery() in this case causes the cursor to be reset to the beginning with no good way to restore the cursor position, as the Oracle ODBC drivers do not support bookmarking across a requery.

LThode
  • 1,843
  • 1
  • 17
  • 28
  • As to the lack of a MWE for this question -- I simply have no clue how to make a self-contained one for an ODBC problem, especially with the spectre of the Oracle ODBC drivers possibly being involved. If someone here has a suggestion as to making a MWE for this, I would love to see it! – LThode Nov 16 '18 at 15:27
  • I think your problem is _""_ -- Requery just Rebuilds (refreshes) a recordset, so will fetch no rows – Edward Clements Nov 16 '18 at 16:13
  • @EdwardClements -- so, how should I go about adding a row, pushing that to the DB, then going back in and editing that row? – LThode Nov 16 '18 at 16:47
  • either make the query-condition so that it will include the newly added row or re-open the recordset after`AddNew + Update` so that you are sure that the newly added row is included – Edward Clements Nov 16 '18 at 20:13
  • I've never used snapshot. There is [this](https://stackoverflow.com/questions/12714310/crecordsetsnapshot-doesnt-work-in-vs2012-anymore-whats-the-alternative). So try it with dynaset, if you have a primary key... Then you can re-select the record you were on. Requery will move the record, you have to go back to your record by key or cursor from what I understand. I always do in my code. – lakeweb Nov 18 '18 at 19:11
  • @lakeweb -- where does Requery leave the query, then? BOF? EOF? some random spot? (P.S. the Oracle ODBC drivers don't provide support for bookmarks persisting across Requery(), does that mean my only option is to return to my record by key?) – LThode Nov 19 '18 at 18:31
  • @EdwardClements -- I tried resequencing the field updates so all the fields in the WHERE clause of the query are updated in the first batch of field updates above, no dice. – LThode Nov 19 '18 at 18:41
  • Hi LThorde, yes, as a requery could close and open the record set, a cursor won't work. Sorry, spoke without thinking enough. I don't use it a lot but if I trace in, I see it doing a `MoveFirst` without a change in the query. In all my code I move to my previous record by key after a requery. My code goes back over a decade but still works fine with VS2015. – lakeweb Nov 19 '18 at 19:25
  • @lakeweb -- looking at the MFC sources that come with VS2012, it seems they have a MoveNext() near the end of Requery() that *should* position the cursor on the first record in the recordset, no? – LThode Nov 19 '18 at 19:34
  • I just traced in to see. The requery takes me back to the record I added. So my move by key could be redundant. Not that I will remove it, as I put it there for a reason at one time. But I do all my database stuff with dynaset, not snapshot. And that may have something to do with it? – lakeweb Nov 19 '18 at 19:44
  • @lakeweb -- I've been trying using dynasets as well as snapshots, and no dice either way – LThode Nov 19 '18 at 19:46
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/183909/discussion-between-lakeweb-and-lthode). – lakeweb Nov 19 '18 at 20:09
  • @EdwardClements it turns out that I had some bugs that were causing the query condition to not catch the newly added row -- fixing those fixed the problem, it seems. – LThode Nov 20 '18 at 16:43
  • Thanks for the info, glad to hear that your issue was resolved – Edward Clements Nov 20 '18 at 16:59
  • @EdwardClements -- well, we're back to square one here. As it turns out, the approach with .Requery() was doomed to fail from the start due to the requirement that this code be able to operate on a multi-row recordset....and even with all the key fields in the query set in the row before the first Updated call, trying to do the AddNew/Update/Edit/Update sequence tosses the "Invalid cursor state" error. Am I simply at the limit of what CRecordset can do? – LThode Jan 14 '19 at 18:17
  • This was from a while back, I am trying to get the details of your problem -- why do a `Requery` instead of re-opening `aRecordset.` with the newly added values? – Edward Clements Jan 14 '19 at 18:34
  • @EdwardClements -- I would have to extract the query from the recordset and reopen it. Furthermore, there's the question of whether the parameters on the recordset would need to be re-setup as well...and it also would yield the same issue that makes Requery() turn out to be a non-starter (i.e. the cursor position reset) – LThode Jan 14 '19 at 18:47

0 Answers0