Month: January 2008

After slogging through connecting Rails and Sugar via SOAP, I was tired and frustrated. The API is slow, and doing anything meaningful took a long time (ok, it took 30 seconds, but that seems slow to me; aren’t computers supposed to be fast?!). So, I came up with an alternative approach.

I know that Active Record (or whatever it’s called) in Rails is really just a fancy wrapper for the database. So, I created a second database connection, directly to the Sugar database!

You just have to setup new models and controllers in Rails. But, that’s not too hard. See this solution (http://pragdave.pragprog.com/pragdave/2006/01/sharing_externa.html). Use option 3. It worked well. Once you get the mapping figured out, you can even use the join tables, relationships, etc. It all works transparently.

I use this to synchronize various objects and fields.

Of course, you have to have access to the database. And, of course, I know I’m majorly in danger of screwing something up. And you have to figure out the inner workings of the Sugar database. And, of course, this might not work on the next version, etc., etc.

But, heck it is fast! And very easy to use!

If you want to know more, just let me know.

I have long wanted to make sure my CRM system (SugarCRM) and my project management system synchonized certain data, mainly company names. I hate having to sync stuff like that manually. So, I’ve been working on integrating the data using a SOAP client on the Rails side. It took all day to get this working. I don’t know why this took forever to find out, or why there aren’t many good references on the Web. (Maybe I’m just dense!)

I had to patch together a bunch of code, re-code some PHP examples, and do quite a bit of experimenting, to get this all working. Here is a list of some of the sites I used:

http://www.sugarcrm.com/forums/showthread.php?t=17954&highlight=get_entry_list

Good PHP examples about how certain SOAP calls are structured.

http://www.ruby-doc.org/stdlib/libdoc/soap/rdoc/index.html

Basics on how the SOAP objects work. Nothing really useful here, but it helped a little.

http://www.beanizer.org/site/content/view/2/29/lang,en/

Another good PHP reference.

http://kousenit.wordpress.com/2006/08/08/ruby-web-service-clients/

Has some general SOAP info, but not on SugarCRM

http://www.sugarcrm.com/wiki/index.php?title=SOAP_in_RUBY

Sugar’s own starter directions. This is the backbone of the code below.

http://www.sugarcrm.com/wiki/index.php?title=SOAP_Documentation

Sugar’s SOAP documentation. I have to admit that this doesn’t make a lot of sense to me.

http://martyhaught.com/articles/2006/08/04/mapping-soap-response-without-wsdl/

Good example of how confusing this can be. Note that Marty’s situation is exactly where I got stuck. It turns out that these “special” attributes in the SOAP Mapping Object response (__xmlattr, __xmele, etc.) are not really important to the programmer. You can access the data with special attributes that refer to the attributes returned from the server. At least that’s my theory. So, in my example below, you access the results you want using the “result.entry_list” attribute, because the “get_entry_list” command returns that variable. Note that when you examine the results object, you don’t see these special attributes. I’m sure this is some special Ruby thing that I don’t understand.

Anyway, here’s the code for getting a list of all companies, called “Accounts” in SugarCRM, into Rails in an array.

 def list_accounts
    require 'soap/wsdlDriver'
    require 'digest/md5'
    u = "username"
    p = Digest::MD5.hexdigest("password")
    ua = {"user_name" => u,"password" => p}
    wsdl = "http://your-sugar-web-site.com/soap.php?wsdl"

    #create soap
    s = SOAP::WSDLDriverFactory.new(wsdl).create_rpc_driver

    #uncomment this line for debugging. saves xml packets to files
    #s.wiredump_file_base = "soapresult"

    #create session
    ss = s.login(ua,nil)

    #check for login errors
    if ss.error.number.to_i != 0 

    	#status message
    	logger.debug "failed to login - #{ss.error.description}"

    	#exit program
    	exit

    else

    	#get id
    	sid = ss['id']

    	#get current user id
    	uid = s.get_user_id(sid)

    	#status message
    	logger.debug "logged in to session #{sid} as #{u} (#{uid})"

         #the part below is general. you can use it to get any type of data you want. just change the "module_name"

         module_name = "Accounts"

         query = "" # gets all the acounts, you can also use SQL like "accounts.name like '%company%'"
         order_by = "" # in default order. you can also use SQL like "accounts.name"
         offset = 0 # I guess this is like the SQL offset
         select_fields = ['name','industry'] # this can't be an empty array, my testing showed
         max_results = "1000000" # if set to 0 or "", this doesn't return all the results, like you'd expect
         deleted = 0 # whether you want to retrieve deleted records, too

         result = s.get_entry_list(sid,module_name,query,order_by,offset,select_fields,max_results,deleted)

         #below is where we build the array of names. note that everything gets returns in a name-value pairs hash (name_value_list), using the field list from the request

         @output = []
         for entry in result.entry_list
            item = {}
            for name_value in entry.name_value_list
              item[name_value.name]=name_value.value
            end
           @output << item
         end
        
    	#logout
    	s.logout(sid)

    	#status message
    	logger.debug "logged out"
    	    	
    end