Hallo,
I have a script in which data is gathered from DB2 tables. These tables have now been placed on SQL server and I have to adapt the script. I read a lot of postings on this forum about this topic but I can’t find (or don’t understand) what exactly I have to modify.
Sub Initialize
Dim sess As New NotesSession
Dim db As NotesDatabase
Dim view As Notesview
Dim doc As Notesdocument
Dim AgentLog As New NotesLog("Update Request for inspection from SQL Server")
Call AgentLog.OpenMailLog("Name xxxxx", "Agent Log from Request for inspection database")
'Dims for SQL lookups
Dim con As New odbcconnection
Dim qry As New odbcquery
Dim res As New odbcresultset
Dim con1 As New odbcconnection
Dim qry1 As New odbcquery
Dim res1 As New odbcresultset
Set db = sess.Currentdatabase
Set view = db.getview("(PartNumber)")
Set doc = view.GetFirstDocument
'connect to the data source
If con.connectto("database","username","passwd") Then
'define the query by attaching it to the connection,
'and defining the SQL statement
Set qry.connection = con
Call AgentLog.LogAction("Connected to SQL server")
Else
Call AgentLog.LogAction("Notes Request for inspection database agent couldn't connect to SQL Server")
End If
FoundPN = 0
ZeroOnHand = 0
While Not ( doc Is Nothing )
Set Partnumber = doc.GetFirstItem("PartNrID")
PartnumberTxt = PartNumber.Text
qry.sql = "select MIF_ECICED, MIF_BOQTY, MIF_TOTOHALLSOS " + _
"from dbo.T_PART_INFO where MIF_ID_PART = '" + PartNumberTxt + "'"
Set res.query = qry
'perform the query
res.execute
Call AgentLog.LogAction("PartNumberTxt = " + PartNumberTxt + " MIF_ID_PART = " + MIF_ID_PART)
'if the partnumber is found, fill the part info into fields on the form
If res.isresultsetavailable = True Then
FoundPN = FoundPN + 1
Call doc.ReplaceItemValue("OH",res.getvalue("MIF_TOTOHALLSOS"))
Call doc.ReplaceItemValue("BO",res.getvalue("MIF_BOQTY"))
Call doc.ReplaceItemValue("ED",res.getvalue("MIF_ECICED"))
Call doc.save(True,False)
Else
If Not PartNumberTxt = "" Then
Call AgentLog.LogAction("Update agent got no resultset for " + PartNumberTxt)
End If
End If
Set doc = view.GetNextDocument( doc )
Wend
res.close con.disconnect
Call AgentLog.LogAction(“Update agent for Request for inspection found " + Trim(Str(FoundPN)) + " partnumbers on SQL server”)
Call AgentLog.LogAction(“Update agent for Request for inspection Info has completed”)
Call AgentLog.Close
End Sub
As you can see I put some displays in the script and these are the top of the results
28-03-2008 11:19:35 AM Update Request for inspection from SQL Server starting
28-03-2008 11:19:35 AM Connected to SQL server
28-03-2008 11:19:35 AM PartNumberTxt = 0688417 MIF_ID_PART =
28-03-2008 11:19:35 AM PartNumberTxt = 1859940 MIF_ID_PART =
28-03-2008 11:19:35 AM PartNumberTxt = 2749365 MIF_ID_PART =
28-03-2008 11:19:35 AM PartNumberTxt = 2185957 MIF_ID_PART =
28-03-2008 11:19:35 AM PartNumberTxt = 1367003 MIF_ID_PART =
28-03-2008 11:19:35 AM PartNumberTxt = MIF_ID_PART =
28-03-2008 11:19:35 AM PartNumberTxt = C69066 MIF_ID_PART =
As a matter of fact it says “Connected to SQL server” but it doesn’ t find equality on partnbr because MIF_ID_PART always shows blanks.
This is the end of the displayed results:
28-03-2008 11:20:09 AM PartNumberTxt = MIF_ID_PART =
28-03-2008 11:20:09 AM PartNumberTxt = 1746769 MIF_ID_PART =
28-03-2008 11:20:09 AM PartNumberTxt = D98935 MIF_ID_PART =
28-03-2008 11:20:09 AM Update agent for Request for inspection found 4888 partnumbers on SQL server
28-03-2008 11:20:09 AM Update agent for Request for inspection Info has completed
Any idea where it goes wrong?
Thanks