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

[Martin Yergeau]

Missing the semicolon after you -IF statement

Where is label RT2

What is the content of FORMS/VALIDATE_TEMP

What is the value in &LINES after you HOLD the file

Why the HOLD file

What is the content of &VALUES

Run without the SQL INSERT part and replace by

-TYPE VALUES: &VALUES

[Justin Thomas 2]

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

[Justin Thomas 2]

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.

[Manoj Chaurasia]

Hi Justin

I think you should open a case with techsupport on this if you havent already.

[Justin Thomas 2]

Ok. We have a case open in which we are trying to sort out another issue between our DB2 server and our Reporting Server. Maybe this will get addressed as part of that.

[Justin Thomas 2]

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.