Is it possible to use a varchar/string for jdbc sql_last_value?

Elastic Stack Logstash
1

I've already tested the sql_last_value with integer and datetime type of fields in my jdbc plugin and it's working perfectly.

I'm just curios, is there any possibility of using a varchar/string type of field, in order to update the sql_last_value time to time.

Any help could be appreciated.

2

I don't see why it wouldn't work. Have you tried?

1 Like
3

@magnusbaeck thanks for the reply.

I thought it wouldn't work since if i'm having a string how will I be able to do the below check for the last sql_last_value:

idString > :sql_last_value

It cannot check right unless it's an integer or a timestamp.? Am I wrong

4

Again, why are you assuming that the < and > operators don't work for strings? Have you tried?

Even if it doesn't work I'm sure there's some way of comparing strings in your SQL dialect. Your query doesn't have to have the form column_name > :sql_last_value; you could say UPPER(column_name) > UPPER(:sql_last_value) or whatever makes sense for you.

1 Like
5

Thanks again @magnusbaeck.

Now lets say i'm having a string type column called name and before running the logstash conf, sql_last_value is empty. I'm having values as text1, text2, text3.. under the column name.

So if I'm going to upload the db records through logstash to the index with some way of comparing strings in the SQL dialect, what if there're two records having the same string value for the name column ? Will sql_last_value compare two of them and assume it as a duplicate?

What does UPPER(column_name) mean in UPPER(column_name) > UPPER(:sql_last_value)?

> : in the SQL dialect means to check if the value is greater than the existing sql_last_value isn't it ? So how can it compare when it comes to a string?

6

So if I'm going to upload the db records through logstash to the index with some way of comparing strings in the SQL dialect, what if there're two records having the same string value for the name column ? Will sql_last_value compare two of them and assume it as a duplicate?

The number of rows with duplicate values is irrelevant. All rows where the column in question is greater than the saved sql_last_value string will be included.

What does UPPER(column_name) mean in UPPER(column_name) > UPPER(:sql_last_value)?

It was an example so it doesn't really matter, but I was thinking of MySQL where UPPER() returns the uppercase version of the argument. When comparing two uppercased strings you're effectively turning the normally case-sensitive comparison into a case-insentitive one.

> : in the SQL dialect means to check if the value is greater than the existing sql_last_value isn't it ?

Yes.

So how can it compare when it comes to a string?

Are you not understanding the concept of comparing strings (as opposed to comparing numbers), or what is unclear?

1 Like
7

yes that's right @magnusbaeck . That's where i'm pretty confused. :confused: