Home    | Software    | Articles    | Tips'n Tricks    | Contacts    | Support Us  
Home arrow Articles arrow SugarCRM integration with custom VB6 applications

SugarCRM integration with custom VB6 applications


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):

PocketSoap sdl wizard 1

Make sure the directory you indicate exists.
After clicking "Next", if everything is correct, we'll be shown the services found by the wizard:

PocketSoap wsdl wizard 2

Again, let's click "Next" and we'll be shown the list of operations (that is methods) available.

PocketSoap wsdl wizard 3

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
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
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)
MsgBox str
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
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.

Hasta la proxima.
Next >

  Articles RSS feed

Latest Articles
Latest Software
designed by allmambo.com