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


(Gokul Kathirvel) #1

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??


Is it possible to get the records from last run time to now
(Christian Dahlqvist) #2

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 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.


(Gokul Kathirvel) #3

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


(Christian Dahlqvist) #4

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.


(Gokul Kathirvel) #5

let me try...thank you for ur reply


(Gokul Kathirvel) #6

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>}


(Gokul Kathirvel) #7

@Christian_Dahlqvist any idea


(Christian Dahlqvist) #8

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


(Gokul Kathirvel) #9

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 "


(Christian Dahlqvist) #10

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.


(system) #11

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