JDBC Logstash Plugin issue

[valleram]

Hi All,

I'm trying to impletement a Logstash pipeline using the JDBC plugin to fetch events from an Oracle database, but I'm receiving following errors.

Java::JavaSql::SQLSyntaxErrorException: ORA-00936: missing expression:

Pipeline input configuration:

input {
        jdbc {
          jdbc_driver_library => "/usr/share/logstash/ojdbc6.jar"
          jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
          jdbc_connection_string => "jdbc:oracle:thin:@...."
          jdbc_user => "...."
          jdbc_password => "...."         
          statement => "SELECT
                      '230'                         AS "plantCode",
                      TRIM(CycleCountingLine.t$item)AS "itemCode",
                      TRIM(GeneralItemData.t$dsca)  AS "itemDesc",
                      CycleCounting.t$cwar          AS "Warehouse",
                      ''                            AS "tmlArea",
                      to_char(new_time(CycleCountingLine.t$trdt,'GMT','CST'),'YYYY-MM-DD HH24:MI:SS')  AS "Timestamp",
                      to_char(new_time(CycleCountingLine.t$cdat,'GMT','CST'),'YYYY-MM-DD HH24:MI:SS')  AS "creationDate",
                      DECODE(CycleCounting.t$otyp,1,'Cycle Counting',2,'Adjustment') AS "orderType",
                      'Inventory Adjustment'        AS "transactionType",
                      CycleCounting.t$orno          AS "Order",
                      CASE 
                        WHEN CycleCountingLine.t$vstk > 0 THEN 'POSITIVE'
                        ELSE                               'NEGATIVE'
                      END                           AS "Signal",  
                      ABS(CycleCountingLine.t$vstk) AS "absoluteQty",
                      CycleCountingLine.t$vstk      AS "Qty",
                      CycleCountingLine.t$cstk      AS "qtyAfterTransaction",
                      ''                            AS "Type",
                      ItemCostingData.t$ecpr$1      AS "unValueUSD",
                      CycleCountingLine.t$vstk * ItemCostingData.t$ecpr$1 AS "totalValueUSD",
                      ItemCostingData.t$ccur        AS "currencyType",
                      ItemWarehousingData.t$abcc    AS "abcCode",
                      UserData.t$user               AS "userName",
                      CycleCounting.t$recd          AS "reasonCode",
                      Reasons.t$dsca                AS "reasonCodeDesc",
                      ''                            AS "Comments",
                      'mycity'                 AS "destCityName",
                      'mycode'                         AS "destCityCode",
                      'MX'                          AS "destCountry",
                      '23.346482 -131.31342'         AS "destLocation"

                    FROM
                      bn.twh550391 CycleCounting,
                      bn.twh551391 CycleCountingLine,
                      bn.ttcd001391 GeneralItemData,
                      bn.ttcd007391 ItemCostingData,
                      bn.twmd400391 ItemWarehousingData,
                      bn.ttcm001391 Employees,
                      bn.tttd200000 UserData,
                      bn.ttcs005391 Reasons

                    WHERE
                      CycleCounting.t$orno = CycleCountingLine.t$orno
                      AND CycleCountingLine.t$item = GeneralItemData.t$item
                      AND CycleCountingLine.t$item = ItemCostingData.t$item
                      AND CycleCountingLine.t$item = ItemWarehousingData.t$item
                      AND CycleCounting.t$emno = Employees.t$emno
                      AND TRIM(Employees.t$nama) = TRIM(UserData.t$name)
                      AND CycleCounting.t$recd = Reasons.t$cdis"
        }
      }

Can someone please help me on this.

thank you

[Badger]

I find it hard to believe it gets that far. There are many strings surrounded by double quotes inside your statement, which is a double quoted string. The configuration compiler should be blowing up around

plantCode",

Escape them, use single quotes if SQL allows it, or put the statement in a file and reference that.