Android Question RDC and MySQL, Calling stored procedures with an OUT parameter

[Anser]

Dear Friends,

How do I call a Stored Procedure with an OUT Parameter using RDC on a MySQL Database.

Here is a sample of MySQL Stored Procedure with OUT Parameter

DELIMITER $$
CREATE PROCEDURE GetCustomerLevel(
    in  p_customerNumber int(11),
    out p_customerLevel  varchar(10))
BEGIN
    DECLARE creditlim double;
    SELECT creditlimit INTO creditlim
    FROM customers
    WHERE customerNumber = p_customerNumber;
    IF creditlim > 50000 THEN
    SET p_customerLevel = 'PLATINUM';
    ELSEIF (creditlim <= 50000 AND creditlim >= 10000) THEN
        SET p_customerLevel = 'GOLD';
    ELSEIF creditlim < 10000 THEN
        SET p_customerLevel = 'SILVER';
    END IF;
END$$

The above stored procedure just returns the CustomerLevel (String) based on the Customer's Credit Limit

In MySQL, we can call the GetCustomerLevel() stored procedure as follows:

CALL GetCustomerLevel(103,@level);
SELECT @level AS level;

To call a stored procedure, I assume that the following statement is to be used in the RDC config.properties file

sql.callsp_myspname= CALL GetCustomerLevel(?,@?)

Not sure the above line in config.properties file is correct or not, I am just assuming

In the B4A

Dim CustomerNumber as Int :CustomerNumber = 22
Dim CustomerLevel as String :CustomerLevel = ''

Dim cmd As DBCommand
cmd.Initialize
cmd.Name="callsp_myspname"
' I don't have any idea regarding the next line
' How to pass the Variable and retrieve its value
cmd.Parameters=Array As Object(CustomerNumber,CustomerLevel)
reqManager.ExecuteCommand(cmd, "AddUser" )

The second parameter ie @? is the OUT parameter. I don't know how to retrieve this OUT parameter's value from the Sub JobDone.

Any advice or a sample is appreciated.

Thanks & Regards

Anser

 

[Anser]

First of all let me thank @DonManfred and @keirS trying to help me out. You guys are really helpful.

So i guess if you rewrite your SPs to not use an out var but instead returning a value (a dataset). Then you can use your SPs inside your query doing a subquery (getting the result from the SP) then it should work with RDC too i believe... I mean; then you can stay using RDC

It would be a Function not an SP.

The problem here is, as I said earlier, I am developing an android extension of my already existing huge Windows desktop application. From B4A, I call the same common Stored procedures that are used in my Windows application too. It is a huge application and changing most of the stored procedures is not a practical solution for my situation. Changing those stored procedure will definitely break the existing desktop application.

Over and above what I have described above, the stored procedure sample that I posted in this thread is a very simple one written to demonstrate the OUT parameter issue of RDC. The actual stored procedures that I use in my application does more than that ie lot of data insertions, updates, commits, rollbacks etc and after doing the said tasks it will send the result of the operation via the OUT parameter. The result returned via OUT parameter can be a string which says the reason why a new record was not inserted to a table, OR why an update was not allowed etc etc.

Functions are mainly used to compute values.
Stored Procedures are mainly used to process the tasks

Stored procedure uses pre-compiled execution plan where as functions are not. Function is Parsed and compiled at runtime. Stored procedures are Stored as a pseudo-code in database i.e. in a compiled form. So the execution of stored procedures are much faster than a function.

Actually you are not allowed transactions inside stored functions. You are allowed transactions inside stored procedures only.

The following Function will throw error. Error Code: 1422. Explicit or implicit commit is not allowed in stored function or trigger.

create function test( a int )
returns int
MODIFIES SQL DATA
BEGIN
  START TRANSACTION ;
  update t set col='some value' where id=a ;
  COMMIT ;
  return 0 ;
END //

But the following Stored procedure will work.

create procedure test( a int )
MODIFIES SQL DATA
BEGIN
  START TRANSACTION ;
  update t set col='some value' where id=a ;
  COMMIT ;
END //

To return values from the SP, we will have to use OUT parameters

Regards
Anser

 

[achtrade]

To return values from the SP, we will have to use OUT parameters

Regards
Anser

Well, this is not quite right.

DonManfred is right, you don't need OUT parameter.

Anser: if you need a return value from a SP, you should use a select statement for that. I'm doing it like that.

Check one of my MySQL SP:

CREATE PROCEDURE `UpdateRide`(v_idRide int)
L_return:
BEGIN
    DECLARE `_rollback` BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
    IF NOT (EXISTS(SELECT * FROM Rides WHERE id = v_idRide )) then
        select '-1' as 'id'; '// record does not exist
        LEAVE L_return;
    END IF;
  
    START TRANSACTION;
    update Rides set Status = 'C' where id = v_idRide;

    IF `_rollback` THEN
        select '-2' as 'id'; // couldn't update
        ROLLBACK;
    ELSE
        COMMIT;
        select '0' as 'id'; //everything is ok
    END IF;
END

 

[Anser]

Well, this is not quite right.

DonManfred is right, you don't need OUT parameter.

Anser: if you need a return value from a SP, you should use a select statement for that. I'm doing it like that.

Check one of my MySQL SP:

Dear archtrade,

Yes, you are right. You can also use SELECT statement to return a value from a stored procedure.

Thank you for the excellent sample.

As I mentioned in my earlier post, I am writing an Android extension of a huge Desktop/Web application. Changing all the existing stored procedure that use OUT parameter in the database will be a nightmare. I am just calling the same stored procedure from the B4A app. I don't want to disturb the existing desktop/Web applications.

I just meant that OUT parameter is not supported by RDC.

Regards

Anser

 

[achtrade]

oK, I got you. Did you try using php instead RDC ? I had this same problem and PHP can resolve it, I couldn't do it because I don't know much about PHP.

check this out https://www.b4x.com/android/forum/threads/call-mysql-stored-procedure-with-executeremotequery.50747/#post-317798

 

[Erel]

It shouldn't be too difficult to replace RDC with a B4J server. jSQL library does support stored procedures: https://www.b4x.com/android/forum/threads/calling-a-mysql-stored-procedure.51312/#post-321351

 

[Anser]

oK, I got you. Did you try using php instead RDC ? I had this same problem and PHP can resolve it, I couldn't do it because I don't know much about PHP.

Yes. I tried with MySQL stored procedure OUT Parameter via php with B4A and it is working fine.

I had this same problem and PHP can resolve it, I couldn't do it because I don't know much about PHP.

Learning the minimum php skills to develop an app using B4A is very simple. I haven't spend more than 3 hours on it.

I learned the minimum required php skills from this forum itself. There is a good tutorial by the Well-Known Member KMatle

For beginners: How to communicate with a server using httputils2 (Part 1: php)
https://www.b4x.com/android/forum/t...h-a-server-using-httputils2-part-1-php.42442/

For beginners: How to communicate with a server using httputils2 (Part 2: MySql)
https://www.b4x.com/android/forum/t...a-server-using-httputils2-part-2-mysql.42456/

For beginners: How to communicate with a server using httputils2 (Part 3: php, MySql & JSON)
https://www.b4x.com/android/forum/t...using-httputils2-part-3-php-mysql-json.42663/

Regards

Anser

 

[achtrade]

I already have those tutorials in my bookmarks

 

[RVP]

Could you not just create a interface script for using from B4A. Pass it the name of the real script you want and the required parameters, execute the script with an Out parameter to get the result, and then use select to pass the parameter back to B4A.

 

[Anser]

Yes. This is also a smart workaround.