Server Action - How To Access MySQL Stored Procedure "Out" Parameter?

Wappler General
1

I use a lot of MySQL stored procedures in my work. Often I want to return a value after running a stored procedure, and I would really appreciate Server Actions giving me access to a stored procedure’s out value.

A common way to do this in MySQL is like this:

-- test_out(input_string, output_string)
CALL test_out("Hello", @the_output);
SELECT @the_output AS returned_value;

Which in MySQL Workbench, gives me the result I expect:

hello_good

So I am trying to replicate this structure within a custom database query. After a lot of attempts at how to format the query, Wappler will let me save this format:

hello_bad_1
hello_bad_1706×383 28.3 KB

However when I test run the query, I get the following message:

hello_bad_message

I’ve also tried this, which I’m not allowed to save… I get messages about parameters being wrong.

-- test_out(input_string, output_string)
CALL test_out(:P1, @the_output);
SELECT @the_output AS returned_value;

Is there a way to do this which I am missing?

If what I want is not possible now, could a new feature be added to Wappler to allow me to use the mySQL variables like @the_output?

Thanks!

Best wishes,
Antony.

=======================================

DELIMITER //
CREATE PROCEDURE test_out ( IN string_in VARCHAR(128), OUT string_out VARCHAR(128))
BEGIN
	SET string_out = string_in;
END 
// DELIMITER ;
2

@teodor, can anyone at Wappler help me with this one?

I have thought of a complicated and inelegant workaround, but don’t want to implement it unless I have to! :slight_smile:

Thanks,
Antony.

@mebeingken, am I right in saying you use a lot of stored procedures? Did you ever find a solution to this?

3

Yes, lots of procedures, but I don’t use any output parameters…any output is the result of a select, etc.

4

Fair enough!

Am i right in saying the SELECT needs to be outside of the procedure for Wappler to pick up the results?

So you would pass something unique into the stored procedure, have that value be one of the fields of the record you INSERT into the database, and then use that unique key to do a SELECT and find that record after the stored procedure has finished?

5

Maybe you want to do something like this:

    SET @result = 'abc';
    select @result;

You can set a variable and the select it, which will be available in Wappler.

EDIT:
And if you are doing an insert, you can do something like this

    insert into table1 set field2 = 'test'; # insert a record
    SET @random = 'random string'; # if you need some other value 
    SET @lastInsert = LAST_INSERT_ID(); # the primary key of the table
    select @random, @lastInsert; # send them both to Wappler custom query