# Is it possible to overwrite the select statement each time when db is called

**URL:** <https://discuss.elastic.co/t/is-it-possible-to-overwrite-the-select-statement-each-time-when-db-is-called/139086>\
**Category:** Logstash\
**Created:** [July 9, 2018, 6:12am UTC](https://discuss.elastic.co/t/is-it-possible-to-overwrite-the-select-statement-each-time-when-db-is-called/139086 "2018-07-09T06:12:51Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![Gokul\_15](https://avatars.discourse-cdn.com/v4/letter/g/5f9b8f/32.png) [@Gokul\_15](https://discuss.elastic.co/u/Gokul_15)\
**Post date:** [July 9, 2018, 6:12am UTC](https://discuss.elastic.co/t/is-it-possible-to-overwrite-the-select-statement-each-time-when-db-is-called/139086/1 "2018-07-09T06:12:51Z")

</div>

i have two dates, eg :(from)2018/01/01 to now ..i have to fetch the records between these two dates and after fetching from will be replaced with now timing and now with new timestamp..how can v do these??  
any body??any suggestion??

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [July 9, 2018, 6:17am UTC](https://discuss.elastic.co/t/is-it-possible-to-overwrite-the-select-statement-each-time-when-db-is-called/139086/2 "2018-07-09T06:17:50Z")

</div>

Can you not just have two where clauses: one that selects everything newer than the specified cutoff date and one that is based on [sql\_last\_value](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#_state) functionality? The first run there would be no `sql_last_value` and all rows would be selected. After that only new data based on the `sql_last_value` could be selected.

---

<div class="post-metadata">

**Author:** ![Gokul\_15](https://avatars.discourse-cdn.com/v4/letter/g/5f9b8f/32.png) [@Gokul\_15](https://discuss.elastic.co/u/Gokul_15)\
**Post date:** [July 9, 2018, 6:23am UTC](https://discuss.elastic.co/t/is-it-possible-to-overwrite-the-select-statement-each-time-when-db-is-called/139086/3 "2018-07-09T06:23:07Z")

</div>

i dont want new updated data ,,i want to find data between two dates,,  
eg:"SELECT \* from contacts where date between "2018-01-01 11:11:19.754371" and CURRENT\_TIMESTAMP "  
after executing these i have to store the current timestamp in log file and execute the query like this  
"SELECT \* from contacts where date between "old current timestamp" and new CURRENT\_TIMESTAMP " alternatively

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [July 9, 2018, 6:28am UTC](https://discuss.elastic.co/t/is-it-possible-to-overwrite-the-select-statement-each-time-when-db-is-called/139086/4 "2018-07-09T06:28:48Z")

</div>

The `sql_last_value` for each run records the last value processed internally and then uses this for the next query. It should behave like you are describing.

---

<div class="post-metadata">

**Author:** ![Gokul\_15](https://avatars.discourse-cdn.com/v4/letter/g/5f9b8f/32.png) [@Gokul\_15](https://discuss.elastic.co/u/Gokul_15)\
**Post date:** [July 9, 2018, 6:29am UTC](https://discuss.elastic.co/t/is-it-possible-to-overwrite-the-select-statement-each-time-when-db-is-called/139086/5 "2018-07-09T06:29:34Z")

</div>

let me try...thank you for ur reply

---

<div class="post-metadata">

**Author:** ![Gokul\_15](https://avatars.discourse-cdn.com/v4/letter/g/5f9b8f/32.png) [@Gokul\_15](https://discuss.elastic.co/u/Gokul_15)\
**Post date:** [July 9, 2018, 7:12am UTC](https://discuss.elastic.co/t/is-it-possible-to-overwrite-the-select-statement-each-time-when-db-is-called/139086/6 "2018-07-09T07:12:10Z")

</div>

is it possible to get the records from last run time to now  
eg:"SELECT \* from contacts where date between 'last\_run\_time' and CURRENT\_TIMESTAMP "  
sql\_last\_value wont fit for this query ,,think so  
Exception when executing JDBC query {:exception=\>#\<Sequel::DatabaseError: Java::OrgPostgresqlUtil::PSQLException: ERROR: invalid input syntax for type timestamp: "sql\_last\_value"  
Position: 76\>}

---

<div class="post-metadata">

**Author:** ![Gokul\_15](https://avatars.discourse-cdn.com/v4/letter/g/5f9b8f/32.png) [@Gokul\_15](https://discuss.elastic.co/u/Gokul_15)\
**Post date:** [July 9, 2018, 7:33am UTC](https://discuss.elastic.co/t/is-it-possible-to-overwrite-the-select-statement-each-time-when-db-is-called/139086/7 "2018-07-09T07:33:03Z")

</div>

@Christian_Dahlqvist any idea

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [July 9, 2018, 7:33am UTC](https://discuss.elastic.co/t/is-it-possible-to-overwrite-the-select-statement-each-time-when-db-is-called/139086/8 "2018-07-09T07:33:41Z")

</div>

What did your configuration look like when you got that error?

---

<div class="post-metadata">

**Author:** ![Gokul\_15](https://avatars.discourse-cdn.com/v4/letter/g/5f9b8f/32.png) [@Gokul\_15](https://discuss.elastic.co/u/Gokul_15)\
**Post date:** [July 9, 2018, 7:43am UTC](https://discuss.elastic.co/t/is-it-possible-to-overwrite-the-select-statement-each-time-when-db-is-called/139086/9 "2018-07-09T07:43:50Z")

</div>

> [@Christian\_Dahlqvist](#):
>
> What did your configuration look like when you got that error?

# file: lastrun\_update.config

input {  
jdbc {  
jdbc\_connection\_string =\> "jdbc:postgresql://localhost:5432/postgres"  
jdbc\_user =\> "postgres"  
jdbc\_password =\>"\*\*\*_"  
jdbc\_driver\_library =\> "C:\logstash-6.3.0\postgresql-42.2.2.jar"  
jdbc\_driver\_class =\> "org.postgresql.Driver"  
last\_run\_metadata\_path =\> 'C:\Users\Gokul.logstash\_jdbc\_last\_run'  
record\_last\_run =\> true  
jdbc\_paging\_enabled =\> "true"  
jdbc\_page\_size =\> "50000"  
schedule =\>'_ \* \* \* \*'

```
    statement => "SELECT * from contacts where date between '2018-01-01 11:11:19.754371' and CURRENT_TIMESTAMP "   
	  }
}

```

output{  
file{  
path=\>"C:\logstash-6.3.0\map.log"  
}  
}  
i want alternative date to be done eg:"SELECT \* from contacts where date between "old current timestamp" and new CURRENT\_TIMESTAMP "

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [July 9, 2018, 7:47am UTC](https://discuss.elastic.co/t/is-it-possible-to-overwrite-the-select-statement-each-time-when-db-is-called/139086/10 "2018-07-09T07:47:30Z")

</div>

Why not instead try `SELECT * from contacts where date > :sql_last_value` ?

The tracking column will be updated for each run so only more recent rows that the last run will be selected.

---

<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:** [August 6, 2018, 7:47am UTC](https://discuss.elastic.co/t/is-it-possible-to-overwrite-the-select-statement-each-time-when-db-is-called/139086/11 "2018-08-06T07:47:30Z")

</div>

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