In my previous post I was explaining how to install the IBM Data Server Runtime Client, including some ODBC DB2 drivers to access a DB2 Database remotely. In this new post I explain the bare minimum to install in order to access a DB2 Data Source via LotusScript Extension for Lotus Connectors (LS LSX-LC).
By the way, the ODBC DataDirect Lotus-branded drivers might be available only to paying customers ...
A. Installing the DB2 ODBC CLI drivers
First you need to install the DB2 Client Drivers on the System (which are different from the those that come with the IBM Data Server Runtime Client). I am using a Windows server, so after download I just unzip either v9.7fp3a_nt32_odbc_cli.zip (23 bits) or v9.7fp3a_ntx64_odbc_cli.zip (64 bits) in a folder (e.g. C:\clidriver).
Then I open a command prompt and navigate to my folder (e.g. C:\clidriver\bin) and type:
db2oreg1.exe -i -setup
Immediately following this you can set up the data sources in ODBC. Open Control Panel -> Administrative Tools -> Data Sources (ODBC).
You will see a screen that looks like the following, click on the "System DSN" tab:
Click "Add..." button to get the following, and select "IBM Data Server Driver for ODBC - C:/clidriver:
Enter a Data Source name, this is used directly in the Lotus Script( use the same name as the Database itself):
Enter the DB2 User ID and password that the agent uses to connect to DB2:
Select the "Save Password" option and click OK on the warning popup for saving the password in db2cli.ini file:
Click on the "Advanced Settings" tab:
Click "Add" and select the "Database" CLI Parameter:
Enter the Database name in the prompt and click OK:
Continue to do this for these parameters:
- Database: The database name
- Hostname: The DB2 server/host name (IP address is not recommended)
- Port: 50000 (The default value for DB2 TCPIP accepting port)
B. Accessing the DB2 Database from LotusScript
1. Get access to the Lotus Connector Extensions (this is always installed)
Option Public
Option Explicit
UseLSX "*lsxlc"
2. Create the LCSession object at the top of all functions or subroutines
Dim session As New LCSession
3. Enable Connection pooling
session.ConnectionPooling = true
4. Create the Connection, using the LCConnection class's constructor that takes a single argument (the name of the connector type). We're using ODBC, which has the Lotus Connector name of "odbc2".
Dim conn As New LCConnection ("odbc2")
conn.Server = "RLS" 'Using the ODBC DATA SOURCE name created previously.
conn.Connect
5. When done with the connection, disconnect - This will not actually disconnect if connection pooling is enabled
conn.Disconnect
Queries
Querying DB2 from a LCConnection object takes a couple of variables for holding the field names. There are multiple ways to issue a query:
LCConnection Execute
The execute command takes a full SQL statement, which is useful to capture complex queries. Unfortunately LSX LC (like LS:DO) does not support any kind of parameterized query syntax or method calls. This means that the parameter values sent to the database need to be encoded specifically for DB2. This kind of encoding may be difficult from LotusScript, and therefore it is recommended that for complex queries we use stored procedures in either SQL or Java. For simple select queries involving one table (or potentially view) and "ANDed" WHERE clause predicates, one can use the Select method against the LCConnection class.Execute example code:
Dim fldLst As New LCFieldList
conn.Execute "SELECT * from TEST.CUSTOMER", fldLst ' fldLst is only used for result set purposes
Set fld = fldLst.Lookup ("CUST_NAME") While (conn.Fetch(fldLst) > 0) Dim sName As string sName = fld.text(0) '' Do something with this column value Wend
LCConnection Select
The Select command is best described in the LC LSX Manual, as there are many options. In the example code it shows the user accessing a "count" of returned records, this is not accurate for the DB2 and ODBC setup we are using. Instead, like the Execute method, the count can only be determined by the amount of times we loop in Fetching each row.When using Select you must set the Metadata property to the schema and table name you're selecting from. Always use the form "schemaname.tablename" to avoid runtime errors later.
Select example code:
Dim result As New LCFieldList
conn.Metadata = "TEST.CUSTOMER" conn.Select Nothing, 1, result ' Is like SELECT * FROM TEST.CUSTOMER
Set fld = result.Lookup ("CUST_NAME")While (conn.Fetch(result) > 0) MessageBox fld.text(0) ' display the result
Wend
For more on LotusScript see the IBM documentation on Lotus Domino
