oracle: elasticsearch jdbc not support sql like - select 'myjdbc' as \"_index\", 'car_statinfo' as \"_type\", id as \"_id\" from mytable

[yinyu00]

Caused by: java.sql.SQLException: ORA-00904: "car_statinfo": 标识符无效

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439) ~[ojdbc5-11.2.0.1.0.jar:11.2.0.1.0]
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395) ~[ojdbc5-11.2.0.1.0.jar:11.2.0.1.0]
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802) ~[ojdbc5-11.2.0.1.0.jar:11.2.0.1.0]
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436) ~[ojdbc5-11.2.0.1.0.jar:11.2.0.1.0]
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186) ~[ojdbc5-11.2.0.1.0.jar:11.2.0.1.0]
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521) ~[ojdbc5-11.2.0.1.0.jar:11.2.0.1.0]
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:194) ~[ojdbc5-11.2.0.1.0.jar:11.2.0.1.0]
    at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:853) ~[ojdbc5-11.2.0.1.0.jar:11.2.0.1.0]
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145) ~[ojdbc5-11.2.0.1.0.jar:11.2.0.1.0]
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1259) ~[ojdbc5-11.2.0.1.0.jar:11.2.0.1.0]
    at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1469) ~[ojdbc5-11.2.0.1.0.jar:11.2.0.1.0]
    at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:389) ~[ojdbc5-11.2.0.1.0.jar:11.2.0.1.0]
    at org.xbib.elasticsearch.jdbc.strategy.standard.StandardSource.executeQuery(StandardSource.java:949) ~[elasticsearch-jdbc-1.6.0.0-uberjar.jar:?]
    at org.xbib.elasticsearch.jdbc.strategy.standard.StandardSource.execute(StandardSource.java:666) ~[elasticsearch-jdbc-1.6.0.0-uberjar.jar:?]
    at org.xbib.elasticsearch.jdbc.strategy.standard.StandardSource.fetch(StandardSource.java:583) ~[elasticsearch-jdbc-1.6.0.0-uberjar.jar:?]
    ... 11 more

[jprante]

This is an Oracle error. Please check syntax, single apostrophe may not work with Oracle.

[yinyu00]

@jprante Here is my sql Config: select 'myjdbc' as \"_index\", 'car_statinfo' as \"_type\", id as \"_id\" from mytable Log: select myjdbc as "_index", car_statinfo as "_type", id as "_id" from mytable

i think elasticsearch-jdbc trim the ['], then oracle throw an exception

[jprante]

Which log is this? You have to set up valid JSON and valid Oracle syntax in the JDBC definition. There is no trimming, SQL is passed as given to driver.

[yinyu00]

@jprante [17:10:39,216][DEBUG][importer.jdbc.source.standard][pool-2-thread-1] org.xbib.elasticsearch.jdbc.strategy.standard.StandardSource@46a1d8d executing SQL without params: statement=select myjdbc as "_index", car_statinfo as "_type", id as "_id", createtime from HALL_CAR_STATINFO parameter=[] write=false callable=false

this log is print by jdbc plugin , when debug is enabled

[jprante]

Then your command line/shell may drop single quotes.

[yinyu00]

@jprante

"sql" : [ { "statement" : "select & apos;myjdbc& apos; as \"_index\", & apos;car_statinfo& apos; as \"_type\", id as \"_id\", createtime from HALL_CAR_STATINFO" }, { "statement" : "select & apos;myjdbc& apos; as \"_index\", & apos;tfm_rules& apos; as \"_type\", id as \"_id\", code, type, name from tfm_rules" } ],

I changed the source code, and manually replace [& apos;] with ['], and it works , have you got any idea more convenient

[jprante]

What is the change of the source code?

[yinyu00]

@jprante manually replace [& apos;] with [']

[jprante]

Not sure I understand. So you used ' from HTML entities, which gives an error, and changed it to '?