Hi All !
I'm implementing a Logstash solution to fetch events from MSSQL database with the relevant data present:
In a fixed set of well known database tables with fixed columns;
BUT also have the most important data in a dynamic number of additional tables - that will be created in the DB at any time - and those tables will also have a dynamic number of columns (some of them are fixed and known, the rest can vary in number and name);
The set of well known database tables with fixed fields don't present a challenge in Logstash - I have several JDBC Inputs present to deal with each table, each one with a simple SQL
statement defined and numeric column tracking, and works like a charm as expected.
The challenge presents itself with the dynamic tables, both in number and in number of columns in them.
I already have a block of SQL code that works very well on generating data from those dynamic tables, but I'm unable to use it with a JDBC Input using a
statement_path configuration, also with numeric column tracking.
It seems that the SQL code is too complex to use together with
statement_path, it simply generates a JDBC Exception and no data is fetch...
Also, it seems that the
:sql_last_value is not being passed as numeric but as a timestamp, which is also strange...
Am I missing something?
How this challenge can be solved?
The SQL code is the following (for MSSQL):
DECLARE @TableName nvarchar(1024) DECLARE @DynSQL nvarchar(MAX) DECLARE cursor1 CURSOR FOR select name from sys.tables where name like 'tablename_pattern_%' Set @DynSQL = '' OPEN cursor1 FETCH NEXT FROM cursor1 INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN Set @DynSQL = @DynSQL + 'SELECT * FROM ' + @TableName FETCH NEXT FROM cursor1 INTO @TableName If @@FETCH_STATUS = 0 BEGIN Set @DynSQL = @DynSQL + ' WHERE [id] > :sql_last_value ; ' END END CLOSE cursor1 DEALLOCATE cursor1 EXEC sp_executesql @DynSQL