Link to home
Start Free TrialLog in
Avatar of notesrookie
notesrookieFlag for United States of America

asked on

Overcoming memory limit when using SQL query with LotusScript

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"
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

hi
did you try to run the same query using some other tool ?
there is no way that because of lack of memory you don't get back all the rows
what happens if you issue this query
SELECT count(*) FROM ORG2.OSINFONSAPR_TIVOLI

what is the result you get ?
SOLUTION
Avatar of Sjef Bosman
Sjef Bosman
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of notesrookie

ASKER

Hi sjef,

I did mark this question for both Lotus Domino and DB2. Thanks.
All - After some research I was able to get past the 4K limit I appeared to have by using the .MaxRows property. So the code with the modification is below after result.Execute.

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
result.FetchBatchSize = 50
result.maxRows = 30000
result.CacheLimit =60
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"

Glad it's solved, if there are less than 30000 records. But I would assume that the default value for MaxRows is zero, in order to "fetch all rows subject to memory limits" (see Designer Help). Strange Notes behaviour... but good you solved the problem!

Now why didn't I think of looking up the ODBCResultSet object in the Help...
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial