5 Replies Latest reply: Feb 14, 2013 4:13 AM by 789736 RSS

    form calling db procedure with commit

    789736
      Hi everyone, Just a quick question to give me a clearer understanding about something. I was under the impression that if I had a form in which I made changes too and then on that form made a call to a database procedure which contained a commit that all the changes to the form would also be committed? I thought I would have to use PRAGMA AUTONOMOUS_TRANSACTION so as the database stuff would commit without committing the stuff on the form. However, In a quick test today I did exactly this and whilst the stuff in the database procedure committed successfully the stuff on the form did not commit at all (which is what I wanted but didnt expect it to work this way). Is this the way it is suppose to work and my understanding of this has been wrong?

      Thanks
        • 1. Re: form calling db procedure with commit
          HamidHelal
          786733 wrote:
          Hi everyone, Just a quick question to give me a clearer understanding about something. I was under the impression that if I had a form in which I made changes too and then on that form made a call to a database procedure which contained a commit that all the changes to the form would also be committed?
          Answer: NO

          For forms changes commit you may use
          commit_form;
          Hope this helps

          Hamid
          • 2. Re: form calling db procedure with commit
            François Degrelle
            Hello,
            As indicated, the Forms changes will not be applied in the database until you explicitely use the COMMIT_FORM() built-in.

            Francois
            • 3. Re: form calling db procedure with commit
              789736
              Thanks for the responses, however I am now coming across a scenario which seems to back up what I originally thought. Basically in the form the user can upload a photo to a table called photo, when they do this but dont save it they can then press a button which calls a database procedure which then copies that photo to a temporary table called temp_photo and commits (the photo has not been saved at form level yet so there should be nothing in the photo table). When this commit in the database procedure is performed my photo on the form is also committed to the photo table as well as the copy being committed to the temp_photo table.

              Why is this happening?

              Thanks again for any help. I appreciate the time you take to help me out and im aware my questions may be silly but I just want to get a better understanding of this scenario.
              • 4. Re: form calling db procedure with commit
                Andreas Weiden
                If you issue a commit in a database procedure, all outstanding "changes" in the database will be committed. Means, if you issue an INSERT from forms (which i guess happens when uploading a photo), this insert sill also be committed when you do a database-commit. What is not committed are outstanding changes in the forms-blocks, which have not been already send to the database (e.g. using POST). So, a COMMIT in a db-procedure commits ALL db-changes, a COMMIT in the forms-side applies all block-changes to the db, then does a DB-commit.
                • 5. Re: form calling db procedure with commit
                  789736
                  Got it! thanks for the help :)

                  Edited by: 786733 on 14-Feb-2013 02:12