Using ODBC on remote agent to test table value

[Taomyn]

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.

[victor]

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

[Taomyn]

@victor said in Using ODBC on remote agent to test table value:

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)

[victor]

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

[Taomyn]

@victor said in Using ODBC on remote agent to test table value:

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

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.

[victor]

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

[Taomyn]

@victor said in Using ODBC on remote agent to test table value:

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

[victor]

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

Best regards,
Victor

---

odbcquery.nsm

[Taomyn]

@victor said in Using ODBC on remote agent to test table value:

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.