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.
