# JDBC streaming filter with SQL IN () condition

**URL:** <https://discuss.elastic.co/t/jdbc-streaming-filter-with-sql-in-condition/354429>\
**Category:** Logstash\
**Tags:** jdbc\
**Created:** [February 29, 2024, 10:41am UTC](https://discuss.elastic.co/t/jdbc-streaming-filter-with-sql-in-condition/354429 "2024-02-29T10:41:04Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Joss](https://avatars.discourse-cdn.com/v4/letter/j/49beb7/32.png) [@Joss](https://discuss.elastic.co/u/Joss)\
**Post date:** [February 29, 2024, 10:41am UTC](https://discuss.elastic.co/t/jdbc-streaming-filter-with-sql-in-condition/354429/1 "2024-02-29T10:41:04Z")

</div>

Greetings community,

I stuck with development of data processing pipeline where is one step is fetch data from DB by array of ids.

Prerequisites:

- logstash 7.17
- logstash-filter-jdbc\_streaming (OOTB)
- jdbc client for maria db 3.3.3

the event in logstash where I put the ids looks like this

```auto
{
    "ids" => [
           [0] "22",
           [1] "7",
           [2] "6"
    ],
    "@timestamp" => 2024-02-28T16:19:26.279Z,
    "str_ids" => "22,7,6",
    "tags" => [..]
}

```

Note! I added `str_ids` for experiments.

My filter config is:

```auto
filter {
    jdbc_streaming {
        jdbc_driver_library => "/usr/share/jdbc/mariadb-java-client-3.3.3.jar"
        jdbc_driver_class => "org.mariadb.jdbc.Driver"

        jdbc_validate_connection => true
        jdbc_validation_timeout => 300
        jdbc_connection_string => "jdbc:mariadb://database:3306/MY_DB?autoReconnect=true"
        jdbc_user => "user"
        jdbc_password => "password"

        statement => "
            select
                id,
                name,
                description
            from MY_TABLE
            where
                id in (:ids)
        "
        parameters => {
            ids => "[ids]"
        }
        target => "result"
    }
}

```

The problems found and actions I tried to self them:

1. this origin set up gives me an error

```auto
[WARN][logstash.pluginmixins.jdbcstreaming.normalstatementhandler][test][1346a67166141ca9d3ccad4459ff19a23511cbe24f256dbb5e99c81628523d36] Exception when executing JDBC query {:statement=>"..", :parameters=>{:ids=>["6", "7", "22"]}, :exception=>#<Sequel::DatabaseError: Java::JavaSql::SQLException: (conn=269) Illegal parameter data types int and row for operation '='>}

```

I have no idea why ids are sorted but any way.

1. then, I tried to do with `%{}`  
changed params definition to

```auto
        parameters => {
            ids => "%{[ids]}"
        }

```

..and it starts give me results.  
BUT! It applies only 1st id from list (in my case it is "7")

1. then, I tried to do it with string representation of list.  
my params looks like this

```auto
        parameters => {
            ids => "[str_ids]"
        }

```

the result is the same as previous - only 1st id is applied

1. I was pretty upset and decided to check it static query works well  
changed statement like this

```auto
        statement => "
            select
                id,
                name,
                description
            from MY_TABLE
            where
                id in (7,22,6)
        "

```

The result is what I expected - all ids were applied.

1. I have tried to use placeholder right in the statement instead of parameters option

```auto
        statement => "
            select
                id,
                name,
                description
            from MY_TABLE
            where
                id in (%{[str_ids]})
        "

```

buy it seems did not go via string processing so I get next error

```auto
{
:exception=>#<Sequel::DatabaseError: Java::JavaSql::SQLSyntaxErrorException: (conn=406) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%{[str_ids]
}

```

1. I tried 1) 2) 3) step but with prepared statement ON in hope it makes more in params processing  
so jdbc config looks like this

```auto
    use_prepared_statements => true
    prepared_statement_name => "my_main_stmt"
    statement => "
            select
                id,
                name,
                description
            from MY_TABLE
            where
                id in (?)
    "
    prepared_statement_bind_values => [
            "[ids]" // then "%{[ids]}", "[str_ids]" , "%{[str_ids]}"
        ]

```

the results:  
a)

```auto
{
    :exception=>java.sql.SQLException: Type org.jruby.RubyArray not supported type
}

```

b) success, only 1st id was applied  
c) success, only 1st id was applied  
d) success, only 1st id was applied

HOW TO DO IT WORKS!?  
Fom my point of view `IN (..)` condition is pretty regular so some where is missmatch between jdbc driver and jdbc streaming plugin.  
Link to source [logstash-filter-jdbc\_streaming/lib/logstash/plugin\_mixins/jdbc\_streaming/parameter\_handler.rb at 2ed23b51c0b9b1a57e39bf672f6f013eae88d5fc · logstash-plugins/logstash-filter-jdbc\_streaming · GitHub](https://github.com/logstash-plugins/logstash-filter-jdbc_streaming/blob/2ed23b51c0b9b1a57e39bf672f6f013eae88d5fc/lib/logstash/plugin_mixins/jdbc_streaming/parameter_handler.rb#L19)

---

<div class="post-metadata">

**Author:** ![Joss](https://avatars.discourse-cdn.com/v4/letter/j/49beb7/32.png) [@Joss](https://discuss.elastic.co/u/Joss)\
**Post date:** [March 12, 2024, 11:19am UTC](https://discuss.elastic.co/t/jdbc-streaming-filter-with-sql-in-condition/354429/2 "2024-03-12T11:19:03Z")

</div>

Refresh topic.

Is it really something that only me caught this issue? Can not believe..

---

<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:** [April 9, 2024, 11:19am UTC](https://discuss.elastic.co/t/jdbc-streaming-filter-with-sql-in-condition/354429/3 "2024-04-09T11:19:40Z")

</div>

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