HomeArticles SugarCRM integration with custom VB6 applications
SugarCRM integration with custom VB6 applications
Introduction
Here we are with another article about SugarCRM integration with various languages/platforms. This time we'll discuss interacting with SugarCRM from VB6. Lots of developers and software houses have decided not to migrate their code from VB6 to VB.NET for various reasons, and I'm constantly being asked for mentoring and consulting by software houses needing integration between their legacy VB6 applications and SugarCRM. There are several ways to cope with SOAP in VB6, MS Soap Toolkit being one the best known and used, even if deprecated by Microsoft. We'll be using another tool, PocketSoap , which in my opinion is one of the most complete and easy to use.
PocketSoap is , as stated on its site: "an Open Source [MPL] SOAP client COM component for the Windows family". We'll use it to automatically build all the classes we need to interact with our SugarCRM installation.
Building our Proxy classes
Easy task, as all the hard work will be done by PocketSoap. Let's launch the program "PocketSOAP Wsdl Wizard". All it needs is the URL for the WSDL exposed by SugarCRM. Just for the lazy people, here is the definition of wsdl taken from Wikipedia: "The Web Services Description Language (WSDL, pronounced 'wiz-dəl' or spelled out, 'W-S-D-L') is an XML-based language that provides a model for describing Web services." Basically, a wsdl file formally describes the methods(operations) exposed by a web service and all the data types used. Using it, pocketsoap wsdl wizard is able to translate the calls to these methods to proper VB code. Now, this is more or less what we'll input in the first form of the wizard("yourhost" being the IP of your SugarCRM installation):
Make sure the directory you indicate exists. After clicking "Next", if everything is correct, we'll be shown the services found by the wizard:
Again, let's click "Next" and we'll be shown the list of operations (that is methods) available.
Make sure they are all selected, and click "Next" once again. At this point, in the previously selected directory, we'll find an "ActiveX DLL" VB6 project containing all the classes we need. The generator will automatically add PocketSoap Type Library to the list of references of our project. Now it's time to use the generated code to connect to SugarCRM.
Let's login
Let's create a VB project(a simple exe project will be just fine) and add our previously generated ActiveX DLL as reference. The first lines of code we need are:
Dim soap As sugarsoapPortType Set soap = New sugarsoapPortType
to instantiate the proxy class we'll use to call the various operations. The first call we'll do is "login", to obtain a session id required by most other calls. "login" requires as parameters a "userauth" type and a custom application name. "userauth" is composed of: a username, an md5 encoded password and a version string. Here is the code:
Dim user As userauth Dim loginresult As setentryresult
Set user = New userauth
user.username = "admin" user.password = "526ee3641d541a646b4adff94e97dc8d" user.version = "1.0" Set loginresult = soap.login(user, "test") If loginresult.Error.Number <> 0 Then MsgBox loginresult.Error.Description + " - " + loginresult.Error.Description Else MsgBox loginresult.id End If
"setentryresult" is the data type we get back from the call. It will contain an error object(with number,name and description properties), and an "id" string. If the login succeeds, "loginresult.Error.Number" wil be "0" and "loginresult.id" will contain a valid session id we'll use later. To get the md5 hash of the password, there are plenty of online services, like this . For example the md5 you see in the code corresponds to the word "adminpassword". "user.version" seems not to be relevant,and the second parameter of the "soap.login" call ("test" in this case) is just a custom name for your application. Now that we have a valid session id , we can call more interesting methods exposed by SugarCRM Soap interface.
Getting data from SugarCRM
We'll query SugarCRM's "Contact" module to get some data. The method involved is "get_entry_list" and the parameters it expects are:
a valid session id, the one we obtained with the "login" operation
the module name, "Contacts" in our case (could be any of SugarCRM modules)
a query, the sql "WHERE" clause
the records order, the sql "ORDER BY" clause
an offset, used in case of pagination of records, where to start in the recordset
a string array containing the fields required in the returned recordset
the maximun number of records to return
a "deleted" flag. If not 0 also deleted records will be returned(SugarCRM doesn't physically delete records, it just flags them as deleted)
Just like before, we first instance the various data type objects we need.
Dim entrylistresult As getentrylistresult Dim elist() As entryvalue Dim nvaluelist() As namevalue Dim fields(0 To 3) As String fields(0) = "first_name" fields(1) = "last_name" fields(2) = "email1"
Dim str As String
And finally we call the "get_entry_list" method.
Set entrylistresult = soap.get_entry_list(loginresult.id, "Contacts", "contacts.last_name <>''", "contacts.last_name desc", 0, fields, 20, 0) If entrylistresult.Error.Number <> 0 Then MsgBox entrylistresult.Error.Description + " - " + entrylistresult.Error.Description Else elist = entrylistresult.entrylist For t = 0 To UBound(elist) nvaluelist = elist(t).namevaluelist str = "" For t1 = 0 To UBound(nvaluelist) str = str + nvaluelist(t1).Name + " -" + nvaluelist(t1).Value + Chr$(10) + Chr$(13) Next MsgBox str Next End If
"entrylistresult", of type "getentrylistresult", contains various data. What we use here is the usual "error" object and "entrylist",which is an array of "entryvalue", representing the recordset. Each entryvalue is in turn an array of "namevalue" objects, representing the single field as a name/value pair.
Writing data to SugarCRM
The last method we're going to see is "set_entry". We use it to write a record in SugarCRM. It requires the following parameters:
a valid session id, the one we obtained with the "login" operation
the module name, "Contacts" in our case (could be any of SugarCRM modules)
a "namevalue" array , containing the list of fields of the record as a name/value pair.
Here is how we do it:
Dim sentryresult As setentryresult
Dim namevaluelist(0 To 2) As namevalue Set namevaluelist(0) = New namevalue namevaluelist(0).Name = "first_name" namevaluelist(0).Value = "ppppp" Set namevaluelist(1) = New namevalue namevaluelist(1).Name = "last_name" namevaluelist(1).Value = "lllll" Set namevaluelist(2) = New namevalue namevaluelist(2).Name = "email1" namevaluelist(2).Value = "
This e-mail address is being protected from spam bots, you need JavaScript enabled to view it
"
Set sentryresult = soap.set_entry(loginresult.id, "Contacts", namevaluelist) If sentryresult.Error.Number <> 0 Then MsgBox sentryresult.Error.Description + " - " + sentryresult.Error.Description Else MsgBox sentryresult.id End If
the result of our call is of type "setentryresult", just like we saw in the "login" call, and actually it will contain an "error" object and the "id" of the record. One important thing to note is that if one of the "namevalue" pair we pass has name "id" and the value owns to an existing record, we will be doing an update,otherwise a new record will be created. That's all for today.
Articles 
