Transaction Code in ASP

now that mySQL handles transactions...

[txbakers]

I understand how transactions work in theory:

BEGIN TRANSACTION
UPDATE 1
UPDATE 2
UPDATE 3
COMMIT

But in ASP, each update is handled by an object, then a command text, then is asked to execute the command.

How would I write transactions in ASP?

Is there a link someone can post to a reference on this?
THANKS.

[aspdaddy]

I think just putting this does it -

<%@ TRANSACTION = required %>

Some info here,
http*//www.oreilly.com/catalog/aspnut2/chapter/ch06.html

[txbakers]

That's good to know - I knew JSP had native suport for Transactions, I didn't know that ASP had it as well.

Do you know if mySQL is one of the databases supported by the MTS?

[duckhunter]

Use the ADO Transaction method of the Connection Object

On Error Resume Next

Set dbConn = server.createobject("ADODB.CONNECTION")
dbConn.ConnectionString = <my connection string>
dbConn.Open
'Should check for errors here too to make sure your connection is opened properly..

Set cmd1 = Server.CreateObject("ADODB.COMMAND")
Set cmd2 = Server.CreateObject("ADODB.COMMAND")

cmd1.CommandText = "Update....."
cmd2.CommandType = adCmdText
cmd1.ActiveConnection = dbConn

cmd2.CommandText = "Update....."
cmd2.CommandType = adCmdText
cmd2.ActiveConnection = dbConn

dbConn.BeginTrans

cmd1.execute
cmd2.execute

'Check for Errors
If dbConn.Errors.Count > 0 Then
If dbConn.Errors.Item(0).Number <> 0 Then
dbConn.RollbackTrans
Response.write "Error Update Records: " & dbConn.Errors.Item(0).Description
dbConn.close
set dbConn = Nothing
response.end
End If
End If

'No errors so commit now
dbConn.CommitTrans

dbConn.close
Set dbConn = Nothing
Set cmd1 = Nothing
Set cmd2 = Nothing

[txbakers]

That is great! I will try it tonight.

One question - what is the variable referenced below:
cmd2.CommandType = adCmdText

Where did the adCmdText come from?

[duckhunter]

include the file adovbs.inc which contains all the ADO constants

'---- CommandTypeEnum Values ----
Const adCmdUnknown = &H0008
Const adCmdText = &H0001
Const adCmdTable = &H0002
Const adCmdStoredProc = &H0004
Const adCmdFile = &H0100
Const adCmdTableDirect = &H0200

Sticky me if you need a copy of it.

Depending on the DB you are using, these may or may not work. If you have trouble with the above code, try removing that line. Using SQL Server 2K you should specify the type (text or Stored Proc, etc)

[txbakers]

Ah yes, I remember now. I've always used the numbers with mySQL.

I could use a new copy of the file though.

[txbakers]

Hi, I rewrore some of my code to include the transaction routine above (I'm using JScript not VBScript) and got an error at:

if (connStudentInsert.Errors.Count > 0) {

saying connStudentInsert.Errors.Count is null or not an object.

earlier in the program I defined as follows:
var connStudentInsert = MM_connCharms_STRINGSQL;
which conects and opens successfully.

Here is the complete bit of code with the transaction stuff:

connStudentInsert.BeginTrans;
cmdInsert.CommandText = MM_editQuery;
cmdInsert.Execute();
cmdInsert.CommandText = cmdParents;
cmdInsert.Execute();
cmdInsert.CommandText = cmdStudetail;
cmdInsert.Execute();
if (connStudentInsert.Errors.Count > 0) {
if (connStudentInsert.Errors.Item[0].Number!= 0){
connStudentInsert.RollbackTrans;
Response.Write("Error Inserting Record: " + connStudentInsert.Errors.Item[0].Description);
}
}

cmdInsert.ActiveConnection.Close();
cmdInsert = null;

And the three inserts work without a problem. Most times. Which is why I want to try the transactions to see why two are failing sometimes.Thanks.

[txbakers]

-one bump for good luck - maybe someone knows the answer....

[plumsauce]

Perhaps the particular provider does not
implement the Errors collection. I know
this works with MsSQL/oledb

Does it work in VB, as a test only?

+++

[txbakers]

I'm using mySql 3.23

I could try it without the errors object, but how will I know if it fails?

[plumsauce]

Sorry, no good news.

If the connection does not implement an
errors object then you have a problem that
is specific to mysql.

I was pointing out that MSSQL/OLEDB works
with the code you provided just so you would
not tear your hair out over something outside
of your control.

You need to look over the mysql docs and the
docs for your connection method to determine
your next move. It might be as drastic as
moving to some other db.

+++

[txbakers]

It might be as drastic as
moving to some other db.

Thanks, but I guess I'll just find my error. I don't want to install and configure a whole new DB.

[plumsauce]

if (connStudentInsert.Errors.Count > 0)
---------------------------------------

I ran into something like this earlier
this evening, because of those cursed
*variants*. In my case it was in
Request.Forms("var").Count

the workaround, in your code, would be:

var p;

p=parseInt(connStudentInsert.Errors.Count);
if(p >0)
{
do some stuff
}

+++

[txbakers]

yes, let me try that too. Those variants often catch me as well.

thanks.

[txbakers]

Here is the most basic code I tried to get to rollback, but it doesn't. It still writes to both tables.

<%@Language="JAVASCRIPT" Transaction="Required"%>

<% var conn ="Driver=mysql;server=localhost;Database=animals;Option=1";
if (Request("go") == "true"){

var s1 = "insert into tab3 (name) values ('bob')";
var s2 = "insert into tab4 (name) values ('fred')";
var cmdInsert = Server.CreateObject('ADODB.Command');
cmdInsert.ActiveConnection = conn;
cmdInsert.BeginTrans;
cmdInsert.CommandText = s1;
cmdInsert.Execute();
cmdInsert.CommandText = s2;
cmdInsert.Execute();
cmdInsert.RollbackTrans;

}

Shouldn't this NOT insert the commands?

[duckhunter]

cmdInsert.ActiveConnection = conn

Earlier you referenced your connection as connStudentInsert. Are you calling the right variable name?

Check your code where you create the Connection Object. ie: Set connStudentInsert = server.createobject("ADODB.CONNECTION")

[plumsauce]

Two possibilities,

in .js, BeginTrans should be BeginTrans()
note the added () which signifies a method
as opposed to some *other* thing. ( i really
hate OO programming). This is the more likely
of the two possibliities. The other follows:

Now, on MS/SQL there is such a thing as an
implied transaction and autocommit. You
may find that you are facing a similar
problem. Especially given the material
below gleaned from msdn about odbc drivers.

++++

Typically, ODBC drivers work in one of two modes:

Auto-commit Mode: When you have not explicitly started a transaction using the BeginTrans method, every operation executed is immediately committed to the database upon completion.

Manual-commit Mode: When you explicitly start a transaction using the BeginTrans method or use the ODBC SQLSetStmtOption function to disable the SQL_AUTO_COMMIT mode, or send an SQL statement to begin a transaction (BEGIN TRANS), operations are part of a transaction and no changes are committed to the database until you use the CommitTrans method. If the connection fails before CommitTrans is executed, or you use the RollbackTrans method, the operations are undone — rolled back.

++++

[aspdaddy]

Did you get this sorted?

I still think its better to find the bug than just rolling back :)

There is some good info/example here inc. how to check if MTS is running

http*//www.asp101.com/articles/chris/asptransactions/default.asp

[txbakers]

I had someone on another board explain to me that the MTS/DTC transaction handling will only work with a database that supports it. I didn't realize it was a double-edged approach.

But it does make sense- how would a textual SQL statement know how to reverse itself? The DB accepts the commands from the ASP page, therefore the DB can do the processing.

If I say "insert into tab (name) values('jim')" the DB or ASP page won't know how to write a "delete from tab where name = 'jim'" which might delete everything by mistake.

So I'm going to look into getting mySQL 4 which has transaction support and try it again.

But I'll try the parentheses first.......

[plumsauce]

Oh, I meant to tell you.

The MTS/DTC thing is implied by the transaction
required directive.

What this does I actually have no idea. Because
sql transactions are a totally different thing.
I had meant to tell you but figured having the
statement couldn't hurt even if the db could
care less.

Having said that, in a *sql* transaction
it is not necessary for sql to have any knowledge
of how to reverse any step. This is because
the rollback statement actually says:

"ok, forget what i just told you to do"

this is possible because of *logged transactions*
i don't know what the equivalent terminology in
mysql is, but you get the idea.

one trick in interactive use of MSSQL in testing stuff
is to issue a begin transaction, do some stuff to
see what the result will be, then logout without
ever issuing a commit. this issues an implicit
rollback.

++++

[duckhunter]

how would a textual SQL statement know how to reverse itself?

That's the Database Engine's job. After your Insert/Update/Delete, think of the Database having a copy of the record in memory only. If you read what is on disk it's different. By committing the transaction, it's written to disk. A little oversimplified but that's one way. What's in memory is on disk too, just in a different place than the "live" data.

Having transactional capabilities is 100% necessary if you intend for a multiple tables to keep their integrity. Without it, your tables will have orphaned records whenever the 2nd, 3rd, etc. Insert/Update/Delete is run.

You should also implement Primary/Foreign key constraints if possible to prevent orphaned records.