Results 1 to 14 of 14

Thread: need advise

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    May 2013
    Posts
    1,126

    need advise

    So i have these different tables to get data, save and update, delete.

    My steps are:

    A. Save first header info to tblheader
    B. Then save grid info to tbldetails
    C. Delete by id the record from previous table container

    My problem is, what if after step b the network connection fail thereby the record is not deleted.

    I want all steps to be executed, if not then, all records should be deleted.

    I can do the on error trap but is that a good way or is there another way?

  2. #2
    Fanatic Member
    Join Date
    Jan 2013
    Posts
    759

    Re: need advise

    The magic word you're looking for is a "Transaction".

    "Start" a transaction, do some work and then "commit" it at the end.

    If the Transaction doesn't complete, then the database will automatically "rollback" everything that's been done within that Transaction.

    If you're working with two separate databases, then things get a mite more complicated; distributed Transaction control, especially across more than one DBMS, can be downright fiddly.

    Regards, Phill W.

  3. #3
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: need advise

    find a method whereby you mark the parts for deletion once you have verified the existance of the now matching data do the deletion

    you need a b+ action check data written and mark deleted parts for deletion and c now reads delete marked parts

    or even when b+ fails go back to b and only when b+ is ok continue to c

    here to help

  4. #4
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: need advise

    real advice is not so easy,since nobody knows the dbms you are using
    but Phill gave you good advice
    but just if you are using msaccess use transactions and error trapping
    if the code errors out, rollback the transaction, if not commit the transaction

    regarding the use of simultanious use by your program of multiple access databases
    if the workspace object is handling the transactions it makes no difference if the tables are in different databases
    (dao uses the workspace object)
    if the connection object handles the transactions the tables have to be in the same database
    (ado uses the connection object)
    dont count on the access database doing an automatic rollback
    (althought ms says it will if you use the dbfailonerror option)
    (if you addnew the records there is no dbfailonerror option)
    do not put off till tomorrow what you can put off forever

  5. #5

    Thread Starter
    Frenzied Member
    Join Date
    May 2013
    Posts
    1,126

    Re: need advise

    Phil, i know transaction. I use stored procedure to add, update, delete. So i have one stored procedure for saving the header, the details and delete of the previous record.

    So the execution of the SP for inserting the header might be successful but when the program starts to execution the next procedure for rhe SP for details but failed due to power outage of the client computer or network connection failure then only the first SP is successfully executed but not the rest.

  6. #6
    Fanatic Member
    Join Date
    Jan 2013
    Posts
    759

    Re: need advise

    Do your procedures do their own transaction management?
    If so, then they shouldn't.

    Or, at least, you should given them the choice.
    Pass a parameter to each procedure that tells it whether to do its own transaction management or to trust that it's caller is doing so, which is what you need here.

    Regards, Phill W.

  7. #7
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: need advise

    Since your talking about Stored Procedures it's probably safe to assume your using SQL Server (though it would be best if you specify).

    SQL Server will allow nested transactions (sort of), meaning you can put one transaction inside another. Commits on the inner transaction are ignored - only the outermost transaction is actually committable. This is cool because it means that, if you wrap the calls to your SPs up into an outer transaction, they will ALL commit or fail together.

    My VB6 skills are way out of date but from what I remember classic ADO had it's own BeginTransaction method (or similar), meaning you can crate a transaction from your client code, call your SPs and then commit the transaction. Any transaction handling that's going on in the SPs themselves will be effectively ignored and everything will be controlled by the outermost transaction you created in the client. If ADO didn't support that then you can still achieve the same goal by creating a SPROC that calls your other sprocs and managing the transaction there.

    Transaction against distributed database shouldn't be a problem. SQL Server handles them for you automatically using the Distributed Transaction Server (DTS). You might have to configure it if you've never used it before but that's pretty simple and instructions are easy to find on time. It does introduce some performance bottlenecks, though, because it has to lock resources at both ends more aggressively than a local transaction and perform a three stage commit. I'll hold off giving any further advice on this, though, because you haven't indicated that it's relevant in your case.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  8. #8

    Thread Starter
    Frenzied Member
    Join Date
    May 2013
    Posts
    1,126

    Re: need advise

    Phil, since they are individual SP, then i guess they do their own transaction.

    FunkyDexter, I am doing mysql SP. But i am curious how to do nested SP especially if each SP has its own parameters.
    Last edited by codesearcher; Feb 17th, 2016 at 07:43 AM.

  9. #9
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: need advise

    I can't speak for SPs (nested or otherwise) in MySQL as I've never used it. I can tell you that it doesn't support nested transactions though. You might be able to use the save points that are discussed in that thread though.

    BTW, I never mentioned nested sps anyway but all I was suggesting is that you can call one sp from another. In SQLServer you might do something like:-
    Code:
    Create Procedure OuterBlah as
    Begin
       Begin Transaction
       Exec Blah1 'Parm1', 'Parm2'
       Exec Blah2 'ParmY', 'ParmZ'
       Commit Transaction
    End
    In SQLServer this would nest any transactions that were in Blah1 and Blah2 so either both sprocs would complete or both would fail. I'm afraid I have no idea what that would look like in MySQL.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  10. #10

    Thread Starter
    Frenzied Member
    Join Date
    May 2013
    Posts
    1,126

    Re: need advise

    FunkyDexter,

    If SP blah1 have parameter A and
    If SP blah2 have parameter B,

    Does that mean, on my main outerblah will have parameters A and B?

  11. #11
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: need advise

    Probably Ultimately it needs enough information to pass appropriate parameter into blah1 and blah2. They might be hard coded values, parameter on OuterBlah, something you read from a table...

    From your description they will probably be parameters on OuterBlah but they don't have to be.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  12. #12

    Thread Starter
    Frenzied Member
    Join Date
    May 2013
    Posts
    1,126

    Re: need advise

    Thanks funkydexter. I will try this. Thanks for your input.

  13. #13

    Thread Starter
    Frenzied Member
    Join Date
    May 2013
    Posts
    1,126

    Re: need advise

    FunkyDexter, I tried it and yes it will work ONLY if you execute the SP with only one record but if you apply it on to loop the grid and save the grid record to table using the stored procedure, it will be difficult.

  14. #14
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: need advise

    Quote Originally Posted by codesearcher View Post
    FunkyDexter, I tried it and yes it will work ONLY if you execute the SP with only one record but if you apply it on to loop the grid and save the grid record to table using the stored procedure, it will be difficult.
    You might want to consider (in case there's more of such complex stuff in the
    foreseeable future) using an Application-Server on the same machine as your DB.

    Such Servers allow easier transport of even complex Parametersets (as e.g.
    Recordsets, Arrays, JSON etc.) to the Server-side - and due to being able to deliver
    such rich Parameter-Blobs "in one go" - you could completely avoid the scenario
    with the "missing Network-Connection" in-between your critical actions - *and*
    you would be able to program the serverside in a full-blown language (similar
    to the one you're using on the client-side).

    Olaf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width