# Load table with jdbc\_static filter with NULL integers **URL:** \ **Category:** Logstash\ **Created:** [December 4, 2018, 3:27pm UTC](https://discuss.elastic.co/t/load-table-with-jdbc-static-filter-with-null-integers/159391 "2018-12-04T15:27:02Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![Micah\_Hunsberger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/micah_hunsberger/32/147436_2.png) [@Micah\_Hunsberger](https://discuss.elastic.co/u/Micah_Hunsberger)\ **Post date:** [December 4, 2018, 3:27pm UTC](https://discuss.elastic.co/t/load-table-with-jdbc-static-filter-with-null-integers/159391/1 "2018-12-04T15:27:02Z")
I'm trying to use the `jdbc_static` filter in my logstash pipeline, but I am getting the following error: ```auto [2018-12-04T10:07:38,952][ERROR][logstash.filters.jdbc.readwritedatabase] Exception when filling lookup db from loader remote-interfaces, query results, original exception: Sequel::DatabaseError, original message: Java::JavaSql::SQLException: Import error on line 1 of file C:/Windows/TEMP/logstash/jdbc_static/import_data/interfaces: Invalid character string format for type INTEGER. {:backtrace=>["org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(org/apache/derby/impl/jdbc/SQLExceptionFactory)", "org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(org/apache/derby/impl/jdbc/SQLExceptionFactory)", "org.apache.derby.impl.jdbc.Util.seeNextException(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.executeStatement(org/apache/derby/impl/jdbc/EmbedStatement)", "org.apache.derby.impl.jdbc.EmbedStatement.execute(org/apache/derby/impl/jdbc/EmbedStatement)", "org.apache.derby.impl.jdbc.EmbedStatement.execute(org/apache/derby/impl/jdbc/EmbedStatement)", "java.lang.reflect.Method.invoke(java/lang/reflect/Method.java:498)", "org.jruby.javasupport.JavaMethod.invokeDirectWithExceptionHandling(org/jruby/javasupport/JavaMethod.java:453)", "org.jruby.javasupport.JavaMethod.invokeDirect(org/jruby/javasupport/JavaMethod.java:314)", "C_3a_.LS.logstash_minus_6_dot_3_dot_0.vendor.bundle.jruby.$2_dot_3_dot_0.gems.sequel_minus_5_dot_14_dot_0.lib.sequel.adapters.jdbc.block in execute(C:/LS/logstash-6.3.0/vendor/bundle/jruby/2.3.0/gems/sequel-5.14.0/lib/sequel/adapters/jdbc.rb:248)", "C_3a_.LS.logstash_minus_6_dot_3_dot_0.vendor.bundle.jruby.$2_dot_3_dot_0.gems.sequel_minus_5_dot_14_dot_0.lib.sequel.database.logging.log_connection_yield(C:/LS/logstash-6.3.0/vendor/bundle/jruby/2.3.0/gems/sequel-5.14.0/lib/sequel/database/logging.rb:38)", "C_3a_.LS.logstash_minus_6_dot_3_dot_0.vendor.bundle.jruby.$2_dot_3_dot_0.gems.sequel_minus_5_dot_14_dot_0.lib.sequel.adapters.jdbc.block in execute(C:/LS/logstash-6.3.0/vendor/bundle/jruby/2.3.0/gems/sequel-5.14.0/lib/sequel/adapters/jdbc.rb:248)"]} ``` the loader it is complaining about has this config: ```auto { id => "remote-interfaces" query => "select id, name, device_id, virtual_machine_id from public.dcim_interface order by id" local_table => "interfaces" } ``` and the local db object has this config ```auto { name => "interfaces" index_columns => ["id", "device_id", "virtual_machine_id"] columns => [ ["id", "integer"], ["name", "varchar(64)"], ["device_id", "integer"], ["virtual_machine_id", "integer"] ] } ``` on the postgresql database, the same query returns this: ```auto netbox=# select id, name, device_id, virtual_machine_id from public.dcim_interface order by id; id | name | device_id | virtual_machine_id ----+----------+-----------+-------------------- 1 | a1 | 1 | 2 | Ethernet | | 1 (2 rows) ``` I think the issue is that the integer fields are returning `NULL` and so the apache derby db is complaining about it. What do I have to do to allow NULL values? Thanks ---
**Author:** ![Micah\_Hunsberger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/micah_hunsberger/32/147436_2.png) [@Micah\_Hunsberger](https://discuss.elastic.co/u/Micah_Hunsberger)\ **Post date:** [December 4, 2018, 3:50pm UTC](https://discuss.elastic.co/t/load-table-with-jdbc-static-filter-with-null-integers/159391/2 "2018-12-04T15:50:51Z")
It seems that `jdbc_static` uses the apache derby import csv file, which [interprets blank columns as null](http://db.apache.org/derby/docs/10.4/tools/ctoolsimportnulls.html), and indeed, the temp data staging file looked like this: ```auto 1, 'a1', 1, NULL 2, 'Ethernet', NULL, 1 ``` which the csv importer must not know to interpret as `null` instead of "NULL" so I tried modifying the select statement to change null values to empty strings: ```auto query => "select id, name, coalesce(device_id,''), coalesce(virtual_machine_id,'') from public.dcim_interface order by id" ``` When I do that, I get a different error: ```auto [ERROR][logstash.filters.jdbc.readonlydatabase] Exception occurred when executing loader Jdbc query count {:exception=>"Java::OrgPostgresqlUtil::PSQLException: ERROR: invalid input syntax for integer: \"\"\n Position: 70", :backtrace=>["org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(org/postgresql/core/v3/QueryExecutorImpl.java:2440)", "org.postgresql.core.v3.QueryExecutorImpl.processResults(org/postgresql/core/v3/QueryExecutorImpl.java:2183)", "org.postgresql.core.v3.QueryExecutorImpl.execute(org/postgresql/core/v3/QueryExecutorImpl.java:308)", "org.postgresql.jdbc.PgStatement.executeInternal(org/postgresql/jdbc/PgStatement.java:441)", "org.postgresql.jdbc.PgStatement.execute(org/postgresql/jdbc/PgStatement.java:365)", "org.postgresql.jdbc.PgStatement.executeWithFlags(org/postgresql/jdbc/PgStatement.java:307)", "org.postgresql.jdbc.PgStatement.executeCachedSql(org/postgresql/jdbc/PgStatement.java:293)", "org.postgresql.jdbc.PgStatement.executeWithFlags(org/postgresql/jdbc/PgStatement.java:270)"]} ``` ---
**Author:** ![Micah\_Hunsberger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/micah_hunsberger/32/147436_2.png) [@Micah\_Hunsberger](https://discuss.elastic.co/u/Micah_Hunsberger)\ **Post date:** [December 4, 2018, 4:29pm UTC](https://discuss.elastic.co/t/load-table-with-jdbc-static-filter-with-null-integers/159391/3 "2018-12-04T16:29:56Z")
The previous error was a postgresql error because I was trying to mix integer and string fields. I changed the query to be ```auto query => "select id, name, case when device_id is null then 'null' else cast(device_id as varchar(10)) end, case when virtual_machine_id is null then 'null' else cast(virtual_machine_id as varchar(10)) end from public.dcim_interface order by id" ``` which does successfully make `NULL` an empty string, but the csv file escaped the empty string: ```auto 1, 'a1', '1', '' 2, 'Ethernet', '', '1' ``` and so the import was still unsuccessful, so I am back where I started. Whatever code is creating the CSV is not building it out correctly for the apache derby database to accept `NULL` values as actually null, and instead treats it as a string: `"NULL"`. Instead of ```auto 1, 'a1', '1', NULL 2, 'Ethernet', NULL, '1' ``` The csv **should** be ```auto 1, 'a1', '1', 2, 'Ethernet',, '1' ``` based on this documentation on csv import/export: [https://db.apache.org/derby/docs/10.7/tools/ctoolsimportnulls.html](https://db.apache.org/derby/docs/10.7/tools/ctoolsimportnulls.html) ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [January 1, 2019, 4:29pm UTC](https://discuss.elastic.co/t/load-table-with-jdbc-static-filter-with-null-integers/159391/4 "2019-01-01T16:29:57Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.