Page 1 of 1
Transaction Processing
Posted: Sun Dec 06, 2009 1:00 am
by David_USA
I have been unable to find code to do Transaction Processing with a database.
Could someone forward the code and testing for proper implementation of a multi user database.
Thank you in advance for your help.
David J. Lamp
Re: Transaction Processing
Posted: Mon Dec 07, 2009 9:38 am
by ale870
Generally speaking, transactions are managed by Database self (there are specific SQL commands to do that).
I think you need to check SQL commands and documentation about you DB.
Which database are you using?
Re: Transaction Processing
Posted: Wed Dec 09, 2009 6:48 am
by David_USA
the transaction will be processed through runrev scripting.
I believe runrev will use the same format of scripting for sqlite, mysql or odbc calls.
The reason for transaction processing is to be able to effect a muti step write to one or more tables in a database. It is important that the state of the database cannot be changed outside of the completion of the muti step process before a commit or rollback which will unlock the database.
I can not find any documentation that establishes the "Begin" to lock the database and prevent potential updating by two processes out of order.
I was hoping to find a sample with the "Begin" and "Commit or Rollback" rev script.
I would think many people have the need for this requirement.
Best Regards,
David J. Lamp
Re: Transaction Processing
Posted: Wed Dec 09, 2009 7:16 am
by sturgis
Look at this thread, theres an example there that might get you started.
http://forums.runrev.com/phpBB2/viewtop ... =12&t=2892
Re: Transaction Processing
Posted: Wed Dec 09, 2009 9:39 am
by ale870
Hello,
I didn'0t work with DB using RunREv, but if you can send SQL commands, you could start / end transaction using SQL server DB commands.
Cheers!
Re: Transaction Processing
Posted: Mon Sep 06, 2010 12:50 pm
by bangkok
nikjerry wrote:I have read this all of the stuff and I just would like to say that I also have the same problem with me as well as I also using the SQL as a DB, but up till I didn't get any of the solution regarding to it.
I don't really understand the issue.
we have one command that executes a SQL query :
revExecuteSQL dbID, dbSQL
So why don't you create the query like :
BEGIN;
INSERT INTO MYTABLE (MYCOLUMN) VALUES('1'),('2'),('3'),('4');
END;
or
BEGIN;
UPDATE MYTABLE SET MYCOLUMN=1 WHERE BLABLA=2
END;
If I remember correctly, It was working fine with SQLlite.
Re: Transaction Processing
Posted: Tue Sep 07, 2010 7:02 pm
by Mark
Bangkok,
You were deceived. The previous message is spam.
Best,
Mark
Re: Transaction Processing
Posted: Tue Sep 07, 2010 8:50 pm
by bangkok
Mark wrote:
You were deceived. The previous message is spam.
Sorry Mark, could you elaborate ????
What do you mean ?
Re: Transaction Processing
Posted: Tue Sep 07, 2010 9:09 pm
by Mark
Bangkok,
First of all, it is really unwise to reply to spam. By replying, you drag the attention of other forum members to the spam message and there's a chance they make the spam attack successful by clicking on the link in the signature. This could even be extremely dangerous, because such a link can lead to a fishing site, which steals your credit card data, twitter password, facebook password, login data for your bank account, and even the login name and password of your computer. Replying to spam is a mistake.
You can recognise spam when you are thinking "I really don't understand the issue" and see a link in the message or the signature. In 99.999% of cases, this indicates spam.
Best,
Mark
Re: Transaction Processing
Posted: Wed Sep 29, 2010 2:55 am
by chris9610
Here is a real example I use in an import.
Code: Select all
revExecuteSQL gConID, "begin;"
repeat for each line aLine in tAllRtnums
put aline into tdata
put char 1 to 9 of tdata into trtnum
put char 10 of tdata into toffcd
-- Insert the data into the table
revExecuteSQL gConID, tSQL, "trtnum", "toffcd"
end repeat
revexecuteSQL gConID, "commit;"
Re: Transaction Processing
Posted: Thu Sep 30, 2010 11:45 am
by Janschenkel
You can also use the
revCommitDatabase command at an appropriate spot, to commit all the INSERT, UPDATE and DELETE commands since the last commit.
Code: Select all
repeat for each line aLine in tAllRtnums
put aline into tdata
put char 1 to 9 of tdata into trtnum
put char 10 of tdata into toffcd
-- Insert the data into the table
revExecuteSQL gConID, tSQL, "trtnum", "toffcd"
end repeat
revCommitDatabase gConID
Similarly, you can use this strategy to rollback if there is an error in the middle of the transaction, using the
revRollbackDatabase command.
Code: Select all
local tError, tLineNumber
repeat for each line aLine in tAllRtnums
add 1 to tLineNumber
put aline into tdata
put char 1 to 9 of tdata into trtnum
put char 10 of tdata into toffcd
-- let's assume there is a business rule here concerning toffcd
if toffcd is not among the items of "a,b,c,d,e,f" then
put "Invalid toffcd:" && toffcd && "on line:" && tLineNumber into tError
exit repeat
end if
-- Insert the data into the table
revExecuteSQL gConID, tSQL, "trtnum", "toffcd"
end repeat
if tError is empty then
revCommitDatabase gConID
else
revRollbackDatabase gConID
answer error tError
end if
The revCommitDatabase and revRollbackDatabase commands will simply be ignored on databases that do not support transactions (yes, there are still some DBMS engines that are not
ACID compliant) so if you ever switch the DBMS, the commands will still work; whereas running your own
and
pairs may cause an error.
HTH,
Jan Schenkel.
Re: Transaction Processing
Posted: Sat Oct 02, 2010 2:39 am
by chris9610
Jan:
That is a very good point. I just learned something new again.
Thanks again.