Solved

Create Document from data in body of email

Posted on 2004-10-05
48
324 Views
Last Modified: 2013-12-18
Hello all,
I've been attempting for the past 2 months to try and get data from the mainframe down into a lotus notes database and I've had no luck.  So,.. I come up with this work around.  I have managed to get the data down to notes by sending it in an email.  How do I extract the data in the body of an email into my application database document(form) ???
I'm really stuck with this whole thing.  If anyone has any ways to do this or even get data from the mainframe (details).
Please Help !!
Thank You !
Paul
0
Comment
Question by:pratigan
  • 20
  • 12
  • 8
  • +3
48 Comments
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 12226057
Hi Paul,

I cannot remember if you asked us before, but did you try DECS and/or LEI to get your data from the mainframe?

Is it possible you construct your mail as an XML mail, in order to structure it? Getting that out of a mail isn't the problem in Notes.

Sjef
0
 
LVL 4

Author Comment

by:pratigan
ID: 12226146
Hello sjef,
I haven't been able to get much details about using DECS.  I've tried very hard to get some assistance w/ LEI but no luck.  i don't know anything about coding DECS or LEI at all.
I also believe that LEI is a secondary product, and I'm sure I could get my organization to buy into it, but I have no experience coding in it.
I could check on the format of the body of the email into XML.  Currently the data is just loaded as raw data like a data file in cobol.
here is an example:
P2M2MULT060BTCHStandard  DRAFT     0008STATA
S1THIS IS A TEST PACKAGE IGNORE
CBL - Cobol         CLNT0020   YesYes   PLOG1224RETEST
CBL - Cobol         CXMC9105   Yes          PLOG1224RETEST

the first row contains the document id, type, status, counter for the number of detailed records listed below and the user name.  Next row is the comments field and the remaining rows are the detailed record fields.
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 12226331
> I also believe that LEI is a secondary product, and I'm sure I could
> get my organization to buy into it, but I have no experience coding in it.

Paul, you don't have to code for LEI.. There are activities that you should configure (like mapping db2 tables to notes form)...This product does fantastic job and worth buying

When using XML kind of transfers use DXL Importer to create docs.. for more details check the help documentation


~Hemanth
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 12226440
Both LEI and DECS are Lotus products. The latter could be considered the smaller half of LEI: it enables a front-end Notes user to contact the back-end mainframe interactively, whereas LEI (formerly called NotesPump) allows for a bi-directional flow of data between Domino and a mainframe (some sort of replication). If you are proficient enough on DECS, you can simulate LEI with it, hence saving some bucks or bob.

In short:
- interactive application, Notes client is front-end to back-end mainframe application? Use DECS
- two-way replication required between two databases (or parts of) on mainframe and Domino, regularly updated? LEI.

LOTS of info on www.lotus.com/ldd

If you intend to send data by mail, you absolutely need some sort of formatting, otherwise you wouldn't be able to distinguish one value from the other.
0
 
LVL 4

Author Comment

by:pratigan
ID: 12226951
The true picture I need is a one way direction.  I need to move data from a mainframe file or mainframe db2 table down to create a document in a lotus notes database.  There is no need for a bi directional response back to the mainframe.  Data from the mainframe will be used to create a document in a lotus notes tracking database.  Then the logic within the notes document takes over.
Is this a situation for DECS ??
If son, what do I need to do to get it up and running ???
Thank You !
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 12226986
Decs is built into R6 !

Simple soln would be to create a DSN using ODBC and access database on mainframe using lsxodbc libraries
0
 
LVL 4

Author Comment

by:pratigan
ID: 12227040
I can have a DSN created on the mainframe using ODBC easy enough.  How do I go about accessing the database on the manframe as far as coding it is concerned.  this is new territory for me and I'm under a timeframe for this.  I've been searching for details all over the place.  You guys are the best source.  Do I create an agent to access the mainframe ???
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 12227082
Ok for quick copy paste soln.. try help documentation which lists under odbcresultset or odbcconnection
0
 
LVL 4

Author Comment

by:pratigan
ID: 12227119
I'll look into it and work with it.  One quesiton.  the DSN on the mainframe using ODBC, is that a DB2 table created using ODBC ?
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 12227191
Most mainframe dbs are db2 .. unless you have different database. Contact your admin for details
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 12227235
HemanthaKumar,

> Decs is built into R6 !
If you mean "some DECS functionality is built into R6", we might agree. To use DECS is a lot simpler than using ODBC and other "difficult" stuff. DECS is plain Notes/LotusScript. R6 contains a feature called DCR, Data Connection Resources, that can be used as an alternative to DECS. Very interesting.

Paul,
To get the complete picture: you want to transfer data from mainframe to Domino, and not the other way round.
- is it time-critical?
- will an agent be enough?

In LEI you can do almost anything without having to code a single line. Drawback: it's a separate product (and rather expensive).

Sjef
0
 
LVL 4

Author Comment

by:pratigan
ID: 12227319
Hello Sjef,
Yes.. I'm running R5 and the data transfer is from mainframe to notes only.
it's kind of time critical, an agent will be enough depending upon how the agent picks up the data.  The business process is this:
a programmer creates and endevor package (endevor is a program version function on mainframe).  when he casts (executes) the package, we are trapping the return code on the cast and grabbing the data and looking to transfer it to a notes database and automatically create a package in the notes database that deals with automated emails and approvals and tracking details.  Then the notes document will send an automated email to the programmer telling them that a package has been created and requires tracking approvals.
The remaining functionality at this point takes place within notes exclusievely.
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 12227640
odbc is not that scary.. it is another set of objects with special handling

First you need to establish connection to odbc data source using the DSN name configured in the odbc panel.
Second, Prepare a SQL statement and assign it to the resultset and execute it... Once executed the resultset holds the data and you just navigate thru the resultset to read the data

I have commented each lines that you have to concentrate and re-modify the script for your app

eg from lotus help

Uselsx "*LSXODBC"

Sub Initialize
     Dim con As New ODBCConnection
     Dim qry As New ODBCQuery
     Dim result As New ODBCResultSet
     Dim msg As String
     Set qry.Connection = con         ' Assign connection to Query
     Set result.Query = qry              ' Assign Query to resultset.. this is implicit connect to odbcconnection object
     con.ConnectTo("ATDB")
     qry.SQL = "SELECT * FROM STUDENTS ORDER BY LASTNAME"        ' Prepare SQL query
     result.Execute         ' Execute... that is it.
     result.LastRow        ' Navigate to last row in resultset

' Rest is getting the data and updating the row...for inserting the data you add a row in the result set (using Addrow method conjunction with SetValue method) and use update row to insert the data...

     currentRow = Inputbox _
       ("Which row do you want to change?", "Which row?")
     If currentRow = "" Then
          Messagebox "No value entered for row",, "Bad row"
          Exit Sub
     End If
     If currentRow < 1 Or currentRow > result.NumRows Then
          Messagebox "Row out of range",, "Bad row"
          Exit Sub
     End If
     result.CurrentRow = currentRow
     For i = 1 To result.NumColumns
          msg = msg & "  " & result.GetValue(i)
     Next
     currentColumn = Inputbox(msg, "Which column?")
     If currentColumn = "" Then
          Messagebox _
          "No value entered for column",, "Bad column"
          Exit Sub
     End If
     If currentColumn = "" Or currentColumn < 1 _
     Or currentColumn > result.NumColumns Then
          Messagebox "Column out of range",, "Bad column"
          Exit Sub
     End If
     newValue = Inputbox$("Enter new value", "SetValue", _
     result.GetValue(Cint(currentColumn)))
     If newValue = "" Then
          Messagebox "No value entered"
          Exit Sub
     End If
     Call result.SetValue(Cint(currentColumn), newValue)
     result.UpdateRow
     result.Close(DB_CLOSE)
     con.Disconnect
End Sub
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 12228088
Hi pratigan,

Open the Lotus Connectors guide, it has examples of connecting to DB2 and extracting data.  You won't even need ODBC, just the DB2 drivers.

Cheers!
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 12228215
even lxdb2.. needs similar coding as that of ODBC !! One good reason to use db2 drivers is that the data types are not messed up..
0
 
LVL 4

Author Comment

by:pratigan
ID: 12229424
ok.. Taking all this into consideration. If I go with db2 connector, then I can have an agent on the domino server query a db2 table on the mainframe and extract the data to populate the fields in the notes form ???
0
 
LVL 4

Author Comment

by:pratigan
ID: 12230171
Gentlemen..... Please don't leave me... I truely need your help... I appologize for not knowing much about this, and I'm over my head on this one !!!
Thank You All !!
I will definitely return the favor !
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 12230889
Yes , Whether it is odbc or db2 your server agent has to have the dsn configured locally to the server.

lsxdb2 is provided native to notes .. this is available as a sperate download from ibm or you get it with lei installation
0
 
LVL 4

Author Comment

by:pratigan
ID: 12236895
now when you say configured locally to the server, what does that mean ?? Do I have to have lotus notes admin do this ?
I also have to check with Notes admin to see if lsxdb2 is available on the server and if not they would have to download it and install it on the server.??

0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 12237838
Yes anyone with access to the server can setup the dsn.
0
 
LVL 4

Author Comment

by:pratigan
ID: 12239996
I hate to ask ythis question... How do I setup the dsn on the server and how does it interact with the db2 connect agent ?
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 12240053
Open Data Sources (odbc) panel from administrative tools of windows.. then configure your server and database, user and password... Test the connectivity.

Once that is done write agent to just make a select and display result.. this will be a good test. Check my example script above
0
 
LVL 4

Author Comment

by:pratigan
ID: 12240336
ok... Thank you Hemanth,
I will setup the connection as you indicate.  I will also have admin download lsxdb2.  I will create the agent with the script above to access the mainframe.  I assume this will access themainframe and query a db2 table, clllect the results and allow me to popluate a notes Form with the results of the query ??
Thanks !
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 4

Author Comment

by:pratigan
ID: 12347937
ok.. I've been working dilignetly on htis.  Here is what I have and the current status.
I have created the following script in an agent that I have runninf on my mail server.  I set the agent up to run upon new mail arrival.  when I do this it doesn't run, but if I set it to run manually and I run it from the action menu itr works fine.
This script reads data from an automated email that gets sent to me from the mainframe into my inobx.  Then the agent should run to create the document in the notes database.
why does it work when run manually but not automatically upon new mail arrival. ????
Please help... I'm almost there !!
Sub Initialize
      Dim session As New NotesSession
      Dim doc As NotesDocument
      Dim it  As NotesItem
      Set doc = session.documentcontext
      
      Dim bodytx As Variant
      Dim btx As String
      Dim subtx As Variant
      Dim luser As String
      Dim lothr As String
      
      subtx = doc.GetItemValue("Subject")
      luser = Trim$(Mid$(subtx(0),11,8)) + "@CSC.com"
      Msgbox(luser)
      lothr = Trim$(Mid$(subtx(0),1,10))
      If lothr <> "ENDVR99999" Then
            Messagebox("Not a Valid Email with package Info")
            Exit Sub
      End If
      
      Dim shfld(6) As String
      Dim shfldlen(6) As Integer
      shfld(1) = "CMpkgID"
      shfld(2) = "CMtype"
      shfld(3) = "CMpkg_status"
      shfld(4) = "CMpkg_desc"
      shfld(5) = "CMcomments"
      shfldlen(1) = 15
      shfldlen(2) = 10
      shfldlen(3) = 10
      shfldlen(4) = 50
      shfldlen(5) = 50
      Dim sdfld(10) As String
      sdfld(1) = "CMel_type"
      sdfld(2) = "CMprog_elem"
      sdfld(3) = "CMc"
      sdfld(4) = "CMb"
      sdfld(5) = "CMd"
      sdfld(6) = "CMj"
      sdfld(7) = "CMplog_X"
      sdfld(8) = "CMplog_CC"
      sdfld(9) = "CMstatus"
      Dim sdfldlen(11) As Integer
      sdfldlen(1) = 20
      sdfldlen(2) = 8
      sdfldlen(3) = 3
      sdfldlen(4) = 3
      sdfldlen(5) = 3
      sdfldlen(6) = 3
      sdfldlen(7) = 4
      sdfldlen(8) = 4
      sdfldlen(9) = 10
      sdfldlen(10) = 3
      bodytx = doc.GetItemValue("Body")
      
      btx = bodytx(0)
      
      Dim bdtx(50) As String
      Dim dtx As String
      dtx = Chr(13) + Chr(10)
      Dim i As Integer
      Dim j As Integer
      Dim blncnt As Integer
      j = 1
      bdtx(1) = ""
      For i = 1 To Len(btx) - 1
            If Mid$(btx,i,2) = dtx Then
                  j = j + 1
                  i = i + 1
                  bdtx(j) = ""
            Else
                  bdtx(j) = bdtx(j) + Mid$(btx,i,1)
            End If
      Next i
      blncnt = j - 1
      
      Dim db As NotesDatabase
      Dim ndc As NotesDocument
      Dim nit As NotesItem
      Dim nvu As NotesView
      
      Dim plx As String
      Dim ii As Integer
      Dim jj As Integer
      Dim rr As Integer
      Dim kk As Integer
      Dim pitm As String
      Dim pval As String
      Dim euser As String
      Dim pkid  As String
      
      pkid = Trim$(Mid$(bdtx(1),1,15))
      Messagebox( pkid )
      
      Set db = session.GetDatabase( "MA-WAL20/SRV/CSC", "NYMetro\emedNYRepository.nsf" )      
      Set nvu = db.GetView("CM6")
      
      Set ndc = nvu.GetDocumentByKey(pkid)
      
      If ndc Is Nothing Then
            Set ndc = Nothing
      Else
            Messagebox( "Packge " + pkid + " already Exists ")
            Exit Sub
      End If
      
      
      Set ndc = db.CreateDocument
      Set nit = ndc.AppendItemValue("Form","CMPro")
      
      plx = bdtx(1)
      
      jj = 1
      For ii = 1 To 3
            pitm = shfld(ii)
            pval = Trim$(Mid$(plx,jj,shfldlen(ii)))
            Set nit = ndc.AppendItemValue(pitm,pval)
            jj = jj + shfldlen(ii)
      Next      
      euser = Mid$(plx,40,8)
      
      plx = bdtx(2)
      pval = Trim$(plx)
      Set nit = ndc.AppendItemValue("CMpkg_desc",pval)
      
      For rr = 3 To blncnt
            plx = Trim$(bdtx(rr))
            jj = 1
            kk = rr - 2
            For ii = 1 To 9
                  pitm = sdfld(ii) + Cstr(kk)          
                  pval = Trim$(Mid$(plx,jj,sdfldlen(ii)))
                  Set nit = ndc.AppendItemValue(pitm,pval)
                  jj = jj + sdfldlen(ii)
            Next
      Next
      
      Set nit = ndc.AppendItemValue("DisplayAuthor",euser)
      
      Call ndc.ComputeWithForm( True, True )
      
      Call ndc.Save( True, True )
      
      Messagebox("Package " + pkid + " Created Succussfully")
      
