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”.
- 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.