Mike Yearwood
Toronto, Ontario, Canada
General information
Category:
Coding, syntax & commands
>>> Spending hours to debug code to find why data got trashed and then not be able to plug in transactions, is just dumb.
>
>Can you give brief examples of how you are retrofitting transactions to (say) a SQL Server RV app? I mean I know the mechanics, just interested in how you typically get it in there if they're not passing all data access through a class. Do you gofish for Tableupdate() (assuming they use it; I've seen SKIP or even go recno() used to commit!) and insert transactions wherever indicated, or try to move everything to a class or ???
First off. If someone asks me about mdot, I will never use a stupid argument like "I don't like how it looks." No one has the right to think something so WRONG. Rights used in that way is offensive to me and I'm left handed.
Please clarify. A class will not aid in transactions. MaxFrame had classes and everything was done on a shared global connection. It was handled by classes. Is that what you mean?
I am talking specifically about having 2 tables that must be kept in sync. Single tables being updated view skip/automatic transactions on sql server is just lazy.
I have 2 views. Updates to them must be completed as a unit of work. That this system has no transactions for such scenarios is again a mark against average programmers. This system uses a 'global' connection handle. It does not use shared connections for views. So SQLExec commands are on one connection. Each view opens on it's own connection, ergo no transactions are POSSIBLE. How stupid. Literally crap, but the company thinks it is good, because like janitors they don't have the education and I don't have the time to explain particle physics to a 5 year old. Queue the peanut gallery.
So, the way around it is to do what I haven't had to do in over 20 years. A single connection is required to make a sql server transaction. The programmers deliberately avoided upgrading from VFP 6. Again, stupid. In VFP 9 or 8 we got the ability to use a view and tell it the connection to use.
Use DBSetProp to make the 2 views share connection. Open the first view. Grab it's connection number. Override the global connection so SQLExecs can be in this connection too. Open the second view, and it grabs the connection of the first.
Requery the views. Set multilocks on, set optimistic table buffering on.
Make bunches of changes to the views.
Set sql server transactions to manual. Begin transaction
if tableupdate(view1) fails set a variable to rollback
if tableupdate(view2) fails set a variable to rollback
if sqlexec(singleconnection,'update some log etc') fails, set a variable to rollback
if the rollback is required
sqlrollback(single connection)
else
sqlcommit(single connection)
endif
set sql server transactions back to auto.
restore the views to non-shared, restore the global connection to what the app expects.
This code is not modular enough - it's mudular. So I have to inject this approach on a case by case basis.
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only