MySQL stored procedure

Databases
1

morning,

not sure if this was asked already, couldn’t find anything …

if have a stored proc on a MariaDB server, this proc does what it is suppose to do - get me some results :wink:

I can execute this proc via different tools and proc languages, but with my XOJO I have problems. The execution does only work ones and than I get >> DB Error: Commands out of sync; you can’t run this command now<<

I can’t figure out what I am doing work… any idea?

THX

Sub GetTotalByByMonth(year as string)
    
  Var ColorbyMonthRS As RowSet
  byMonthRS =   db.GetTotalByMonth(year)
  
  If byMonthRS <> Nil Then
  //do whatever is needed
    ColorbyMonthRS.Close    
  end if
  
End Sub

Function GetTotalByByMonth(year as string) As RowSet
  Var sql As String
  
  sql="CALL sp_ValueMonth('"+year+"')" //this can be executed once

  Try
    dim rs As RowSet =  app.mdb.SelectSQL(sql)
    
    Return rs
  Catch e As DatabaseException
    MessageBox("DB Error: " + e.Message)
    
  End Try
End Function
2

If you get “you can’t run this command now”, you are calling client functions in the wrong order. This can happen, for example, if you are using mysql_use_result() and try to execute a new query before you have called mysql_free_result() .

3

Hi, sure that is clear.

I am wondering why this does only happen after calling a SUB…

4

hi, have spend a lot of time on this one and it is going nowhere …

I have no idea how I can call a Proc / Func on a MySQL sever several times without getting a out of sync .
The mentioned mysql_free_result also raises a question mark, how should I call it, I am not using PHP :wink:

can somebody help?

THX

5

Well, I think these commands are (or should be) called from the plugin internally, so if it doesn’t work you’d have to report it. In your shoes I’d give Christian’s MBS SQL-Plugin a shot.

6

If anyone likes to use MBS Xojo SQL Plugin for this.
You pass the name of the stored procedure to SQLCommandMBS constructor or Prepare function. Then the plugin will lookup parameters, so you can fill them. Finally run the command.

e.g. check blog post:
Call stored procedures with output parameters

1 Like
7

Another way around the issue might be to create functions instead of stored procedures in your MySQL instance. Functions can be called just like any old SQL select-statement. Look here for a good discussion about functions vs. stored procedures in MySQL.

8

that is unfortunately not possible as the server is in production with the current DB.

9

OK, will have a look later today