Domino web query returns incomplete result set

400 pts.
Tags:
Lotus Domino
LotusScript
SQL
Web
I've inherited a a web based application which when executed via the notes form returns a false result set. The sql query executes and returns the proper result set when run through query manager however, when executed via the Notes web form using the ALL ALL input search parameter only a partial result set is returned. Any help would be appreciated. The agent is called via a button executing the Tools run macro command and populates a table on the web form. Any help, before I pull my hair out would be much appreciated Option Public
Uselsx "*LSXODBC"
Use "ZScript"
Const TDefH = "<TD COLSPAN = 1><B><FONT FACE='Arial' SIZE=2 > "
Const TDefHX = "</b></td>"
Const TDef = "<TD COLSPAN = 1><FONT FACE='Arial' SIZE=1 > "
Const TDefX = "</td>"

Const TDefR=  "<TD COLSPAN = 1><DIV ALIGN=RIGHT><FONT SIZE=1 FACE='Arial'>"
Const TDefRX= "</DIV></TD>"

' opens URL in same window
Const urla = "<a href = ""http://"
Const urlb = """>"
Const urlc = "</a>"

' opens URL in new window
Const One = " <a href = ""http://"
Const Two = " "" target=""_blank"">"
Const Three = "</a>"

Dim db As NotesDatabase
Dim view As notesview
Dim con As ODBCConnection
Dim qry As ODBCQuery
'Dim result As  ODBCResultSet
'Dim result2 As ODBCResultSet
Dim doc As NotesDocument
Dim PageSize As Integer

Dim ResultArray (20000 , 25) As String
REM  For Future Changes =  This defines the array that takes the rows.
REM If more than 20,000 is needed, change the 1st value
Sub Initialize
Dim session As New NotesSession
Dim datetime As notesdatetime
Dim asondt As notesdatetime

Set db = session.CurrentDatabase
Set doc = session.documentcontext
Dim CVQry As String
Dim RTFData As NotesRichTextItem
Redim fieldnamearr(1 To 1) As String
Dim ResultArr() As Variant
Dim ResultArrNew() As Variant

Dim ReturnedRowCounter&
Dim FileNumber%
Dim firstQuery%, lastRowNumberIsKnown%, lastRowNumber%
firstQuery = True
lastRowNumberIsKnown% = False

REM   The following table defineds the order that the colums are put into table
Dim ReportSequence% (1 To 25)
ReportSequence(1) = 1
ReportSequence(2) = 2
ReportSequence(3) = 3
ReportSequence(4) = 4
ReportSequence(5) = 5
ReportSequence(6) = 6
ReportSequence(7) = 7
ReportSequence(8) = 8
ReportSequence(9) = 9
ReportSequence(10) = 17
ReportSequence(11) = 10
ReportSequence(12) = 11
ReportSequence(13) = 12
ReportSequence(14) = 13
ReportSequence(15) = 14
ReportSequence(16) = 19
ReportSequence(17) = 15
ReportSequence(18) = 16
ReportSequence(19) = 18
ReportSequence(20) = 20
ReportSequence(21) = 21
ReportSequence(22) = 22
ReportSequence(23) = 23
ReportSequence(24) = 24
ReportSequence(25) = 25

Region = doc.Input1(0)
ProdL = doc.Input2(0)

On Error GoTo errorHandler
Set con = New ODBCConnection
Set qry = New ODBCQuery
Set result = New ODBCResultSet
Set qry.Connection = con
Set result.Query = qry
Set RTFData = New NotesRichTextItem(doc, "RTF")

ServerName = doc.server(0)
DBPath = doc.DBName(0)

If ServerName = "usnyapp06" Then
ServerAlias ="Infonet.zeiss.org"
Else
ServerAlias = Servername+".zeiss.org"
End If

Dim CVConType As String
Dim CVConSrv As String
Dim CVConUid As String
Dim CVConPwd As String
Call CVLogin (CVConType, CVConSrv, CVConUid, CVConPwd)
st = con.ConnectTo (CVConType, CVConUid, CVConPwd)

If Not con.IsConnected Then
doc.errorMsg = "No connection made"
GoTo ExitProgram
Else
doc.status =  "We are connected"
End If
GoSub defineQuery

If doc.showQuery(0) = "Yes" Then _
Call RTFData.AppendText(CVQry)

RowStart& = clng(doc.RowStart(0))
If IsNumeric(doc.RowDisplayNumber(0))Then
RowDisplayNumber& = CLng(doc.RowDisplayNumber(0))
Else
RowDisplayNumber& = 9999999
End If

GoSub WriteHeaderHtml

If doc.extraQueryRetries(0) = "No" Then
maxQueryRetry% = 15      ' will iterate this many times to get to end of result set
Else
maxQueryRetry% = 25
End If

gosub DB_Query

ReturnedRowCounter = 0
If  Not ( result.NumRows = DB_ROWSUNKNOWN) Then
lastRowNumberIsKnown = True
lastRowNumber = result.NumRows
doc.status = dos.status(0) + Chr(13) + "last row number is " + cStr(lastRowNumber)
Else
doc.status = doc.status(0) + Chr(13) + "last row number is unknown at start of get-next routine"
End if

Dim QueryBlockSize as Integer
Dim BlockFetchAttempts as Integer
BlockFetchAttempts = 5  'REM there are 5 blocks fetch attempts
QueryBlockSize = 4000      'REM get 4000 rows at a time

For Blockfetches% = 1 To BlockFetchAttempts
For k% = 1 To QueryBlockSize

ReturnedRowCounter = ReturnedRowCounter + 1
result.CurrentRow = ReturnedRowCounter
For valueNumber% = 1 To 25
RepSeqNum% = reportSequence(ValueNumber%)

If ((RepSeqNum% = 13 Or repseqNum% = 14) And CStr(result.GetValue(RepSeqNum%) = "0")) Or _
(RepSeqNum% = 19 And CStr(result.GetValue(RepSeqNum%) = "12:00:00 AM")) Then
ResultArray(ReturnedRowCounter , valueNumber% ) = " - "
elseif      (CStr(result.GetValue(RepSeqNum%)) <> "") Then
If valueNumber% = 8 Or valueNumber% = 10 Or valueNumber% = 13 or valueNumber% = 19 Then
'Is this one of the column that is currency, then format currency

ResultArray(ReturnedRowCounter ,
valueNumber% ) = Format(result.GetValue(RepSeqNum%),"Currency")
ElseIf valueNumber% = 12 Or valueNumber% = 16  Then

ResultArray(ReturnedRowCounter ,
valueNumber% ) = Format(result.GetValue(RepSeqNum%), "Short Date")
Else
ResultArray(ReturnedRowCounter , valueNumber% ) = result.GetValue(RepSeqNum%)
End if

Else
ResultArray(ReturnedRowCounter , valueNumber% ) = " - "
End IF
Next

If lastRowNumberIsKnown And lastRowNumber = ReturnedRowCounter Then
doc.status = "Search completed at last record"
GoTo FormatResults
End If

If result.IsEndOfData Then
if (ReturnedRowCounter < 12000) Then
doc.errorMsg = doc.errorMsg(0) + Chr(13) + "False End of Data at row " + CStr(ReturnedRowCounter)
Call doc.save(True,True)
gosub db_query
ElseIf  Not ( result.NumRows = DB_ROWSUNKNOWN) Then
lastRowNumberIsKnown = True
lastRowNumber = result.NumRows
If lastRowNumber = returnedRowCounter Then
doc.status = "Search completed at last record"
GoTo FormatResults
End if
GoSub db_query
End If
End if
Next

Next
FormatResults:
GoSub FormatTableResults
goto exitProgram

Call RTFData.AppendText("</Table>]")

DB_Query :

If maxQueryRetry% > 0 Then
If Not firstQuery  Then
maxQueryRetry% = maxQueryRetry% - 1

doc.FalseEndOfDataReport = doc.FalseEndOfDataReport(0) +
Chr(13) + "Retrying Query at row" + CStr(ReturnedRowCounter)
End if
result.CacheLimit = DB_ALL

result.Execute

If result.geterror <> dbstssuccess Then
ResultErrorMsg$ = result.geterrormessage()

doc.errorMsg = doc.errorMsg(0) + Chr(13) + "Error during
re-query, msg = " + CStr(result.geterror) + ResultErrorMsg$
GoTo DBerror
End If
If  firstQuery Then
firstQuery = False

End if
Else

doc.errorMsg = doc.errorMsg(0) + Chr(13) + "Maximum Query Retry
exceeded at row number = "  + CStr(ReturnedRowCounter)
GoTo ExitProgram
End if
Return

dbError:
doc.errorMsg = doc.errorMsg(0) + Chr(13) + " code at line " + CStr(Erl) +  " " + result.GetExtendedErrorMessage + _
" at row number = "  + CStr(ReturnedRowCounter)
GoSub formatTableResults
GoTo exitProgram

errorHandler:
doc.errorMsg = doc.errorMsg(0) + Chr(13) +  Error$ + " on line number " + CStr (Erl)_
+ "  Returned rows "  + CStr (ReturnedRowCounter) + " {" + result.GetExtendedErrorMessage + ")"
GoSub FormatTableResults
GoTo exitProgram

defineQuery:
CVQry =                   " select i.item_no, i.description, i.item_group,e.serial_no, e.equip_code, e.equip_sts, "
CVQry
= CVQry +      "       e.install_date, max(ws.cmp_time),
bl.location_name,max(se.end_date), min(se2.start_date),  "
CVQry
= CVQry + "       cl.cont_no, cl.start_date,
cl.end_date,c.cont_sts,emp.last_name, emp.first_name,
bl.service_branch_code, "  '11-17
CVQry = CVQry + "       cp.first_name, cp.last_name, cp.phone, bl.addr1, bl.city, bl.state_code, bl.postal_code "
CVQry = CVQry + " from cvmicroprod.dbo.equipment e "
CVQry = CVQry + " join cvmicroprod.dbo.item i on e.item_id = i.item_id"
CVQry = CVQry + " Left Join cvmicroprod.dbo.work_task wt On wt.serial_no = e.serial_no "
CVQry = CVQry + " Left Join cvmicroprod.dbo.work_sched ws On ws.work_no = wt.work_no And ws.worktask_no = wt.worktask_no "
CVQry
= CVQry + " left join cvmicroprod.dbo.business_location bl on
(e.business_id = bl.business_id and e.location_no = bl.location_no)"
CVQry
= CVQry + " left join cvmicroprod.dbo.service_entitlement se on
e.equip_no = se.equip_no and se.serv_type = '04' and se.end_date >=
getutcdate() "
CVQry = CVQry + " left join
cvmicroprod.dbo.service_entitlement se2 on e.equip_no = se2.equip_no and
se2.serv_type = '06' "
CVQry = CVQry + " left join
cvmicroprod.dbo.contract_line cl on e.equip_no = cl.equip_no and
cl.end_date >= getutcdate() and cl.cancel_date is null "
CVQry = CVQry + " left join cvmicroprod.dbo.contract c on cl.cont_no = c.cont_no "
CVQry = CVQry + " left join cvmicroprod.dbo.employee emp on cl.technician_id = emp.emp_id  "
CVQry = CVQry + " left join cvmicroprod.dbo.contact cp on e.contact_no = cp.contact_no  "
CVQry
= CVQry + " where e.remove_date is null and  e.equip_sts not in
('TBDEL', 'DEINST') and i.item_no <> 'META' and i.item_group not
like '%e'  "
CVQry = CVQry + " and (c.cont_sts <> 'RQtAccp' or c.cont_sts is null) "
If Region = "All"      Then
Else
CVQry = CVQry +  "  and bl.service_branch_code like '"+Region+"' "
End If
If ProdL = "All"      Then
Else
CVQry = CVQry +  "  and i.item_group in ('"+ProdL+"') "
End If
CVQry = CVQry + " group by i.item_no, i.description, i.item_group,e.serial_no, e.equip_code,  "
CVQry = CVQry + "       e.equip_sts,e.install_date, e.remove_date,bl.location_name,cl.cont_no, cl.start_date,  "
CVQry = CVQry + "       cl.end_date,c.cont_sts,emp.last_name, emp.first_name, bl.service_branch_code, "
CVQry = CVQry + "       cp.first_name, cp.last_name, cp.phone,bl.addr1, bl.city, bl.state_code, bl.postal_code "
CVQry = CVQry + " order by i.item_group, i.item_no, e.serial_no "

qry.SQL = CVQry

Call RTFData.AppendText("[<TABLE BORDER=1 CELLSPACING=0  CELLPADDING=2  width = '100%'  BGCOLOR=F5F5F5>")
Call RTFData.AppendText("<TR>")
Call RTFData.AppendText(TDefH + "Item No"                         + TDefHX)
Call RTFData.AppendText(TDefH + "Description!"             + TDefHX)
Call RTFData.AppendText(TDefH + "PL"                               + TDefHX)
Call RTFData.AppendText(TDefH + "Serial #"                   + TDefHX)
Call RTFData.AppendText(TDefH + "Zeiss ID"                   + TDefHX)
Call RTFData.AppendText(TDefH + "Status"                         + TDefHX)
Call RTFData.AppendText(TDefH + "Install Date"             + TDefHX)
Call RTFData.AppendText(TDefH + "Last Service Dt"             + TDefHX)
Call RTFData.AppendText(TDefP + "Customer Name"       + TDefPX)
Call RTFData.AppendText(TDefP + "Contact Name"       + TDefPX)
Call RTFData.AppendText(TDefP + "Phone #                  "       + TDefPX)
Call RTFData.AppendText(TDefH + "Under Warranty?"       + TDefHX)
Call RTFData.AppendText(TDefH + "Under Contract?"       + TDefHX)
Call RTFData.AppendText(TDefH + "Cont Status"             + TDefHX)
Call RTFData.AppendText(TDefH + "FSR"                         + TDefHX)
Call RTFData.AppendText(TDefH + "Customer Branch"       + TDefHX)
Call RTFData.AppendText(TDefH + "Address"       + TDefHX)
Call RTFData.AppendText(TDefH + "City"       + TDefHX)
Call RTFData.AppendText(TDefH + "State Code"       + TDefHX)
Call RTFData.AppendText(TDefH + "Zip Code"       + TDefHX)
Call RTFData.AppendText("</TR>")

result.Execute

Call RTFData.AppendText("<TR>")
Do
result.NextRow

If  CStr(result.GetValue(1)) <> "" Then Call RTFData.AppendText(TDef & result.GetValue(1) &  TDefX)

If  CStr(result.GetValue(2)) <> "" Then Call
RTFData.AppendText(TDef & result.GetValue(2) &  TDefX)

If  CStr(result.GetValue(3)) <> "" Then Call RTFData.AppendText(TDef & result.GetValue(3) & TDefX)
If  CStr(result.GetValue(4)) <> "" Then Call RTFData.AppendText(TDef & result.GetValue(4) &  TDefX)

If  CStr(result.GetValue(5)) <> "" Then Call
RTFData.AppendText(TDef & result.GetValue(5) &  TDefX) Else Call
RTFData.AppendText(TDef & " - " & TDefX)
If  CStr(result.GetValue(6)) <> "" Then Call RTFData.AppendText(TDef & result.GetValue(6) & TDefX)

If  CStr(result.GetValue(7)) <> "12:00:00 AM" Then Call
RTFData.AppendText(TDef & Format(result.GetValue(7),"Short Date")
& TDefX) Else Call RTFData.AppendText(TDef & " - " & TDefX)

If  CStr(result.GetValue(8)) <> "12:00:00 AM" Then Call
RTFData.AppendText(TDef & Format(result.GetValue(8),"Short Date")
& TDefX) Else Call RTFData.AppendText(TDef & " - " & TDefX)

If  CStr(result.GetValue(9)) <> "" Then Call
RTFData.AppendText(TDef & result.GetValue(9) & TDefX) Else Call
RTFData.AppendText(TDef & " - " & TDefX)
If
CStr(result.GetValue(19)) <> "" Then Call RTFData.AppendText(TDef
& result.GetValue(19) & " " & result.GetValue(20)  &
TDefX)       Else Call RTFData.AppendText(TDef & " - " & TDefX)

If  CStr(result.GetValue(21)) <> "" Then Call
RTFData.AppendText(TDef & result.GetValue(21) &  TDefX)
Else Call RTFData.AppendText(TDef & " - " & TDefX)

If  CStr(result.GetValue(10)) <> "12:00:00 AM" Then Call
RTFData.AppendText(TDef & Format(result.GetValue(11),"Short Date")
& " - " &  Format(result.GetValue(10),"Short Date") & TDefX)
Else Call RTFData.AppendText(TDef & " no " & TDefX)

If  CStr(result.GetValue(13)) <> "12:00:00 AM" Then Call
RTFData.AppendText(TDef & result.GetValue(13) & " - " &
result.GetValue(14) &  TDefX)             Else Call
RTFData.AppendText(TDef & " no " & TDefX)
If
CStr(result.GetValue(15)) <> "" Then Call RTFData.AppendText(TDef
& result.GetValue(15) &  TDefX) Else Call
RTFData.AppendText(TDef & " - " & TDefX)
If
CStr(result.GetValue(16)) <> "" Then Call RTFData.AppendText(TDef
& result.GetValue(16) & ", " & result.GetValue(17) &
TDefX) Else Call RTFData.AppendText(TDef & " - " & TDefX)

If  CStr(result.GetValue(18)) <> "" Then Call
RTFData.AppendText(TDef & result.GetValue(18) &  TDefX)

If  CStr(result.GetValue(22)) <> "" Then Call
RTFData.AppendText(TDef & result.GetValue(22) &  TDefX) Else
Call RTFData.AppendText(TDef & " - " & TDefX)
If
CStr(result.GetValue(23)) <> "" Then Call
RTFData.AppendText(TDef & result.GetValue(23) &  TDefX) Else
Call RTFData.AppendText(TDef & " - " & TDefX)
If
CStr(result.GetValue(24)) <> "" Then Call
RTFData.AppendText(TDef & result.GetValue(24) &  TDefX) Else
Call RTFData.AppendText(TDef & " - " & TDefX)
If
CStr(result.GetValue(25)) <> "" Then Call
RTFData.AppendText(TDef & result.GetValue(25) &  TDefX) Else
Call RTFData.AppendText(TDef & " - " & TDefX)

Call RTFData.AppendText("</tr>")
Loop Until result.IsEndOfData ' 5412 result set 3/3 3:09

writeHeaderHtml:

Call RTFData.AppendText("[<TABLE BORDER=1 CELLSPACING=0  CELLPADDING=2  width = '100%'  BGCOLOR=F5F5F5>")
Call RTFData.AppendText("<TR>")

Call RTFData.AppendText(TDefH + "Item No. "             + TDefHX)
Call RTFData.AppendText(TDefH + "Description"       + TDefHX)
Call RTFData.AppendText(TDefH + "PL"                         + TDefHX)
Call RTFData.AppendText(TDefH + "Serial #"             + TDefHX)
Call RTFData.AppendText(TDefH + "Zeiss ID"                         + TDefHX)
Call RTFData.AppendText(TDefH + "Status"                         + TDefHX)

Call RTFData.AppendText(TDefH + "Install Date"             + TDefHX)
Call RTFData.AppendText(TDefH + "Last Service Dt"             + TDefHX)

Call RTFData.AppendText(TDefH + "Customer Name"             + TDefHX)
Call RTFData.AppendText(TDefH + "Contact Name"             + TDefHX)
Call RTFData.AppendText(TDefH + "Phone #"             + TDefHX)

Call RTFData.AppendText(TDefH + "Under Warranty"       + TDefHX)
Call RTFData.AppendText(TDefH + "Under Contract"       + TDefHX)

Call RTFData.AppendText(TDefH + "Cont Status"       + TDefHX)

Call RTFData.AppendText(TDefH + "FSR " + TDefHX)

Call RTFData.AppendText(TDefH + "Customer Branch" + TDefHX)
Call RTFData.AppendText(TDefH + "Address"             + TDefHX)
Call RTFData.AppendText(TDefH + "City"             + TDefHX)
Call RTFData.AppendText(TDefH + "State Code"             + TDefHX)
Call RTFData.AppendText(TDefH + "Zip Code"             + TDefHX)

Call RTFData.AppendText(TDefH + "Inv GL"                               + TDefHX)
Call RTFData.AppendText(TDefH + "COGS"                               + TDefHX)
Call RTFData.AppendText(TDefH + "Svc PC"                         + TDefHX)
Call RTFData.AppendText(TDefH + "Sales PC"                         + TDefHX)
Call RTFData.AppendText(TDefH + "Item Type"                   + TDefHX)
Call RTFData.AppendText("</TR>")
Call RTFData.AppendText("<TR>")

Return

FormatTableResults:
For i = 1 To ReturnedRowCounter
For j% = 1 To 25
Call RTFData.AppendText(TDef & ResultArray(i , j%) & TDefX)
Next
Call RTFData.AppendText("</tr>")
Next

doc.status = doc.status(0) + Chr(13) + "Status:  Rows displayed starting at " + CStr (RowStart&) + " ...for...  " _
+ CStr(ReturnedRowCounter ) + " rows."
doc.errorMsg = doc.errorMsg(0) + Chr(13)
doc.RemainingQueries =  "Queries left = "+ CStr (maxQueryRetry%)
Return

appendDash:
Call RTFData.AppendText(TDef & " - " & TDefX)
Return
ExitProgram:
Call doc.save(True,True)
End sub

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Stiletto
    I haven't walked through every line of your code, but it seems to me that providing some more information would be helpful.  For example, how much data are we talking about for the result set?  10000 records or 19 records?  How can you tell something is missing, the result count?  Is the result count always the same?  Have you tried inserting some debugging code to see where the breakdown is happening?  Is there something common with the missing data?  If the result sets are small, can you give an example of what you should get and what you actually get?
    3,950 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following