Transaction Commit & Rollback 2

[donutman]

I've been Googling this topic, but I've got a few questions that I don't find answers to:
If a SP has the Commit & Rollback logic within it and the VB program that calls it raises an error during the call because of a connection timeout (eg. possible network cable/router failure) will that trigger the Rollback logic? I do have the check for @@Error after each SQL statement of the multi-stepped SP.
Will any error that might occur in a SP raise an error at the client or is explicit logic required to fully inform the client?
Aren't there errors (short of server power failure) that cause a SP to discontinue processing the next SQL statement so that the Rollback statement is never reached? If so doesn't that imply that best practice is to apply Commit & Rollback logic at the client app rather than within the server's SP?
What are the implications/side effects of a Begin Transaction without a Commit or Rollback Transaction? If there are no negative effects upon the server, then why bother coding Rollback logic in a SP, just don't Commit.
This whole area of programming appears to me to be full of subtle pitfalls or am I just being paranoid?

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

In order to bring some closure to the business end of this thread, I'll summarize what I've learned subsequent to ESquared contributions, but I'll lay some ground work first. It's very confusing because there are so many permutations involving: Cursor location, how the RS is instantiated and whether or not you execute the Command without creating a RS. All of these variables affect the outcome of the use of the NextRecorset method.

You have the choice of using a server side cursor or a client side cursor before opening an OLEDB native SQL Server connection object (Provider=SQLOLEDB.1) using the CursorLocation property.

Assume for the moment that you choose a client side cursor (which is the default for ado controls added within the environment, but not the default for in-line VB code).

Assume that you are creating a RS when you execute the command object (Set adoRS=adoCmd.Execute). This is permissible even if you don't have a Select within the SP.

Also assume that you are trapping errors within VB.

You can retrieve both the Return Value of a SP (along with any explicit output parameters) at the same time as you step through the various RecordSets (RSs) using the NextRecordset method.

You can step through one extra RS using NextRecordset before VB6 will raise the error "Object variable or With block variable not set". BUT the number of RSs you expect may be cut short if SQL Server throws an exception before the completion of all statements.

Each Select, Update, Delete and Insert counts as a RS as far as NextRecordset is concerned.

A VB exception will be thrown when you execute the NextRecordset method on a "returned" RS (which could be an update, delete or insert).

SQL Server may decide to discontinue further SP execution based upon the severity of an exception.

Without Commit/Rollback logic statements prior to an exception will persist.

If you don't set the CursorLocation property, the default is a ServerSideCursor.

Assume you are using a Server side cursor which means the RS cannot be updated (at least I haven't found a way to update the RS within code).

Continue to assume that you are creating a RS when you execute the command object (Set adoRS=adoCmd.Execute).

I have found no way to retrieve both the RSs and the Return Value of a SP nor its explicit output parameters.

If you close the RS object before you execute the first NextRecordset method, then you will get your Return Value and all explicit output parameters. If you attempt close the RS after you have used the NextRecordset method, you will raise an exception as though the RS was already closed and you won't get the Return value.

Once you close the RS object any further execution of the NextRecordset method will raise the VB exception: "Current provider does not support returning multiple recordsets from a single execution." IMHO, that Err.Description is a red herring. Read it as "Operation is not allowed when object is closed."

If you don't close the RS object you will not be able to get the Return Value of the SP nor its explicit output parameters.

The NextRecordset method can continue to be run as long as there are more RSs (including that extra one), but you won't see the Return Value nor its explicit output parameters.

Here's a change in behavior: if the SP throws an exception sufficient to terminate further processing, then the very next execution of the NextRecordset method will throw a VB exception with the above red herring description. The different behavior is that you do not get that EXTRA execution of NextRecordset method before encountering the more appropriate message, "Object variable or With block variable not set".

The behavior of VB when the command object is executed without creating a RS object (adoCmd.Execute) follows the expected course regardless of the location of the cursor and regardless of the presence of a RS within the SP.

The red herring error message continues to flourish if you foolishly attempt to use the NextRecordset method.

Statements prior to the exception will persist.

With a Client side cursor all is well.

Assume you use a Server side cursor.

You cannot use an explicit output parameter and you cannot update the RS.

The NextRecordset method works as usual if no statement in the SP fails.

The interesting behavior is that any secondary-step error will rollback prior statements that were successful without the use of Commit/Rollback logic. The VB error handler will fire on the adoRS.Open statement and report "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

I find this very difficult to commit to memory, so I will no longer code SPs so that NextRecordset is necessary.

The KISS approach is to code SPs in two flavors: ones that do nothing more than return one RS or ones that return no RS. After each Update, Insert or Delete check @@Error and exit if necessary.
Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[gmmastros]

Thinking about the nature of interactions with the database, there is Insert, Update, Delete, and Select. We both know that it's trivial to code a stored procedure that does any amount of inserts, updates, and deletes. Even in conjunction with a final select, it's trivial.

Things get interesting when you start thinking about returning multiple recordset. Why do we want to do that? To save time, of course. If you want another way to return multiple recordset from a stored procedure without the hassle of .NextRecordset, then you should consider using XML. SQL Server 2000's XML support isn't the greatest, but with some tweaking, twisting, poking and prodding, you can write SP's that return XML. Then, from the VB side, just process the XML and use the data. You can even send XML to the database to do your updates, inserts, and deletes.

Using XML with sql 2000 is a bit murky, but if you are interested, let me know and I'll try to help out where I can.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[donutman]

Things get interesting when you start thinking about returning multiple recordset. Why do we want to do that? To save time, of course.

I'll go the extra mile to save computer time and resources, but if you are referring to programmer's time, then I'd prefer to stick to KISS. Unfortunately I have to learn that lesson over and over, because I tend to be lazy and generally look for the quickest way to get the job done.
Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

It is very confusing! I wasn't able to distill a unifying principle out of all that in one pass... maybe I'll make a logic grid so I can see it, maybe something will pop out.