How are you seeing the error message you included in the post? Is the NSA process aborting with that error?
If you call the query as "tab,rc,err = database.query(select...)" you will get:
tab: result table address
rc: query retcode
err: query error text (if any)
In my experience it's nil when the query goes through succesfully. What's the return code?
I a produced error with a typo in the query I get "err" as:
[Microsoft OLE DB Provider for SQL Server] Incorrect syntax near the keyword 'from'
I don't know in case the query itself is fine but other errors occur, if this is catched be the process.
jonhcw wrote:In my experience it's nil when the query goes through succesfully. What's the return code?
nil as well
NSA or NAS?
The descriptions of database.query() in the whitepapers are different.
keithk wrote:NSA or NAS? The descriptions of database.query() in the whitepapers are different.
Hopefully you are also using the latest version of the NSA, just in case the behavior of the database.query() function changed.
Anyone out there getting a return code and error message from the NSA? I suspect that only happens in the NAS.
I have used the pcall() function to safely call a function that was generating an error. I think the error was causing the NSA to terminate rather than just log an error, but I am not sure. It might help in your situation or maybe not. To use it, you would change this:
local result = database.query(query_string)
local status,result = pcall(database.query, query_string)
If all is well, status would be true and result would be the normal result of the database.query() function. If there is an error, status would be false, and result would be the error message.
Give it a try and see if that helps.
Thanks, I just found that pcall function too. I'm going to test that out and report back my findings.
Didn't work . Pcall doesn't capture the COMerror.
Here's a bad query,notice the 1 inside NimsoftSLM...
rb = database.open(connstr)
myInsert = "select top 10 * from Nims1oftSLM.dbo.NAS_TRANSACTION_SUMMARY"
local status,results = pcall(database.query, myInsert)
if status then print ("success")else print ("error")end
---------- Capture Output ----------> "C:\Program Files\Nimsoft\sdk\nsa\nsa.exe" -d 1 -l stdout "C:\Nimsoft Scripts\Logs_testing.lua"Mar 12 10:54:06:194 nsa: COM Error [0x80040e37] IDispatch error #3127 - [Microsoft OLE DB Provider for SQL Server] Invalid object name 'Nims1oftSLM.dbo.NAS_TRANSACTION_SUMMARY'.success
> Terminated with exit code 0.
well I could not find anything that would work to capture those database comm errors. So I wrote a dirty test to pull one record each time before an insert to make sure the database connection is alive. Not ideal or elegant, but it works to resolve my problem of the database connection erroring or timing out.
--check to see if database connection is active by querying for a record. If a record is returned than database connection is active
query = "select top 1 nimid from NimsoftSLM.dbo.NAS_TRANSACTION_SUMMARY"results = database.query(query)if results == nil then --restart database connection database.close() load_database() --retest, if failed, restart probe results = database.query(query) if results == nil then restart () endend