-
May 30th, 2006, 01:39 AM
#1
Thread Starter
Addicted Member
how to use BeginTrans, CommitTrans, & RollBackTrans?
hi...
i just came across these methods... BeginTrans, CommitTrans and RollBackTrans... i really have no idea on how to use this..
can anybody teach me how? or give me a simple program having these methods?
i'm using vb6 and mysql...
thanks .in advance... =)
Last edited by jeanette_db; May 30th, 2006 at 01:44 AM.
-
May 30th, 2006, 01:57 AM
#2
Fanatic Member
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
This is for database transactions. Before you start writing data to the db, you can BeginTrans (from the db object). should you get an error during the data writing, you can RollbackTrans in your error handler. If you wrote all the data without errors, you CommitTrans.
An example would be a bank. Say you want to transfer $100 from 1 account to another. The db deducts this $100 from the first account but suddenly, something goes wrong. Now there's $100 missing because the second account has not been credited. The transactions checks that everything is fine BEFORE making the actual changes to the db. If soething is not right, it doesn't make the changes.
My work doesn't really require that I use transactions, so I haven't used it for a long time. But now that you mentioned it, I can think of 1 or 2 places I can utilize it.
r0achâ„¢
Don't forget to rate the post
-
May 30th, 2006, 02:09 AM
#3
Thread Starter
Addicted Member
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
i see... that's clear...
can you give me a simple program with these methods?...
i tried using it... but an error occurred...
it's 'no transaction active...' blah blah blah....
can you help me with this?
-
May 30th, 2006, 02:21 AM
#4
Fanatic Member
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
OK, well...
I can't quite remember, but I think you need to use it in an ActiveX DLL, and you need to set the dll's MTSTransactionMode to RequiresTransaction, NewTransaction or RequiresNewTransaction depending on your needs. Also, you would have to write it to be used in Microsoft Transaction Server. As far as I can remember.
r0achâ„¢
Don't forget to rate the post
-
May 30th, 2006, 02:21 AM
#5
PowerPoster
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
you can do something like this
VB Code:
oConn.BeginTrans
sSQL = "INSERT INTO tblCashSales (TransactionID,Cashpayment) VALUES " & _
"('" & txtTransactionID.Text & " ','" & CCur(txtAmountDue.Text) & "')"
oConn.Execute sSQL
For i = 1 To lstStocks.ListItems.Count
sSQL = "INSERT INTO tblSales (transactionID, Code,QuantitySold," & _
"SellingPrice,Discount,DateSold,TimeSold) VALUES " & _
"('" & txtTransactionID.Text & _
"','" & lstStocks.ListItems.Item(i).Text & _
"','" & lstStocks.ListItems.Item(i).SubItems(2) & _
"','" & lstStocks.ListItems.Item(i).SubItems(3) & _
"','" & lstStocks.ListItems.Item(i).SubItems(4) & _
"','" & Date & _
"','" & Format(Time, "hh:mm:ss AM/PM") & "')"
oConn.Execute sSQL
Next
oConn.CommitTrans
-
May 30th, 2006, 02:27 AM
#6
Fanatic Member
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
I haven't quite looked at this, but here's a link
r0achâ„¢
Don't forget to rate the post
-
May 30th, 2006, 02:28 AM
#7
PowerPoster
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
BTW, Begintrans and commitrans are usually used when there are more than one sql statement that you would like to execute.
-
May 30th, 2006, 02:59 AM
#8
Thread Starter
Addicted Member
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
Originally Posted by r0ach
I haven't quite looked at this, but here's a link
is it ok to use 'workspace' if im using ADO?
-
May 30th, 2006, 03:02 AM
#9
Fanatic Member
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
r0achâ„¢
Don't forget to rate the post
-
May 30th, 2006, 03:08 AM
#10
Thread Starter
Addicted Member
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
Originally Posted by Simply Me
BTW, Begintrans and commitrans are usually used when there are more than one sql statement that you would like to execute.
how about rollbacktrans? what's used if there is only one sql statement to be executed or aborted? is it advisable to used committrans and rollbacktrans?
AND...
instead of assigning the sql statement to a variable before executing it:
SQLstring = "insert into...."
dbconnect.execute SQLstring
i used this code (instead):
con.execute "insert into..."
then i placed an 'if else statement' to either abort or execute the statement... but the error "no transaction active...." occurred...
what's wrong?
-
May 30th, 2006, 03:08 AM
#11
Fanatic Member
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
I think you should follow Simply Me's example. I'd just add some error handling:
VB Code:
On Error Goto Error_Handler
oConn.BeginTrans
sSQL = "INSERT INTO tblCashSales (TransactionID,Cashpayment) VALUES " & _
"('" & txtTransactionID.Text & " ','" & CCur(txtAmountDue.Text) & "')"
oConn.Execute sSQL
For i = 1 To lstStocks.ListItems.Count
sSQL = "INSERT INTO tblSales (transactionID, Code,QuantitySold," & _
"SellingPrice,Discount,DateSold,TimeSold) VALUES " & _
"('" & txtTransactionID.Text & _
"','" & lstStocks.ListItems.Item(i).Text & _
"','" & lstStocks.ListItems.Item(i).SubItems(2) & _
"','" & lstStocks.ListItems.Item(i).SubItems(3) & _
"','" & lstStocks.ListItems.Item(i).SubItems(4) & _
"','" & Date & _
"','" & Format(Time, "hh:mm:ss AM/PM") & "')"
oConn.Execute sSQL
Next
'// Everything is ok. Commit the changes
oConn.CommitTrans
Error_Handler:
'// An error occurred. Do not commit changes
oConn.RollbackTrans
r0achâ„¢
Don't forget to rate the post
-
May 30th, 2006, 03:26 AM
#12
Thread Starter
Addicted Member
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
how can you rollback a transaction if has not yet been executed?
dont you think there's something wrong with the code??
in my program... i need to execute a statement first then if it has an error, the program will display the error and rollback the transaction...
here's the code in unix-informix:
begin work
insert into x_mafmast values
(grt_mafnmds.class, grt_mafnmds.te, grt_mafnmds.stock_num,
grt_mafnmds.stock_cd, grt_mafnmds.ui, grt_mafnmds.stat,
grt_mafnmds.srce, grt_mafnmds.std_pqty, grt_mafnmds.std_pui,
grt_mafnmds.mfg, grt_mafnmds.pn_indic, grt_mafnmds.part_num,
grt_mafnmds.desc_main, grt_mafnmds.desc_ext,
grt_mafnmds.trans_date, 0, 0, 0, " ", " " , " ", 0,
" "," ", 0, 0)
if sqlca.sqlcode = notfound or status < 0 then
error sqlca.sqlcode using "-<<<<", " has occured.(x_mafmast)"
attribute(yellow,blink)
sleep 3
rollback work
else
commit work
end if
-
May 30th, 2006, 03:42 AM
#13
PowerPoster
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
Originally Posted by jeanette_db
how can you rollback a transaction if has not yet been executed?
dont you think there's something wrong with the code??
in my program... i need to execute a statement first then if it has an error, the program will display the error and rollback the transaction...
You read what was posted in post #2 about the rollback and look at the edited sample in post#11 particularly the error handler part
-
May 30th, 2006, 03:52 AM
#14
Fanatic Member
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
I don't know anything UNIX related, but this is (sortof) what it will look like in vb:
VB Code:
On Error Goto Error_Handler
oConn.BeginTrans
'// funny statement goes here
sSQL = "... statement ..."
oConn.Execute sSQL
oConn.CommitTrans
On Error Goto 0
'// exit the sub/function here
Error_Handler:
oConn.RollbackTrans
'// notify user of error
'// end sub/function
You don't need an If statement because if there's an error, the code will immediately go to the error handler label and start executing from there.
Oh, I just thought of something else; If you have an error handler, and you get the message mentioned above, it might be that the error occurs between On Error and BeginTrans somewhere (if there's code). So you try to rollback a transaction that's not started yet.
Last edited by r0ach; May 30th, 2006 at 03:56 AM.
r0achâ„¢
Don't forget to rate the post
-
May 30th, 2006, 05:04 AM
#15
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
what's used if there is only one sql statement to be executed or aborted?
If there is only one SQL statement, is is effectively in a transaction anyway (either it all works, or it all fails), so you don't actually need to write any code to implement it.
-
May 30th, 2006, 05:51 AM
#16
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
To further what Si just said (and others as well)...
Yes - ADO executes all statements in an IMPLIED transaction - if an error occurs the statement is rolled back (a SQL level error that is).
The only reason to BEGINTRANS on the client side is because you are going to be executing a series of SQL statements from that client and want to insure that all of them go in cleanly. The banking example given describes that situation.
There is huge danger in using BEGINTRANS on the client side. If for instance you have a MSGBOX in your error handler - before the ROLLBACKTRANS - then you leave an expensive "lock" on the table/DB waiting for the user to click OK. Also - the goal is to have transactions that last milliseconds - so take care when starting a TRANSACTION that might last for some time...
-
Jun 15th, 2022, 03:42 AM
#17
New Member
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
Hello all,
In the same frame as this question, here is a collateral issue. I am using Excel 365 and its VBA.
I am trying to write a function which executes 4 SQL for each line in a text file, containing between 10 and 100 lines. I would like to wrap these 4 SQLs into ONE transaction, so they either pass or fail all together.
Therefore, can I include BeginTrans and CommitTrans into a loop wrapping my 4 SQLs?
Actually I tried this but it fails at the second iteration of the loop, which makes me think that one can use BeginTrans/CommitTrans only ONCE per procedure execution! Is this true??? Should I call a proc into my loop so that the whole BeginTrans/SQL1/SQL2/SQL3/SQL4/CommitTrans/IfErrorRollback gets executed only once per procedure call? Is this required?
I hope my question is understandable.
Last edited by stefano.gatto; Jun 15th, 2022 at 04:03 AM.
-
Jun 15th, 2022, 04:10 AM
#18
Addicted Member
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
With DAO you could have nested transactions but I think ADO only allows one open transaction.
If you have one global connection object that you use for everything and keep open while you're program is running you need to be even more especially careful that every begintrans is inevitably followed by a commit or a rollback.
Also don't have any user interaction in the middle of a transaction or any call to Doevents.
Basically you don't want the possibility for any other code to execute except what is explicitly between the begin and the commit/rollback.
Don't put any calls to stuff like a modal form where user could wander off leaving a transaction open indefinitely.
If you have background stuff going on in a timer that's, say, firing off emails to a list of people and updating the database then if the user later comes back and cancels the transaction, all the stuff that happened in the timer gets rolled back too.
So the emails have been sent but there's no record of it.
Last edited by vbrad; Jun 15th, 2022 at 04:17 AM.
-
Jun 15th, 2022, 04:44 AM
#19
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
Wow... a 16 year old thread revived.
Talk about necromancy........
Why in blazes would you need a loop for the SQL?
You just need a Loop for the Lines of the Textfile
Start Loop
Read a Line from textfile
Start the Transaction
Concat all 4 SQL's into one single line, delimited with the Statement-Delimiter of your Database, and execute it.
If No Error Then Commit Else Rollback
End loop --> Exit Loop when EOF of TextFile
Last edited by Zvoni; Jun 15th, 2022 at 04:53 AM.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Jun 15th, 2022, 04:48 AM
#20
Addicted Member
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
Crikey, I thought it was only asked yesterday!
-
Jun 15th, 2022, 06:20 AM
#21
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
@vbrad, you are correct, the thread was resurrected from the dead by stefano.gatto
-
Jun 15th, 2022, 06:40 AM
#22
New Member
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
Hello vbrad and thank you very much for your answer! Yes, i am using DAO.
I never meant I needed to nest transactions... All I am doing looks like the code below (nothing is nested, ie only ONE transaction happens at every single second, never more):
As you can see there nothing that gets into the game between BeginTrans and CommitTrans, except SQLs!
Code:
Dim gwksCRM As Workspace
Dim gdbsCRM As Database
Sub LoadRecords()
Open "C:\toto.txt" For Input As 1
Do Until EOF(1)
On Error GoTo ErrorTrans
Line Input #1, strLine
gwksCRM.BeginTrans
gdbsCRM.Execute "INSERT INTO activity (duedate, comment) VALUES (#3/4/2022#,'This is test1')"
gdbsCRM.Execute "INSERT INTO activity (duedate, comment) VALUES (#3/5/2022#,'This is test2')"
gdbsCRM.Execute "INSERT INTO activity (duedate, comment) VALUES (#3/6/2022#,'This is test3')"
gdbsCRM.Execute "INSERT INTO activity (duedate, comment) VALUES (#3/7/2022#,'This is test4')"
gwksCRM.CommitTrans
GoTo EndOfLoop
ErrorTrans:
gwksCRM.Rollback
EndOfLoop:
Loop
End Sub
At the second iteration I am getting error 3034 when executing "gwksCRM.CommitTrans", saying that I should not try to Commit something that was never "Begintrans"ed !!!
Last edited by stefano.gatto; Jun 15th, 2022 at 12:49 PM.
-
Jun 15th, 2022, 06:42 AM
#23
New Member
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
Well sorry about that, but I am using fresh Excel 365, from 2022, and VBA which comes with it! Is DAO not current anymore?
-
Jun 15th, 2022, 06:46 AM
#24
New Member
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
Hello Zvoni and thank you for your answer!
I am going to test what you say, although it's not that different from what I do and let you know.
Have a nice rest of the day!
-
Jun 15th, 2022, 06:52 AM
#25
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
Originally Posted by stefano.gatto
Well sorry about that, but I am using fresh Excel 365, from 2022, and VBA which comes with it! Is DAO not current anymore?
It's not "current" since a decade and more
Use ADO
Originally Posted by stefano.gatto
Hello Zvoni and thank you for your answer!
I am going to test what you say, although it's not that different from what I do and let you know.
Have a nice rest of the day!
No, you're not, since you mention a "second" iteration of the loop
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Jun 15th, 2022, 11:41 AM
#26
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
Originally Posted by stefano.gatto
Hello all,
In the same frame as this question, here is a collateral issue. I am using Excel 365 and its VBA.
I am trying to write a function which executes 4 SQL for each line in a text file, containing between 10 and 100 lines. I would like to wrap these 4 SQLs into ONE transaction, so they either pass or fail all together.
Therefore, can I include BeginTrans and CommitTrans into a loop wrapping my 4 SQLs?
Actually I tried this but it fails at the second iteration of the loop, which makes me think that one can use BeginTrans/CommitTrans only ONCE per procedure execution! Is this true??? Should I call a proc into my loop so that the whole BeginTrans/SQL1/SQL2/SQL3/SQL4/CommitTrans/IfErrorRollback gets executed only once per procedure call? Is this required?
I hope my question is understandable.
Excel is the wrong Tool for that
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Jun 15th, 2022, 12:42 PM
#27
New Member
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
Originally Posted by ChrisE
Excel is the wrong Tool for that
You are right, but it's not my choice...
Last edited by stefano.gatto; Jun 16th, 2022 at 02:42 AM.
-
Jun 15th, 2022, 12:43 PM
#28
New Member
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
Yes, it's the second iteration, happening because there is a second line in the text file opened (#1)
-
Jun 15th, 2022, 12:46 PM
#29
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
Thing is ... Excel may not support transactions. Sure, the method is there, but it's for supporting transactions on databases. I don't think the Excel provider supports it... and that may be (may) the reason it isn't working.
-tg
-
Jun 15th, 2022, 12:59 PM
#30
New Member
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
Originally Posted by Zvoni
Wow... a 16 year old thread revived.
Talk about necromancy........
Why in blazes would you need a loop for the SQL?
You just need a Loop for the Lines of the Textfile
Start Loop
Read a Line from textfile
Start the Transaction
Concat all 4 SQL's into one single line, delimited with the Statement-Delimiter of your Database, and execute it.
If No Error Then Commit Else Rollback
End loop --> Exit Loop when EOF of TextFile
I did exactly this and am getting error 3034 at the second iteration of the loop you are describing above. The step where the error occurs is where you wrote "If No Error Then Commit Else Rollback". It does not want to Commit!
Did you personally ever experienced BeginTrans/CommitTrans into a loop?
-
Jun 15th, 2022, 01:22 PM
#31
New Member
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
Hello all - I just found out what was wrong in my code. I commented out one line and every thing runs smoothly. It is:
Code:
Set rstLastID = gdbsCRM.OpenRecordset("SELECT @@IDENTITY")
This line is between my first and my 2nd SQL, because I need the id created by the 1st SQL to feed my 2nd SQL (it's an audit trail).
So, apparently, I shouldn't use OpenRecordset() in the middle of the 4 Execute(INSERT) which are committed all together. I have no idea why! Actually it works in the first loop but not in the second!
-
Jun 15th, 2022, 02:00 PM
#32
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
this is how to use ADO BeginTrans.... etc..
with the ID's of other Tables
Code:
Private Sub Command1_Click()
Dim sSQL As String
Dim KT_ID As Long
Dim AD_ID As Long
On Error GoTo Fehler
Cn.BeginTrans
'Insert to first Table
sSQL = "Insert Into Kontakt (KT_Ort) Values ('Hamburg1')"
Cn.Execute sSQL
KT_ID = GetNewID(Cn)
'Insert to second Table
sSQL = "Insert Into Adresse (AD_Strasse) Values ('Hafengasse1')"
Cn.Execute sSQL
AD_ID = GetNewID(Cn)
'insert to 3rd Table and use the ID's from the first and second Insert's
sSQL = "Insert Into Person (PE_Name, PE_KT_ID, PE_AD_ID) " & _
"Values ('Willi', " & KT_ID & ", " & AD_ID & ")"
Cn.Execute sSQL
Cn.CommitTrans
Exit Sub
Fehler:
'Note if the Rollback occurs the ID's are gone !!
Cn.RollbackTrans
End Sub
Public Function GetNewID(Cn As ADODB.Connection) As Long
Dim Rs As ADODB.Recordset
Dim NewID As Long
Set Rs = New ADODB.Recordset
Rs.Open "Select @@Identity As ID", Cn, , , adCmdText
NewID = Rs.Fields("ID").Value
Rs.Close
Set Rs = Nothing
GetNewID = NewID
End Function
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Jun 15th, 2022, 02:45 PM
#33
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
Originally Posted by stefano.gatto
Hello all - I just found out what was wrong in my code. I commented out one line and every thing runs smoothly. It is:
Code:
Set rstLastID = gdbsCRM.OpenRecordset("SELECT @@IDENTITY")
This line is between my first and my 2nd SQL, because I need the id created by the 1st SQL to feed my 2nd SQL (it's an audit trail).
So, apparently, I shouldn't use OpenRecordset() in the middle of the 4 Execute(INSERT) which are committed all together. I have no idea why! Actually it works in the first loop but not in the second!
I'm guessing you never closed the recordset afterwards either.
Because it left open a line... so when you then try to use the transaction again, it's getting a busy signal. If all you're after is the new number, get it, then close the underlying recordset. That should then allow the transaction to happen on the next go around. OR... get your ID inside your transaction (can't guarantee it'llwork, but it can't hurt at this point either).
-tg
-
Jun 15th, 2022, 02:55 PM
#34
New Member
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
Hello ChrisE,
Thank you very much for your constructive answer and solution! It's true that, by isolating the OpenRecordSet, which I forgot to Close, into a dedicated procedure would clean up everything I left open, even if by mistake!
Vielen Dank!
Stefano
Last edited by stefano.gatto; Jun 16th, 2022 at 02:46 AM.
-
Jun 15th, 2022, 02:57 PM
#35
New Member
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
Hello techgnome and thank you very much for the heads up about closing Recordsets... I am going to test your suggestion, but it looks great!
What did you mean by "get your ID inside your transaction"?? Can I avoid opening a Recordset to get that ID?
-
Jun 15th, 2022, 03:03 PM
#36
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
No, but if you should still be able to get it from inside the transaction... but still remember to close the recordset.
-tg
-
Jun 15th, 2022, 03:18 PM
#37
New Member
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
Originally Posted by techgnome
No, but if you should still be able to get it from inside the transaction... but still remember to close the recordset.
-tg
Done! Thank you so much!
Code:
Set rstLastID = gdbsCRM.OpenRecordset("SELECT @@IDENTITY")
lngLastId = rstLastID.Fields(0).value
rstLastID.Close
-
Jun 15th, 2022, 03:20 PM
#38
New Member
Re: how to use BeginTrans, CommitTrans, & RollBackTrans?
Originally Posted by stefano.gatto
Hello techgnome and thank you very much for the heads up about closing Recordsets... I am going to test your suggestion, but it looks great!
What did you mean by "get your ID inside your transaction"?? Can I avoid opening a Recordset to get that ID?
Oh ok I just got what you mean by "get your ID inside your transaction". You mean between BeginTrans and CommitTrans!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|