Sendemail:
      
      Dim mndc As NotesDocument
      Dim nitm As NotesItem
      Dim ritm As NotesRichTextItem
      
      Set mndc = db.CreateDocument()
      
      Set nitm = mndc.AppendItemValue("Form","Memo")
      Set nitm = mndc.AppendItemValue("Subject","Package " & pkid & " Created")
      Set nitm = mndc.AppendItemValue("SendTo",luser)
      Set ritm = mndc.CreateRichTextItem("Body")
      
      Call ritm.AppendText("Package " & pkid & " Created in DRAFT Mode.")
      Call ritm.AddNewLine(1)
      Call ritm.AddNewLine(1)
      Call ritm.AppendText("Click following link to View/Modify this Package    ")
      Call ritm.AppendDocLink(ndc,"Click to goto docuemnt " & pkid)
      Call mndc.Send(False)
      
End Sub
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 12351284
Set it to run "After mail arrives" and second..check log.nsf for any errors that this agent has spit out
0
 
LVL 4

Author Comment

by:pratigan
ID: 12358434
Hello hemanth,
i have set it to run after mail arrives but it still does not run.  When I check the log, I don't see that the agent ran at all.
I have checked into this more and I believe that Ineed authorization to run on the mail server.  There is a run agent un restricted section in the server properties that I believe my id needs to be part of to obtain the authority to run the agent on the server ?!?!?!
Is this true ??

0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 12358526
Yes your id needs to be setup to run restricted ls agents if they are using restricted operations and similarly for unrestricted calls too.

I am sure this would have been appended in the log.. unless you overlooked it.
0
 
LVL 4

Author Comment

