Database table doesn't have rising column totally?

[sieutruc]

Hello,

My table doesn't have column with an inreasing value totally. It has ID, but that ID changes such that it often increases and sometimes decrease (but its value is unique), so i think it's not appropriate to be a rising column.

There is another timestamp field,it often increases and sometimes keeps the same value when 2 events arrive at the same time.

So my question is how to monitor my table ? Is that timestamp field possible to be rising column ?

I need your help .

[ziegfried · Accepted solution]

Multiple entries with the same rising-column value should not be a problem. The only thing where this might becomes a problem is when one of the entries with the same value is inserted into the table after the other one has already been fetched by DB Connect. There is a workaround for this situation by specifying a custom SQL statement for the database input:

SELECT * FROM mytable WHERE $rising_column$ < sysdate-(1/1440) {{AND $rising_column$ > ?}} ORDER BY $rising_column$

This forces that entries with a current timestamp are not fetched immediately, but the one of the subsequent invocations of the database input, when the timestamp is at least one minute older than the current system time. The query syntax is database specific. The example here is for Oracle.

[sieutruc]

sorry for my mistake :), it's worked now , thanks for your help

[ziegfried]

You actually don't need to replace $rising_column$. It's automatically substituted with the column you specify as rising column in the input.

Your custom sql query should rather look something like:

SELECT * FROM mytable WHERE $rising_column$ < dateadd(minute,-1,getdate()) {{AND $rising_column$ > ?}} ORDER BY $rising_column$

Note that dateadd is used with -1 instead of 1, so entries are only fetched if they are at least one minute old.

[sieutruc]

My rising column is modifiedTime, do i need to replace $rising_column$ by modifiedTime to have

SELECT * FROM mytable WHERE modifiedTime < dateadd(minute,1,getdate()) {{AND modifiedTime > ?}} ORDER BY modifiedTime

?? (MSSQL)