SQL Rollback

[Mkpatil]

I have seen some of the posts about the sql commit and rollback using the conn.BeginTransaction(), conn.CommitTransaction(), conn.RollbackTransaction(), etc.
I am still not able to rollback once I insert a transaction. Is there any setup that needs to be done on Alpha Properties (UX properties, etc) or on MYSQL server to enable SQL transactions?

Regards!

[Mkpatil]

Re: SQL Rollback

I have noticed that if I use a select statement after starting a Transaction [conn.BeginTransaction()] I am getting the following error

conn.BeginTransaction()
Insert ...
update ....
select .... <<<<<<<< Error here
Insert ...
update ....
conn.CommitTransaction()

ERROR:
Database API specific error Your database has returned the following error code and description to Alpha Five. Consult your database documentation for further information.2014 - 'Commands out of sync; you can't run this command now'
Can I not use a select to check something before further inserts in a transaction?

Regards!

[kotinkarwak]

Re: SQL Rollback

Please see this note. Appears transactions will be negated if a select is used

http://www.alphasoftware.com/alphafo...ginTransaction

[Mkpatil]

Re: SQL Rollback

Thanks Kotin.

My transaction has few inserts, but before I do each, I need to check something (latest) with a select, I wonder how I can do that then.

Regards!

[kotinkarwak]

Re: SQL Rollback

Not much db work myself but assume, the transaction begin/end can just surround the operation, I.e update. All the selects and logic can sit outside that construct.

[Al Buchholz]

Re: SQL Rollback

Thanks Kotin.

My transaction has few inserts, but before I do each, I need to check something (latest) with a select, I wonder how I can do that then.

Regards!

Use multiple connections?

[Mkpatil]

Re: SQL Rollback

Thanks Kotin & Alan!

I did not know I could have multiple connections
Dim conn as sql::Connection 'connection for inserts and may be
Dim conn2 as sql::Connection 'connections for selects

I may try that out.
I was also trying to do all the selects etc (checks and balances) before hand, and prepare a bunch of inserts and when ready sending these inserts as one transaction using conn.BeginTransaction().
The risk here is that, if a record from another user gets inserted right after I complete my checks and balance and before I start the transaction. I am checking how to lock few records before I start my checks and balances and automatically release the lock when I commit.

Regards!

[Mkpatil]

Re: SQL Rollback

Fyi- The risk with multiple connections is that, the uncommitted records in connection 1, remain invisible in (selects of) connection 2. So I will not get the right totals using the select, unless I keep a running total of what I inserted that are still uncommitted and include them in my calculations. Appreciate your input.

Regards!

[GGandhi]

Re: SQL Rollback

I think your approach is correct, it is my understanding that you could have one or more
insert, update, delete and select statements in a begin and end transaction code block.
if some statements did not get transacted (if there is an error) the rollback should work. so if your select statement gets executed then there is no error you cannot rollback.
it is also my understanding that you cannot manually rollback if you check a condition in that code block, it has to be an error in execution of the statements.
I could be wrong in my understanding.

[Mkpatil]

Re: SQL Rollback

Thanks Gandhi.
I use either rollback or commit depending on how the updates were executed, so both may be used. If one of the insert failed, then I just rollback all the inserts.
The code is as shown (but little more efficient, because I jump out when the first insert failed)

conn.BeginTransaction()

heading_to_rollback = "N"

sqlSelect = "INSERT INTO ..."
result = conn.Execute(sqlSelect,args)
if (conn.AffectedRows() = 0) then
heading_to_rollback = "Y"
end if

sqlSelect = "INSERT INTO ..."
result = conn.Execute(sqlSelect,args)
if (conn.AffectedRows() = 0) then
heading_to_rollback = "Y"
end if

sqlSelect = "INSERT INTO ..."
result = conn.Execute(sqlSelect,args)
if (conn.AffectedRows() = 0) then
heading_to_rollback = "Y"
end if

if (heading_to_rollback = "Y") then
conn.RollbackTransaction() ' ROLLBACK ALL
else
conn.CommitTransaction()
end if

Regards!

[GGandhi]

Re: SQL Rollback

I think these are all independent statements they get executed regardless, I am wondering if you can try something like this.

sqlStatement1 = "something"
sqlStatement2  "something"
sqlStatement3 ="something"

open connection
begin transaction
result = cn.execute(cn, sqlStatment1)
   if result then
          result = cn.execute(cn, sqlStatment2)
            if result then
                 result = cn.execute(cn, sqlStatment3)
                      if result then
                          commit transaction
                    else
                    rollback transaction
             else
             rollback transaction
 else 
 rollback transaction
 end if

something like this, does it make any sense?

[Mkpatil]

Re: SQL Rollback

Thanks Gandhi.

I am just having issues doing a select in between inserts to check something. Otherwise the inserts all get committed, or none based on the command I give.

So, I still think none will get executed if one of them fails. I have written code where it rollbacks whenever I program it to rollback even after some records were inserted.

In this example below I fail the second insert (say invoice header) statement 2 below by using 1=2 clause, the first Insert (say invoice line) runs fine with 1=1, then at the end it rollbacks even the inserted one. Try it.

Table creation:

CREATE TABLE `temp123` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`N1` int(11) DEFAULT NULL,
`C1` varchar(25) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=230 DEFAULT CHARSET=utf8;

function functionajaxsql3 as c (e as p)
Dim cn as sql::Connection
Dim result as l
Dim sqlSelect as c
Dim args as sql::Arguments
Dim rs as sql::ResultSet
Dim rows as N = 0
Dim value1 as N
Dim heading_to_rollback as C = "N"

functionajaxsql3 = "alert('Starting');"
result = cn.Open("::Name::Connection1")
if result then
if cn.BeginTransaction() then
args.add("args_N1","1")
args.add("args_C1","OK VALUE")

sqlSelect = "INSERT INTO temp123 (N1,C1) (select distinct '1', 'A' from websecurityusers where 1=1)"
result = cn.Execute(sqlSelect,args)
functionajaxsql3 = functionajaxsql3 +"alert('cn.AffectedRows() = " + cn.AffectedRows() + "');"
if (cn.AffectedRows() = 0) then
heading_to_rollback = "Y"
end if

args.clear()
args.add("args_N1","2")
args.add("args_C1","OK VALUE")
sqlSelect = "INSERT INTO temp123 (N1,C1) (select distinct '2', 'B' from websecurityusers where 1=2)"
result = cn.Execute(sqlSelect,args)
functionajaxsql3 = functionajaxsql3 +"alert('cn.AffectedRows() = " + cn.AffectedRows() + "');"

if (cn.AffectedRows() = 0) then
heading_to_rollback = "Y"
end if

args.clear()

if (heading_to_rollback = "N") then
if cn.CommitTransaction() then
functionajaxsql3 = functionajaxsql3 + "alert('Committed - test');"
end if
else
if cn.RollBackTransaction() then
functionajaxsql3 = functionajaxsql3 + "alert('Rollbacked - test');"
end if
end if
end if
cn.close()
end if
end function

Regards!