by:pratigan
ID: 12359216
Where is the log, I checked the agent log and that said the agent hasn't run, I do not see the server log..
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 12360090
Is the agent present on the server? Or is it a local agent present only on your local system? Is the server the same server as where the mail comes in?

The log to check is the mail-server's Notes.log database. It should contain errors about processing your mail when a new one comes in. A trigger can be delayed up to 30 minutes or so.
0
 
LVL 4

Author Comment

by:pratigan
ID: 12360258
that's the problem.  The agent is on my server version of my mail, when I do a file database open and go to the server, I do not see a note log database to open.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 12360389
Try to ^O, select the server and type notes.log at the bottom of the dialog box, where it says FIlename.
0
 
LVL 4

Author Comment

by:pratigan
ID: 12360507
I open the server and typed in log.nsf and I got an error, you are not authorized to open that database.  That's probably why I couldn't see it in the list.
Thanks !
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 12361157
One week holiday, and I make mistakes like that. Of course, log.nsf. *sigh*
Sorry :|

Ask your Administrator to give you read permission. To enhance the likelihood of success, attach a crate of beer to your request.
0
 
LVL 4

Author Comment

by:pratigan
ID: 12361347
lol... good idea.... However I hate to give up the beer.
I'll keep you posted.
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 12371083
Contact the admin ask for permission to check the log
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 12371918
EE: Expert Echoing ?
0
 
LVL 4

Author Comment

by:pratigan
ID: 12708837
Hello,
Believe it or not I am still working on this issue and I'm running into alot of red tape with notes admin.  However I have not abandoned it.
I do still intend on awarding points as soon as I have a resolution on my end.  I always appreciate everyones input and I am one that keeps questions alive and I appologize for the extensive time for this one.
I will return, with feedback from my notes admin.
Thank you !!
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 12709988
I think it would be appropriate to close this quetsion and open another when you aer ready to proceed further.  Theoretically, you coudl have red tape for years with that admin.
0
 
LVL 4

Author Comment

by:pratigan
ID: 12710057
lol... I hate notes admin.
The main problem I'm having is:
I created an agent that runs upon new mail arrival, however I had to put the agent into a mailin database because they wouldn't let it run on a mail only server, so I created a mailin database on an application server.
I have the agent running upon new mail arrival but nothing happens.  The problem is I cannot look at the log as I do not have authorization to view the log although I own the mailin database.  CRAZY... so I can't debug the agent if I can't see the log to determine what's happening.
so I'm battling admin ot give me authorization to view the notes log on that server and view the agent log on the database.
Rediculous.....
thanks !
Paul
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 12711234
Hey, I'm a Notes admin.  Well, tehcnically, and engineer, since I do mostly planning, research, and implementation, not user registration/termination and request tickets.

You can debug by creating a NotesLog object, to log al your stuff to Yet Another Database, or, in the short term, to log them to an e-Mail that will be sent to you.

Personally, if the admins are being such big asses that they won't give application support, I would probably hack the system to gain access.  But that's just me.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 12713917
Hi Paul,

There's only one solution to your problem: kick brass or kick ass. If you think the problem you have is major, and you do not get the support you want, then write down your case, motivate what you want and present it to your boss. If he wants to back you up, it's his turn to try with the admin's boss. Escalation, up to the highest level.

Last resort: get one of them oldie PC's, install RedHat, install Domino for Linux, and run your own bloomin' server! Ah, yes, X-cert... They might concede there, the admins. Flexible like a brick wall (99% of them).

Sjef
0
 
LVL 4

Author Comment

by:pratigan
ID: 12715265
hahahahaha..... I agree.  I'll keep you posted.
Thanks !
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 13875093
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
    PAQ with points refunded

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

Zvonko
EE Cleanup Volunteer
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 14274284
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
    PAQ with points refunded

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

Zvonko
EE Cleanup Volunteer
0
 
LVL 4

Author Comment

by:pratigan
ID: 14275636
Hello Zvonko,
I appologize for leaving questions opened.  I have had a very hard time gettting support for this.  Go ahead and cancel the question until I can fight through the office red tape here and get the admin support I need.
I will issue a new question once I get past the BS.
Thank you !
Paul
0
 
LVL 63

Expert Comment

by:Zvonko
ID: 14275662
Ok.
Thanks for comming back.
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 14302901
PAQed with points refunded (250)

modulo
Community Support Moderator
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now