[SOLVED] Sc_lookup in onAfterInsert Event breaks the insert itself

Scriptcase 9 Bugs
#1

I have a form which has the following sequences -

  1. onValidate with some validations
  2. onBeforeInsert to generate an unique ID using postgres’ sc_lookup(rs, “SELECT gen_random_uuid()”);
  3. in onBeforeInsert after step (2) above, i store the generated ID into global variable
  4. in onAfterInsert, I have to send a verification email to the newly registered user in a form. This requires to retrieve the newly stored email for the user with a generated ID. (Note I do not want to use the email from the form field input, but from the one freshly stored in db)
  5. I use sc_lookup to get the new user details
  6. BUT not only this fails, it even breaks the insert of the new record. If I comment out the sc_lookup , then the insert is successful and I am able to see the newly inserted record’s global variable also in onAfterInsert with an echo command

Scriptcase + Postgres with pg generated ID using random id gen.
Solution : used sc_commit_trans before the sc_lookup in the onAfterInsert

QUESTION: Should not sc_commit_trans occur with the form’s insert ?

#2

As I understand it:

The OnAfterInsert is the last event in the transaction cycle before the record is committed(This is applicable for the other transaction types OnAfterUpdate / OnAfterDelete as well.)

The record has not been committed.

This allows for referential / logging / custom actions to happen before the transaction is finely committed as all the field values from the form is “set” and available for use. Meaning that there is no manipulation of the values in this event as they have already been set ready for commit.

The auto number key/value is available for use and can be access using the {key} notation.

At this point it is still possible to do a sc_error_message() which will not commit the record but return the execution back to the form without affecting the database.

As there is a transaction lock on the record, sc_commit_trans needs to happen before you can access the record through sc_lookup or any other function.

#3

Hi Dirk
Sincere thanks !!
Your explanation is very very clear , in fact crystal clear.

#4

Hi @sriraminfotec, So if you need to do a redir for example onAfter insert you must do a sc_commit… before the redir.

OnAfterInsert
sc_commit_trans();
sc_redir(…);

Regards

#5

thank you . That is the fix used