Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

We have an html page with controls (edit boxes) that passes ...


Justin Thomas 2

Recommended Posts

We have an html page with controls (edit boxes) that passes values to a procedure that uses SQL DB2 syntax to insert a record into a table. The values are passed as parameters into the SQL statement and multiple rows are being inserted into the table instead of just one, which is what we want/expect. We added a timestamp value to the insert statement, and most of the time the rows appear to be inserted at the same time. However, sometimes the will be a second apart. Anyone have any ideas what is happening here, and how we can fix it We tried putting all the parameters into one parameter outside of the SQL insert, thinking there was an issue with the timing of how the parameters were being resolved by dialogue manager. This did reduce the number of duplicate rows being inserted from 3 or 4 down to 2. But, we are still getting two instead of one.

-*-SET &ECHO = ALL;

 

ENGINE DB2 SET ISOLATION NC

-DEFAULTH &USER_ID = NULL;

-DEFAULTH &FORM = NULL;

-DEFAULTH &VALIDATE_KEY = NULL;

-IF &USER_ID EQ NULL OR &FORM EQ NULL OR &VALIDATE_KEY EQ NULL THEN GOTO RT2 ELSE GOTO RT1;

-RT1

-SET &USERID = UPCASE(&USER_ID.LENGTH,&USER_ID,A&USER_ID.LENGTH);

TABLE FILE FORMS/VALIDATE_KEY

PRINT *

ON TABLE HOLD AS FORMS/VALIDATE_TEMP

WHERE USER_ID EQ &USERID.EVAL AND FORM EQ &FORM.EVAL AND KEY EQ &VALIDATE_KEY.EVAL

END

-RUN

-SET &DATE_TIME_STAMP = &MDYY.EVAL || - || &TOD.EVAL;

-SET &LINES_ = LTRIM(&LINES.EVAL);

-SET &VALUES = VALUES ( || &USERID.EVAL || , || &FORM.EVAL || , || &DATE_TIME_STAMP.EVAL || , || &LINES_.EVAL || );;

SQL DB2

INSERT INTO FORMS.RETURN_CODES(USER_ID,FORM,DATE_TIME_STAMP,RETURN_CODE)

&VALUES

END

-RUN

TABLE FILE FORMS/RETURN_CODES

PRINT RETURN_CODE

WHERE USER_ID EQ &USERID.EVAL AND FORM EQ &FORM.EVAL AND DATE_TIME_STAMP EQ &DATE_TIME_STAMP.EVAL

END

-RUN

-EXIT

-RT2

-EXIT

Link to comment
Share on other sites

I added the rest of the lines after the INSERT step into the original post above.

RT2 just exits if one of the parameters is not passed a value. The FORMS/VALIDATE_TEMP is just get the number of rows in the FORMS/VALIDATE_KEY table to make sure the user has permission to access the form. If not, the &LINES will be 0. When &LINES is 1, this populates a control on the form that is read by a custom JavaScript function to determine whether to display the contents of the form for the user.

When I use -TYPE to display the value for &VALUES, this is what I get:

(JLT000OD,DAI_STAFFING_REPORT,04112021-15.06.03,1);

The image below shows the kind of results I get in my RETURN_CODES table. These are the results of two inserts. Two records are added one second apart for each INSERT.

Link to comment
Share on other sites

  • 2 weeks later...
  • 1 month later...
It turned out that we had a task in our HTML page that was causing this. We were calling the procedure from an on-click event to submit the record, and then we were using a Refresh Action to return the value from the last PRINT statement in the procedure to an Input Control. Not sure why it took us so long to figure this out; but, it did not occur to us that the Refresh Action was calling the procedure another time. To avoid this, we created a separate procedure that included just the last PRINT statement, connected that to the Input Control through Settings, and then applied the Refresh Action to the task after the INSERT statements were complete.
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...