troubleshooting Question

Overcoming memory limit when using SQL query with LotusScript

Avatar of notesrookie
notesrookieFlag for United States of America asked on
DB2Lotus IBM
6 Comments2 Solutions1792 ViewsLast Modified:
Greetings experts,

I am working in Domino 7.0.1 and attempting to get results of an SQL query from a DB2 database. I appear to be running into an upper limit for my results and I believe it has to do with the amount of memory that I have on my laptop. Currently the sample code below only returns 4368 rows. How can I get around this upper limit and actually retrieve all the rows that the SQL query returns? Thank you.

Uselsx "*LSXODBC"

Dim session As New NotesSession
Dim db As NotesDatabase
Dim doc As NotesDocument
Dim con As ODBCConnection
Dim qry As ODBCQuery
Dim result As ODBCResultSet
Dim dataSource As String
Dim userName As String
Dim password As String
Dim x As Long

Set db = session.CurrentDatabase
Set con = New ODBCConnection
Set qry = New ODBCQuery
Set result = New ODBCResultSet
      
dataSource = "ABCDEFG"
userName = "userid"
password = "xxxxxxxx"
If Not con.ConnectTo(dataSource, _
userName, password) Then
Messagebox "Could not connect to " & dataSource
Exit Sub
End If
      
Set qry.Connection = con
Set result.Query = qry
qry.SQL = "SELECT * FROM ORG2.OSINFONSAPR_TIVOLI;"
result.Execute
msg = "Hostnames:" & Chr(10)
Do
result.NextRow
Set doc = New NotesDocument(db)
With doc
.form = "fTest"
.test_hostname = result.GetValue("HOSTNAME", test_hostname)
.test_type = result.GetValue("HWTYPE", test_type)
.test_serial = Cstr(result.GetValue("HWSERIAL", test_serial))
.test_status = result.GetValue("STATUS", test_status)
.test_status_desc = result.GetValue("INACTIVE_DESCRIPTION", test_status_desc)
.test_classification = result.GetValue("CLASSIFICATION", test_classification)
.test_compliance = result.GetValue("COMPLIANCE", test_compliance)
.test_os = result.GetValue("OS", test_os)
.test_ipaddr = result.GetValue("IPADDRESS", test_ipaddr)
.test_bldg = result.GetValue("BUILDING", test_bldg)
.test_room = result.GetValue("LOCATION", test_room)
.test_nsa_scan_time = result.GetValue("NSA_SCAN_TIME", test_nsa_scan_time)
.test_nsa_results = result.GetValue("NSA_SCAN_RESULT", test_nsa_results)
.test_pr = result.GetValue("PR_NOTESADDRESS", test_pr)
.test_pr_internet = result.GetValue("PR_INTRANETADDRESS", test_pr_internet)
.test_division = result.GetValue("PR_DIVISION", test_division)
End With

x = x + 1
Print "Processed: " & Cstr(x)
Call doc.Save(True, True)
      
Loop Until result.IsEndOfData

result.Close(DB_CLOSE)
      
Print "Disconnecting from database..."
con.Disconnect
Print "Done"
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros