B4J Question DBM Module and Calling a Stored Procedure in MySQL database

Discussion in 'B4J Questions' started by Anser, Dec 1, 2017.

  1. Anser

    Anser Well-Known Member Licensed User

    Hi,

    I am testing ABMaterial. I am connecting to a MySQL database and I am able to make use of the DBM.SqlSelect() to get data from the MySQL Table using SELECT queries

    For Eg
    Code:
    Dim SQL As SQL = DBM.GetSQL

    Dim AcHeadsList As List = DBM.SQLSelect(SQL"SELECT FirstName, LastName FROM customer",Null)
    How do I call a Stored Procedure available in the database. I tried the following, unfortunately it is not returning any results.

    For Eg
    Code:
    Dim SQL As SQL = DBM.GetSQL

    Dim AcHeadsList As List = DBM.SQLSelect(SQL"CALL MyStoredProcedure('Parameter1', 'Parameter2') ",Null)
    When it comes to a enterprise application, there will be different GUI applications accessing the same common database for Eg. Android Application, Desktop application and Web application. The common database will contain the Stored Procedures and all the front end applications will make use of this stored procedures by calling it.

    Is this feature already available in the DBM Module ? I went thru the DBM Module code unfortunately I could not find anything special for calling Stored Procedures. I expected that DBM.SQLSelect() would server the purpose, unfortunately it is not.

    Any help will be appreciated.
     
  2. alwaysbusy

    alwaysbusy Expert Licensed User

  3. Anser

    Anser Well-Known Member Licensed User

    The DBM Module is working perfect for all the Database activities, except for calling a stored procedure. Hence I mentioned the [ABMaterial] in the question. I shall edit and remove the [ABMaterial] tag from the topic heading.
    This above link discuss about using php to execute a remote query and how to execute a stored Procedure thru php.

    I use Stored Procedures extensively in my database. In B4A, I use jRDC to call/execute Stored Procedure. Here I though DBM and ABMaterial, as a one single solution and do not require another server application (jRDC) serving the requests from clients.

    I believe that soon or later this becomes a requirement for many. When different applications connect and use a single databae, to avoid mistakes and complexities, the complex database queries and its rules are written inside stored procedures and Application programmers from different front end applications just make a call to this stored procedure.

    Now I am stuck at this issue now. Don't know how to move forward further. To keep another php server just to execute stored procedure does not look as an elegant solution. Shall keep digging further to find a solution that works along with ABMaterial
     
  4. alwaysbusy

    alwaysbusy Expert Licensed User

    As I said, this is a general database B4J question (callng a stored procedure in a MySQL database that is). I would be surprised if that wouldn't be possible.

    How about this one? https://www.b4x.com/android/forum/threads/sql-simplyfying-calling-stored-procedures.73181/

    So you probably want to wait till Erel gets up before making any big decisions :)

    Note that the DBM module is in there just as an example of how to use SOME database with ABM. After all, ABM is just a UI library. All the rest, like database access, is pure B4J.
     
    Anser likes this.
  5. Erel

    Erel Administrator Staff Member Licensed User

    jRDC is a B4J program. You can easily implement all of jRDC features in your server solution.

    Check SQL.CreateCallStatement and SQL.ExecCall.
     
    Anser likes this.
  6. magi6162

    magi6162 Well-Known Member Licensed User

    Anser likes this.
  7. Anser

    Anser Well-Known Member Licensed User

    Hi,

    I could execute the Stored Procedure and get the result using the following code.
    Code:
    Dim SQL As SQL = DBM.GetSQL

        
    Dim oStmntObj As Object = SQL.CreateCallStatement("CALL test()",Null)

        
    Dim AcHeadsList As ResultSet = SQL.ExecCall(oStmntObj)
     
        
    Do While AcHeadsList.NextRow
            
    Dim RowContents As List
            RowContents.Initialize
          
            
    Dim i As Int = AcHeadsList.GetInt("myCol1")
          
            RowContents.Add( AcHeadsList.GetString(
    "myCol1") )
            RowContents.Add( AcHeadsList.GetString(
    "myCol2") )
            tblCustomers.AddRow(  
    "uid" & i, RowContents)
          
        
    Loop

    Thank you all
     
  8. keirS

    keirS Well-Known Member Licensed User

    I wrote a library to simplify using stored procedures. It supports returning parameters and multiple result sets:

    https://www.b4x.com/android/forum/threads/sql-simplyfying-calling-stored-procedures.73181/
     
    Anser, inakigarm and magi6162 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