I have this script on 20 different buttons in the project:
‘’‘results = system.db.runQuery( “SELECT * FROM MachineA” )
results = system.dataset.toDataSet(results)
spreadsheet = system.dataset.datasetToExcel( 1 , [results])
filePath = “C:\output\results.xls”
system. file .writeFile(filePath, spreadsheet)’’’

And so far I did not face any issue.
Trying to create some more buttons for next group of machines, I got “Java.Lang,OutOfMemoryError: Java heap space”.
Increasing the Client Initial/Maximum memory did not help me. Any ideas how to solve this issue?

You’re going to want to do this in batches then. I’m unsure how to integrate this with the datasetToExcel excatly, but basically write 5000 rows, query the next 5000 and then write those. Currently you are querying more rows from the database than you your clients can handle in doing this.

Alternatively, is this running on the client? This might be a situation where you want to have the Gateway run this script with a Message Handler if presumably your Gateway has a lot more resources allocated than any individual client (though again eventually you will run the risk of this table eventually becoming so big you run into the same issue).

Dear bkarabinchak, I see your point. Let me do some checks and I will revert ASAP.

1 Like
results = system.db.runQuery( “SELECT * FROM MachineA” )

99 times out of 100, you shouldn’t be doing this (unless the table is cleaning itself up to keep the row count reasonable and there’s very good reason why someone needs the same data exported repeatedly from the beginning of time.

Depending on how many rows are accumulated over time, the more date that needs to be stored from the query then written to a file will require more memory to accomplish.

Introducing a where clause will help alleviate. Using a date range component to set a start to end date is a more common approach.

startDate = event.source.parent.DateRange.startDate
endDate = event.source.parent.DateRange.endDate

results = system.db.runQuery( “SELECT * FROM MachineA where timestamp between ? and ?”, [startDate, endDate] )

#... do the rest

Hi, guys,

yes, it was my mistake - just I had left by mistake the execution schedule 1 s, so you can imagine how many rows I found accumulated. And, Stevenson.Yuan, thank you for your input, it makes perfect sense.

1 Like