Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467

    Unanswered: Need some Forms insight

    SETUP: Forms 9i, 9i data base, W2K

    Okay. Here is the issue I face. (Self-inflicted unfortunately). I have a design where I need to set the status of a record in the data base to "EDIT" when the user is editing the record within my form. THen when they are "finished" (ie. either close form or query new record) I need to set the status to "AVAILABLE". I have not been able to find an appropriate trigger to allow this type of behavior. As noted by others there is no document that says what triggers allow DML. Done some research but have been unlucky in a solution. Hoping for someone who has done this kind of thing can provide insight.
    NOTE: Please disregard the label "Senior Member".

  2. #2
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110

    Re: Need some Forms insight

    Can you define what 'AVAIL','EDIT' mode really are? We used to have an EDIT button to switch between 'EDITABLE','QUERY-ONLY' mode.

    Originally posted by Todd Barkus
    SETUP: Forms 9i, 9i data base, W2K

    Okay. Here is the issue I face. (Self-inflicted unfortunately). I have a design where I need to set the status of a record in the data base to "EDIT" when the user is editing the record within my form. THen when they are "finished" (ie. either close form or query new record) I need to set the status to "AVAILABLE". I have not been able to find an appropriate trigger to allow this type of behavior. As noted by others there is no document that says what triggers allow DML. Done some research but have been unlucky in a solution. Hoping for someone who has done this kind of thing can provide insight.

  3. #3
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    EDIT means that other users will not get the record in their list of AVAILABLE records

    AVAILABLE means other users can get the record.

    Basically an LOV in the Form will select where status = 'AVAILABLE';

    The result I am looking for is to keep the other users from even selecting that record when one user is editing it (i.e. status in the db would be EDIT.
    NOTE: Please disregard the label "Senior Member".

  4. #4
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    Originally posted by Todd Barkus
    EDIT means that other users will not get the record in their list of AVAILABLE records

    AVAILABLE means other users can get the record.

    Basically an LOV in the Form will select where status = 'AVAILABLE';

    The result I am looking for is to keep the other users from even selecting that record when one user is editing it (i.e. status in the db would be EDIT.
    I don't you can acheive the goal by using a table column STATUS.
    Since other user don't see the STATUS change before you commit it, that 's how Oracle concurrency works.

    You might be able to use
    select for update no wait instead.

  5. #5
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    I understand concurrancy. I am looking for a way to update (and commit) the record or any record programmatically when the user starts to change the record.
    NOTE: Please disregard the label "Senior Member".

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I think you could do this in an ON-LOCK trigger on the block:

    update t1 set status='EDIT'
    where id = :t1.id;
    forms_ddl('commit');

    You would then need to update it back to 'AVAILABLE' in any circumstance where the record is "released", e.g. commit, clear_record, clear_form, clear_block, ...

    I imagine the chances of records ending up left in EDIT status when no one is actually editing them is quite high...

  7. #7
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Tony,

    Thanks. That might be what I need. I agree the possibility of "locked" records in the data base is high but that is a bridge to fall off when I get there. The people editing is proposed to be low (how often have you heard that from clients) so it might be okay. I perceive a need for an unlock routine.

    Reading the doc on forms_ddl I am not quite sure how to interpret "Commit (or roll back) all pending changes before you issue the FORMS_DDL command. All DDL operations issue an implicit COMMIT and will end the current transaction without allowing Forms Developer to process any pending changes, as well as losing any locks Forms Developer may have acquired. " I think they are saying I loose any changes I made to this point. I will be calling forms_ddl() when I attempt to lock (ie. no changes yet) so I think I am okay without a commit. Am I on track with that thinking?
    NOTE: Please disregard the label "Senior Member".

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Here is an alternative approach that may work (seems to work in simple test) without using a status column.

    In POST-QUERY trigger on the block:

    Code:
    declare
      x int;
    begin
      forms_ddl('savepoint a');
      select id into x from t1 where t1.id = :t1.id for update nowait;
      forms_ddl('rollback to a');
    exception
      when others then raise form_trigger_failure;
    end;
    When the block is queried, we attempt to lock each record for update. If successful we release it again (since we don't actually want to lock it) via the rollback to savepoint; if we fail, i.e. locked by another user, then we raise FTF and the record is not brought into the block. No other code is necessary, and there is no possibility of records becoming "stranded" in EDIT status.

  9. #9
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    That is a good approach. I will keep that one in reserve for now. There are other processes that depend on the status being EDIT and not AVAILABLE so until I need to rethink that approach (i.e. all the records in the data base have been "locked" :-) I am going to persue the first approach. Any other thoughts are of course welcomed.
    NOTE: Please disregard the label "Senior Member".

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by Todd Barkus
    Tony,

    Thanks. That might be what I need. I agree the possibility of "locked" records in the data base is high but that is a bridge to fall off when I get there. The people editing is proposed to be low (how often have you heard that from clients) so it might be okay. I perceive a need for an unlock routine.

    Reading the doc on forms_ddl I am not quite sure how to interpret "Commit (or roll back) all pending changes before you issue the FORMS_DDL command. All DDL operations issue an implicit COMMIT and will end the current transaction without allowing Forms Developer to process any pending changes, as well as losing any locks Forms Developer may have acquired. " I think they are saying I loose any changes I made to this point. I will be calling forms_ddl() when I attempt to lock (ie. no changes yet) so I think I am okay without a commit. Am I on track with that thinking?
    I think there may be issues here: unless you force the user to commit or rollback between each record, you could get into difficulties.

    Suppose user can do this:
    - update record 1 (don't commit)
    - next record
    - update record 2 (don't commit)
    - next record
    ...

    As soon as the user starts to update record 2, the ON-LOCK trigger will perform a database commit which will release the lock held on record 1. You will then be relying solely on your "roll your own" locking mechanism to prevent lost updates. Remember, the changes you made to record 1 haven't been posted to the database yet - unless you used POST in the next record processing. And if you did use POST, then the changes to record 1 are now commited, despite the fact that the user may subsequently "roll back" by clearing the form.

    Hopefully you can use my other suggestion and get away from this highly non-standard processing!

  11. #11
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Your counsel is wise. It looks like it is time to explain to the customer that EDIT will not be appearing.

    I just realized I can still mimic that behavior by displaying EDIT or AVAILABLE based on your PQ example.

    Do you see a reason to not put your PQ example into the ON-LOCK trigger? The other record being locked is not an issue unless changes are made.
    NOTE: Please disregard the label "Senior Member".

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by Todd Barkus
    Do you see a reason to not put your PQ example into the ON-LOCK trigger? The other record being locked is not an issue unless changes are made.
    No, I imagine it would work in the ON-LOCK trigger also.

  13. #13
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Thanks. I will post my results and any additional thoughts after I am done.
    NOTE: Please disregard the label "Senior Member".

  14. #14
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    Enjoy Todd!

Posting Permissions

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