SQL Related Articles: Transactions and Prop:SQL
2003-08-13 -- Dan Pressnell and others
Dan Pressnell wrote:
Hi, everybody.
How does Clarion's LOGOUT/COMMIT/ROLLBACK affect prop:sql and vice versa?
Is there a definitive answer?
Dan
Richard Bryce wrote:
Dan,
It all seems to work rather well with MSSQL 2000 and the latest version of
CW55
I have code inside a Clarion Logout() statement that is using both SQL
Select and SQL Update with Prop:SQL coding and it all seems to be working
rather well.
Comments from others would be appreciated for my benefit also as I am not
100% sure as to exactly how the clarion runtime environment handles this.
All I know is that it works!!!
Shawn Mason wrote:
But when you have to rollback the backend would know nothing about the
Transaction Frame because you have basically bypassed it using a Prop:SQL.
Why don't you run Trace Log and see if the driver is actually wrapping it
all inside the Transaction. I haven't checked this out myself, but I have
serious doubts about this working correctly . but, please make a
believer out of me.
Rick Martin wrote:
Shawn, et.al.
The Clarion Logout issues a Begin Transaction on the SQL Server. By
definition a transaction on the server automatically includes all of the
tables referenced from the Begin Transaction until the End Transaction or
Rollback. So updates performed by a prop:Sql statement are definitely
included in the transaction regardless of what table the statement
references. In fact the Logout statement can be on pretty much any table.
For example both the biditem and project tables are part of this
transaction:
Open(Project,42h)
Assert(Not ErrorCode())
Open(BidItems,42h)
Assert(Not ErrorCode())
Logout(.1,Project)
Assert(Not ErrorCode())
PRJ:PRJUnique = 3648
Get(Project,PRJ:UniqueKey)
Message('Past first Fetch - '&PRJ:Description)
BIM:BIMUnique = 123253
Get(BidItems,BIM:UniqueKey)
BIM:ChgDate += 1
Put(BidItems)
Project{Prop:SQL} = 'Update BidItems Set ChgDateTime = GetDate()-10'
!Note we are updating biditems via Prop:SQL on Project
Project{Prop:SQL} = 'Update Project Set ChgDateTime = GetDate()-200'
Message('Fetched - '&PRJ:Description)
Rollback
!This rollback cancels changes to both bid-items and project tables.
Close(Project)
Close(BidItems)
Food for thought,
Dan Pressnell wrote:
My testing indicates this effect, with MSSQL at least. (And even the
closing and reopening of a file doesn't terminate or interfere with the
current transaction.)
What I see in the ODBC help file for the MS Data Access Kit indicates leads
me to believe that other SQL systems might well behave the same way. When
you consider that SQL access in Clarion is superimposed on top of memory
structures and procedures that were originally designed for desktop data
files, it can explain why there is some confusion about how this works with
an SQL system, and how the documentation showing the listing of tables in
the LOGOUT procedure (or prop:logout) can be misleading with SQL systems.
But it sure would be nice to have an official answer from SV in any case. I
can imagine that many of us are jumping through hoops that don't need to be
jumped through, and if transactions really are this simple in Clarion with
SQL, then it would help to know it and know that we can rely on it.
Dan
Jim Kane wrote:
You can control the transaction using prop:isolationlevel.
The default isolation level is serializable which can cause table locks
since inserts are blocked. The problem with using CW logout is the locks
are appear immediately after the logout. Although I have not repeated the
experiment with c6, as of c5b what I found is if you do a CW logout of a
file and then put a message statement so stop execution with the logout
active and go to another computer or another copy of the app on the same
computer and try to browse the table, the browse will not fill due to the
locks. I got burned because I used clarion logout() on a system that was
having a lot of data entry with a lot of users (about 500) and browses would
not fill. Eventually when I figured out what was going on, I re-wrote a lot
of the code to use stored procedures and only started the transaction in the
stored procedure. Since the data was already at the server (parameters to
the stored procedure) starting the transaction within the stored procedure,
and lowering the isolation level to read committed reduced the amount of
locking considerably and since then I've made every effort to write stored
procedures instead of using logout when possible to keep locking as brief as
possible and only use the minimum isolation level that is required for what
I'm doing.
If you want more details look in the odbc reference at transactions and it
will explain the odbc implementation in great detail. That is what clarion
uses down to the same constants for levels.
Jim Kane
Printed November 23, 2024, 7:00 am
This article has been viewed/printed 35271 times.
Google search
has resulted in 495 hits on this article since January 25, 2004.