Writing AS/400 files with Domino Agent

Rayboneh
Rayboneh used Ask the Experts™
on
I am running Domino R5 on the AS/400. I have a Domino Workflow application from which I need to export data on a scheduled basis to an AS/400 File. I'd like to create a scheduled agent to run on the server & output the data to a file where an RPGLE program can use it. If the agent could kick off the RPG process, that would be an added bonus.

Any ideas on how to do this? Writing Lotusscript to get the data & output it to a PC file via the client is easy, but how can I get it to write to the AS/400 file system?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Sub Initialize
      Dim db As NotesDatabase
      Dim view As Notesview
      Dim doc As Notesdocument
      Dim form As Notesform
      Dim con As New ODBCConnection
      Dim qry As New ODBCQuery
      Dim res As New ODBCResultSet
      Dim ds As String
      Dim user As String
      Dim pw As String
      
      Print("Agent started")
      
      Set db= New NotesDatabase("server1/ourCompany", "Web\DealDetails.nsf")
      Set view = db.GetView("Records for AS400")
      Set doc = view.GetFirstDocument
      
      'set odbc variables
      ds = "BF2"
      user = "YourUser"
      pw = "YourPassword"
      
      If con.ConnectTo(ds, user, pw) Then      
            Set Qry.Connection = Con
            con.AutoCommit = False
            
' Clear the file on the AS/400 first          
            qry.SQL = "DELETE FROM PRISMBFLIB.DEALDTLS"
            Set Res.Query = Qry
            If Not Res.Execute() Then
                  Messagebox res.GetExtendedErrorMessage,, _
                  res.GetError & " " & res.GetErrorMessage
            End If
            
'  Select all the records that are currently in the file (this file should have been cleared before this agent is scheduled to run) in order to create the result set      
            qry.SQL = "SELECT * FROM PRISMBFLIB.DEALDTLS"
            Set Res.Query = Qry
            If Not Res.Execute() Then
                  Messagebox res.GetExtendedErrorMessage,, _
                  res.GetError & " " & res.GetErrorMessage
            End If
            
'  Add new rows to the results set and update with values from documents in Notes
'  Use the Left$ function to substring the text field to only include the number of characters that are in the field on the AS/400 file
'  Use the Val function to convert a text field to a numeric field in the AS/400 file (i.e. dates)
            
            Do Until doc Is Nothing
                  Call res.Addrow()
                  Call res.SetValue(1, Left$(doc.DealNum(0), 5))
                  Call res.SetValue(2, 1)
                  Call res.SetValue(3, doc.Line(0))
                  Call res.SetValue(4, Left$(doc.DDes(0),60))
                  Call res.SetValue(5, doc.CurBro(0))
                  Call res.SetValue(6, Left$(doc.BroNam(0), 30))
                  Call res.SetValue(7, Left$(doc.rsm(0), 30))
                  Call res.SetValue(8, Left$(doc.BLine(0), 3))
                  Call res.SetValue(9, doc.Cus(0))
                  Call res.SetValue(10, Left$(doc.CusNa(0), 30))
                  Call res.SetValue(11, Val(doc.P(0)))
                  Call res.SetValue(12, Left$(doc.Payee(0), 30))
                  Call res.SetValue(13, Left$(doc.DD(0), 80))
                  Call res.SetValue(14, Val(doc.SSD(0)))
                  Call res.SetValue(15, Val(doc.ESD(0)))
                  Call res.SetValue(16, Left$(doc.Dev(0), 30))
                  Call res.SetValue(17, Left$(doc.PLine(0), 4))
                  Call res.SetValue(18, Val(doc.PSD(0)))
                  Call res.SetValue(19, Val(doc.PED(0)))
                  If Isnumeric(doc.RPP(0)) Then
                        Call res.SetValue(20, doc.RPP(0))
                  Else
                        Call res.SetValue(20, 0)            
                  End If
                  
                  Call res.SetValue(21, Left$(doc.res1(0), 15))
                  Call res.SetValue(22, Left$(doc.pd1(0), 30))
                  Call res.SetValue(23, "1" + Left$(doc.cd1(0), 2))
                  Call res.SetValue(24, Left$(doc.pc1(0), 40))
                  Call res.SetValue(25, doc.bls1(0))
                  Call res.SetValue(26, doc.q1(0))
                  Call res.SetValue(27, doc.ec1(0))
                  Call res.SetValue(28, Left$(doc.ba1(0), 20))
                  Call res.SetValue(29, Left$(doc.Comments(0), 80))
                  Call res.SetValue(30, Left$(doc.CB(0), 20))
                  Call res.SetValue(31, Val(doc.TD(0)))
                  Call res.SetValue(32, Left$(doc.C1(0), 80))
                  Call res.SetValue(33, Left$(doc.DealClosed(0), 1))
                  Call res.SetValue(34, Left$(doc.Status(0), 10))
                  Call res.SetValue(35, doc.DealWeeks(0))
                  Call res.SetValue(36, Left$(doc.p1(0), 7))
                  If Isnumeric(doc.ap1(0)) Then
                        Call res.SetValue(37, doc.ap1(0))
                  Else
                        If doc.ap1(0) = ""  Then
                              Call res.SetValue(37, 0)
                        Else
                              If Mid(doc.ap1(0),1,1) = "$" Then
                                    Call res.SetValue(37, Val(Mid(doc.ap1(0),2)))
                              Else
                                    Call res.SetValue(37, Val(doc.ap1(0)))
                              End If              
                        End If
                  End If
                  Call res.SetValue(38,  "  ")
                  
                  Call res.UpdateRow()
                  
                  Set doc = view.GetNextDocument(doc)
                  
            Loop
      Else
            Messagebox("Cound not connect to server")
      End If
      
