Jonesy's Babble

"Don't touch that please, your primitive intellect wouldn't understand things with alloys and compositions and things with... molecular structures." Ash, Army of Darkness

Alan Lepofsky
Andrew Pollack
Bruce Elgort
Chris Linfoot
Chris Whisonant
Curt Stone
Ed Brill
Jake Howlett
Joe Litton
Julian Robichaux
Mac Guidera
Paul Mooney
Phil Randolph
Philip Storry
Richard Schwartz
Rocky Oliver
Wild Bill Buchan
Pulling data from a DB2 database using LSX (Show-n-Tell)
Thursday, February 16, 2006
Rocky Oliver and Bruce Elgort came up with the idea of a show-n-tell Thursday for those in the IBM Lotus Domino/Notes community to share a technique, tip, trick, app, etc. that you've developed or use to do your job better.

We have a business system designed by Caterpillar and it drives a lot of our business. With it being a lovely 5250 green-screen application we pull a lot of data out of it and into various Notes databases to give the end user better interfaces to view/sort data as starting points for workflow applications, CRM apps, etc. etc.

Here's just a quick and easy (hopefully efficient) way to pull certain fields from a table and create a Notes document out of it.

You have the Options first - OpenLog from is amazing - you should use it.

Option Public
Option Explicit
Uselsx "*lsxlc"
Use "OpenLogFunctions"
%INCLUDE "lsconst.lss"
Then the main section of code in your routine. Pretty simple code with some comments that should be enough to understand.

On Error Goto errorHandler

Dim session As New LCSession

Dim src As New LCConnection("db2")
Dim fldOut As New LCFieldList

'// -- Database that is named in the "wrkrdbdire" command on the AS400
src.Database = "THOMPSON"
src.UserID = "userID"
src.Password = "password"

'// -- Format used is "Libraryname.Tablename"
src.Metadata = "LIBD42.EMPORDH0"

'// -- Column(s) that are returned
'// -- Order#, MSO, Order Date, Location, Ship Date, Ship Status, Ready Date, Comments, Ack. Date

If (src.Select(Nothing, 1, fldOut) = 0) Then '// -- No records found
End If

Dim nDB As New NotesDatabase("","")
Dim nDoc As NotesDocument

Call nDB.OpenByReplicaID("Notes1","86256F55006013D6")

While (src.Fetch (fldOut) > 0) '// -- Get 1 row at a time from the recordset
Set nDoc = New NotesDocument ( nDB )
nDoc.Form = "frmPO"
nDoc.fldIDNum = Trim$(fldOut.ORNO(0))
nDoc.fldMSO = Trim$(fldOut.SPORNO(0))
nDoc.fldOrderDate = Trim$(fldOut.ORDT8(0))
nDoc.fldLocation = Trim$(fldOut.EQPLNO(0))
nDoc.fldCATSDate = Trim$(fldOut.DTSHP8(0))
nDoc.fldCATSS = Trim$(fldOut.RDYSP(0))
nDoc.fldTMCCDate = Trim$(fldOut.DLRD04(0))
nDoc.fldMemo = Trim$(fldOut.CMNT2(0))
nDoc.fldAckDate = Trim$(fldOut.AKISD8(0))
Call nDoc.Save(True, False)

Exit Sub

Call LogError

This LotusScript was converted to HTML using the ls2html routine,
provided by Julian Robichaux at

posted by David Jones @ 10:07 AM  
Post a Comment
<< Home
About Me

Name: David Jones
Home: Murfreesboro, Tennessee, United States
About Me:
See my complete profile
Previous Post
- Template Change
- Looking back at the football season
- Lotusphere 2006 Report
- Lotusphere Day Last
- Lotusphere Day Three
- Last two sessions today
- Lotusphere Day Two
- Opening Session
- Beach Club Resort
- Arrived in Tampa
Taking Notes Podcast
The Morning News
TMN - The Non-Expert
Google News
Lotus Notes Wikipedia
Template by

Free Blogger Templates