Notes/Domino Fix List
 |  |
| SPR # MORY4SWP7E | Fixed in 5.0.6a release |  |
Product Area: Server Technical Area: LS Data Object Platform: AS/400
SPR# MORY4SWP7E - Fixed a problem in LSDO with the Close and SetParameter methods of ODBCResultSet class
Technote Number: 1100194
Problem:
This issue was reported to Lotus Quality Engineering and has been addressed in
Domino Release 5.0.6a. The problem is in the LSDO code in the Close and
SetParameter methods of ODBCResultSet class.
The following two workarounds are available:
1. Use different ODBCResultSet objects for each SQL statement that needs to be
executed. Here is the example ...
Example:
Sub Initialize
'Dim ws As New NotesUIWorkspace
'Dim uidoc As NotesUIDocument
'Set uidoc = ws.CurrentDocument
Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet
Dim result2 As New ODBCResultSet
Dim result3 As New ODBCResultSet
Dim result4 As New ODBCResultSet
Dim result5 As New ODBCResultSet
Dim result6 As New ODBCResultSet
Dim session As New NotesSession
Dim db As NotesDatabase
Set db = session.CurrentDatabase
Dim collectionA As NotesDocumentCollection
Set collectionA = db.Search("@IsAvailable(DriverUniform) &@length (DriverName)
> 1",Nothing,0)
Dim dt As New NotesDateTime("07/03/95")
Dim dc As notesdocumentcollection
notesFormula = " (Processed = 'N') & ( Form = 'Eval Form Design Two')"
Set dc = db.Search(notesFormula, dt, 0)
Dim doc As notesdocument
Set doc = dc.getfirstdocument
Do While Not (doc Is Nothing)
txt = doc.OrderNumber(0)
'txt = uidoc.FieldGetText ("OrderNumber")
Dim arrayA() As String
Dim arrayB() As String
value = txt
If txt <> "" Then
'ps = uidoc.FieldGetText ("OrderNumber2")
ps = doc.OrderNumber2(0)
Print "Establishing Network Connection"
con.SilentMode = True
Print "Logging On"
If Not con.ConnectTo("xxx", "xxx", "") Then
Messagebox "Could not connect to Evaluation",, _
"Error connecting"
Exit Sub
End If
Set qry.Connection = con
Set result.Query = qry
qry.SQL = "SELECT * FROM AMSPRDDB.XAOMSTP WHERE XAOMST_NBR_ORD = ?avalue?
AND XAOMST_CODE_PS = ?bvalue? ORDER BY XAOMST_DATE_ACT_DELV"
' Gross
Call result.SetParameter("avalue","'" & Ucase(value) & "'")
If ps = "" Then
Call result.SetParameter("bvalue","'" & "" & "'")
Else
Call result.SetParameter("bvalue","'" & ps & "'")
End If
result.Execute
result.MaxRows = 10
If result.IsResultSetAvailable Then
result.LastRow
txt = result.GetValue("XAOMST_NAME_SHPR_FRST") & " " &
result.GetValue("XAOMST_NAME_SHPR_LAST")
'Call uidoc.FieldSetText ("CustomerName",txt)
doc.CustomerName = txt
txt = result.GetValue("XAOMST_CONT_DEST")
'Call uidoc.FieldSetText ("AccountName",txt)
doc.AccountName = txt
txt = result.GetValue("XAOMST_ADDR_DEST") & " " &
result.GetValue("XAOMST_APT_DEST")
'Call uidoc.FieldSetText ("Address",txt)
doc.Address = txt
txt = result.GetValue("XAOMST_CITY_DEST") & ", "
'Call uidoc.FieldSetText ("City",txt)
doc.City = txt
txt = result.GetValue("XAOMST_ST_DEST")
'Call uidoc.FieldSetText ("State",txt)
doc.State = txt
txt = result.GetValue("XAOMST_ZIP_DEST")
'Call uidoc.FieldSetText ("Zip",txt)
doc.Zip = txt
txt = result.GetValue("XAOMST_AGT_ORG")
'Call uidoc.FieldSetText ("OA" , Cstr(txt))
doc.OA = Cstr(txt)
txt = result.GetValue("XAOMST_AGT_DEST")
'Call uidoc.FieldSetText ("DA", Cstr(txt))
doc.DA = Cstr(txt)
txt = result.GetValue("XAOMST_AGT_HAUL")
'Call uidoc.FieldSetText ("Haul", Cstr(txt))
doc.Haul = Cstr(txt)
coordID = result.GetValue("XAOMST_ID_COORD")
coordAgt = result.GetValue("XAOMST_AGT_COORD")
'Call uidoc.FieldSetText ("CoordinatorNum", Cstr(coordID))
doc.CoordinatorNum = Cstr(coordID)
txt = result.GetValue("XAOMST_NBR_NATL_ACCT")
'Call uidoc.FieldSetText ("AccountNum", Cstr(txt))
doc.AccountNum = Cstr(txt)
Print "Validating Information"
'result.Close(DB_CLOSE)
Else
Messagebox "OrderNumber Not Found In AS400" & Chr(10) & "(Library:
AMSPRDDB File: XAOMSTP)",0+16, _
"No data"
'Call uidoc.GotoField ("OrderNumber")
'Exit Sub
End If
qry.SQL = "SELECT * FROM AMSPRDDB.DSOSTAP WHERE OS_NBR_ORD = ?avalue? AND
OS_CODE_PS = ?bvalue? AND INDT_CURR_STAT = ?cvalue?"
' Driver Numbers
Set result2.Query = qry
'>>>>>TVC: need to set the avalue and bvalue parameters again...
Call result2.SetParameter("avalue","'" & Ucase(value) & "'")
If ps = "" Then
Call result2.SetParameter("bvalue","'" & "" & "'")
Else
Call result2.SetParameter("bvalue","'" & ps & "'")
End If
Call result2.SetParameter("cvalue","'" & "C" & "'")
result2.execute
result2.MaxRows = 10
If result2.IsResultSetAvailable Then
result2.LastRow
drvrntxt = result2.GetValue("OS_NBR_DRVR")
If Len(drvrntxt) = 1 Then
drvrntxt = "0" & result2.GetValue("OS_NBR_DRVR")
End If
drvratxt = result2.GetValue ("OS_NBR_DRVR_AGT")
txt = drvrntxt & "-" & drvratxt
'Call uidoc.FieldSetText ("DriverNum",txt)
doc.DriverNum = txt
'result2.Close(DB_CLOSE)
Else
Messagebox "OrderNumber Not Found In AS400" & Chr(10) & "(Library:
AMSPRDDB File: XAOMSTP)",, _
"No data"
'Call uidoc.FieldSetText ("DriverName", "Unknown")
'Call uidoc.FieldSetText ("DriverNum", "Unknown")
doc.DriverName = "Unknown"
doc.DriverNum = "Unknown"
End If
Qry.SQL = "SELECT * FROM AMSPRDDB.XASCRWP WHERE XASCRW_ID_SERV_CREW =
?dvalue? AND XASCRW_NBR_SERV_AGT = ?evalue?"
' Driver Name
Set result3.Query = qry
Call result3.SetParameter("dvalue","'" & Cstr(drvrntxt) & "'")
Call result3.SetParameter("evalue",Cstr(drvratxt))
result3.Execute
result3.MaxRows = 1
If result3.IsResultSetAvailable Then
txt = result3.GetValue("XASCRW_NAME_SERV_CREW_FIRST") & " " &
result3.GetValue("XASCRW_NAME_SERV_CREW_LAST")
'Call uidoc.FieldSetText ("DriverName",
result3.GetValue("XASCRW_NAME_SERV_CREW_FIRST") & " " &
result3.GetValue("XASCRW_NAME_SERV_CREW_LAST"))
doc.DriverName = Cstr(txt)
'result3.Close(DB_CLOSE)
Else
'Call uidoc.FieldSetText ("DriverName","Unknown")
doc.DriverName = "Unknown"
End If
qry.SQL = "SELECT * FROM AMSPRDDB.XASCRWP WHERE XASCRW_ID_SERV_CREW =
?dvalue? AND XASCRW_NBR_SERV_AGT = ?evalue?"
' Coordinator Name
Set result4.Query = qry
Call result4.SetParameter("dvalue","'" & Cstr(coordID) & "'")
Call result4.SetParameter("evalue",Cstr(coordAgt))
result4.Execute
result4.MaxRows = 1
If result4.IsResultSetAvailable Then
txt = result4.GetValue("XASCRW_NAME_SERV_CREW_FIRST") & " " &
result4.GetValue("XASCRW_NAME_SERV_CREW_LAST")
'Call uidoc.FieldSetText ("CoordinatorName",
result4.GetValue("XASCRW_NAME_SERV_CREW_FIRST") & " " &
result4.GetValue("XASCRW_NAME_SERV_CREW_LAST"))
doc.CoordinatorName = Cstr(txt)
'result4.Close(DB_CLOSE)
Else
'Call uidoc.FieldSetText ("CoordinatorName","Unknown")
doc.CoordinatorName = "Unknown"
End If
qry.SQL = "SELECT * FROM CORLIBDB.ORDERH WHERE ORD#00 = ?avalue?"
'Salesperson(s) Initials
Set result5.Query = qry
Call result5.SetParameter("avalue","'" & Ucase(value) & "'")
result5.Execute
result5.MaxRows = 1
If result5.IsResultSetAvailable Then
i = 0
For c = 1 To 4
txt = result5.GetValue("SLS" & c & "00")
ptxt = result5.GetValue("SPR" & c & "00")
If txt = "OOA" Or txt = "OSA" Or txt = "XXX" Or txt = "" Then
Else
i = i + 1
Redim Preserve arrayA(i)
arrayA (i) = txt
Redim Preserve arrayB(i)
arrayB (i) = ptxt
End If
Next
Else
Exit Sub
End If
c = 0
For i = 1 To Ubound(arrayA)
qry.SQL = "SELECT * FROM CORLIBDB.SALES WHERE SCOD20 = ?avalue?"
'Salesperson(s) Names
Set result6.Query = qry
Call result6.SetParameter("avalue","'" & arrayA(i) & "'")
result6.Execute
result6.MaxRows = 1
If result6.IsResultSetAvailable Then
If Instr(1,result6.GetValue ("SNAM20")," HOUSE") Then
Else
c = c + 1
'Call uidoc.FieldSetText ("SalespersonName" & c, result6.GetValue
("SNAM20"))
txt = result6.GetValue ("SNAM20")
'Dim sales List As String
sales = doc.SalespersonName(0) + txt
doc.SalespersonName = sales
'Call uidoc.FieldSetText ("SalespersonNum" & c, arrayA(i))
sales = doc.SalespersonNum(0) + arrayA(i)
doc.SalespersonNum = sales
'inittext = uidoc.FieldGetText ("SalespersonsInit")
inittext = doc.SalespersonInit(0)
'Call uidoc.FieldSetText ("SalespersonsInit",inittext & " " & arrayA(i) &
" ")
sales = inittext + " " + arrayA(i) + " "
doc.SalespersonsInit = sales
'Call uidoc.FieldSetText ("SalespersonPer" & c,arrayB(i))
sales = doc.SalespersonPer(0) + arrayB(i)
doc.SalespersonPer = sales
End If
Else
Messagebox "Salesperson's Initials Not Found In AS400" & Chr(10) &
"(Library: CORLIBDB File: SALES)",0+48, _
"No data"
Exit For
End If
Next
Print ""
doc.Processed = "Y"
Call doc.save(True,True)
End If
Set doc = dc.getnextdocument(doc)
Set doc = Nothing
Loop
con.Disconnect
Exit Sub
errCondition:
Print errMsg
'Beep
'Call uidoc.GotoField ("OrderNumber")
End Sub
2. Set the Qry.SQL statements without using parameters and
result.SetParameter(). Here is the example...
Example:
Sub Initialize
'Dim ws As New NotesUIWorkspace
'Dim uidoc As NotesUIDocument
'Set uidoc = ws.CurrentDocument
Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet
Dim session As New NotesSession
Dim db As NotesDatabase
Set db = session.CurrentDatabase
Dim collectionA As NotesDocumentCollection
Set collectionA = db.Search("@IsAvailable(DriverUniform) &@length (DriverName)
> 1",Nothing,0)
Dim dt As New NotesDateTime("07/03/95")
Dim dc As notesdocumentcollection
notesFormula = " (Processed = 'N') & ( Form = 'Eval Form Design Two')"
Set dc = db.Search(notesFormula, dt, 0)
Dim doc As notesdocument
Set doc = dc.getfirstdocument
Do While Not (doc Is Nothing)
txt = doc.OrderNumber(0)
'txt = uidoc.FieldGetText ("OrderNumber")
Dim arrayA() As String
Dim arrayB() As String
value = txt
If txt <> "" Then
'ps = uidoc.FieldGetText ("OrderNumber2")
ps = doc.OrderNumber2(0)
Print "Establishing Network Connection"
con.SilentMode = True
Print "Logging On"
If Not con.ConnectTo("xxx", "xxx", "xxx") Then
Messagebox "Could not connect to Evaluation",, _
"Error connecting"
Exit Sub
End If
Msgbox "step 0"
Set qry.Connection = con
Set result.Query = qry
SQLstmt = "SELECT * FROM AMSPRDDB.XAOMSTP WHERE XAOMST_NBR_ORD ="
SQLstmt = SQLstmt & "'" & Ucase(value) & "'"
SQLstmt = SQLstmt & " AND XAOMST_CODE_PS = "
If ps = "" Then
SQLstmt= SQLstmt & "'" & "" & "'"
Else
SQLstmt= SQLstmt & "'" & ps & "'"
End If
SQLstmt = SQLstmt & " ORDER BY XAOMST_DATE_ACT_DELV "
qry.SQL = SQLstmt
result.Execute
result.MaxRows = 10
If result.IsResultSetAvailable Then
result.LastRow
txt = result.GetValue("XAOMST_NAME_SHPR_FRST") & " " &
result.GetValue("XAOMST_NAME_SHPR_LAST")
'Call uidoc.FieldSetText ("CustomerName",txt)
doc.CustomerName = txt
txt = result.GetValue("XAOMST_CONT_DEST")
'Call uidoc.FieldSetText ("AccountName",txt)
doc.AccountName = txt
txt = result.GetValue("XAOMST_ADDR_DEST") & " " &
result.GetValue("XAOMST_APT_DEST")
'Call uidoc.FieldSetText ("Address",txt)
doc.Address = txt
txt = result.GetValue("XAOMST_CITY_DEST") & ", "
'Call uidoc.FieldSetText ("City",txt)
doc.City = txt
txt = result.GetValue("XAOMST_ST_DEST")
'Call uidoc.FieldSetText ("State",txt)
doc.State = txt
txt = result.GetValue("XAOMST_ZIP_DEST")
'Call uidoc.FieldSetText ("Zip",txt)
doc.Zip = txt
txt = result.GetValue("XAOMST_AGT_ORG")
'Call uidoc.FieldSetText ("OA" , Cstr(txt))
doc.OA = Cstr(txt)
txt = result.GetValue("XAOMST_AGT_DEST")
'Call uidoc.FieldSetText ("DA", Cstr(txt))
doc.DA = Cstr(txt)
txt = result.GetValue("XAOMST_AGT_HAUL")
'Call uidoc.FieldSetText ("Haul", Cstr(txt))
doc.Haul = Cstr(txt)
coordID = result.GetValue("XAOMST_ID_COORD")
coordAgt = result.GetValue("XAOMST_AGT_COORD")
'Call uidoc.FieldSetText ("CoordinatorNum", Cstr(coordID))
doc.CoordinatorNum = Cstr(coordID)
txt = result.GetValue("XAOMST_NBR_NATL_ACCT")
'Call uidoc.FieldSetText ("AccountNum", Cstr(txt))
doc.AccountNum = Cstr(txt)
Print "Validating Information"
'result.Close(DB_CLOSE)
Else
Messagebox "OrderNumber Not Found In AS400" & Chr(10) & "(Library:
AMSPRDDB File: XAOMSTP)",0+16, _
"No data"
'Call uidoc.GotoField ("OrderNumber")
'Exit Sub
End If
<<<< more code after this >>>>
Supporting Information:
Related Documents:
More >
 A fix for this SPR has been developed but is currently undergoing testing. IBM reserves the right to remove this fix from the targeted release if it does not pass quality assurance tests. Please consider this information to be provisional. Do not base irreversible business decisions on this information until this notice has been removed.
 |
Last Modified on 04/03/2001
Go back
|