# Confused about the behaviour of last\_run\_metadata\_path in JDBC input

**URL:** <https://discuss.elastic.co/t/confused-about-the-behaviour-of-last-run-metadata-path-in-jdbc-input/237669>\
**Category:** Logstash\
**Created:** [June 18, 2020, 3:32pm UTC](https://discuss.elastic.co/t/confused-about-the-behaviour-of-last-run-metadata-path-in-jdbc-input/237669 "2020-06-18T15:32:18Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![pk.241011](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pk.241011/32/86285_2.png) [@pk.241011](https://discuss.elastic.co/u/pk.241011)\
**Post date:** [June 18, 2020, 3:32pm UTC](https://discuss.elastic.co/t/confused-about-the-behaviour-of-last-run-metadata-path-in-jdbc-input/237669/1 "2020-06-18T15:32:18Z")

</div>

I am trying to get some data out of a SQL database.  
Here is the configuration:

```
input
{
        jdbc
        {
                jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
                jdbc_connection_string => ""
                jdbc_user => ""
                jdbc_password => ""

                statement => "select TOP 500 a.ResultID, a.UnitIdentifier, b.Result from MyDB.Res a inner join MyDb.ResData b on a.ResultID=b.ResultID where a.ResultID > :sql_last_value and a.Code like '9840%' and a.CreatedOn > '2020-01-01' order by a.ResultID"

                tracking_column => "ResultID"
                use_column_value => true
                tracking_column_type => "numeric"
                schedule => "*/1 * * * *"
                jdbc_paging_enabled => "true"
                jdbc_default_timezone => "Australia/Sydney"
                jdbc_page_size => "500"
                last_run_metadata_path => "/Drive/logstash/logs/logstash_jdbc_last_run"
        }
}

```

Funny thing about ResultID is that it is a negative number. Why I do not know. But it keeps increasing in subsequent records.

The contents of the `logstash_jdbc_last_run` remains zero after I run it on terminal.  
`--- 0`

In the logs I see  
`where a.ResultID > 0 ` in repeated runs. Should it not be the value of last run instead of 0?  
Logstash version is : 7.7.0

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [June 18, 2020, 5:20pm UTC](https://discuss.elastic.co/t/confused-about-the-behaviour-of-last-run-metadata-path-in-jdbc-input/237669/2 "2020-06-18T17:20:29Z")

</div>

If ResultID is negative then I would expect your SQL to test 'where a.ResultID \< :sql\_last\_value' (\< instead of \>). I believe 0 is the default initial value when you use a numeric tracking column.

---

<div class="post-metadata">

**Author:** ![pk.241011](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pk.241011/32/86285_2.png) [@pk.241011](https://discuss.elastic.co/u/pk.241011)\
**Post date:** [June 23, 2020, 8:00am UTC](https://discuss.elastic.co/t/confused-about-the-behaviour-of-last-run-metadata-path-in-jdbc-input/237669/3 "2020-06-23T08:00:27Z")

</div>

@Badger They started the numbering of the primary field from maximum negative so as to get maximum range.

The rough representation is:  
-11, -10, -9, -8, -7

If I keep a.ResultID \< :sql\_last\_value, then at end of first run, :sql\_last\_value will have value -7.  
Then the second run will also ingest the first 4 elements and :sql\_last\_value will have value -8.

Two things I have noticed:

1. `a.ResultID > :sql_last_value` leads to this in logs `where a.ResultID > 0`. It remains unchanged between the queries.

2. I tried to get around it by creating an alias.

This gives error:  
`Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::ComMicrosoftSqlserverJdbc::SQLServerException: Invalid column name 'resid'.`

The query runs fine in my Microsoft SQL Server Management Studio.

**EDIT 1**  
Just read through [this](https://stackoverflow.com/questions/13031013/how-do-i-use-alias-in-where-clause).  
Bottomline: column\_alias can be used in an ORDER BY clause, but it **cannot be used in a WHERE, GROUP BY, or HAVING clause**.

**EDIT 2**  
I am trying out this logic:  
statement =\> "select a.ResultID , a.UnitIdentifier, b.Result from MyDB.Res a inner join MyDb.ResData b on a.ResultID=b.ResultID where a.ResultID \< :sql\_last\_value and a.Code like '9840%' and a.CreatedOn \> '2020-01-01' order by a.ResultID DESC"

This means that the data is pulled in reverse chronological order:  
Dec, Nov, Oct, Sept......

This is fine by me. As long as the whole data comes in it is fine.  
But I am concerned if I have turned the logic of incremental pulling of future data from database on it head.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 21, 2020, 8:00am UTC](https://discuss.elastic.co/t/confused-about-the-behaviour-of-last-run-metadata-path-in-jdbc-input/237669/4 "2020-07-21T08:00:32Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.