IM_ODBC always executes same query
Hi i am trying to use the im_odbc module with nxlog-4.1.4016 running on a windows 2016 server. but it seems like i am in an loop. I looked at the documentation and took notice of the required id field, but the output is always the same ID, as if the position (savepos ) is not saved.
sqlserver : 14.0.2002.14
this is my minimalized input config
<Input database>
Module im_odbc
SavePos TRUE
ConnectionString DSN=dbserver;database=db;UID=sa;Pwd=password;
SQL select Id as id from table where id = ?
Exec $Level = "INFO";
</Input>
this is my output config
<Output test>
Module om_file
File "D:\_Data\log_null_output.log"
</Output>
this is the output of the file
2018-09-18 16:39:49 adss-dbserver INFO
id: 26335
2018-09-18 16:39:50 adss-dbserver INFO
id: 26335
2018-09-18 16:39:51 adss-dbserver INFO
id: 26335
2018-09-18 16:39:52 adss-dbserver INFO
id: 26335
debug logging nxlog
2018-09-18 17:26:14 DEBUG CONFIG: adss_sql
2018-09-18 17:26:14 DEBUG SQL: select Id as id from SigningTransactionLogs where id = ?
2018-09-18 17:26:14 DEBUG nx_expr_new_field: Level
2018-09-18 17:26:14 DEBUG field declared at line 9, character 12 in C:\Program Files\nxlog\conf\inputs\adss_sql.in
2018-09-18 17:26:14 DEBUG left_value expr
2018-09-18 17:26:14 DEBUG adding string [INFO]
2018-09-18 17:26:14 DEBUG string literal declared at line 9, character 21 in C:\Program Files\nxlog\conf\inputs\adss_sql.in
2018-09-18 17:26:14 DEBUG literal
2018-09-18 17:26:14 DEBUG assignment declared at line 9, character 22 in C:\Program Files\nxlog\conf\inputs\adss_sql.in
2018-09-18 17:26:14 DEBUG assignment: left_value = expr
2018-09-18 17:26:14 DEBUG statement: assignment
2018-09-18 17:26:14 DEBUG finished parsing statements
....
2018-09-18 17:26:16 DEBUG new event in event_thread [adss_sql:READ]
2018-09-18 17:26:16 DEBUG nx_event_to_jobqueue: READ (adss_sql)
2018-09-18 17:26:16 DEBUG event added to jobqueue
2018-09-18 17:26:16 DEBUG future event, event thread sleeping -697014233us in cond_timedwait
2018-09-18 17:26:16 DEBUG worker 0 got signal for new job
2018-09-18 17:26:16 DEBUG worker 0 processing event 0x164caf0
2018-09-18 17:26:16 DEBUG PROCESS_EVENT: READ (adss_sql)
2018-09-18 17:26:16 DEBUG odbc read
2018-09-18 17:26:16 DEBUG im_odbc_execute with last id 0
2018-09-18 17:26:16 DEBUG resultset column: id (bigint identity)
2018-09-18 17:26:16 DEBUG fetching from id 0
2018-09-18 17:26:16 DEBUG NO DATA
2018-09-18 17:26:16 DEBUG worker 0 waiting for new event
2018-09-18 17:26:16 DEBUG new event in event_thread [_fileop:SCHEDULE]
2018-09-18 17:26:16 DEBUG new event in event_thread [_fileop:SCHEDULE]
2018-09-18 17:26:16 DEBUG new event in event_thread [adss_sql:READ]
2018-09-18 17:26:16 DEBUG future event, event thread sleeping 1000000us in cond_timedwait
2018-09-18 17:26:17 DEBUG event thread wait time is up (after 1000000us)
2018-09-18 17:26:17 DEBUG new event in event_thread [_fileop:SCHEDULE]
2018-09-18 17:26:17 DEBUG new event in event_thread [_fileop:SCHEDULE]
2018-09-18 17:26:17 DEBUG new event in event_thread [adss_sql:READ]
2018-09-18 17:26:17 DEBUG nx_event_to_jobqueue: READ (adss_sql)
2018-09-18 17:26:17 DEBUG event added to jobqueue
2018-09-18 17:26:17 DEBUG future event, event thread sleeping -698029889us in cond_timedwait
2018-09-18 17:26:17 DEBUG worker 1 got signal for new job
2018-09-18 17:26:17 DEBUG worker 1 processing event 0x164c910
2018-09-18 17:26:17 DEBUG PROCESS_EVENT: READ (adss_sql)
2018-09-18 17:26:17 DEBUG odbc read
2018-09-18 17:26:17 DEBUG im_odbc_execute with last id 0
2018-09-18 17:26:17 DEBUG resultset column: id (bigint identity)
2018-09-18 17:26:17 DEBUG fetching from id 0
2018-09-18 17:26:17 DEBUG NO DATA
Your query is SQL select Id as id from table where id = ?
It's pretty obvious that it starts with 0 and will keep returning 0 for the id column since this is what you are telling it to do via where id = ?.
