Login
Templates, Tools and Utilities
for Clarion Developers
Templates, Tools and Utilities
|
||
|
|
|
Icetips Article
Back to article list
Search Articles
Add Comment
Printer friendly
Direct link
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 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 Today is April 20, 2024, 5:09 am This article has been viewed 35114 times. Google search has resulted in 495 hits on this article since January 25, 2004.
|
|