# Variables in SQL statements, running only after first statement

**URL:** <https://discuss.elastic.co/t/variables-in-sql-statements-running-only-after-first-statement/233893>\
**Category:** Logstash\
**Tags:** elastic-stack-sql\
**Created:** [May 22, 2020, 11:11am UTC](https://discuss.elastic.co/t/variables-in-sql-statements-running-only-after-first-statement/233893 "2020-05-22T11:11:49Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Alex\_Korn](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/alex_korn/32/68883_2.png) [@Alex\_Korn](https://discuss.elastic.co/u/Alex_Korn)\
**Post date:** [May 22, 2020, 11:11am UTC](https://discuss.elastic.co/t/variables-in-sql-statements-running-only-after-first-statement/233893/1 "2020-05-22T11:11:49Z")

</div>

Hello all  
I'm new to logstash, and at the moment I'm playing around with my sql-requests, the almost all of them are using temp variable. It  
I'm running sql-request like `select my_id from table1 where date > $start and date < $en`d  
$start and $end are timestamps which i'm setting before running this requests.

After executing a statement, I got a list with my id's and then I'm running new bunch of sql-requests like `select Max(message) from table2 where id_ba_fa = '$my_id'`. ($my\_id is variable which will be replaced with real my\_id that I've got from first request.

So first I'm getting list of id's from time range, then I need exact data from every id I've become.

It approach works fine in my old tool, but since I want to use logstash, I don't understand how to config the same statements. Any suggestion, help?

---

<div class="post-metadata">

**Author:** ![ptamba](https://avatars.discourse-cdn.com/v4/letter/p/7feea3/32.png) [@ptamba](https://discuss.elastic.co/u/ptamba)\
**Post date:** [May 22, 2020, 12:18pm UTC](https://discuss.elastic.co/t/variables-in-sql-statements-running-only-after-first-statement/233893/2 "2020-05-22T12:18:46Z")

</div>

1. for the variables, you can store them in environment variables then reference them in your jdbc config file. see [here](https://www.elastic.co/guide/en/logstash/current/environment-variables.html) for documentation about environment variables

2. for the second scenario , you will need two jdbc filter. say your first jdbc filter contains this statement

> [@Alex\_Korn](#):
>
> `select my_id from table1 where date > $start and date < $en` d

that filter will produce a field called my\_id

you can then call it with second jdbc filter

```
if [my_id] 
{
  jdbc { 
     #some jdbc settings 
      statement => “select Max(message) from table2 where id_ba_fa = %{my_id}”
  }
} 

```

use %{field\_name} to access the value of field\_name

---

<div class="post-metadata">

**Author:** ![Alex\_Korn](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/alex_korn/32/68883_2.png) [@Alex\_Korn](https://discuss.elastic.co/u/Alex_Korn)\
**Post date:** [May 22, 2020, 2:34pm UTC](https://discuss.elastic.co/t/variables-in-sql-statements-running-only-after-first-statement/233893/3 "2020-05-22T14:34:44Z")

</div>

> [@ptamba](#):
>
> for the variables, you can store them in environment variables then reference them in your jdbc config file. see [here](https://www.elastic.co/guide/en/logstash/current/environment-variables.html) for documentation about environment variables

Even for sql statements? Hm.

I'll take a look at filter and if-statement, thanks.  
Should I copy all jdbc settings, when I'm running any statement?

---

<div class="post-metadata">

**Author:** ![ptamba](https://avatars.discourse-cdn.com/v4/letter/p/7feea3/32.png) [@ptamba](https://discuss.elastic.co/u/ptamba)\
**Post date:** [May 22, 2020, 2:46pm UTC](https://discuss.elastic.co/t/variables-in-sql-statements-running-only-after-first-statement/233893/4 "2020-05-22T14:46:28Z")

</div>

yes, you can use env var anywhere in the config file. and yes, you need all required jdbc settings anytime you initiate a jdbc filter

---

<div class="post-metadata">

**Author:** ![Alex\_Korn](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/alex_korn/32/68883_2.png) [@Alex\_Korn](https://discuss.elastic.co/u/Alex_Korn)\
**Post date:** [May 27, 2020, 1:32pm UTC](https://discuss.elastic.co/t/variables-in-sql-statements-running-only-after-first-statement/233893/5 "2020-05-27T13:32:20Z")

</div>

Nah, can't figured out how the settings should be. I tried the filter you shared, with "if", but failed.  
When I'm putting `%{my_id}` into sql statement, I'm getting error SQLSyntaxErrorException, invalid character.

My settings in logstash are, and I'm totally sure it's wrong. Do I didn't add `%{my_id}`to global environment variable, btw? The idea was to use the my\_id value after running first statement, because if have no id, other statements are useless.

```
input {
	jdbc {
		some jdbc_settings
		statement => "select my_id from table1 where date >= (1588291200)"
	}

if [my_id] 
{	
	jdbc {
		some jdbc_settings
		statement => "select sum(message) from table2 where date >= (1588291200) and param1= 'value1' and param2 =%{request_id}"
	}	
}
} 
output {
	elasticsearch {
		hosts => "localhost:9200"
		index => "my_test"
	}
	stdout {
		codec => rubydebug
	}
}

```

I thought statement "if" should equals to something like `if [my_id] is not null` or similar 🤔

---

<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:** [May 27, 2020, 2:13pm UTC](https://discuss.elastic.co/t/variables-in-sql-statements-running-only-after-first-statement/233893/6 "2020-05-27T14:13:09Z")

</div>

You cannot reference a field of an event, like [my\_id], in the input section since there are no events when the inputs are being configured. Note that ptamba was suggesting the use of a filter, not a second input. If you want to use the value of my\_id in a query that would look something like

```
input {
	jdbc {
		some jdbc_settings
		statement => "select my_id from table1 where date >= (1588291200)"
	}
}
filter {
    if [my_id] {	
	    jdbc_streaming {
    		some jdbc_settings
            statement => "select sum(message) from table2 where date >= (1588291200) and param1= 'value1' and param2 = :request_id"
            parameters => { request_id => my_id }
    	}	
    }
}
```

---

<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:** [June 24, 2020, 2:13pm UTC](https://discuss.elastic.co/t/variables-in-sql-statements-running-only-after-first-statement/233893/7 "2020-06-24T14:13:16Z")

</div>

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