• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1672
  • Last Modified:

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"
0
notesrookie
Asked:
notesrookie
2 Solutions
 
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
 
notesrookieAuthor Commented:
Hi sjef,

I did mark this question for both Lotus Domino and DB2. Thanks.
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
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
 
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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now