# Issue using Logstash JDBC Input Plugin with Multiple Dynamic Tables **URL:** \ **Category:** Logstash\ **Created:** [June 6, 2018, 2:49pm UTC](https://discuss.elastic.co/t/issue-using-logstash-jdbc-input-plugin-with-multiple-dynamic-tables/134832 "2018-06-06T14:49:24Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Joaquim\_Freitas](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/joaquim_freitas/32/20113_2.png) [@Joaquim\_Freitas](https://discuss.elastic.co/u/Joaquim_Freitas)\ **Post date:** [June 6, 2018, 2:49pm UTC](https://discuss.elastic.co/t/issue-using-logstash-jdbc-input-plugin-with-multiple-dynamic-tables/134832/1 "2018-06-06T14:49:25Z")
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 ``` ---
**Author:** ![Joaquim\_Freitas](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/joaquim_freitas/32/20113_2.png) [@Joaquim\_Freitas](https://discuss.elastic.co/u/Joaquim_Freitas)\ **Post date:** [June 6, 2018, 5:32pm UTC](https://discuss.elastic.co/t/issue-using-logstash-jdbc-input-plugin-with-multiple-dynamic-tables/134832/2 "2018-06-06T17:32:50Z")
Some advances... Created an Stored Procedure with the above SQL Code, but with `statement` using `EXECUTE`, I'm getting an JDBC exception: `[2018-06-06T18:23:01,008][ERROR][logstash.inputs.jdbc] Java::ComMicrosoftSqlserverJdbc::SQLServerException: Incorrect syntax near the keyword 'EXECUTE'.: SELECT TOP (1) count(*) AS [COUNT] FROM (EXECUTE [dbo].[sp_FetchDynaTablesEntries] @TrackColumn = N'id', @LastValue = N'0') AS [T1]` ---
**Author:** ![Joaquim\_Freitas](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/joaquim_freitas/32/20113_2.png) [@Joaquim\_Freitas](https://discuss.elastic.co/u/Joaquim_Freitas)\ **Post date:** [June 7, 2018, 11:03am UTC](https://discuss.elastic.co/t/issue-using-logstash-jdbc-input-plugin-with-multiple-dynamic-tables/134832/3 "2018-06-07T11:03:46Z")
Some more advances... At it seems we have here a **Logstash BUG**! Created another file with the execution of the Stored Procedure: `EXECUTE [dbo].[sp_FetchDynaTablesEntries] @TrackColumn = N'id', @LastValue = N':sql_last_value'` and referenced it with `statement_filepath`, BUT I'm getting the same JDBC Exception: `[ERROR][logstash.inputs.jdbc] Java::ComMicrosoftSqlserverJdbc::SQLServerException: Incorrect syntax near the keyword 'EXECUTE'.: SELECT TOP (1) count(*) AS [COUNT] FROM (EXECUTE [dbo].[sp_FetchDynaTablesEntries] @TrackColumn = N'id', @LastValue = N'0') AS [T1]` **WHEN I USE** `--log.level=debug` ! If I use `--log.level=error` the statement is executed... So what gives? ---
**Author:** ![guyboertje](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guyboertje/32/31592_2.png) [@guyboertje](https://discuss.elastic.co/u/guyboertje)\ **Post date:** [June 7, 2018, 2:11pm UTC](https://discuss.elastic.co/t/issue-using-logstash-jdbc-input-plugin-with-multiple-dynamic-tables/134832/4 "2018-06-07T14:11:14Z")
This is a well known problem (to the LS developers at least). When set to debug logging, we log out a count of the records we are going to fetch. However, as we internally don't build the SQL, we get it from you and we don't have a universal SQL parser for all dialects, so we wrap your statement as a sub select to execute the count statement. ```auto SELECT TOP (1) count(*) AS [COUNT] FROM ( EXECUTE [dbo].[sp_FetchDynaTablesEntries] @TrackColumn = N'id', @LastValue = N'0' ) AS [T1] ``` The first and last lines ^ are the wrapper. Your statement is on line 2. Mostly this works quite well, but occasionally the statement will not parse. One alternative we are considering is to give you the option of supplying a `count_statement` setting - however you would have to ensure that the result was a single row with one column called `COUNT` or `count` having a integer datatype. ---
**Author:** ![Joaquim\_Freitas](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/joaquim_freitas/32/20113_2.png) [@Joaquim\_Freitas](https://discuss.elastic.co/u/Joaquim_Freitas)\ **Post date:** [June 7, 2018, 2:35pm UTC](https://discuss.elastic.co/t/issue-using-logstash-jdbc-input-plugin-with-multiple-dynamic-tables/134832/5 "2018-06-07T14:35:17Z")
Ok, I see... So without debug logging I would probably could use directly the MSSQL T-SQL Code with `statement_filepath`, without needing to make a Store Procedure, right? I'm having now one more issue... That code return several sets of results (one different for each table), but I'm only getting some of them (of some tables, others do not appear). What can be causing this? ---
**Author:** ![guyboertje](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guyboertje/32/31592_2.png) [@guyboertje](https://discuss.elastic.co/u/guyboertje)\ **Post date:** [June 7, 2018, 4:18pm UTC](https://discuss.elastic.co/t/issue-using-logstash-jdbc-input-plugin-with-multiple-dynamic-tables/134832/6 "2018-06-07T16:18:30Z")
> [@Joaquim\_Freitas](#): > > without needing to make a Store Procedure, right? Its worth a test and report back of your results - for others to read 🙂 > [@Joaquim\_Freitas](#): > > That code return several sets of results I can't recall anyone returning multiple result sets and the input plugin handling it properly. Maybe investigate multiple pipelines with a JDBC Input in each that loads from a specific Stored Procedure. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 5, 2018, 4:18pm UTC](https://discuss.elastic.co/t/issue-using-logstash-jdbc-input-plugin-with-multiple-dynamic-tables/134832/7 "2018-07-05T16:18:37Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.