Android Question Calling a MySQL StoredProcedure in simple way? Which library recommend me to use???

demonio_jack

Member
Licensed User
Hi everyone:

I been learning a lot from all of you and it's incredible the knowledge base we have available. BTW I've searching for a standard solution to call a MySQL stored procedure from my Android solution. In some case the solutions codes published in this forums "assumens" we know libraries required, but is not clear which one are.
Anyone of you that could share a simple example solution to call a store procedure with 2 input and 1 out parameters????

Thanks.
 

MarcoRome

Expert
Licensed User
Longtime User
 
Upvote 0

demonio_jack

Member
Licensed User
Thanks MarcoRome,
However, I think I did not explain myself well. I am developing a small application in B4A so that some users can consult data from a MySQL database. I am not using php (and this may be an error) or web services, it is only a first app that queries data and performs actions to insert and update records.
I have followed the examples of Erel and other experts and it works great for running asynchronous queries. But to call stored procedures I can't find the way.
This is an example of the procedure I use:

Any idea? Wich library need to use???
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Any idea? Wich library need to use???
The same one you are already using (JdbcSQL). Have you checked out CreateCallStatement/ExecCall (https://www.b4x.com/android/help/jdbcsql.html#jdbcsql_createcallstatement)?

Examples (just a couple I found here on the forum):
1) https://www.b4x.com/android/forum/t...ing-set-var-with-execquery2.85520/post-541844
2) This one is MSSQL, but still shows running stored procedures: https://www.b4x.com/android/forum/t...-procedure-with-in-out-params.106694/#content
 
Upvote 0

Albert Kallal

Active Member
Licensed User
You can use the SAME code and library you are using now for simple SELECTS - that is ALL you need!

I not done this with MySQL - but I often do this SQL server.

However, before you try a store procedure. (No need to start out with brain surgery here), do you have a basic and simple SQL select working now?

ONCE you get a basic SQL select working, then the NEXT step and mountain to climb is to get store procedures working.

There are of course two types of store procedures.

Ones that you run – they don’t return values. For these, you simply call + use the same command to run ANY sql command (say an update, or insert command). These commands don’t return data.

So, “existing” code you have to say execute a UPDATE statement can be used to call store procs.

So these so called “action” queries (updates, inserts, and call/run store procs) don’t return data. So, the command for this in all 3 cases is the same.

The last one, returning parameters?

Well, what one can do in these cases is turn the store proc into a standard SELECT statement, and the parameters returned back are thus retuned back as a data set.

Now, what if that store proc is NOT yours to re-write?

Well, if you can’t (or don’t want to) re-write the store proc to return a data-set? (And often store process do!!!), then what you do add a “select” statement RIGHT after the store proc that gets/grabs the return parameters and retunes them as a data set anyway!

So convert the parameters to a select, and you off to the races here.

So, typical MySQL looks like this:
B4X:
Call GetOrderCountByStatus('Edmonton', @OrderCount)

In above, we have an input parameter of City, and an output of the count of orders from that store proc.
Now, if you not using an object model that supports output parameters (say ODBC!!!), then simply convert the above into a select command. Now you can use the standard connection ad object model you are stuck with!

B4X:
Call GetOrderCountByStatus('Edmonton', @OrderCount);
SELECT @OrderCount;

So, the above will return a dataset of one row!!!
(And the first column will be order Count).

So, now you can use the SAME code as if you were going to execute a select command. (And this is why I stated that you of course WAY LONG TIME ago got the basic connection working, then achieved the baby steps of ensuring that simple SELECTS against the database now work).

Really, all you need here is the ability to execute selects on the database. With the above Rosetta stone concept (converting return values into a select), then the SAME approach and SAME code used for a plane jane SELECT command can now be used to call store procs, and the return values come back like any old plane jane data set.
So, in your code, simply convert the store proc to a select like this:

B4X:
     Dim strSQL As String = "Call GetOrderCountByCity('Edmonton', @OrderCount);" & _
                               "SELECT @OrderCount;"
   Dim rst As JdbcResultSet
     Try
          Dim sf As Object = Main.mysql.ExecQueryAsync("mysqlWAIT", strSQL ,Null)
          Wait For (sf) mysqlWAIT_QueryComplete(Success As Boolean,   rst As JdbcResultSet)
          If Success Then
              Dim sOrderCount As Long = 0
              rst.NextRow
              sOrderCount = rst.GetLong("@OrderCount")
          End If
          rst.Close
     Catch
          Success = False
          Log(LastException)
     End Try

So, in above, we plucked out the Order count return parameter. And the way we did this was toss in that EXTRA line with a simple select that gets/grabs the return value as a data set.

So, if it is one, or 5 return values, just toss in that extra select line right after the call to the store proc, select the parameter(s) you need, and the whole she-bang will be returned as a simple row of data.

So, the above approach gets around having to use a specific object model that supports input, and out parameters.

You often will not have the luxury of some object model that supports JUST input and output parameters to that is SPECIFIC to some particular dialect of SQL server, oracle, or in this case MySQL.

However, to my knowledge, ALL of these systems (and a good many not mentioned here) all tend to have a “relative” easy way of converting the return values from store process into a simple SELECT statement that results in the return values being viewed as a simple row of data.

So, if you already taken the baby steps and simple SELECTS and your simple connection to the database works all fine and dandy?

Once you have the baby steps and simple SELECT’s working? Then apply the above logic and approach, and you have a solution that now works for retuning values from a store procedure.

Regards,
Albert D. Kallal.
Edmonton, Alberta Canada
 
Last edited:
Upvote 0
Cookies are required to use this site. You must accept them to continue using the site. Learn more…