AD 203 - Connecting Domino to Relational Databases
Mary Peterson Notes/Domino Product Manager
Agenda
Why Domino + RDBMS Technologies Visual Mapping DECS @Commands Programmatic RDBMS access using agents When to use what Q&A
Agenda
Data Access Using Agents LotusScript (Client, Server and RunOnServer) LotusScript Extensions (LSX) Lotuscript Data Object LS:DO DB2 LSX Lotus Connector LSX ActiveX Data Object Java JDBC via JDBC-ODBC bridge JDBC via pure JDBC drivers
N-Tier Applications
Notes/Domino is the best means to find, access, analyze, manage and share information throughout the enterprise
Why EI and Domino?
The Majority of Data is in Enterprise Systems "Convergent Application Platform"
Improve work processes Improve decision making Improve business productivity
Connect People, Data & Information with Standard Technology
Modular Architecture
Lotus Domino Server Programmatic Access Lotuscript Java
JDBC ADO/COM LSX DECS "Live" Declarative
Lotus Enterprise Integrator
Domino Connectors
Transaction Systems
ERP Applications
Relational Databases
Unstructured Data
Best of Domino
Internet Enabled Rich content Store for unstructured data Robust security model Mobile capabilities Messaging integration Easy to customize applications Standards enabled
Best of RDBMS
Best at structured data storage Able to hold terabytes of data Standard SQL to access data Commodity now
Customer Applications
Help Desk - Workflow in Domino; product details in RDBMS Government Financial Record Processing Domino bring RDBMS data to web Track Process of Property Development data in RDBMS, project members comments in Domino Music storage and web distribution - Domino brings MIDI files stored in RDBMS to web
State of RDBMS market
Forrester predicts RDBMS market growing to $9.6 billion by year end 1999 Drivers Data warehouses Datamarts Internet applications
Source: Forrester Research Database Market 2001 8/97
Web is the frontier for RDBMS
Other challenges: Hand held devices Tools integration
Notes/Domino Access to RDBMS
Notes Client
@Db
DBMS Client DBMS Server
Domino Server Web Browser
LS:DO LSX JDBC JDBC-ODBC DECS LC LSX ADO
(DECS)
Seamless Integration with Domino "Live" data access Data stored in Enterprise System Visual Mapping Interface High Performance Persistent, Parallel, Pooled Connections Advanced Options Stored Procedures, Filter formulas, MetaLinks
Current Lotus Connectors
Resource
DB2 Oracle Sybase ODBC File System no Win95 EDA/SQL no Win95 Text no Win95
Platforms
Win 95 Win NT NT Alpha AIX 4.14+ OS/2 Warp HP UX 11.0+ Sun Solaris 2.51+ TBD AS/400 OS/390
DECS
Macro Language
@DBColumn
Keyword list i.e. return list of customer names
@DBLookup
Single-value or multi-value lookup i.e. lookup phone number, given customer name
@DBCommand
Launches external DBMS stored procedures and queries
Programmatic Web Techniques
Sample Application Same app using many technologies Query Update Report How not to save input data Calling agents Passing parameters between pages Combine HTML with RDBMS data
Web Client
http://.../app.nsf/MyForm?OpenForm
DOMINO SERVER
HTTP Task
Domino API
Employee Name : Elvis Age : 25
.NoteId .items .Created
HTML Engine
Notes API
NSF Storage
Web Client
Use $$Return field else Default Message
SUBMIT
DOMINO SERVER
HTTP Task
Domino API HTML Engine
Employee Name : Elvis Age : 25
.NoteId .items .Created
Notes API
NSF Storage
SaveOptions:="0"
Web Client
DOMINO SERVER
HTTP Task
Domino API
HTML Engine
Employee Name : Elvis Age : 25
.NoteId "MyAgent" .items .Created
Trigger Agent
$$QueryOpenAgent Field
Notes API
NSF Storage
Web Client
DOMINO SERVER
HTTP Task
Domino API
Notes API
NSF Storage
.NoteId .items .Created
Employee Name Elvis : Age 25 : "MyAgent"
HTML Engine
Trigger Agent
$$QueryOpenAgent Field OR (4.6 )
WebQueryOpen Form Event @Command([ToolsRunMacro];
"MyAgent")
Web Client
DOMINO SERVER
HTTP Task
Domino API
HTML Engine
Employee Name Elvis :Age : 25
.NoteId .items .Created
Trigger Agent
LotusScript
Dim s as New NotesSession Set doc = s.DocumentContext doc.Name = "Elvis Presley" str = Mid(doc.Query_String(0),10) 'str equals "value1&value2 .."
Notes API
NSF Storage
Web Client
DOMINO SERVER
HTTP Task
Domino API
Employee Name Elvis : Age : 25
.NoteId .items .Created
HTML Engine
Trigger Agent
Notes API
NSF Storage
Java
Session s = getSession(); Document doc = s.getDocumentContext(); doc.replaceItemValue("Name", "Elvis Presley");
Web Client
SUBMIT
DOMINO SERVER
HTTP Task
Domino API
Agent Output else Use $$Return field else Default Message
Employee Name : Elvis Age : 25
.NoteId .items .Created
HTML Engine
Trigger Agent
$$QuerySaveAgent Field OR (4.6) WebQuerySave Form Event
Notes API
NSF Storage
Formula, LotusScript, Java SaveOptions:="0"
Web Client
http://.../app.nsf/MyForm?OpenForm&Value1&Value2...
DOMINO SERVER
HTTP Task
Domino API
Employee Name : Elvis Age : 25 Query_String .NoteId
.items .Created
HTML Engine
Trigger Agent
Notes API
NSF Storage
"OpenForm&Value1&Value2..."
Data Access Steps
RDBMS Driver
Connection
DBMS
Query
select name,cost from parts where partno = 123
Result Set
What is the LotusScript Data Object?
An ODBC LotusScript Extension (LSX) 3 LotusScript object classes
ODBCConnection ODBCQuery ODBCResultSet
Remember to Load the LSX
UseLSX "*lsxodbc"
Browser
LS:DO Demo Architecture
DOMINO SERVER
LS:DO Classes
ODBC Driver Manager
DB/2 ODBC Driver
DB/2 CLI
DB/2
How does it work?
Step Establish a connection Methods Con.ConnectTo("DB") Set Qry.Connection = Con Qry.SQL = "Select..." Set Res.Query = Qry Res.Execute x = Res.GetValue("COL")
Define the SQL statement
Get the result set
How to Add/Update
Writing data to an external database Establish a connection Define your SQL statement Get the result set (for metadata) Add a new row (optional) Specify new values Update the external database
How does it work?
Step Add a new row (optional) or position on row to change Specify new values Update the external database Methods Res.AddRow
Res.SetValue("COL", "TEXT") Res.UpdateRow
LS:DO Demo
Creating HTML from Agent
Use "Print" Statement Print "
Query Results (" & doc.form(0) & ") for:" Print "" & doc.Remote_User(0) & "
" Print "
" HTML URL links
Print "
Click Here" Print "
Click Here"
LS:DO Demo
Create HTML page with Query results Create URL links
RunOnServer
Profile Doc 2 Profile Doc
1. Elvis 1. "FirstName" 2. G. 2. "Initial" 3. Presley 3. "LastName"
Agent.RunOnServer
RDBMS
Client, Server and RunOnServer
Notes Client Access all drivers and configuration on every client Use Notes client events Server Agents Web access Drivers and configuration on server only RunOnServer Combination solution Drivers on server only Use profile documents
ODBC Configuration
ODBC requires: Driver Manager and Administrator Specify custom data source name Driver Sometimes back end client (i.e. Oracle) ODBC platforms: Not limited to Windows Intersolv offer OS/2, AIX, Solaris, HP-UX
LS:DO Cross Platform Support
Windows 3.1 Windows 95 Windows NT UNIX Solaris Sparc Solaris Intel AIX HP-UX OS/2 S/390 NEW in 98 OS/400 NEW in 98
New and Improved for 4.6x
Notes specific implementation uses Notes engine Domino features i.e. multithreading Improved memory management Give scripter caching control Transaction processing (Commit/Rollback) Positioned Updates Update by Row ID ExecProcedures handle >30 parameters
New In 4.62/4.63
ExecDirect More Multi-threading improvements for AsynchronousAgent=1 New Notes.ini variables to control serialization of ODBC function calls ODBCThreadSafeDriver = ALL or SQLSrv32.dll,...(list threadsafe drivers) ODBCNonThreadSafeDriver = (list non-threadsafe drivers)
5.0 LS:DO Features
Intersolv OEM drivers version 3.11 free Connection caching on win32 Codeshare with AS/400 and DB2 LSX Better DB2 CLOB/BLOB handling More to come...
Futures
Mac Port for PowerPC Rewrite parser to improve performance Platform independent connection pooling Single sign-on Better integration with Domino Designer
LotusScript Extensions (LSX)
By pass ODBC via Native Driver Extends access to RDBMS functionality not supported via ODBC Create via LSX toolkit DB2 LSX MQ LSX SAP R/3 LSX
DB2 LSX
Version 2.0 - upgrade to support multithreading in Q2 In 5.0, integrate into LS:DO source Functionality beyond ODBC Large Object Data User Defined Types Transact Commit DB2 Data Access Classes DB2Connection DB2Query DB2ResultSet
Using the DB2 Data Access Classes
Install client software DB2 Client Application Enabler (CAE) or DB2 Software Developer's Kit (SDK) Register each database as an ODBC data source via ODBC Driver Manager Specify UseLSX "*LSXDB2" in agent
Browser
DOMINO SERVER
DB/2 LSX Classes
DB/2 Driver
DB/2 CLI
DB/2
DB2 LSX Demo
Compare with LS:DO
ActiveX Data Object (ADO)
Registered OLE classes available to Lotuscript ADO is similar abstraction to ODBC as LS:DO Supports OLE-DB drivers to unstructured formats Classes available to IDE help Use if familiar with ADO
ADO Demo Architecture
Browser
DOMINO SERVER
ADO Objects
ODBC Driver Mgr or OLEDB provider
DB/2 ODBC or OLEDB Driver
DB/2 CLI
DB/2
ADO Classes (Objects)
Connection - connection to data source and allows execution of commands Command - query or statement that can be processed by data source Parameter - parameter of command Recordset - virtual table in memory including cursor functionality Field - column in a Recordset Error - error returned from data source
Lotus Connector LSX
Programmatic access to same Connectors used by DECS One object model for all connectors Use if need to access many back end types, learn model once, use for many connections. Ships with Domino
Modular Architecture
Lotus Domino Server Programmatic Access Lotuscript Java
JDBC ADO/COM LSX DECS "Live" Declarative
Lotus Enterprise Integrator
Domino Connectors
Transaction Systems
ERP Applications
Relational Databases
Unstructured Data
LC LSX Classes
LCSession - establish LC session LCConnection - represent instance of LC Connector Con.Writeback - used to update data Con.Select - query data Con.Fetch - bring retrieved data to local memory LCFieldlist - manipulating data through connection LCField - storage class containing one or more data values
JDBC Basics
Java DataBase Connectivity API are Java classes that allow an application to send SQL Statements to a DBMS and retrieve results JDK 1.1 API contains JDBC API in new java.sql package Notes 4.6 contains JDK 1.1.1 API Notes/Domino 5.0 contains Sun JDK 1.1.6 API Require JDBC Driver Classes JDBC can also handle Dynamic SQL, stored Procedures & retrieve BLOBS (pictures,
JDBC Drivers
JDBC-ODBC Bridge interfaces with local ODBC Driver Manager contained in Domino 4.6/5.0 Pure Java Driver implements all communication layers in java Native-API partly-Java Driver requires resident local C DLL's from database vendor
Browser
DOMINO SERVER
JDBC-ODBC Bridge Classes
ODBC Driver Manager
DB/2 ODBC Driver
DB/2 CLI
DB/2
Browser
DOMINO SERVER
JDBC Driver Manager Classes
DB/2 JDBC Driver
DB/2 CLI
DB/2
java.sql Package Classes
DriverManager Connection Statement Resultset ResultSetMetaData
Remember to import sql package import java.sql.*
Query via Native JDBC Query via JDBC:ODBC Bridge Driver
Step Load JDBC Driver Methods Class.forName("sun.jdbc.odbc.JdbcOdbcDriver") Class.forName("COM.ibm.db2.jdbc.net.DB2Driver");
Set Database String URL = "JDBC:ODBC:datasource"; String URL = "JDBC:DB2://host:port/database"; URL e.g "JDBC:DB2://127.0.0.1:2200/demo" Establish Connection Create SQL Statement Execute Query Iterate Results Connection con = DriverManager.getConnection ( URL, user, password ); Statement stmt = con.createStatement(); ResultSet res = stmt.executeQuery( "SELECT .." ); res.next( );
JDBC Demo
Which tool to choose
Platform Application Criteria How process is triggered Volume of data transfered Data type Simple or complex transfer Programming skills available See Using DB2 in a Domino Environment Redbook - Chapter 9
Other Technologies
Lotus Enterprise Intgrator (formerly NotesPump) Enterprise Java Beans Java Servlets CORBA/IIOP Using standards to get to Domino data NotesSQL ODBC Driver Domino Driver for JDBC
Business Partner Tools
Data "Pump" Tools Casahl Replic-Action Percussion Software Notrix Data Extraction tools MayFlower Software - Sentinel Data Integrator Hayes Computer Systems - HCS Exporter Granite Software ZMERGE Application Developer Tools Adience Design - SQLTrax Percussion Software VB/Link for Lotus Notes InfoImage Essential Tools Check BP catalog for complete list
More Information
http://www.lotus.com/devtools http://www.eicentral.lotus.com http://www.software.ibm.com/data/db2/fordomino IBM Redbooks: DB2 and Domino - NEW! Demo Db at www.redbooks.ibm.com Additional Materials SG244918 cafeplus.nsf http://www.lotus.com/redbooks Notes and Domino Advisor The VIEW
Questions???