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

Discussion in 'Android Questions' started by Anser, Mar 3, 2015.

  1. Anser

    Anser Well-Known Member Licensed User

    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
    Code:
    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 >= 10000THEN
            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:

    Code:
    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

    Code:
    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
    Code:
    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
     
    Last edited: Mar 3, 2015
  2. Erel

    Erel Administrator Staff Member Licensed User

    It is currently not possible with RDC.

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

    Anser Well-Known Member Licensed User

    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
     
  4. Erel

    Erel Administrator Staff Member Licensed User

    Currently yes.

    I don't know.
     
  5. Anser

    Anser Well-Known Member Licensed User

    Dear Erel,

    Thanks for the reply.

    Regards
    Anser
     
  6. Erel

    Erel Administrator Staff Member Licensed User

  7. Anser

    Anser Well-Known Member Licensed User

    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
     
  8. incendio

    incendio Well-Known Member Licensed User

    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.
     
  9. Anser

    Anser Well-Known Member Licensed User

    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
     
    Last edited: Mar 8, 2015
  10. Anser

    Anser Well-Known Member Licensed User

    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
     
  11. incendio

    incendio Well-Known Member Licensed User

    I don't know with MySQL, if using Firebird, then your sample would be something like this
    Code:
    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 >= 10000THEN 
           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.
     
  12. Anser

    Anser Well-Known Member Licensed User

    Thank you for the sample.

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

    Regards
    Anser
     
  13. keirS

    keirS Well-Known Member Licensed User

    There is one now :)
     
  14. Anser

    Anser Well-Known Member Licensed User

    Thanks. Looks promising.

    Regards
    Anser
     
  15. Anser

    Anser Well-Known Member Licensed User

    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.
    Code:
    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
    Code:
    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
    Code:
    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
    Code:
    ** 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
     
  16. Peter Simpson

    Peter Simpson Expert Licensed User

    @Anser so you are still having OUT issues then?
     
  17. keirS

    keirS Well-Known Member Licensed User

    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 likes this.
  18. Anser

    Anser Well-Known Member Licensed User

    @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
     
  19. DonManfred

    DonManfred Expert Licensed User

    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 :D) can help with an rewritten version of your SP!?
     
    Anser likes this.
  20. keirS

    keirS Well-Known Member Licensed User


    It would be a Function not an SP.

    Code:
    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 >= 10000THEN
            SET p_customerLevel = 
    'GOLD';
        ELSEIF creditlim < 10000 THEN
            SET p_customerLevel = 
    'SILVER';
        END IF;

    RETURN  p_customerLevel;
    END
    Then for the config file

    Code:
    sql.callsp_myspname= SELECT GetCustomerLevel(?)
     
    Anser and DonManfred like this.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice