# Jdbc\_static not looking up **URL:** \ **Category:** Logstash\ **Created:** [January 16, 2019, 11:57pm UTC](https://discuss.elastic.co/t/jdbc-static-not-looking-up/164553 "2019-01-16T23:57:37Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![michaeleino](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/michaeleino/32/17349_2.png) [@michaeleino](https://discuss.elastic.co/u/michaeleino)\ **Post date:** [January 16, 2019, 11:57pm UTC](https://discuss.elastic.co/t/jdbc-static-not-looking-up/164553/1 "2019-01-16T23:57:37Z")
after a long time, getting the plugin starts... it can't resolve the actual values from the derby DB! > ``` > local_lookups => [ > { > id => "get-status" > query => "SELECT name from localstatus WHERE id = :stID" > parameters => {stID => "[statusID]"} > target => "statusdata" > } > ] > > ``` I got this in the logstash logs: > [2019-01-16T23:54:23,503][DEBUG][logstash.filters.jdbc.lookup] Executing Jdbc query {:lookup\_id=\>"get-status", :statement=\>"SELECT name from localstatus WHERE id = :statusID", :parameters=\> **{:statusID=\>"1"}}** > [2019-01-16T23:54:23,504][WARN][logstash.filters.jdbc.lookup] Exception when executing Jdbc query {:lookup\_id=\>"get-status", :exception=\>"Java::JavaSql::SQLSyntaxErrorException: Comparisons between 'INTEGER' and 'CHAR (UCS\_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')", :backtrace=\>["org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(org/apache/derby/impl/jdbc/SQLExceptionFactory)", "org.apache.derby.impl.jdbc.Util.generateCsSQLException(org/apache/derby/impl/jdbc/Util)", "org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(org/apache/derby/impl/jdbc/TransactionResourceImpl)", "org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(org/apache/derby/impl/jdbc/TransactionResourceImpl)", "org.apache.derby.impl.jdbc.EmbedConnection.handleException(org/apache/derby/impl/jdbc/EmbedConnection)", "org.apache.derby.impl.jdbc.ConnectionChild.handleException(org/apache/derby/impl/jdbc/ConnectionChild)", "org.apache.derby.impl.jdbc.EmbedStatement.execute(org/apache/derby/impl/jdbc/EmbedStatement)", "org.apache.derby.impl.jdbc.EmbedStatement.executeQuery(org/apache/derby/impl/jdbc/EmbedStatement)"]} When I manually set the query statement to: > query =\> "SELECT name from localstatus WHERE id = 1" it returns what is should return! Here is the tables: > local\_db\_objects =\> [ > { > name =\> "localstatus" > index\_columns =\> ["id"] > columns =\> [ > ["id", "int"], > ["name", "varchar(255)"] > ] > } > ] > loaders =\> [{ > id =\> "get-status" > query =\> "SELECT id, name FROM status ORDER BY id" > local\_table =\> "localstatus" > } > ] logstash version: [docker.elastic.co/logstash/logstash:6.5.4](http://docker.elastic.co/logstash/logstash:6.5.4) with mysql driver: jdbc\_driver\_class =\> "com.mysql.jdbc.Driver" jdbc\_driver\_library =\> "/usr/share/logstash/config/jlib/mysql-connector-java-5.1.47.jar" ---
**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:** [January 17, 2019, 1:36am UTC](https://discuss.elastic.co/t/jdbc-static-not-looking-up/164553/2 "2019-01-17T01:36:23Z")
OK, so ``` query => "SELECT name from localstatus WHERE id = 1" ``` works. But ``` query => "SELECT name from localstatus WHERE id = :stID" parameters => {stID => "[statusID]"} ``` does not. The error message says ``` Comparisons between 'INTEGER' and 'CHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')" ``` The way I read it :stID is CHAR (UCS\_BASIC) and id is INTEGER. You want :stID to be an integer. The error message is telling you to fix that using a CAST. You are left with an SQL question, not a logstash question. ---
**Author:** ![michaeleino](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/michaeleino/32/17349_2.png) [@michaeleino](https://discuss.elastic.co/u/michaeleino)\ **Post date:** [January 20, 2019, 7:55am UTC](https://discuss.elastic.co/t/jdbc-static-not-looking-up/164553/3 "2019-01-20T07:55:28Z")
Thnks badger... I was just wondering which of them are not integer ! the event field, or the imported mysql DB.. However I'm getting the `statusID` as `statusID=%{NUMBER:statusID}` also tried to do it as `statusID=%{INT:statusID}` it didn't work, as i was supposing it should capture the event variable as INT ... adding below solved the issue... thanks a lot 🙂 > mutate { > convert =\> { > "serviceID" =\> "integer" > "statusID" =\> "integer" > } > } OR optionally, we can set it on the capture directly like: > %{INT:serviceID:int} To get a number field, see this paragraph in the grok filter docs: > Optionally you can add a data type conversion to your grok pattern. By default all semantics are saved as strings. If you wish to convert a semantic’s data type, for example change a string to an integer then suffix it with the target data type. For example %{NUMBER:num:int} which converts the num semantic from a string to an integer. Currently the only supported conversions are int and float. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [February 17, 2019, 7:55am UTC](https://discuss.elastic.co/t/jdbc-static-not-looking-up/164553/4 "2019-02-17T07:55:29Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.