Introduction to COM+ in Camelot .NET Connector 2

The Camelot .NET Connector version 2 comes with COM+ support out of the box. This extends the Connector to be used in a large range of applications running on windows, including VB Script, ASP Classic and PHP for IIS. In this article I will give a very simple example that shows how to get started using VB Script. This adds many new possibilities to create maintenance scripts, automated tasks and integrations with enterprise applications with minimal effort.

This blog is based on the VB Script example that comes with the Connector version 2 located under “$installpath\Bendsoft\CamelotSharePointConnector_v2\Examples\VBScript”.

Requirements

• The Camelot .NET Connector version 2.

The COM+ component is installed with the Connector on the same machine. You should be able to see it under Component Services in Windows.

The COM+ methods are more or less the same as the ones that are offered with the Camelot Integration Toolkit For SharePoint.

• DownloadFile (download file from document library)
• Execute (execute any query and returns the results)
• ExecuteCount (returns the number of rows of query)
• ExecuteNonQuery (execute any query without returning any results)
• ExecuteScalar (execute any query and returns the first column of the first row)
• GetUser (shows the user under which the query is executed)
• UploadFile (upload file to document library)

Editing the VB Script file

Simply make a copy of the example script included with the Connector. Change the associated connection string and edit the SQL command to any SELECT command of your choice. I created a small list called “Customers” in our example that contains Name and Email of each customer. The example includes some basic fault handling. The output is by shown in a standard message box using the WScript.Echo method. Therefore, it can be a good idea to set a limit to the number of rows retrieved in this particular example.

On Error Resume Next

Dim connectionString
connectionString = "Server=sharepointserver.com;User=username;Password=xxxxx;Database=;Authentication=Ntlm;RecursiveMode=RecursiveAll;"

Dim connector
Set Connector = CreateObject("Camelot.SharePointConnector.Com.Connector")

Dim sql
sql = "SELECT * FROM `Customers` ORDER BY ID ASC LIMIT 5"
	
Dim data
data = connector.Execute(sql, connectionString)

If Err.Number <> 0 Then
	WScript.Echo "Error: " &  Err.Description
	WScript.Quit
End If

Dim i, j
If IsArray(data) Then
	Dim s
	For i = 0 To UBound(data, 1)
	    For j = 0 To UBound(data, 2)
	    	If Not IsArray(data(i, j)) Then
		    	If j = 0 Then
		    		s = s & data(i, j) & ""
		    	Else
		        	s = s & " : " & data(i, j)
		    	End If
			Else
				dim k
				dim s2
				For k = 0 To UBound(data(i, j))
					s2 = s2 & "" & "[" & data(i, j)(k) & "]"
				Next	    	
		    	If j = 0 Then
		    		s = s2 & ""
		    	Else
		        	s = s & " : " & s2
		    	End If
	    	End If
	    Next
	    s = s & vbCrLf
	Next
	WScript.Echo(s)
Else
	WScript.Echo("No data")
End If

Test the script

Simply click on your script file and see what happens. If the connection string is correct and the SQL statement is ok, you should see a popup message similar to the below.