|
midrange.com
Greetings,
We have a Domino Database located on AS/400 A (V4R5 OS/400 and 5.0.2a on
Domino). We have an agent we have written to perform the replication to a
DB2 database located on AS/400 B (V4R5 OS/400). The agent looks like the
following:
Option Public
Option Declare
Uselsx "*LSXODBC"
Dim ODBCcon As ODBCConnection
Dim ODBCconnected As Variant ' set to true when connected to an ODBC data
source
Sub Initialize
' setup the connection to the as400
connectODBC
'Print "Start run-----------------------------------<br>"
End Sub
Sub connectODBC
Set ODBCcon = New ODBCConnection
If ODBCcon.ConnectTo("...", "..." , "...") Then ' Names removed to
protect the innocent.
ODBCconnected = True
Else
Print "Replicate Item Documents Is NOT Connected"
Print ODBCcon.GetExtendedErrorMessage,,
ODBCconnected = False
End If
End Sub
Sub process
' get all changed documents
Dim session As New notessession
Dim db As notesdatabase
Set db = session.currentdatabase
Dim dc As notesdocumentcollection
Set dc = db.unprocesseddocuments
'Set dc = db.alldocuments
Print "Item Document Replication Running on " & dc.count & "
documents."
Dim doc As notesdocument
Set doc = dc.getfirstdocument()
Dim ctr As Integer
' for each document
While Not doc Is Nothing
Print "Running on document " & doc.DocumentID(0)
'Print "Processing Document " & doc.DocumentNumber(0) & "
datasource " & doc.datasource(0) & "<br>"
' Print doc.form(0) & "-" & doc.DataSource(0) & "<br>"
If ( doc.form(0) = "DocumentDetail" ) Then
' see if need to update existing document.
If ( existsAS400 ( doc ) ) Then
If ( doc.DeleteDocument(0) <> "Yes" ) Then
' if so update the as400
'Print "Update Document " & doc.DocumentNumber(0)
& "<br>"
Call updateAS400 ( doc )
Else
' See if need to delete document.
If ( doc.DeleteDocument(0) = "Yes" ) Then
' 'Print "Delete Document " &
doc.documentnumber(0) & "<br>"
Call deleteAS400 ( doc )
End If
End If
Else
' See if need to insert document.
' Print "add<br>"
If (doc.DeleteDocument(0) <> "Yes") Then
'Print "Insert Document " & doc.documentnumber(0)
& "<br>"
Call addAS400 ( doc )
End If
End If
End If
' mark the document as processed
Call session.updateprocesseddoc ( doc )
' loop
Set doc = dc.getnextdocument ( doc )
Wend
End Sub
Sub Terminate
Print "Finish Run----------------------------"
End Sub
Function existsAS400( doc As notesdocument ) As Integer
' return true if this document exists on the as400
Dim result As ODBCResultSet
Set result = runQuery ( " select count(*) from bpcsf.itemdocs " & _
"where DOCUMENTID = '"_
& doc.GetItemValue( "DocumentID" )(0) & "'")
result.NextRow
existsAS400 = ( result.GetValue(1) <> "0" )
End Function
Sub updateAS400 ( doc As notesdocument )
' update the selected document on the as400
Dim sql As String
Print "Updating a document " & doc.getitemvalue("DocumentID")(0)
sql = "update bpcsf.itemdocs set ITEMNUMBER" & " = '" &
doc.GetItemValue ( "ItemNumber" )(0) & "'"_
& ", DOCSEQNBR = " & doc.GetItemValue( "DocumentSequenceNumber" )(0)
& ", "_
& "REVNBR = '" & doc.GetItemValue( "RevisionNumber" )(0) & "', " _
& "DOCTYPE = '" & doc.GetItemValue( "DocumentType" )(0) & "', " _
& "DOCDESC = '" & doc.GetItemValue("DocDescription")(0) & "', " _
& "PRINTONSO = '" & doc.GetItemValue("PrintOnShopOrder")(0) & "', "_
& "WORKURL = '" & doc.GetItemValue("WorkingDocumentURL")(0) & "', "_
& "VIEWURL = '" & doc.GetItemValue("ViewingDocumentURL")(0) & "', "_
& "ACTIVEREV = '" & doc.GetItemValue("ActiveRevision")(0) & "', "_
& "WHODISC = '" & doc.GetItemValue("WhoDiscontinued")(0) & "', " _
& "DOCVENDOR = '" & doc.GetItemValue("Vendor")(0) & "', "_
& "CREATEDBY = '" & doc.GetItemValue("CreatedBy")(0) & "', "_
& "AUTHORS = '" & doc.GetItemValue("Authors")(0) & "', "_
& "DOCUMENTID = '" & doc.GetItemValue("DocumentID")(0) & "'"
sql = sql & " where DOCUMENTID = '"_
& doc.GetItemValue( "DocumentID" )(0) & "'"
Print sql
runQuery ( sql )
End Sub
Sub addAS400 ( doc As notesdocument )
' add the selected document to the as400
Dim sql As String
Print "Adding a document " & doc.getitemvalue("DocumentID")(0)
sql = "insert into bpcsf.itemdocs ( " & _
"ITEMNUMBER, "_
& "DOCSEQNBR, "_
& "REVNBR, "_
& "DOCTYPE, "_
& "DOCDESC, "_
& "PRINTONSO, "_
& "WORKURL, "_
& "VIEWURL, "_
& "ACTIVEREV, "_
& "WHODISC, "_
& "DOCVENDOR, "_
& "DATECREATED, "_
& "CREATEDBY, "_
& "LASTACCDTE, "_
& "LASTMODDTE, "_
& "AUTHORS, "_
& "DOCUMENTID) "
sql = sql &_
"values("_
& "'" & doc.GetItemValue("ItemNumber")(0) & "', "_
& doc.GetItemValue("DocumentSequenceNumber")(0) & ", "_
& "'" & doc.GetItemValue("RevisionNumber")(0) & "', "_
& "'" & doc.GetItemValue("DocumentType")(0) & "', "_
& "'" & doc.GetItemValue("DocDescription")(0) & "', "_
& "'" & doc.GetItemValue("PrintOnShopOrder")(0) & "', "_
& "'" & doc.GetItemValue("WorkingDocumentURL")(0) & "', "_
& "'" & doc.GetItemValue("ViewingDocumentURL")(0) & "', "_
& "'" & doc.GetItemValue("ActiveRevision")(0) & "', "_
& "'" & doc.GetItemValue("WhoDiscontinued")(0) & "', "_
& "'" & doc.GetItemValue("Vendor")(0) & "', "_
& "Current Date, "_
& "'" & doc.GetItemValue("CreatedBy")(0) & "', "_
& "Current Date, "_
& "Current Date, "_
& "'" & doc.GetItemValue("Authors")(0) & "', "_
& "'" & doc.GetItemValue("DocumentID")(0) & "')"
Print sql
runQuery ( sql )
End Sub
Sub deleteAS400 ( doc As notesdocument )
' delete the selected document on the as400
Dim sql As String
Print "Deleting a document " & doc.getitemvalue("DocumentID")(0)
sql = "Delete from bpcsf.itemdocs "
sql = sql & " where DOCUMENTID = '"_
& doc.GetItemValue( "DocumentID" )(0) & "'"
Print sql
runQuery ( sql )
End Sub
Function runQuery( sql As String ) As ODBCResultSet
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet
ODBCcon.disconnect
connectODBC
result.close
Set qry.Connection = ODBCcon
qry.SQL = sql
' debug "<!-- runQuery = " & qry.SQL & " -->"
'Print "runQuery " & qry.SQL & "<br>"
Set result.Query = qry
result.Execute
Set runQuery = result
End Function
This agent is set to run on Add/Change of documents in Domino. If I change
the agent to run on Selected documents, in effect it appears to be running
from my PC and everything works great.
When it is set to run on Add/Change of documents and run on the server,
based on the "debug" print statements I have in the above source, I get
connected to AS/400 B okay. It appears to function as far as finding
existing entries or determining it is a new entry and makes it to the
proper Sub (Insert, Update, or Delete).
What is NOT happening is the actual SQL does not appear to do anything when
run from the Server (again, if I run the agent on selected documents from
my PC everything works great). That tells me the basic SQL syntax is
correct.
My questions are these:
1. What is wrong?
2. With no answer there, how can I debug this stuff on AS/400 B. I try to
watch for the QRWTSRVR jobs but I can't seem to catch them on a consistent
basis and the one I did catch provided the message "Token
<END-OF-STATEMENT> was not valid. Valid tokens: (." That message
indicates an SQL syntax problem but again, if I run the agent on selected
documents, everything works fine which should mean the SQL syntax is
correct.
Any help will be greatly appreciated.
Dwight Slessman
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
