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

[Anser]

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

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

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.

 

[alwaysbusy]

You may want to remove the [ABMaterial] tag in your subject to get more responses to this question, as it is not directly related to ABMaterial, but to general use of databases in B4J.

This may be helpful:

https://www.b4x.com/android/forum/threads/stored-procedure-with-parameter-in-out.82413/#content

 

[Anser]

You may want to remove the [ABMaterial] tag in your subject to get more responses to this question, as it is not directly related to ABMaterial, but to general use of databases in B4J.

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.

https://www.b4x.com/android/forum/threads/stored-procedure-with-parameter-in-out.82413/#content

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

 

[alwaysbusy]

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.

 

[Erel]

Here I though DBM and ABMaterial, as a one single solution and do not require another server application (jRDC) serving the requests from clients.

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

Check SQL.CreateCallStatement and SQL.ExecCall.

 

[Anser]

Hi,

I could execute the Stored Procedure and get the result using the following 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

 

[keirS]

Hi,

I could execute the Stored Procedure and get the result using the following 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

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/