NetXMS Support Forum

I'm trying to get a Windows remote agent to read an ODBC sourced database (a CSV file), that contains a single row of data. I've added the ODBC nsm and configure as per the wiki the agent with:

Query = Name:TempSensorTemp DSN:TempSensor Query:"select 'Temperature Value' from 'database.csv'"

Whenever I try to create a DCI as ODBC.QueryResult(TempSensorTemp) I get an error that it's unsupported by the agent.

The agent log shows:

[01-Jul-2013 18:19:53.460] [session:0] Requesting parameter "ODBC.QueryResult(TempSensorTemp)"
[01-Jul-2013 18:19:53.805] [session:0] GetParameterValue(): result is 404 (UNKNOWN_PARAMETER)

Can anyone tell me where my config is wrong? I have a similar ODBC call from another app to the same database and my query does work.

Hi!

Looks like documentation page is misleading. You supposed to replace "Name", "DSN", etc. with actual values. Your configuration record should be

Query = TempSensorTemp:TempSensor:select 'Temperature Value' from 'database.csv':60

(60 at the end means poll every 60 seconds).

Best regards,
Victor

Quote from: Victor Kirhenshtein on July 01, 2013, 11:40:48 PM
Hi!

Looks like documentation page is misleading. You supposed to replace "Name", "DSN", etc. with actual values. Your configuration record should be

Query = TempSensorTemp:TempSensor:select 'Temperature Value' from 'database.csv':60

(60 at the end means poll every 60 seconds).

Best regards,
Victor

Thanks, but still not working. My exact lines are now:

SubAgent = odbcquery.nsm
Query = TempSensorTemp:TempSensor:select 'Temperature Value' from 'Draycott Corp.csv':60

and the debug log on the agent machine still shows:

[02-Jul-2013 18:06:39.590] [session:0] Received message CMD_GET_PARAMETER
[02-Jul-2013 18:06:39.865] [session:0] Requesting parameter "ODBC.QueryResult(TempSensorTemp)"
[02-Jul-2013 18:06:40.434] [session:0] GetParameterValue(): result is 404 (UNKNOWN_PARAMETER)
[02-Jul-2013 18:06:40.994] [session:0] Sending message CMD_REQUEST_COMPLETED (size 32)

or if I put quotes on the query:

[02-Jul-2013 18:06:39.590] [session:0] Received message CMD_GET_PARAMETER
[02-Jul-2013 18:06:39.865] [session:0] Requesting parameter "ODBC.QueryResult("TempSensorTemp")"
[02-Jul-2013 18:06:40.434] [session:0] GetParameterValue(): result is 404 (UNKNOWN_PARAMETER)
[02-Jul-2013 18:06:40.994] [session:0] Sending message CMD_REQUEST_COMPLETED (size 32)

Hi!

Don't forget to put query parameter into correct section, ODBC, like this:


SubAgent = odbcquery.nsm

*ODBC
Query = TempSensorTemp:TempSensor:select 'Temperature Value' from 'Draycott Corp.csv':60


Best regards,
Victor

Quote from: Victor Kirhenshtein on July 02, 2013, 11:59:07 PM
Hi!

Don't forget to put query parameter into correct section, ODBC, like this:

Code Select Expand


SubAgent = odbcquery.nsm

*ODBC
Query = TempSensorTemp:TempSensor:select 'Temperature Value' from 'Draycott Corp.csv':60


Best regards,
Victor

Ah, can't believe I missed that, thanks again. Unfortunately I still have an issue in that data I receive is not correct - no errors now in the debug logging.

I've left it running for a bit and exported the values, here's a small sample:

"03.07.2013 10:21:43","??8"
"03.07.2013 10:20:43","??1"
"03.07.2013 10:19:43","??1"
"03.07.2013 10:18:43","??5"
"03.07.2013 10:17:43","??5"
"03.07.2013 10:16:43","??5"
"03.07.2013 10:15:43","??9"
"03.07.2013 10:14:43","??9"
"03.07.2013 10:13:43","??3"

On the console, the ?? appear as Chinese characters. I have tried setting the value as both string and floating-point (which is what the value is) and it's the same.

I wanted to add, I just used Microsoft's ODBC test tool as I wanted to be sure this wasn't a 32bit/64bit ODBC issue - my other working connection is 32bit.

I used the tool to confirm both DSNs and it all seems well:

Connect to DSN

   Full Connect(Default)

   Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3

   Successfully connected to DSN 'TempSensor'.

select "Temperature Value" from "Draycott Corp.csv"

SQLExecDirect:
            In:            Statementhandle = 0x0000000000177FB0, StatementText = "select "Temperature Value" from "Draycott Corp.csv...", Statementlength = 51
            Return:   SQL_SUCCESS=0

Get Data All:
"Temperature Value"
19.94
1 row fetched from 1 column.

Yes, just found a bug in ODBCQUERY subagent. Are you using 32 or 64 bit version? I'll post patched subagent here.

Best regards,
Victor

Quote from: Victor Kirhenshtein on July 03, 2013, 12:57:21 PM
Yes, just found a bug in ODBCQUERY subagent. Are you using 32 or 64 bit version? I'll post patched subagent here.

Best regards,
Victor

64bit on both server and agent

Try to replace odbcquery.nsm with attached one. Should work!

Best regards,
Victor

Quote from: Victor Kirhenshtein on July 03, 2013, 07:33:55 PM
Try to replace odbcquery.nsm with attached one. Should work!

Best regards,
Victor

Perfect, it's all working now. Many thanks.

It's working for this CSV DSN and also a SQLite one I'm also testing.