Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

do it in database layer or application layer Expand / Collapse
Author
Message
Posted Thursday, January 26, 2006 12:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 10, 2012 8:32 PM
Points: 134, Visits: 54

Hi all,

I don't know how people deal with the issue in production database:

Let's say you have a transaction through your web site, in your application program, you need to call different store procedures to insert data to different tables. In case there is one store procedure call failed, what would you do to other tables' data which already successfully insert data to them?

It seems that you can create one store procedure to call all those store procedures in database layer , in case one fails and you can rollback the transactions,but it will have to pass to many parameters to that store procedure, besides, what about you need to call one procedure many times based on the application, (insert an array of data set)?

Any comments?

Post #253882
Posted Thursday, January 26, 2006 1:14 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, May 30, 2012 8:02 AM
Points: 302, Visits: 294

I had this same problem and unfortunatly we did not find a way to do this other than wrapping all of the procedure calls into one stored procedure.  When we had to call a sp several time we simply built a function to loop through the array until all the values had been entered.  Once completed the code would then move to the next call.

the only other thing that was considered was to build a procedure that could undo the data changes made to a table should an error be returned.  However it ended up being too complicated.  How it worked was at the end of each of these procedures we would check for an error and if there was one we would execute a procedure to backout the previous change based on the passed in parameters.




Post #253892
Posted Thursday, January 26, 2006 1:14 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 10:01 AM
Points: 3,844, Visits: 3,841

Can't you define a transaction through your application and call all 3 stored procedures from within that one transaction?  I don't have alot of experience from the application side of SQL Server, but I believe this is how our developers do it.  There are times when we control the transactions using the TRANSACTION, COMMIT, and ROLLBACK keywords inside our stored procedures, but we do have instances where the transaction scope is handled through the ADO connection. 





John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #253893
Posted Thursday, January 26, 2006 1:46 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:16 AM
Points: 1,035, Visits: 410
It is possible to do this, but I prefer to handle most transactions at the data layer.  Handling this at the application layer leads to lots of round trips.


/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek



*****************/
Post #253902
Posted Friday, January 27, 2006 8:01 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, March 28, 2007 8:31 AM
Points: 61, Visits: 1

This may be too complicated in your app but I once had to insert to many tables from an app and roll back all if one went wrong.

What I ended up doing was creating temporary duplicates of the tables I was inserting to, writing to these instead, if this was successful, running a single procedure that moved all the data into the real tables.

Because the temp tables were exact matches of the real ones any errors would occur when putting data in them and not at the copying across step, then I could just delete the temp tables when a failure did occur.

The final copying across step I further wrapped in a transaction just in case it ever failed, which it never did as it was a simple 1 to 1 copy of rows from identical tables.

 




"Don`t try to engage my enthusiasm, I don`t have one."

(Marvin)
Post #254099
Posted Friday, January 27, 2006 9:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 10, 2012 8:32 PM
Points: 134, Visits: 54

thank you for all your postings and contribution. Yes, basically there should be a way doing this in data access layer not application layer. I got this in microsoft aspnet discussion forum.

In asp.net, there is a class called sqltransaction which has commit and rollback transaction.

So on doubt, we can do this in application other than database layer.

Betty

Post #254157
Posted Saturday, December 30, 2006 12:37 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 11, 2014 12:48 PM
Points: 139, Visits: 601

I have not read the entire thread and I am certainly 'Johnny Come Lately' on this one.  When I read the reply about iterating through the data to be inserted I felt compelled to reply.

 

You could (Should) simply declare (depending upon the size of the result set) a variable table or a #TEMP table. Smaller sets go with a Variable table no question. Easier to deal with, is not DEALLOCATED, does not use disc access time as it is memory resident.

 

Anyway Simply feed your complex data as a | delimitated string, or if multiple layers as XML. From there you read this directly into your variable (or temp) table and insert the data, or work with it, in a set based operation rather than looping.

 

 



<hr noshade size='1' width='250' color='#BBC8E5'>


Regards,

Jeffery Williams
http://www.linkedin.com/in/jwilliamsoh
Post #333580
Posted Saturday, December 30, 2006 12:43 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 11, 2014 12:48 PM
Points: 139, Visits: 601

You don't really want to do this. It provides for more overhead for one thing. Secondly when multiple SP's are in question often times it is a case of say the first SP stores some data and also gets the result of something (sets a bit field, or gets some value or derived value) which perhaps influences the next SP that is called, or if it is called at all, and so on.

 

If you have what I call a lead SP then it can call what it needs to as well as handle the business rule chain if there is one.

 

As an example you call your Lead SP. Based upon the data supplied it calls SP 2 and does an insert. Also because of the data it has or perhaps a mix of what it was fed as well as some return value from SP 2 it does not call SP 3 rather it calls SP 5 then 6 and skips 7 because of some accumulated result.

 

If the outcome had been different with the initial insert using SP2 perhaps it would have called SP 3 and that would have been the end of it.

 

You really don't need this logic at the application layer. This can all be handled with a single 'transaction' between the client and the DB resulting in a returned result to the client side.

 

Additionally business rules can be more dynamic without having to make calls to the DB as they can be stored in tables and acted upon within SP's.

 



<hr noshade size='1' width='250' color='#BBC8E5'>


Regards,

Jeffery Williams
http://www.linkedin.com/in/jwilliamsoh
Post #333581
Posted Wednesday, January 3, 2007 6:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:00 AM
Points: 1,069, Visits: 898

You could (Should) simply declare (depending upon the size of the result set) a variable table or a #TEMP table. Smaller sets go with a Variable table no question. Easier to deal with, is not DEALLOCATED, does not use disc access time as it is memory resident.

 

Actually this is incorrect, table variables are not memory resident.




Post #333997
Posted Wednesday, January 3, 2007 8:59 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 12:01 PM
Points: 33,188, Visits: 15,326
#temp tables are not necessarily memory resident either. They both can go to disk.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #334040
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse