Visual Basic Forum for Visual Basic Programmers VB

[p2kpradeep]

Hi.

No other issue in my 8 years of programming practice (most of which has been with VB6) ever presented an experience as challenging as I am about to describe below. I hope some of my readers would be able to appreciate the problem and share their insights with me.

While I have successfully programmed and implemented several database applications, I have never used (nor do I ever plan to use) class modules in VB6 (my reasons don't matter in this context). Instead, I use general modules to represent entities.

In general, I structure my code as follows:
Every table will have a representative general module (modTab). The module will declare one public variable corresponding to each column in the table (colVar). The module will also define functions to perform the required manipulations on the data (I don't use stored procedures because most of my programs also require to work with databases that don't support them). Of particular interest here is the function doSave() that inserts a record into the corresponding table and returns True/False based on whether the insert was successful or not. The doSave() function is built with the necessary error handling routines to catch database/application errors and notify the user about them before returning False to the caller.

Sample snippet showing an insert operation (Ex: frmTab.cmdSave_Click()):

[dougthomas]

Hi Pradeep,

Not sure whether this is going to help or not but it may help to step back from the problem and take a new look:

Looking at the logic of the problem:

(1) You have code that has been proved to work OK (your modTab) in lots of applications. You've retested them in the same environment that this problem is occurring in.

(2) Only this implimentation is causing problems

(3) The "special" thing about this implimentation is 6 Tables are being updated.

(4) The strange behaviour happens irrespective of the Database you use.

(5) The code is not reporting any processing errors.

Given points (1), and (4) you can, for the moment, rule them out as being the cause of the problem. Which leaves (2) and (3).

It is unlikely that there are any "features" common to ALL the Database systems you use that would "stop" you from updating 6 tables in one transaction, so you can rule out (3).

This just leaves point (2) - the error lies in this implimentation.

Given that no processing errors are being reported the code *must* be doing what it is being told to do. Therefore you could conclude that it is being told erroneously, somewhere, to committ the transaction.

I would go through your code very carefully (I assume you have an Option Explicit so mis-spelt variables would have been detected). Do you, perhaps copy and paste the If - Then - Else - Endif sections and change the modTab number. If so, have you missed a change somewhere ? Have you pasted a 'bit' into the wrong place ?. I suspect that if you go through the code you will find a 'spare' modMain.cnn.CommitTrans that shouldn't be there, or an "If modTabn.doSave Then " with the wrong value for "n". (Could be in more than one place as well !)

As I said, this may help, hope it does.

Good luck

Regards
Doug

[p2kpradeep]

Hi!

Thank you for your response.

Firstly, I must congratulate you on your superb method of analysis. I've not known many who could match such clarity of thought. I am sure that discussing such "strange" implementation problems with you would certainly enrich us both in more ways than one. In fact, I'd love to work with someone like you.

Secondly, I must admit that when I had written the original thread of this discussion, I had already realized (1)/(4) and (2)/(3) of what you have pointed out. In fact, that was the reason why I called it a "strange behaviour".

Maybe I was not very clear while I described my module naming scheme. When I used the example modTab/frmTab, I meant the table name prefixed with mod/frm. For example, modEmployee and frmEmployee would be the module and form corresponding to the table Employee. Similarly, modClient and frmClient would correspond to the table Client. There are no numeric postfixes. Hence, there cannot be any confusion regarding the form-module-table relationship.

Yes, I am ever well aware of the Copy-Paste devils. They are my first suspects when faced with such phenomena. I hate to admit that they are innocent in this case.

Nevertheless, your last paragraph was quite interesting. It showed me some new possibilities (with respect to module naming) which I shall have to consider seriously.

The irritation caused by this problem was so much that I've moved on to another project, keeping this one aside until I can figure something out. The more I think of it, the more I believe that it's just a little jinx. A small bug. Something so small that we aren't able to see it. Reminding us that no matter how much programming experience we gather, we can never be perfect. Don't we blame Microsoft for so many silly bugs in their products?

Anyway, I'm keeping this discussion open. Please do write back if you think of any new (even silly) ideas.
_________________
Warm regards,
PRADEEP K.

[dougthomas]

Hi again Pradeep,

(Thanks for your comments - I did a course a number of years ago called "a Structured Approach to Management" which taught me how to solve problems - it's the best course I've ever been on )

Using my methodology, given that you've been through the code with a fine tooth comb, we'll have to look at some of the things we initially dismissed or "dismissed for now". I would still say that the liklihood of all the Database Systems you are using of not supporting a 6 table update is very remote. So, for now, I would still discount that.

I would take a moment to backtrack and make sure that the problem actually exists ! No doubt you have test data that is designed to fail so have you checked that the test data is "correct" in the sense that it will make an update fail ? (Stupid things like not having "duplicates not allowed" on Key definitions which means that an Insert you expected to fail will, in fact, work !)

When you're happy with your test data, take a look at why the 1st, 3rd and 4th tables are being updated and not the 2nd, 5th and 6th ? What's "special" about Tables 1,3 and 4 or 2, 5 and 6 ? Bearing in mind the cascading nature of your logic it seems a *very* illogical combination of Tables being updated or left alone. Perhaps checking the Table definitions, especially the Keys, may be worthwhile. (After all, the Database and Tables are the other things unique to this implementation) Locking Strategy may be another thing to look at although I would expect an error to be raised if you were trying to insert to, or update a locked table.

If you get no joy from the above, you may have to ask whether there is a circumstance where the .doSave function is returning True when is should be False (this is still on the grounds that something is telling the Database to commit the transaction) Whilst the code is working perfectly well in other applications, it is just possible that a condition (or multiple conditions) has / have arisen in this one, which causes an incorrect return from the .doSave function. I assume that in "transaction mode" the return from the Insert or Update is actually a time dependent thing. i.e. it's more of an "if asked you to do this Insert / Update now, would it work ?" I wonder if anything changes between getting that result and the update actually being performed (ie at the Commit)

The fact that you've stepped through the code checking that the values returned are consistant with a RollBack implies that a doSave might be wrong. - It's a long shot. All you see on the step through is doSave is True or False, not whether it should be True or not.

Now, you did say I could be "silly", have you checked things like RollBack buffer sizes and number of, (if you can control them). What if the buffer(s) get full before you have committed the transaction ? Does it do "something useful" like auto commit by default ? It doesn't explain why certain tables get updaed and some don't unless the "something special" about Tables 1, 3, and 4 is the number of records affected. Is there any way you can put transaction logging on so you can actually see what's in the buffer when is it committed ? (That's my mainframe days talking - we had thousands of miles of Mag. Tape of transaction logs!)