'  Close the results set and disconnect from the AS/400
      
      Res.Close(DB_Close)
      con.DisConnect
      
      Print("Agent finished")
      
End Sub

Commented:
Ok that was some sample code for you to experiment with.  Post that in a Lotusscript agent under INITIALIZE.  Also, almost forgot; post the following in the agent OPTIONS

Option Public
Uselsx "*LSXODBC"

Commented:
Also you will need to create an ODBC data source for it to work...
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

nice, sno.

Commented:
I had it laying around so I figured why not cut and paste it...
That's right, you guys do a lot of work on AS/400 there at B.F., right?   I recall you worked with N.L. up the road from me with that stuff.  

That's good, you'll get a call for me if I run into trouble on one.  ;-)
Well done Sno.  Atlast these points are yours for sure.

:-)

Commented:
Yeah but we're getting away from it... moving to SAP.  I can't wait because it is very cool stuff and I hate those green screens.  Hmmm, N.L. is not sounding familiar... probably something obvious but can't think of it.  

Hi Arun.

Author

Commented:
Nothing like being greedy, but after your quick response, I might as well ask....

I remember reading somewhere that an agent could also kick off an AS/400 stored procedure, etc. The icing on the cake would be if this agent could then start the process to deal with this data. Any samples there?

BTW,

I plan to try this tomorrow (it's getting late in the day here in NY). Once It works (as I'm sure it will), I'll award you the points.

Thanks for your quick & unbelievably detailed response.

Ray

Commented:
Sorry to ruin your party - but can an scheduled agent running on an AS/400 use ODBC connections? Isnt ODBC Windows only?

Commented:
You're welcome... unfortunately I don't know anything about kicking off a stored procedure though.  Maybe you should post a second question.

Stitt, you can indeed use ODBC on the AS400... we've been doing it for a while.  I'm not clear on how our admin sets up the drivers though...
btw, SNO --

take a close look at converting from LSCODBC to LCLSX.  Much more reliable, and updated by Lotus (at least on the x86 platforms).

on my website, look at the sample database for the presentation I did in Amsterdam and Miami on reading data from an RDB.  99% of the pres will be nothing new for you, but the sample db has some interesting code structures you can use.

Commented:
Ok I will check it out... thanks.

Commented:
SNO - are you sure you are not running Domino on Windows on as/4000? This is a native AS/400 server?

I'm just asking. I've always belived ODBC to be a Windows only properitery connection service?

And for LSCODBC or LCLSX - I would believe LCLSX was the best choice for all platforms. Its extensions for native connections to database systems (not via ODBC). (But - I admit I'm on thin ice here... )
stitt, you're basically right.  lclsx acts as a layer above the native connectors so you can use them all with the same code.  

Commented:
Ok I'm confused... maybe I'm not actually using ODBC in this agent.  I just know it works.  Andrew help me out... I don't have a good understanding of connectors.
it sure looks like odbc is what that agent is running.  ODBC is a standard created by microsoft, but it also has a counterpart in some other operating systems.  Frankly though in this case, you could probably use the db2 connector more efficiently.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial