Topic: How does find_by_sql behave with webservices?

I'm currently working on an application whose central component is based on Rails and has several outlying components based on .NET. The communication is all done using SOAP.

I currently have a method in Rails, exposed through the webservice that has a call like this:

def get_config_tree(uid, orgid, compid)
    return Componentgroup.find_by_sql(['SELECT <NDA SNIP!> WHERE = ? AND u.organization_id = ? AND = ? ', uid, orgid, compid])

That method is then exposed by my web service API as:
#get config for phone gateway
  api_method :get_config_tree,
    :expects => [{:user_id, :int}, {:org_id, :int}, {:comp_id, :int}],
    :returns => [[Componentgroup]]

So .NET gets the data back as a ComponentGroup array. I know this isn't right because in my find_by_sql I get more data back than just ComponentGroups. What is the correct way to call find_by_sql and return it as a generic datatype? I'm hoping to wind up with at least an array if not some other, more easily manipulated structure.

Re: How does find_by_sql behave with webservices?

I've decided to use ActiveRecord::Base.connection.execute then map that data into an array made up of my own custom class to hold the data.

I'm still having some trouble figuring out how to read SQL result sets though.

Re: How does find_by_sql behave with webservices?

I believe it returns an instance of MySQL::Result. Check out the documentation for what you can do with this object.

Railscasts - Free Ruby on Rails Screencasts

Re: How does find_by_sql behave with webservices?

I found it. I had buggered up the loop on the resulting object and never reset the pointer so I kept getting nil's. Once I reran the query it was fine and I was able to get the right data and fix my loop.

Code just in case this comes up in a search and someone needs it:

result = ActiveRecord::Base.connection.execute('SELECT <SNIP!! Normal SQL goes here>')
     retval =
     k = result.fetch_row
     while(k != nil)
      newitem = = k[0]
      newitem.parameter = k[1]
      newitem.value = k[2]
      newitem.stored_string = k[3]
      newitem.stored_float = k[4]
      k = result.fetch_row

    return retval

Last edited by jbartels (2007-03-19 16:39:37)