Moving on from "silly" to "stupid" is there any problem with "delayed write" ie for some reason the information is never actually getting written to the physical device ?

I'll stop now as I'm drifting away from a logical methodology !

Good luck

Please let me know how you get on, this is really interesting me !!

Regards
Doug

[p2kpradeep]

Hi again, Doug.

The probability of database systems (like Oracle and SQ Server) not supporting 'n' table inserts/updates? Interesting as it sounds, never in my programming or teaching career have I ever been exposed to such a possibility.

For instance, I had once been an academic participant in an enterprise solution being tested for an online stock trading client. Studying their system, I had come across 2 distinct scenarios:

(1) A transaction inserting/updating to several tables in parallel

(2) A transaction inserting/updating to several tables in sequence (cascades)

Both scenarios had been duely taken care of, and the system was operational.

I admit though, that they had used Stored Procedures in SQL Server to perform such transactions. In fact, the whole transaction was managed (committed/rolled back) within the stored procedure. The application would call the procedure and only receive a parameter indicating the success or failure of the transaction as a whole. Or an error, if any was thrown.

However, my deduction is that if such stored procedures work, then the database engine supports such operations. Hence, it should have supported my transaction as well. In the event that for some reason it failed to do so, I believe it is fair to expect that it would complain with an error message. After all, these database systems are known to support mission-critical systems.

You suspected autocommit behaviour. Trust me, that had never occured to me before. I checked it out. With and without autocommits. And in any case, autocommit (under 3 database engines) couldn't be partial to an illogical set of tables.

I even implemented an event logging mechanism within the doSave function of each module. Nothing to learn from there. All events show clear.


Suspects:

My latest primary suspect is Memory. You know that I hold public variables (corresponding to columns in the associated table) in each module until the doSave is invoked. So, I'm sure that you can appreciate my reasons for suspecting a little confusion in the RAM affecting the return value from doSave.

There's another reason for my suspecting memory. If you remember, you had once before answered my question about the limitations on the number of parameters a user-defined function can accept. That was my first posting on this forum. I had posed this question because I had suspected one of my functions that takes 25 parameters (one of the 6 tables that participate in this transaction has 25 columns). In answer to my question, you had ventured that there are no documented limitations with this regard, but maybe 64K... Which brought me back to describe my problem at length.

You (and now I, as well) suspect that there could be a problem with "delayed write". Now, this is something that has arrested my attention. I am not clear as to the exact reasons that cause this problem, leave alone attempt a solution to it.

Having surfed the net for online resources, comments and experiences on similar lines, I gather that yet another possible suspect could be the VB 6 event model. Nobody seems to be really sure about how the event model works. There are many who complain a lot about it. I plan to verify the facts/fallacies of their arguments. I have scheduled myself to conduct a few very fundamental experiments tonight. I shall keep you posted about them.
_________________
Warm regards,
PRADEEP K.