# Multiple statements in logstash JDBC Input **URL:** \ **Category:** Logstash\ **Created:** [September 22, 2020, 5:36am UTC](https://discuss.elastic.co/t/multiple-statements-in-logstash-jdbc-input/249450 "2020-09-22T05:36:57Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![vikramaddagulla](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vikramaddagulla/32/139858_2.png) [@vikramaddagulla](https://discuss.elastic.co/u/vikramaddagulla)\ **Post date:** [September 22, 2020, 5:36am UTC](https://discuss.elastic.co/t/multiple-statements-in-logstash-jdbc-input/249450/1 "2020-09-22T05:36:58Z")
Hello, I have configured jdbc input plugin with 3 statements as below ( providing just an example ) ```auto input { jdbc { statement => "select from DATABASE1 where TRANSACTION_DATE > :sql_last_value" use_column_value => true tracking_column => "TRANSACTION_DATE" tracking_column_type => "timestamp" } jdbc { statement => "select from DATABASE1 where TRANSACTION_DATE > :sql_last_value" use_column_value => true tracking_column => "TRANSACTION_DATE" tracking_column_type => "timestamp" } jdbc { statement => "select from DATABASE2 where TRANSACTION_DATE > :sql_last_value" use_column_value => true tracking_column => "TRANSACTION_DATE" tracking_column_type => "timestamp" } } ``` The 1st two statements are from Database1 and the 3rd statement is from a different Database. The results for the 1st two statements are always correct. However the output for the third query seems to be wrong....We are getting data even from the past even though i have provided tracking column and looking for data only for the last 1 day. Is the above config correct ? Do I need to make any changes to run the sql statements on different databases ? Any suggestions ? ---
**Author:** ![Wolfram\_Haussig](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wolfram_haussig/32/70528_2.png) [@Wolfram\_Haussig](https://discuss.elastic.co/u/Wolfram_Haussig)\ **Post date:** [September 22, 2020, 12:32pm UTC](https://discuss.elastic.co/t/multiple-statements-in-logstash-jdbc-input/249450/2 "2020-09-22T12:32:35Z")
Hello, By default, Logstash JDBC input stores the last value in the path $HOME/.logstash\_jdbc\_last\_run - which is a simple text file. So the sql\_last\_value from the first SQL is stored and used by the second SQL and so on. I don't know why you only have problems with the last SQL but the solution is to set [last\_run\_metadata\_path](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#plugins-inputs-jdbc-last_run_metadata_path): - Create a directory like $HOME/.logstash\_jdbc\_last\_run - Configure last\_run\_metadata\_path for each SQL to point to a separate file within this directory Best regards Wolfram ---
**Author:** ![vikramaddagulla](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vikramaddagulla/32/139858_2.png) [@vikramaddagulla](https://discuss.elastic.co/u/vikramaddagulla)\ **Post date:** [September 22, 2020, 2:13pm UTC](https://discuss.elastic.co/t/multiple-statements-in-logstash-jdbc-input/249450/3 "2020-09-22T14:13:22Z")
Hello, Yes you are correct....I have re-examined the output for the other SQL's as well and it looks like we the issue for all the SQL's. Below is the current output of jdbc last run : > cat .logstash\_jdbc\_last\_run > --- 1970-01-01 00:00:00.000000000 Z Below is my JDBC input > jdbc { > jdbc\_validate\_connection =\> true > jdbc\_connection\_string =\> "jdbc:oracle:thin:@//**_" > jdbc\_user =\> "_" > jdbc\_password =\> "**\*_" > jdbc\_driver\_library =\> "/elk/dependencies/ojdbc7.jar" > jdbc\_driver\_class =\> "Java::oracle.jdbc.driver.OracleDriver" > schedule =\> "_/20 \* \* \* \*" > statement =\> "select CALLNUMBER, STATUS, SOURCESYSTEM, DESCRIPTION, INSTANCE\_NUM, TRANSACTION\_DATE, CUSTOMERID, DESTINATIONSYSTEM, CUSTOMERPROBLEMNUMBER, CUSTEQUIPMENTNUMBER from TABLE\_NAME where status in ('SIEBELERROR','ERROR', 'EXHAUSTED') and TRANSACTION\_DATE \> trunc(sysdate) and TRANSACTION\_DATE \> :sql\_last\_value order by TRANSACTION\_DATE desc" > use\_column\_value =\> true > tracking\_column =\> "TRANSACTION\_DATE" > tracking\_column\_type =\> "timestamp" > type =\> "IN\_CTL" > } Could you please let me know what am I missing here and why is the .logstash\_jdbc\_last\_run set to 1970 which is the default value. The requirement is to get the output of only the recent data ( which got generated after the previous run of the query ) ---
**Author:** ![Wolfram\_Haussig](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wolfram_haussig/32/70528_2.png) [@Wolfram\_Haussig](https://discuss.elastic.co/u/Wolfram_Haussig)\ **Post date:** [September 23, 2020, 4:47am UTC](https://discuss.elastic.co/t/multiple-statements-in-logstash-jdbc-input/249450/4 "2020-09-23T04:47:37Z")
Hi, I think I know what the problem is as I fell over the same problem when I started using JDBC input: Your `tracking_column` is in uppercase but by default the JDBC input converts all column names to lowercase. Can you please try to either change the `tracking_column` to lowercase or try setting [lowercase\_column\_names](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#plugins-inputs-jdbc-lowercase_column_names) to false? Best regards Wolfram ---
**Author:** ![vikramaddagulla](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vikramaddagulla/32/139858_2.png) [@vikramaddagulla](https://discuss.elastic.co/u/vikramaddagulla)\ **Post date:** [September 23, 2020, 6:59am UTC](https://discuss.elastic.co/t/multiple-statements-in-logstash-jdbc-input/249450/5 "2020-09-23T06:59:05Z")
Thanks...Will try this and let you know... However, could you please let me know what difference does it make even if the tracking column is changed to lower case ?? ---
**Author:** ![Wolfram\_Haussig](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wolfram_haussig/32/70528_2.png) [@Wolfram\_Haussig](https://discuss.elastic.co/u/Wolfram_Haussig)\ **Post date:** [September 23, 2020, 7:20am UTC](https://discuss.elastic.co/t/multiple-statements-in-logstash-jdbc-input/249450/6 "2020-09-23T07:20:29Z")
The difference is that the SQL currently returns columns like `transaction_date`, `callnumber`, `status`, ... the jdbc input then gets the value of the column `TRANSACTION_DATE` which cannot be found as the column was converted to lowercase =\> therefore, JDBC input stores the default value of a date which is `1970-01-01 00:00:00.000000000 Z`. After converting the value of `tracking_column` to lowercase the JDBc input will correctly read the last value of `transaction_date` and store e.g. `2020-09-23 09:20:13.000000000 Z` ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [October 21, 2020, 7:20am UTC](https://discuss.elastic.co/t/multiple-statements-in-logstash-jdbc-input/249450/7 "2020-10-21T07:20:31Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.