SQL Server transaction won't roll back?

[nlights]

I wonder if I'm doing something wrong here: SQL Server 2008 BeginTransaction -> RollBackTransaction() appears to work fine in Xbasic code, but the data in the backend database is not rolled back for some reason.

Here's my code snippet:

errors = 0

if conn.open("::Name::PSShip")
	if conn.BeginTransaction()
          (do stuff such as inserts, selects, etc., increment error counter if there are errors)
             if errors>0
		if conn.CommitTransaction()
			cResult = "Success (committed)"
		end if
	   else
	      if .NOT. conn.RollBackTransaction()
		cResult = "Error: Unable to roll back transaction."
	      else
		cResult = "Success (Rolled back)"
	      end if
	   end if
      end if
end if

When I trigger an error intentionally in one of the statements after some data has already been inserted within the transaction, the system indicates rollback success, but the data inserted into the database before the error is retained in the database.

Any ideas?

[Davidk]

Re: SQL Server transaction won't roll back?

I haven't tried to duplicate your code yet... but... are you sure you should be testing CommitTransaction() and RollBackTransaction()?

I thought it was....

conn.BeginTransaction()
if .not. conn.Execute(sqlStmt) then
   conn.RollBackTransaction()
else
   conn.CommitTransaction()
end if

[compuaid]

Re: SQL Server transaction won't roll back?

One thing that I see is that your code should read "if errors = 0" prior to your commit otherwise you are telling the system to commit when you have errors and rollback when you don't.

[nlights]

Re: SQL Server transaction won't roll back?

Thanks, David --

Except for the "if errors > 0" -logic error, that's exactly what I'm doing. I actually just threw in the "errors > 0" code as an afterthought to this post, my actual code does much more complex and sophisticated error checking, but I didn't want to include it because it would distract from the core problem.

The question still remains as to why Xbasic tells me rollback was successful, but SQL Server doesn't act on it. Transactions are such an important part of the multi-table update that my code is trying to accomplish that I would sure love to have them work as expected rather than trying to write my own code to delete parent records if children records are not successfully committed.

Maybe I'll do more testing with a much simpler setup to see if something in my code between begin and end transaction throws the things off kilter.

On a separate nit: Why would A5 documentation state that API_UUID() returns a 32 character string, when it clearly returns a 36 character string (albeit with 4 dashes)? Things like this drive me up the wall, because my SQL inserts fail due to the fact that I'm trying to stuff a 36 character GUID into a 32 character space, and then I have to somehow figure out that the documentation,rather than my own code or something in the SQL server itself is wrong. Makes me wary of anything I do with A5/AA before empirically verifying that the code does what the documentation says it does. Makes for long development cycles and cranky programmers...

[Peter.Greulich]

Re: SQL Server transaction won't roll back?

Why would A5 documentation state that API_UUID() returns a 32 character string, when it clearly returns a 36 character string (albeit with 4 dashes)?

Well, yeah, Alpha documentation....

Use: remspecial(api_uuidcreate())

[nlights]

Re: SQL Server transaction won't roll back?

Right, I noticed that in (I think your) earlier posts. I think I will rather make the column wide enough to save me from additional potential for errors and unnecessary typing in the future. Disk space is cheap these days...

[compuaid]

Re: SQL Server transaction won't roll back?

In fairness to Alpha while they misleadingly state that it returns a 32 character string in the body of the documentation they do mention that the 32 character does not include the four dashes.

This is a 32 character (128 bit) hexadecimal string (not including dashes)

Based on their documentation I'd have set the field length to 32 as well.

[nlights]

Re: SQL Server transaction won't roll back?

Brad --

You are correct, and I didn't see the disclaimer (and if I didn't see it, I'm sure a lot of other busy programmers won't see it, either.) It is an odd way to write documentation, though -- I would much rather have it say: "This is a 36 character string (32 characters without dashes)..." In other words, highlight the default but mention an option, rather than the other, rather obtuse, way around.

While A5/AA is a great tool once you learn to work around it's quirks and dark corners, it would be much greater if the written documentation was kept up-to-date, easier to search, and with many more examples. To me, for example the .NET documentation is a good example of a great help system.


Onwards...