fn!executestoredprocedure error

Hello,

we are having the same message "Use the Execute Stored Procedure Smart Service to modify data...etc",
when using in SAIL the stored procedure described in the pdf here attached: link on forum

Please note that :

- the same procedure used in Smart Service works fine.
- if we simplify the stored procedure, removing the select that is giving the result set, it works fine again in SAIL.

We are using Appian 16.3 with MySql 5.7  on Ubuntu 14.04.

Do you have any suggestion about?

Thanks in advance, regards.
Paolo

I do not understand why you say "stored procedures that modify data".

In fact - in my understanding -  this simple stored procedure does not modify anything, it just return (if any) a set of rows.

Am I missing something?

Thanks again, regards.

It's hard to say without viewing the contents of your stored procedure, but if you are getting the error message "Use the Execute Stored Procedure Smart Service to modify data...etc" then the plugin thinks you are trying to update data. Here is the relevant snippet from ExecuteStoredProcedureFunction.java

// Loop through results (make sure user isn't writing data, rollback if so)
        while (true) {
          if (call.getMoreResults()) {
            resultList.add(getProcedureResultSet(call, tf, procedureName));
          } else if (call.getUpdateCount() > 0) {
            conn.rollback(save);
            returnValue.put("success", tf.createBoolean(false));
            returnValue.put(
              "error",
              tf.createString(
                "Use the Execute Stored Procedure Smart Service to modify data. This function must only be used to query data, not modify."));
            returnValue.put("parameters", emptyResults);
            returnValue.put("result", emptyResults);
            return tf.toTypedValue(returnValue);
          } else if (call.getUpdateCount() < 0) {
            // nothing else to process
            break;
          }
        }

I think I have found a workaround to make working the stuff.

The code in example provided was like the following one, and the SAIL code was not working
(please note the order of select statements)

SELECT title, author, quantity, price FROM books WHERE title LIKE title_search;
SELECT SUM(quantity * price) INTO total_cost FROM books WHERE title LIKE title_search;

Well I've just changed the order of select statements, like the following, and now SAIL code works!

-- first the select for calculation
SELECT SUM(quantity * price) INTO total_cost FROM books WHERE title LIKE title_search;
-- then the select for putting rows
SELECT title, author, quantity, price FROM books WHERE title LIKE title_search;

Hope this can help someone.

Bye, Paolo

I think I have found a workaround to make working the stuff.

The code in example provided was like the following one, and the SAIL code was not working
(please note the order of select statements)

SELECT title, author, quantity, price FROM books WHERE title LIKE title_search;
SELECT SUM(quantity * price) INTO total_cost FROM books WHERE title LIKE title_search;

Well I've just changed the order of select statements, like the following, and now SAIL code works!

-- first the select for calculation
SELECT SUM(quantity * price) INTO total_cost FROM books WHERE title LIKE title_search;
-- then the select for putting rows
SELECT title, author, quantity, price FROM books WHERE title LIKE title_search;

Hope this can help someone.

Bye, Paolo