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