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

 

[Erel]

It is currently not possible with RDC.

It is possible however to create a solution similar to RDC with B4J that does support it.

 

[Anser]

Dear Erel,

Thanks for the reply.

I use stored procedures extensively in my applications, so that I can avoid complicated business logic in the front end. Any front end application can call the stored procedure. In fact I am developing a small android app as an add-on to my other windows desktop application.

B4A is a wonderful product. Within a couple of week I could develop an android app.

I opted the RDC option to connect and use a remote MySQL database and I reached more than half way through. Hired a VPS server too. Now it's a sad news to know that RDC does not support this OUT parameter feature. The sample stored procedure that I posted above is a very simple one and I posted it to show a simple sample of what I am planning to achieve, but my real stored procedure is much more complicated and it process very complicated task and returns the result and the status of the operation etc.

I understand that php can do this process. But I haven't done anything in php till now.

I am very much new to B4A itself and haven't tried B4J till now. You said that similar feature can be achieved with B4J.

Is this feature impossible with RDC ?. Or will this feature will be available in the future version of RDC ?

Any advice will be appreciated.

Thanks & Regards

Anser

 

[Erel]

Is this feature impossible with RDC ?.

Currently yes.

Or will this feature will be available in the future version of RDC ?

I don't know.

 

[Anser]

Dear Erel,

Thanks for the reply.

Regards
Anser

 

[Erel]

Try to implement the back end with B4J. It will allow you to build a much more powerful solution.
Check this example: https://www.b4x.com/android/forum/t...device-desktop-and-web-reports.37254/#content
It works with SQLite database but can easily be modified to work with MySQL.

 

[Anser]

To reach a knowledge level to modify the server code which you mentioned in your previous post, I will have to invest more time to master B4J

As I don't have much time to finish this android project, I need to make a quick decision and I feel that I should move to the php solution. In any case I will have to change the RDC codes that I have written in all the activity modules.

Regards
Anser

 

[incendio]

I use RDC with store procedure a lot, but database is not mysql.

Don't familiar with mysql, in my database, i will convert same procedure not to returns value, but returns single row contain the value. This procedure can be used in RDC.

 

[Anser]

I use RDC with store procedure a lot, but database is not mysql.

Don't familiar with mysql, in my database, i will convert same procedure not to returns value, but returns single row contain the value. This procedure can be used in RDC.

First of all let me thank you for providing a hint.

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.

I wish that handling the OUT parameter's of stored procedures was there in RDC. Unfortunately, it is not there in RDC and I understand that it is not available in any other MySQL libs available for B4A.

I assumed that this feature would be there and started my B4A project using RDC. Only after reaching more than half way of my project, I understood that this feature is not there and I am stuck at this point now. Now I am reading and learning php solution. I am new to php world.

Regards
Anser

 

[Anser]

i will convert same procedure not to returns value, but returns single row contain the value. This procedure can be used in RDC.

Would you mind sharing a simple sample of a stored procedure returning a row instead of a value as you said in your post ?

Regards
Anser

 

[incendio]

Would you mind sharing a simple sample of a stored procedure returning a row instead of a value as you said in your post ?

Regards
Anser

I don't know with MySQL, if using Firebird, then your sample would be something like this

CREATE PROCEDURE GetCustomerLevel(p_customerNumber int) 
Returns (p_customerLevel  varchar(10))
AS
DECLARE creditlim double;
BEGIN
    SELECT creditlimit INTO creditlim
    FROM customers
    WHERE customerNumber = p_customerNumber;

    IF creditlim > 50000 THEN 
       p_customerLevel = 'PLATINUM';
    ELSE IF (creditlim <= 50000 AND creditlim >= 10000) THEN 
       p_customerLevel = 'GOLD';
    ELSEIF creditlim < 10000 THEN
       p_customerLevel = 'SILVER';
    END IF;
    Suspend;
END

In Firebird, the reserved word Suspend will make procedure returns a row, without it, procedure will returns a value.

I don't know with MySQL, perhaps something similar exist.

 

[Anser]

Thank you for the sample.

Sadly there is no "Suspend" equivalent in MySQL. So back to square one again.

Regards
Anser

 

[keirS]

First of all let me thank you for providing a hint.

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.

I wish that handling the OUT parameter's of stored procedures was there in RDC. Unfortunately, it is not there in RDC and I understand that it is not available in any other MySQL libs available for B4A.

There is one now

 

[Anser]

There is one now

Thanks. Looks promising.

Regards
Anser

 

[Anser]

Dear Erel,

I played a little with the RDC and tried Calling a stored procedure with OUT parameter

in the RDC's cong.properties file, to call a stored procedure with OUT parameter, I created the following entries.
1st line is the SQL to call the stored procedure with one IN parameter and one OUT parameter (The @ is the OUT parameter)
2nd line is the SQL to retrieve the OUT parameter's value, which is supposed to be available after the successful execution of the stored procedure.

sql.insert_newuser=call Sp_MySpName(?, @cOutMsg)
sql.insert_newuser_result=SELECT @cOutMsg AS 'cOutMsg'

Then in B4A code I call the stored procedure via RDC as given below

Sub Registration(cUserName As String)
   Dim cmd As DBCommand
   cmd.Initialize
   cmd.Name="insert_newuser"
   cmd.Parameters=Array As Object(cUserName)
   reqManager.ExecuteCommand(cmd, "AddUser" )
End Sub

In the Sub Jobdone I check for the result of the above Task

If result.Tag = "AddUser" Then 'Finished the execution of the stored procedure
    Log( "Reached AddUser")

    ' If the code execution reached till here means the Stored procedure is executed successfully

    ' Here I call the next SQL command via RDC to retrieve the OUT parameter's value that is supposed 
    ' to be stored in the @cOutMsg after the succesfull execution of the stored procedure.
    ' ie SELECT @cOutMsg AS cOutMsg

    Dim cmd As DBCommand
    cmd.Initialize
    cmd.Name="insert_newuser_result"
    reqManager.ExecuteQuery(cmd, 0, "GetOutParameterValue")

Else If result.Tag = "GetOutParameterValue" Then 
      
     If result.Rows.size = 1 Then
         Log( "Columns :"& result.Columns ) ' Log Value is Columns :(MyMap) {cOutMsg=0}

         Log( "Rows : " & result.Rows) ' Log Value is Rows : (ArrayList) [[Ljava.lang.Object;@b4e2c4e0]
         Log(" ONE records retrieved after insert" ) 'Upto here it is woking
                        
         'The next line throws error
         Log( "cOutMsg Value " & records(result.Columns.Get("cOutMsg")) )
      Else
         Log(" No records retrieved after insert" )
      End If
End If

The run time error message available in the Log window is pasted just below

** Activity (main) Create, isFirst = true **

** Activity (main) Resume **
** Service (httputils2service) Create **
** Service (httputils2service) Start **

JobName = DBRequest, Success = true
HandleJob: 8

Reached AddUser
JobName = DBRequest, Success = true

HandleJob: 33

Columns :(MyMap) {cOutMsg=0}

Rows : (ArrayList) [[Ljava.lang.Object;@b4e2c4e0]
ONE records retrieved after insert
main_jobdone (java line: 600)


java.lang.NullPointerException
    at com.popularjcb.emptool.main._jobdone(main.java:600)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:515)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:187)
    at anywheresoftware.b4a.keywords.Common$5.run(Common.java:962)
    at android.os.Handler.handleCallback(Handler.java:733)
    at android.os.Handler.dispatchMessage(Handler.java:95)
    at android.os.Looper.loop(Looper.java:136)
    at android.app.ActivityThread.main(ActivityThread.java:5017)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:515)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:779)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:595)
    at dalvik.system.NativeStart.main(Native Method)
java.lang.NullPointerException

The purpose of posting this here in this thread is just to hear from you that whether am I somewhere near the goal of retrieving the OUT parameter value via RDC. OR is it that it is not possible with this present RDC. May be you know the reason for the error that I posted above.

This is my last try to get this done via RDC. If I get a confirmation from you, then, I will understand that there is no point in wasting time to get this done via RDC and move to some other solutions like php or MySQL libs to deal with the stored procedure OUT parameter situations

Thanks & Regards

Anser

 

[Peter Simpson]

@Anser so you are still having OUT issues then?

 

[keirS]

Dear Erel,

I played a little with the RDC and tried Calling a stored procedure with OUT parameter

<snip>

The purpose of posting this here in this thread is just to hear from you that whether am I somewhere near the goal of retrieving the OUT parameter value via RDC. OR is it that it is not possible with this present RDC. May be you know the reason for the error that I posted above.

This is my last try to get this done via RDC. If I get a confirmation from you, then, I will understand that there is no point in wasting time to get this done via RDC and move to some other solutions like php or MySQL libs to deal with the stored procedure OUT parameter situations

Thanks & Regards

Anser

That's not going to work with RDC because it uses connection pooling and MySQL variables (your out parameter) are scoped to a session (connection). You can't guarantee that your two queries will use the same connection in the pool so the variable will not be in scope which is why you get a null value back.

Saying that it might work with RDC if you can configure it to only have one connection in the pool. You would set the min_pool size and max_pool size to 1 in your c3po.properties file.

 

[Anser]

@keirS ,

Your explanation is perfect and direct hit to the target. Now I perfectly understand why it is not possible with RDC. Limiting the min and max connection pool size will be a limitation.

@Peter Simpson ,

That was my last try to check whether I could continue with RDC or not. Overall I like the RDC technology very much. This is the only limitation that I have come across regarding RDC.

I am a newbie here in B4A. I already invested many hours in this forum to grab information to understand how B4A works, RDC works etc. I achieved some knowledge on how to handle database via RDC and B4A. Changing the technology to handle database means I will have to spend more time once again and if I am lucky enough then I may get an apt sample that matches my requirement. Having such a sample will definitly boost the learning and development speed. So I was trying my best to stick on with RDC itself, what I know. Hope you understand.

Now it is very clear that, if I need to utilise the OUT parameter in a stored procedure, then I will have to move to other database handling solutions.

Regards
Anser

 

[DonManfred]

That was my last try to check whether I could continue with RDC or not. Overall I like the RDC technology very much. This is the only limitation that I have come across regarding RDC.

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

Maybe our MySQL-Stored-procedure-Profi (@Peter Simpson ) can help with an rewritten version of your SP!?

 

[keirS]

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

Maybe our MySQL-Stored-procedure-Profi (@Peter Simpson ) can help with an rewritten version of your SP!?

It would be a Function not an SP.

CREATE FUNCTION `GetCustomerLevel`(p_customerNumber int(11)) RETURNS varchar(20) CHARSET latin1
BEGIN
 DECLARE creditlim double;
 DECLARE p_customerLevel VARCHAR(20);
    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;

RETURN  p_customerLevel;
END

Then for the config file

sql.callsp_myspname= SELECT GetCustomerLevel(?)