MySQL stored procedures with Ruby

Ruby's getting an incredible amount of attention recently, largely as the result of Ruby on Rails.  I've played a little with Ruby on Rails and it certainly is the easiest way I've seen so far to develop  web interfaces to a back-end database.

At the same time,  I've been shifting from perl to Java as my language of choice for any serious database utility development.  But I still feel the need for something dynamic and hyper-productive when I'm writing something one-off or for my own use.  I've been playing with Python, but if Ruby has the upper ground as a web platform then maybe I should try Ruby. 

So seeing as how I've just finished the MySQL stored procedure book, first thing is to see if I can use Ruby for MySQL stored procedures.

Database - and MySQL - support for Ruby is kind of all over the place.  There's a DBI option (similar to perl) which provides a consistent interface and there's also native drivers.  For MySQL there are pure-ruby native drivers and drivers written in C.  Since the DBI is based on the native driver, I thought I'd try the native driver first.  The pure-ruby driver gave me some problems so I started with the C driver on Linux (RHAS4). 

Retrieving multiple result sets

The main trick with stored procedures is that they might return multiple result sets. OUT or INOUT parameters can be an issue too, but you can always work around that using session variables. 

If you try to call a stored procedure that returns a result set, you'll at first get a "procedure foo() can't return a result set in the given context error".  This is because the CLIENT_MULTI_RESULTS flag is not set by default when the connection is created.  Luckily we can set that in our own code:

dbh=Mysql.init
dbh.real_connect("127.0.0.1", "root", "secret", "prod",3306,nil,Mysql::CLIENT_MULTI_RESULTS)

The "query" method returns a result set as soon as it is called, but I found it easier to retrieve each result set manually, so i set the query_with_result attribute to false:

dbh.query_with_result=false

The next_result and more_results methods are implemented in the Ruby MySql driver, but there's some weird things about the more_results C API call that causes problems in python and PHP.  In Ruby, the more_results call returns true whether or not there is an additional result.   The only reliable way I found to determine if there is another result set is to try and grab the results and bail out if an exception fires (the exception doesn't generate an error code, btw);
      
    dbh.query("CALL foo()")
    begin
      rs=dbh.use_result
    rescue Mysql::Error => e 
      no_more_results=true
    end
.
We can then call more_results at the end of each rowset loop.  So here's a method that dumps all the result sets from a stored procedure call as XML using this approach (I'm know the Ruby is probably crap, it's like my 3rd Ruby program):

def procXML(dbh,sql)
  connect(dbh)
  no_more_results=false
  dbh.query(sql)
  printf("<?xml version='1.0'?>\n");
  printf("<proc sql=\"%s\">\n",sql)
  result_no=0
  until no_more_results
    begin
      rs=dbh.use_result
    rescue Mysql::Error => e 
      no_more_results=true
    end 
     if no_more_results==false
      result_no+=1
      colcount=rs.fetch_fields.size
      rowno=0
      printf("\t<resultset id=%d columns=%s>\n",result_no,colcount)
      rs.each do |row|
        rowno+=1
        printf "\t\t<row no=%d>\n",rowno
        rs.fetch_fields.each_with_index do |col,i|
          printf("\t\t\t<colvalue column=\"%s\">%s</colvalue>\n",col.name,row[i])
        end
        printf("\t\t</row>\n")
      end
      printf("\t</resultset>\n");
      rs.free
      dbh.next_result
    end
  end
  printf("</proc>\n")
end

No C programming required!

Whew!  No need to hack into the C code.  So you can use MySQL stored procedures in Ruby with the existing native C driver. The problem is that the C driver is not yet available as a binary on Windows yet and trying to compile it turns out to be beyond my old brain (and yes, I used minGW and all the other "right" things).   Hopefully a copy of the MySQL binary driver it will be available in the one-click installer Ruby installer eventually.

The above code doesn't work using the pure-Ruby driver on windows by the way -  there's an "out of sequence" error when trying to execute the stored proc.  I might hack around on that later (at the moment I'm 35,000 ft with 15 minutes of battery left on the way to the MySQL UC).  For now if you want to use MySQL stored procedures in a ruby program on windows I can't help.

Note that ruby seems to hit a bug that causes MySQL to go away if there are two calls to the same stored proc in the same session and the stored proc is created using server-side prepared statements.  Fixed soon hopefully, but for now if you get a "MySQL server has gone away error" you might be hitting the same problem.   Wez posted on this problem here.

I suppose the end of this investigation will probably be to see if there's any way to use stored procedure calls to maintain a Rails AcitveRecord object.  Not that I think you'd necessarily want to, but it would probably be a good learning exercise.

Comments

[Oliv]

Thanks a lot for your article, the CLIENT_MULTI_RESULTS saved me lots of time.

[Steve]

I've been round the houses trying to find a good explanation of accessing multiple result sets from a stored procedures. You're article is a great find by me, and a great post by yourself. Cheers.

[Ric]

Thanks for pointing out the CLIENT_MULTI_RESULTS stuff.

[Bill Bray]

The CLIENT_MULTI_RESULTS was a good call; it saved me more headache getting my Ruby project finished.

I'll be sure to give your take on recordsets a whirl, too.

Cheers!

[Justin Bailey]

This is a particular problem with Rails, because you have to hack the source to get the flag passed to the connection.

I've submitted a patch to Rails 2.3.4 that allows this flag to be passed via configuration. Please visit my ticket and show your support!

https://rails.lighthouseapp.com/projects/8994/tickets/3151-mysql-adapter-update-to-enable-use-of-stored-procedures

[Chatters Spokane]

hmm.. funny.