Module im_odbc - how to reset value in "?"

#1 Firemind 7 years ago

Hi!

I have a problem using module im_odbc. I use sql query to receive some data from sql server. For example, query looks like "SELECT [Name] From [MyTable] WHERE [Id] > ?". In some moment, the "?" has stored incorrect value (I don't know how, but it's bigger then MAX(Id) value in my table) and sinse that moment, query has not return any data.

Please, help me reset value of "?" (nxlog service restart not help). And, maybe, you can give me some recommendations, how to avoid this problem in future?

UPD: I reset value of "?" by renaming input name in config file. The first query after input renaming was executed with "0" as value of "?" and return all rows from table. But next time query executed with "?"-value "161027120023", wich bigger than max value of "Id" column in my table (max value was 161027115813 at that time). What am i doing wrong?

<Input odbc1>
Module               im_odbc
ConnectionString   DSN=im_odbc_test;database=MyDB;
SQL                   SELECT [Name] From [MyTable] WHERE [Id] > ?;
PollInterval       120
SavePos               TRUE
IdIsTimestamp       FALSE
</Input>

Permalink

#2 b0ti Nxlog ✓ 7 years ago

#1 Firemind

Hi! I have a problem using module im_odbc. I use sql query to receive some data from sql server. For example, query looks like "SELECT [Name] From [MyTable] WHERE [Id] > ?". In some moment, the "?" has stored incorrect value (I don't know how, but it's bigger then MAX(Id) value in my table) and sinse that moment, query has not return any data. Please, help me reset value of "?" (nxlog service restart not help). And, maybe, you can give me some recommendations, how to avoid this problem in future? UPD: I reset value of "?" by renaming input name in config file. The first query after input renaming was executed with "0" as value of "?" and return all rows from table. But next time query executed with "?"-value "161027120023", wich bigger than max value of "Id" column in my table (max value was 161027115813 at that time). What am i doing wrong? <Input odbc1> Module im_odbc ConnectionString DSN=im_odbc_test;database=MyDB; SQL SELECT [Name] From [MyTable] WHERE [Id] > ?; PollInterval 120 SavePos TRUE IdIsTimestamp FALSE </Input>

You can reset it by deleting the configcache.dat file. This is where it saves the last known position. Note that this is used to store similar data for other modules (e.g. im_file, im_msvistalog) also.

Regarding the value of ID it doesn't look it is an issue caused by signed vs unsigned. You should figure out how it gets 161027115813 when MAX(id) is only 161027120023. Perhaps records are also deleted from the table? Also you should make sure ID is an increasing value (i.e. serial) and not some random integer.