Transaction Processing

[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

[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?

[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

[sturgis]

Look at this thread, theres an example there that might get you started. http://forums.runrev.com/phpBB2/viewtop ... =12&t=2892

[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!

[bangkok]

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.

[Mark]

Bangkok,

You were deceived. The previous message is spam.

Best,

Mark

[bangkok]

You were deceived. The previous message is spam.

Sorry Mark, could you elaborate ????

What do you mean ?

[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

[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;"

[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

Code: Select all

revExecuteSql gConID, "begin;"

and

Code: Select all

revExecuteSql gConID, "commit;"

pairs may cause an error.

HTH,

Jan Schenkel.

[chris9610]

Jan:

That is a very good point. I just learned something new again.

Thanks again.