How to connect to DB2 from Lotus Notes Client via LotusScript?

Question

Just simple using JDBC driver in Java agent works fine. Now I need to connect DB2 from LotusScript. There are many articles like those: http://www.proudprogrammer.no/web/ppblog.nsf/d6plinks/GANI-9DFMRB https://openntf.org/XSnippets.nsf/snippet.xsp?id=db2-run-from-lotusscript-into-notes-form

but they use ODBC connection or something else. Anyway I don't see where I can define DB2 host and port in my LotusScript agent. Users won't be able to configure ODBC connection on each workstation. I need some Domino native method to connect to DB2. Or where do I define DB2 host/IP and port in this example: https://openntf.org/XSnippets.nsf/snippet.xsp?id=db2-run-from-lotusscript-into-notes-form

There is no "Domino native method to connect to DB2" -- it relies on the Db2 client that must be installed and configured where you run your LotusScript code. — mustaccio
so there is no any script library (like JDBC java driver) for LotusScript? — John Glabb
That 'library' is in the Db2-client (for example, the Db2-runtime-client) and in this case the interface is the CLI interface (not jdbc), and yes that client can be installed unattended (via a response file) if not already present, also from a script (e.g. a powershell script or a CMD script , or bash etc) and then the DSN can be created and configured also by an unattended script that can be invoked from LotusScript if you want. — mao
I want users let to get data from DB2. It's not good idea ask thousands of them to configure their systems accordingly. They want to run just simple LotusScrip agent. If you have a script that does it - please put it here — John Glabb

David Morton David Morton · Accepted Answer · 2019-08-13T16:21:57

You could use LSXODBC library but that is deprecated so you probably shouldn't. The current supported method is to use the LSXLC library but be warned that it provides a very OO-centric approach to sending/consuming data but it is very quick and if you use it as designed, can make moving data from one data provider (say Notes) to another (say DB2) somewhat easy.

If you want to stick with standard SQL strings you can still do that with LSXLC with the "execute" method off of the LSConnection object.

As far as connecting to it goes you just need to make sure the appropriate driver is installed on the machine and then use the appropriate connection parameter in the when creating a new LSConnect object (e.g., ODBC2 for ODBC, DB2 for the CLI DB2 driver, OLEDB for an SQL OLE driver, etc).

If you stick with ODBC or OLEDB you can control the connection string via code. If you use the CLI DB2 driver (which is very, very fast) you need to configure the connection on each machine the driver is installed on.

All this is documented in the Designer help but it is, in my opinion, not organized in the best fashion. But it is all there.

So, some example code that has been largely copied from some code I have sitting around and is not tested is:

Option Declare

UseLSX "*lsxlc"

Sub Initialize
Dim LCSession As LCSession
Dim lcRDBMS As LCConnection
dim lcFieldList as new LCFieldList()
dim lcField_FirstName as LCField
dim lcField_LastName as LCField

dim strFirstName as string
dim strLastName as string

dim strConnectionType as string

' Hard-coding this here just for this example
' I think you will either want an ODBC (odbc2) or a CLI DB2 (db2) connection
strConnectionType = "odbc2"

Set lcRDBMS = New LCConnection (strConnectionType)

' Set some standard properties on the LCConnection object
lcRDBMS.Userid="<userid>"
lcRDBMS.Password="<password>"   
lcRDBMS.MapByName=True

' Properties and property values that are different
' depending on the connection type
select case strConnectionType
    case "odbc2" :
        ' Use the DSN name as configured in the ODBC Control Panel (if on Windows)
        lcRDMBS.Database = "<SYSTEMDSN>"
    case "oledb" :
        lcRDBMS.Server = "<myserver.company.net>"
        lcRDBMS.Provider = "sqloledb"
        lcRDBMS.Database = "<my_database_name>"

        ' Not sure this actually changes anything or is even setting the correct property
        ' But the intent is to make sure the connection to the server is encrypted
        lcRDBMS.INIT_ProviderString = "ENCRYPT=TRUE"
    case "db2" :
        ' I am afraid I have lost the connection properties we used to use
        ' to form up a DB2 CLI connection so the following is just a best guess
        ' But if you are not going to be using the advance features of LSX to 
        ' connect to DB2 you might as well just a standard ODBC driver/connection
        lcRDBMS.Database = "<connection_name>"
End Select

Call lcRDBMS.Connect()

' This call returns a status code and populate the lcFieldList object with our results
lngQueryStatus = LcRDBMS.Execute("<Select FirstName, LastName from SCHEMA.Table WHERE blah>", lcFieldList)

If lngQueryStatus <> 0 Then
    If lcFieldList_Destination.Recordcount > 0 Then

        ' Get our fields out of the lcFieldList object before going into the loop.
        ' Much more performant
        Set lcField_FirstName = lcFieldList.Lookup("FirstName")
        Set lcField_LastName = lcFieldList.Lookup("LastName")

        While (lcConn.Fetch(lcFieldList) > 0 )
            strFirstName = lcField_FirstName.Text(0)
            strLastName = lcField_LastName.Text(0)

            ' Do something here with values
        Wend
    End If
End If
End Sub

How to connect to DB2 from Lotus Notes Client via LotusScript?

1 Answers