Solved

VFP complains about Cursors in Table Buffering Mode when they're NOT

Posted on 2014-04-16
7
635 Views
Last Modified: 2014-04-26
I have a method which prepares non dbc tables for transactions. Most of the time it works as expected.

I am well aware of the dangers of doing that within transactions or on tables already in table buffering mode. So I test for those conditions before attempting to run the command

Debugging confirms that the Buffering for the relevant table at the point the command is applied is 1 (no buffering)

Despite which it still fails with the error "Command cannot be issued on a table with
cursors in table buffering mode "

I've tried setting the buffering to 3 (optimistic Row) and I've ensure no related table are open in the forbidden mode.

Nada.

Suggestions?
0
Comment
Question by:mjacobs2929
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 42

Expert Comment

by:pcelba
ID: 40004311
Even the code sample in VFP help shows transaction on a table in buffering mode 5... so your assumption is not correct probably.

To be sure the transaction can start you should work in a "clean environment". It means open all necessary tables, BEGIN TRANSACTION, do all necessary updates, END TRANSACTION, close tables. Nothing else.

Because VFP isn't 100% bug free the transaction started in a middle of some more complex process can fail for whatever reason...

Also remember the fact transaction affects ALL tables and cursors open in the current data session (except free tables) so you should close all other tables or you should check buffering for all other tables if you still think the buffering causes the problem (I also cannot say buffering is OK in all cases)...
0
 

Author Comment

by:mjacobs2929
ID: 40005403
thanks for the reply.

It's not my "assumption" - it's VFP's own guidance!

"If row buffering is enabled, then a table update is performed if the free table or free cursor has pending changes before the free table or free cursor is made to support transactions. You cannot use MAKETRANSACTABLE( ) to enable transactions for a free table that has table buffering enabled."

and don't forget, I use this method to PREPARE for transactions, so it's definitely not taking place inside a transaction. (and the first line of the method tests TXNLEVEL() just in case)

However, I think your comments implying  that the state of ALL tables affects the MAKETRANSACTABLE, together with the reminder that VFP isnt 100% bug free, probably brackets the real problem.

I certainly have a number of other tables open whenever I call the method but NONE are in Table Buffering mode.

There must be some rare condition it doesn't like. I'm debugging as we speak and I've just tested my transactions for 4 different processes and they all worked perfectly. It fell over on the fifth with this irritating and, I'm guessing, misleading error message...
0
 
LVL 42

Expert Comment

by:pcelba
ID: 40005451
You could implement the transactional processing in a private data session. If you create a Session class which will do the whole process of opening files, making changes, and closing files then your environment should be relatively clean with lower possibility of some mistake.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Accepted Solution

by:
mjacobs2929 earned 0 total points
ID: 40012341
Think I've cracked the problem though it isn't a definitive answer. I've found two things relevant. First if you make any table Transactable by default any time you open it, most of the problems disappear.  And, as I've seen no performance hit as a result of defaulting to transactable, you might as well do it for every table you open.  (at least for any table which may be involved in transactions)

Second, even if you do that, I have found one (undocumented - so far as I can tell)  condition (at least) which turns table buffering on, even for non dbc free tables, and that is a freeform browse (as opposed to a properly set up grid). There are a couple of places in my software, where I couldn't be arsed to set up a grid (the user just needs to pick a record from a handful of possibles and I just presented the options as a BROWSE FIELDS NOMODIFY)

As soon as the Browse fires, Table Buffering gets switched on and, of course, if you then try to do anything forbidden in that mode, the error smacks you in the face. The solution (for those who still can't be arsed to run the grid) is to test for the buffering level immediately after the browse and switch it off.

In conclusion:
1 I think pcelba is right to remind us that VFP is not bug free. At the very least, this "table buffering " block is NOT restricted to table buffering (in my tests, the block activates in ANY active buffering mode), and that is a bug by any definition

2 Making tables transactable every time you open them seems to cure 90% of the problem but

3 you still have to watch out for gotchas that can switch on buffering outside your control (eg, at least, free browsing) and either avoid them, or write code that immediately disables the buffering after the (browse).
0
 
LVL 42

Expert Comment

by:pcelba
ID: 40012454
Mjacobs, you've investigated the most of your problem here so it is not necessary to divide points etc.

MAKETRANSACTABLE() after the table opening is a good option or it solves some VFP problems at least...

The Buffering property switching is suspicious and I've never seen it before and I am unable to simulate it under simplified conditions on my PC. But it is very good to know about...

Thanks for your input!
0
 

Author Comment

by:mjacobs2929
ID: 40014388
just on the question of buffer property switching, I haven't investigated which conditions must be present to cause that behaviour. I suspect it the general networking options (like Set Multilocks On, etc). I might find time, once I've dealt with the present re-coding project, to isolate the exact conditions and post a follow up.
0
 

Author Closing Comment

by:mjacobs2929
ID: 40024348
Awkward points allocation problem. I'm happy for pcelba to have the points, or at least half of them for preventing me going down certain blind alleys but obviously he hasn't provided the solution, which is largely spelt out by my last post. Please sort in whatever way is available and acceptable.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
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.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

737 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