© 2006 Wellesley Information Services. All rights reserved.
Integrating Domino Data with DECS, DCRs, and LC/LSX
Paul T. CalhounNetNotes Solutions Unlimited
2
What We’ll Cover …
• Exploring RDBMS integration options in Domino• Implementing DECS• Using DCRs• Coding with LC-LSXs
3
DECS
LEI
DCRs
LC-LSX
JDBC
RDBMSDATA
Domino Applications
RDBMS Integration Options in Domino
4
LEI
• Lotus Enterprise Integrator (LEI) is purchased separately from Domino; current release is 7.0
Extends DECS to includeVirtual Agents, documents
Base connectorsDB2, Oracle, Sybase, ODBC, OLE DB, and File System
Premium connectorsSAP R/3, PeopleSoft
• LEI is beyond the scope of this session
5
DECS
• Domino Enterprise Connection Services (DECS) allows real-time access to RDBMS data
Allows connection to DB2, Oracle, Sybase, ODBC, OLE DB, and the file systemShipped as part of Domino with R5ND6 and ND7 have the same feature set as R5Real-time activities are now called virtual field activitiesin ND6 and ND7Allows Domino form/field access to RDBMS datavia a key fieldConnections and activities are managed in the decsadm.nsf database
6
DCRs
• Data Connection Resources (DCRs)New in ND6Requires DECS to be installedExposes DECS connection capabilities at the Domino Designer client levelEliminates the need for configuring connections in theDECS admin databaseOnly supports virtual field mapping
Changes to RDBMS data via a Domino formAre design elements that can be replicatedAllows connection to:
DB2, Oracle, Sybase, ODBC, OLE DB, and File System
7
LC-LSX
• Lotus Connector LotusScript eXtension (LC-LSX)Now included with Domino server and Designer client
ND6 and ND7Provides programmatic access between Domino andRDBMS via LotusScriptSame code logic that is used in DECS, LEI, ESBIncludes support for connection poolingAllows programmatic connection to
Notes, DB2, File System, ODBC, Oracle 7, Oracle 8, OLE, Sybase
8
JDBC
• Java Database Connectivity (JDBC)Allows access to RDBMS data via Java standards-based functionality in JDBC 2.0 API
Uses drivers provided by the RDBMS vendorNot limited to the connectors that ship with ND6 viaDECS, LEI, LC-LSX
For example, can connect to MySQL RDBMSCan be used in Agents, applets, servlets, and standalone Java applications
9
Compare Domino Data Integration Options
YesYesNoNoNoProgrammable
Limitedonly by vendor driver
LimitedLimitedLimitedLimitedDatabase Support
NoYesYesYesNoShips with Domino
JDBCLC-LSXDCRsDECSLEI
10
What We’ll Cover …
• Exploring RDBMS integration options in Domino• Implementing DECS• Using DCRs• Coding with LC-LSXs
11
DECS Solutions
• Optional install duringServer installationDesigner client installation
• Server process runs as a taskAt the server console: Load DECSInclude in the server notes.ini variable ServerTasks to run at startup
• Administration database created from the following design template
decsadm.ntfAdministration database is used to create connection and activity documents to map data between Domino and the RDBMS
12
How to Use DECS
• Create a Domino form that maps the Domino fields to columns in the RDBMS
• Use the DECS administration database to:Create a connection document
Connects Domino to an RDBMS systemCreate a virtual field activity document
Uses the connection document to connect to the RDBMS and map key fields and data fields to documents in Domino via the specified form design
Building Block
13
Looking at DECS
• Design objects used in this demoDatabase
DECS admin databasedecsadm.nsf
Relational database connectivityDEV2006/RDBMSDOM.nsf
FormsDecsDataForm
ViewsDecsDataView
Demo
DECS
14
What We’ll Cover …
• Exploring RDBMS integration options in Domino• Implementing DECS• Using DCRs• Coding with LC-LSXs
15
Steps for Implementing DCRs
• Ensure DECS are installed on the server• Test the connection between Domino and the RDBMS• Create the DCR in the Designer client• Enable the database property to allow connections to
external databases• Use the DCR in a form• Map fields from the Domino form to the RDBMS
via the DCR
16
Ensure DECS Are Installed on the Server
• This makes the Base Lotus Connectors available for use• DCRs are an ALTERNATIVE to using DECS• DECS server task does not have to be running in order
for DCRs to access data• New server
When installing the server, make sure that DECS supportis selected as part of the install
• Existing serverRe-run the server setup and select only the DECS support options to add it by itself
Don't Forget
17
Testing a Connection
• For systems with only DECS installedFrom a command prompt on the Domino server, in the program directory, issue the following command:
For Windows (Win32) platforms, use "ndctest.exe"On UNIX platforms, use "dctest"For the iSeries platform, use "call Qnotes/dctest"
18
Testing a Connection (cont.)
• For systems with only LEI installedFrom a command prompt on the Domino server, in the program directory, issue the following command:
For Windows (Win32) platforms, use "ncontest.exe" From LEI 7, this can be done from the admin interface
On UNIX platforms, use "contest"For the iSeries platform, use "call Qnotes/contest"
19
Using the ndctest Utility to Test a Connection
20
Create the DCR in the Designer Client
• In the Domino Designer client, openShared Resources Data ConnectionsCreate on a server that can access the RDBMS
Local databases cannot browse metadata (field definitions)
21
Create the DCR in the Designer Client (cont.)
• Provide a name and an alias• Choose the type of RDBMS you are connecting to
DB2, Oracle, Oracle 8ODBC, OLE DBSybase
• Depending upon the RDBMS type, the remaining options will change
22
Create the DCR in the Designer Client (cont.)
• Select the object in the RDBMS that you are connecting to
Table, view, procedure• If the database is located on
the server you can “Browse metadata”
23
Create the DCR in the Designer Client (cont.)
• The General Options tab allows you to set many of the same options that you would in the DECS administrator database
24
Enable the Database Property for DCRs
• Enable the database property to “Allow connections to external databases using DCRs”
Option is grayed out until at least one DCR is created in the database
GOTCHA!
25
Use the DCR in a Form
• Use the DCR in a Form to map fieldsIn the form properties on the defaults tabNot required, but makes mapping fields easierCan only browse metadata on server-based databases
26
Map the Fields
• Map fields from the Domino form to the RDBMS via the DCR
In field properties,on the field info. tabchoose “External data source”If DCR is added to the form, then bottom fields will already be completed
27
Map the Fields (cont.)
• Click on the “Browse” button to choose the field
28
Map the Fields (cont.)
• Every form must have one key fieldThis matches the primary key in the RDBMSKey field data is always stored as part of the Domino database
29
Map the Fields (cont.)
• All other fields will be data fieldsData fields will optionally allow the data to be stored in the Domino database
30
Import External Records
• Why?Creates Domino documents from existing records in the RDBMS
• Run the data import actionIn order for the data import action to work
The DECS admin database, decsadm.nsf, must existThe designer must have at least designer access to the DECS administration database, decsadm.nsf
Help documentation in 6, 6.01, and 6.02 is incorrectStates run “Export external records” instead of“Import external records”
31
Import External Records (cont.)
• Disable the database property that allows data connections
• Run the data import action• Verify the data• Re-enable the database property that allows
data connections
32
Import External Records (cont.)
• Disable the database property that allows data connections
If you don’t, you will receive the following error dialog during import
33
Import External Records (cont.)
• In the Data Connections view in the Domino Designer client
Select the DCR you want to useClick on “Import external records” in the Action barSelect the form to be used during the import
34
Import External Records (cont.)
• Run the data import actionThis will import data from the RDBMS and create a Domino document for each record
35
Import External Records (cont.)
• Verify the data• Enable the database property that allows data
connections
Don't Forget
36
Import External Records (cont.)
• Key data can now be used in your Domino applications• Views can only display data from key fields and fields
with the “Store data locally” selected in the Field Properties
37
Import External Records (cont.)
• Notes client can now be used to add, update, and delete the records in the RDBMS using the specified form
• Additions made in the RDBMS will NOT be reflected in the Domino database
If additions are made in the RDBMS, then the keys will haveto be re-imported
All keys will be re-importedCreating New Records even if they exist in Domino
Can not choose a subset or delta import
Caution
38
DCR Solutions
• Design objects used in this demoDatabase
Relational database connectivityDEV2005/RDBMSDOM.nsf
FormsDCRDataForm
ViewsDCRDataView
DCRDB2SampleData
Demo
Brief overview of demo
39
What We’ll Cover …
• Exploring RDBMS integration options in Domino• Implementing DECS• Using DCRs• Coding with LC-LSXs
40
Lotus Connector LotusScript eXtension
• Provides programmatic access between Domino and RDBMS via LotusScript
Code logic that is used in DECS, LEI, ESBSupport for connection poolingAllows programmatic connection to:
Notes, DB2, File System, ODBC, Oracle 7, Oracle 8, OLE, Sybase
Now included with Domino 6 and 7 server and Designer clients
41
LC-LSXs Classes
• LCSession• LCConnection• LCFieldList• LCField• LCStream• LCNumeric• LCCurrency• LCDateTime
Building Block
42
Steps for Scripting with LC-LSXs
• Make classes available to the script object• Create a session• Create a connection• Create a result set• Manipulate the result set• Manipulate the data in the result set
43
Make Classes Available to the Script Object
• In the LotusScript “Options” objectUselsx "*lsxlc“
The “*” allows this to work on all platforms
44
Create a Session
• Creating a session is not really necessary, but several methods for error reporting are useful
Session propertiesStatus, ConnectionPooling
Session methodsClearStatus, GetStatus, GetStatusText, ListConnector, ListMetaConnector, LookupConnector, LookupMetaConnector, Sleep
45
LC-LSX Code to List Available Connectors
Option PublicOption DeclareUselsx "*lsxlc"Sub Initialize
Dim lcs As New LCSessionDim conName As String, Dim conList As Stringlcs.ClearStatuslcs.ConnectionPooling = TrueCall lcs.ListConnector(LCLIST_FIRST, conName)conList = conNameDo While lcs.ListConnector(LCLIST_NEXT, conName)
conList = conList + ", " + conNameLoopMsgbox "The usable Connectors are " & conList,0,"Connector Types"
End Sub
46
Looking at the Session Agent
• Design objects used in this demoDatabase
Relational database connectivityDEV2005/RDBMSDOM.nsf
ViewsLCLSXOutputView
AgentLSXSessionAgent
Demo
LC-LSX
47
Connection Pooling
• Connection pools may be used• Combine common properties and methods with specific
RDBMS target properties and methodsSee Domino Designer Help for specific target RDBMS properties and methods
48
Code for Connection and Connection Status
Dim lcdb2 As New LCConnection("db2")
lcdb2.Database = "Sample"lcdb2.UserID = "db2admin"lcdb2.Password = "arbrcoda"On Error Goto Traplcdb2.ConnectMsgbox "Connection Status is:" & lcdb2.isConnected,0,"Connection Status"Exit Sub
Trap:Msgbox "Connection failed with error " & Err & ": " & Error,0,"Error"Exit Sub
49
Looking at the Connection Agent
• Design objects used in this demoDatabase
Relational database connectivityDEV2005/RDBMSDOM.nsf
ViewsLCLSXOutputView
AgentLSXConnectionDB2Agent
Demo
LC-LSX
50
Create a Result Set
• LCConnection methods that return result setsLCConnection.CallLCConnection.CatalogLCConnection.ExecuteLCConnection.Select
51
Manipulate the Result Set
• Update data in back-end RDBMS from Domino data• Update data in Domino from back-end RDBMS
52
Manipulate the Result Set (cont.)
• LCConnection methods that manipulate result setsLCConnection.FetchLCConnection.InsertLCConnection.UpdateLCConnection.Remove
53
Manipulate the Data in the Result Set
• Classes that manipulate dataLCFieldListLCFieldLCStreamLCCurrencyLCNumericLCDatetime
Building Block
54
Sub Initialize
Dim lcs As New LCSessionDim conName As StringDim conList As Stringlcs.ClearStatuslcs.ConnectionPooling = TrueDim lcdb2 As New LCConnection("db2")
lcdb2.Database = "Sample"lcdb2.UserID = "db2admin"lcdb2.Password = “db2password"
Code to Create a Single Domino Document
55
On Error Goto Traplcdb2.Connect
Dim keys As New LCFieldListDim field As LCField
lcdb2.Metadata = “Administrator.Employee"Set field = keys.Append ("Empno", LCTYPE_TEXT)field.Flags = LCFIELDF_KEYfield.Text = "000010"
lcdb2.FieldNames = "FirstNme,MidInit,LastName,Job,PhoneNo,BirthDate"
Code to Create a Single Domino Document (cont.)
56
Dim fields As New LCFieldListIf (lcdb2.Select (keys, 1, fields) = 0) Then EndDim ns As New NotesSessionDim db As NotesDatabaseSet db = ns.CurrentDatabaseDim doc As notesdocumentSet doc = db.CreateDocumentCall doc.AppendItemValue("Form","LSLSXOutputForm")Dim rti As NotesRichTextItemDim personName As String
Code to Create a Single Domino Document (cont.)
57
If (lcdb2.Fetch (fields) > 0) ThenpersonName = fields.FirstNme(0)+" "+fields.MidInit(0)
+" "+fields.LastName(0)Call doc.AppendItemValue("Name", personName)Set rti = New NotesRichTextItem(doc,"Body")rti.AppendText(personName)rti.AddNewline(1)rti.AppendText(fields.job(0))rti.AddNewline(1)rti.AppendText(fields.phoneno(0))rti.AddNewline(1)rti.AppendText(fields.birthdate(0))
End If
Code to Create a Single Domino Document (cont.)
58
Call doc.Save(True,True)End
Trap:Msgbox "Connection failed with error " & Err & ": " & Error,0,"Error"
Exit SubEnd Sub
Code to Create a Single Domino Document (cont.)
59
Sub InitializeDim lcs As New LCSessionDim conName As StringDim conList As Stringlcs.ClearStatuslcs.ConnectionPooling = TrueDim lcdb2 As New LCConnection("db2")
lcdb2.Database = "Sample"lcdb2.UserID = "db2admin"lcdb2.Password = “db2password"
Code to Create Multiple Domino Documents
60
On Error Goto Traplcdb2.Connect
lcdb2.Metadata = “Administrator.Employee"lcdb2.FieldNames = ""
Dim ns As New NotesSessionDim db As NotesDatabaseSet db = ns.CurrentDatabaseDim doc As notesdocumentDim rti As NotesRichTextItemDim personName As String
Code to Create Multiple Domino Documents (cont.)
61
Dim fields As New LCFieldListIf (lcdb2.Select (Nothing, 1, fields) = 0) Then EndDo While (lcdb2.Fetch (fields) > 0)
Set doc = db.CreateDocumentCall doc.AppendItemValue("Form","LSLSXOutputForm")personName = fields.FirstNme(0)+" "+fields.MidInit(0)+"
"+fields.LastName(0)Call doc.AppendItemValue("Name", personName)Set rti = New NotesRichTextItem(doc,"Body")rti.AppendText(personName)rti.AddNewline(1)rti.AppendText(fields.job(0))rti.AddNewline(1)rti.AppendText(fields.phoneno(0))
Code to Create Multiple Domino Documents (cont.)
62
rti.AddNewline(1)rti.AppendText(fields.birthdate(0))Call doc.Save(True,True)
LoopEnd
Trap:Msgbox "error " & Err & " line " & Erl & ": " & ErrorExit Sub
End Sub
Code to Create Multiple Domino Documents (cont.)
63
Sub InitializeDim lcs As New LCSessionlcs.ClearStatuslcs.ConnectionPooling = TrueDim lcdb2 As New LCConnection("db2")Dim fields As New LCFieldListDim field As LCField
lcdb2.Database = "Sample"lcdb2.UserID = "db2admin"lcdb2.Password = “db2password"
Insert RDBMS Record from Domino
64
On Error Goto Traplcdb2.Connectlcdb2.Metadata = “Administrator.Employee"Dim ns As New NotesSessionDim doc As notesdocumentSet doc = ns.DocumentContextSet field = fields.Append ("EMPNO", LCTYPE_TEXT)field.Value = doc.GetItemValue("EmpNo")Set field = fields.Append ("FIRSTNME", LCTYPE_TEXT)field.Value = doc.GetItemValue("FirstName")Set field = fields.Append ("MIDINIT", LCTYPE_TEXT)field.Value = doc.GetItemValue("MidInitial")
Insert RDBMS Record from Domino (cont.)
65
Set field = fields.Append ("LASTNAME", LCTYPE_TEXT)field.Value = doc.GetItemValue("LastName")Set field = fields.Append ("WORKDEPT", LCTYPE_TEXT)field.Value = doc.GetItemValue("Department")Set field = fields.Append ("PHONENO", LCTYPE_TEXT)field.Value = doc.GetItemValue("PhoneNo")Set field = fields.Append ("HIREDATE", LCTYPE_DATETIME)field.Value = doc.GetItemValue("HireDate")Set field = fields.Append ("JOB", LCTYPE_TEXT)field.Value = doc.GetItemValue("Position")Set field = fields.Append ("EDLEVEL", LCTYPE_INT)field.Value = doc.GetItemValue("Education")
Insert RDBMS Record from Domino (cont.)
66
Set field = fields.Append ("SEX", LCTYPE_TEXT)field.Value = doc.GetItemValue("Sex")Set field = fields.Append ("BIRTHDATE", LCTYPE_DATETIME)field.Value = doc.GetItemValue("BirthDate")Set field = fields.Append ("SALARY", LCTYPE_CURRENCY)field.Value = doc.GetItemValue("Salary")Set field = fields.Append ("BONUS", LCTYPE_CURRENCY)field.Value = doc.GetItemValue("Bonus")Set field = fields.Append ("COMM", LCTYPE_CURRENCY)field.Value = doc.GetItemValue("Commission")Call lcdb2.Insert (fields, 1, 1)
Insert RDBMS Record from Domino (cont.)
67
EndTrap:
Msgbox "Connection failed with error " & Err & ": " & Error,0,"Error"
Exit Sub
End Sub
Insert RDBMS Record from Domino (cont.)
68
LC-LSX Data Manipulation
• Design objects used in this demoDatabase
Relational DB ConnectivityDEV2006/RDBMSDOM.nsf
FormsLCLSXExampleFormLCLSXOutputFormLCLSXInsertDataIntoRDBSForm
ViewsLCLSXOutputView
AgentsLSXCreateDominoDocumentFromRDBMSLSXCreateMultipleDocumentsFromRDBMSLSXInsertRecordIntoRDBMS
DemoLC-LSX
69
Resources
• Lotus/IBM Web siteswww.alphaworks.ibm.comwww.ibm.com/developerworkswww.lotus.com/lddwww.redbooks.ibm.comwww.ibm.com/software/data/db2
70
7 Key Points to Take Home
• New to ND6/works in ND7Data Connection ResourcesNew properties and methods in LC-LSXs
• DCRs allow functionality of DECS without using the administration database
• DCRs cannot be used outside of the databasethe DCR was created in
• DCRs can be replicated
71
7 Key Points to Take Home (cont.)
• LC-LSXs extend beyond the canned capabilityof DECS and DCRs
• Use LC-LSXs to write custom code• DCRs and LC-LSXs support the following
base connectors:DB2, Oracle, Sybase, ODBC, OLE DB, and File System