LUA script - capture database query errors

[sdale]

Hi, I'm currently working on a LUA script to do some custom database transactions, but running into some database errors. Example of the error when I run database.query(query_string): Mar 11 11:18:16:564 nsa: COM Error [0x80040e37] IDispatch error #3127 - [Microsoft OLE DB Provider for SQL Server] Invalid object name 'Logs_2013_03_111'. I know the database.open() function can accept error parameters, but I need something to capture errors when I run a database.query(). Any suggestions?

[Anon Anon]

How are you seeing the error message you included in the post? Is the NSA process aborting with that error?

[Leandro Zampieri]

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)

[sdale]

@Iza: That does not appear to work for me. The third variable is always nil for failed queries with error text.

[Anon Anon]

In my experience it's nil when the query goes through succesfully. What's the return code?

[Leandro Zampieri]

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.

[sdale]

---

jonhcw wrote:

In my experience it's nil when the query goes through succesfully. What's the return code?

---

nil as well

[Anon Anon]

NSA or NAS?

 

The descriptions of database.query() in the whitepapers are different.

[sdale]

---

keithk wrote:

NSA or NAS?

 

The descriptions of database.query() in the whitepapers are different.

---

NSA

[Anon Anon]

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)

...to this:

 

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.

[sdale]

Thanks, I just found that pcall function too.  I'm going to test that out and report back my findings.

[sdale]

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

 

Prints:

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

[sdale]

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[1] == nil then
   --restart database connection
   database.close()
   load_database()
   --retest, if failed, restart probe
   results = database.query(query)
   if results[1] == nil then
      restart ()
   end
end

[sdale]

I see it in the logs from the probe I wrote in LUA. The NSA is not aborting the probe after that error, just my data does not get inserted into the MS SQL tables.