Using ADO in LotusScript

Friends, few days I have been working with Lotus Notes and Ms-SQL 2005 integration and used a technology called ADO (ActiveX Data Objects) to manipulate SQL data. The experience was quite unique and was easy to implement. ADO uses both DSN and DSN less connections, to use ADO objects you donot require any registered lotus library, but to use the createObject method of lotus script.


e.g.
set connObj = createObject("ADODB.Connection")
set recSetObj = createObject("ADODB.Recordset")


' To open a connection with a RDBMS use the following lines
connObj.open = "Provider=SQLOLEDB;Initial Catalog =DatabaseName;Data Source =SQLServer;uid=userID;pwd=password"


' I have only listed two of the drivers, there are more and can be found in the Microsoft website.
drivers available :
Microsoft SQL Server - SQLOLEDB
Oracle databases - MSDAORA


' To execute a query you can use the following lines
set recSetObj = connObj.execute("select * from tableName",myvar)


' mvar stores the number of records effected by executing the query
totalCols = recSetObj.Fields ' Number of columns return by the query executed, returned as an array.
print totalCols(0).value ' returns the value of the first column

This are just few lines of code help you to create an ADO connection and manipulate SQL data with your LotusScript code, and yes one more thing dont forget to close the connection after you are done by the following line of code.

connObj.close