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"
notesrookieAsked:
Who is Participating?
 
qwaleteeCommented:
Just an FYI...

IBM has deprecated LSXODBC, and the preferred engine is LSXLC.  There are many similarities, but they do work differently.  LSXLC is more flexible, and IBM has been keeping it fairly up to date.  LSXODBC has basically been static for over five years.
0
 
momi_sabagCommented:
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 ?
0
 
Sjef BosmanGroupware ConsultantCommented:
Maybe you can call the SELECT repeatedly, so you get a block of e.g. 1000 entries each time? You can do that using the LIMIT ... OFFSET clause, if that's allowed in DB2. See also
http://dev.mysql.com/doc/refman/4.1/en/select.html

This question had better be asked in the Lotus Notes area...
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
notesrookieAuthor Commented:
Hi sjef,

I did mark this question for both Lotus Domino and DB2. Thanks.
0
 
notesrookieAuthor Commented:
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"

0
 
Sjef BosmanGroupware ConsultantCommented:
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...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.