How to prepare and execute multiple statements in MySQL?

Question

I have a stored procedure on MySQL db that I use to update my records. Based on the inputs, either, add hours to a column, or subtract hours to a column. I call this SP using the Appian smart service for store procedures. My problem is, that if tried to do both, add and subtract (for different rows) then the store procedure doesn't do anything. It runs in my process model fine but the DB is not updated. If I run the PM with a only one input(either add or subtract) then it updates the db accordingly. Could anyone tell me what I'm doing wrong in my SP? Note: addAttendeeUsername and removeAttendeeUsername inputs are both VARCHAR's strings delimited by commas

    BEGIN

    DECLARE startTime datetime;
    DECLARE SQLStateCode varchar(5) DEFAULT '00000';
    DECLARE ErrorNumber int;
    DECLARE MessageText varchar(1000);

    -- insert / update

    set @sql = concat("UPDATE
      XCS_APP_CERTIFICATION
    SET
      COMPLETED_HOURS = COMPLETED_HOURS + ", courseHours , ",
      LAST_MODIFIED_BY = '", lastModifiedBy, "',
      LAST_MODIFIED_ON = NOW()
    WHERE
    XCS_APP_CERTIFICATION.CREATED_BY in (" , addAttendeeUsername , ") AND VALID_START_DATE <= '", offeringEndDateTime, "'
      AND GRACE_PER_END_DATE >= '", offeringEndDateTime,"' ");


    set @sql2 = concat("UPDATE
      XCS_APP_CERTIFICATION
    SET
      COMPLETED_HOURS = COMPLETED_HOURS - ", courseHours, ",
      LAST_MODIFIED_BY = '", lastModifiedBy, "',
      LAST_MODIFIED_ON = NOW()
    WHERE
      CREATED_BY IN (", removeAttendeeUsername, ")  AND VALID_START_DATE <= '", offeringEndDateTime, "'
      AND GRACE_PER_END_DATE >= '", offeringEndDateTime, "' ");

        PREPARE stmt FROM @sql;
        EXECUTE stmt;


        PREPARE stmt2 FROM @sql2;
        EXECUTE stmt2;

    set successFlag = 1;

    -- committing transaction
    COMMIT;

    END

Bill Karwin · Answer 1 · 2019-02-11T22:22:41.517

It's not clear because you haven't provided the procedure argument declaration, but I would guess addAttendeeUsername and removeAttendeeUsername are strings. But you don't use quotes around them in your SQL syntax. So they'll be interpreted as column names, which are no doubt not found.

You really should be combining input variables using query parameters, NOT concatenating them into your SQL.

set @sql = concat("UPDATE
  XCS_APP_CERTIFICATION
SET
  COMPLETED_HOURS = COMPLETED_HOURS + ?
  LAST_MODIFIED_BY = ?,
  LAST_MODIFIED_ON = NOW()
WHERE
XCS_APP_CERTIFICATION.CREATED_BY in (?) AND VALID_START_DATE <= ?
  AND GRACE_PER_END_DATE >= ? ");

SET @courseHours = courseHours, 
    @lastModifiedBy = lastModifiedBy,
    @addAttendeeUsername = addAttendeeUsername,
    @offeringEndDateTime = offeringEndDateTime;

PREPARE stmt FROM @sql;
EXECUTE stmt USING @courseHours, @lastModifiedBy,@addAttendeeUsername, 
  @offeringEndDateTime, @offeringEndDateTime;

When you EXECUTE, you must use session variables (the kind with @ sigil), not local variables.

Hey Bill, I'm using the input variables: courseHours, lastModifiedBy, addAttendeeUsername, offeringEndDateTime are all input parameters which is why I'm not using the '@' at the front of each. Also, the other 2 variables are strings and I'm passing them as "'val1', 'val2', ....". This what the call would look like with one string for each of those 2 inputs - CALL XCS_SP_UPDATE_HOURS("'aapplicant'", "'aapplicant001'", NOW(), 2.5, 'stewie', NOW(), @out). I'm using myphpadmin which is why it doesn't show the input params declarations in the script — polaris, Feb 12 '19 at 14:44

How to prepare and execute multiple statements in MySQL?

1 Answers1