Error with JDBC statement on MaxDB

[franckfct (franck)]

Hi,

I would like to synchronize a MaxDB table to ES via logstash/JDBC, so in my pipeline i have =>

input {
  jdbc {
    jdbc_driver_library => "<path>/sapdbc.jar"
    jdbc_driver_class => "com.sap.dbtech.jdbc.DriverSapDB"
    jdbc_connection_string => "jdbc:sapdb://ip:7200/schema"
    jdbc_user => "User"
    jdbc_password => "passwd"
    jdbc_paging_enabled => true
    jdbc_validate_connection => true
    clean_run => true
    tracking_column => "unix_ts_in_secs"
    use_column_value => true
    tracking_column_type => "numeric"
    schedule => "*/1 * * * *"
    statement => "SELECT OBJECT_ID FROM TABLE"
    jdbc_page_size => 100000 
    jdbc_paging_enabled => true
    last_run_metadata_path => "/.logstash_jdbc_last_run"
  }
}

and i received this error:

[2021-09-28T18:13:00,189][ERROR][logstash.inputs.jdbc     ][pipeline_ticket][a0519e841cc0d8e39dde3bdea12d077f54b35d4948173191186898141d83a875] Java::ComSapDbtechJdbcExceptionsJdbc40::SQLSyntaxErrorException: [-3008] (at 77): Invalid keyword or missing delimiter: SELECT * FROM (SELECT OBJECT_ID FROM TABLE) AS "T1" LIMIT 100000 OFFSET 0
[2021-09-28T18:13:00,192][WARN ][logstash.inputs.jdbc     ][pipeline_ticket][a0519e841cc0d8e39dde3bdea12d077f54b35d4948173191186898141d83a875] Exception when executing JDBC query {:exception=>"Java::ComSapDbtechJdbcExceptionsJdbc40::SQLSyntaxErrorException: [-3008] (at 77): Invalid keyword or missing delimiter"}

So i tested directly from maxdb client with and without "LIMIT 100000 OFFSET 0"

without LIMIT and OFFSET => SELECT * FROM (SELECT OBJECT_ID FROM TABLE)
Good result

user@hostname:/home/user> 
| OBJECT_ID  |
| ---------- |
| 8000000000 |
| 8000000001 |
| 8000000002 |
| 8000000003 |
| 8000000004 |

With LIMIT and OFFSET => SELECT * FROM (SELECT OBJECT_ID FROM TABLE) LIMIT 100000 OFFSET 0

user@hostname:/home/user>  * -3008: POS(69) Invalid keyword or missing delimiter SQLSTATE: 42000

Position 69 coresponding to OFFSET word

How can i solve this issue or remove offset word from the statement ?

Thanks

[Badger]

@franckfct

How can i solve this issue or remove offset word from the statement ?

The jdbc input is built on top of Jeremy Evans' sequel library. Its pagination code assumes the underlying database uses LIMIT and OFFSET to control this. If your DB does not support that then you cannot use pagination.

Try removing both of your jdbc_paging_enabled => true options on the input.

[franckfct (franck)]

Yes, Exactly. Thanks a lot