SOLR & Sybase: DIH full-import failure

Question

I try to import from a DB that requires Sybase driver for connection.

Full-import from DIH with data-config:

<dataConfig>
    <dataSource type="JdbcDataSource"
        driver="sybase.jdbc4.sqlanywhere.IDriver"
               url="jdbc:sqlanywhere:eng=dbn;dbn=dbname;links=tcpip{host=10.94.XXX.XX}"
                user="usr" 
        password="pwd" />
    <document name="products">
      <entity name="company" pk="id" query="call WS.getFieldsLimited('RU', '2011-10-01', 10)">
            <field column="ID" name="crefo" />
...
...
        </entity>
    </document>
</dataConfig>

Fails and throws an exeption

SEVERE: Full Import failed org.apache.solr.handler.dataimport.DataImportHandlerException: Unable to execute query: call WS.getCrefoSearchFieldsLimited('RU', '2011-10-01', 10) Processing Document # 1 at org.apache.solr.handler.dataimport.DataImportHandlerException.wrapAndThrow(DataImportHandlerException.java:72) at org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.(JdbcDataSource.java:253) at org.apache.solr.handler.dataimport.JdbcDataSource.getData(JdbcDataSource.java:210) at org.apache.solr.handler.dataimport.JdbcDataSource.getData(JdbcDataSource.java:39) at org.apache.solr.handler.dataimport.SqlEntityProcessor.initQuery(SqlEntityProcessor.java:58) at org.apache.solr.handler.dataimport.SqlEntityProcessor.nextRow(SqlEntityProcessor.java:71) at org.apache.solr.handler.dataimport.EntityProcessorWrapper.nextRow(EntityProcessorWrapper.java:237) at org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:357) at org.apache.solr.handler.dataimport.DocBuilder.doFullDump(DocBuilder.java:242) at org.apache.solr.handler.dataimport.DocBuilder.execute(DocBuilder.java:180) at org.apache.solr.handler.dataimport.DataImporter.doFullImport(DataImporter.java:331) at org.apache.solr.handler.dataimport.DataImporter.runCmd(DataImporter.java:389) at org.apache.solr.handler.dataimport.DataImporter$1.run(DataImporter.java:370) Caused by: java.lang.NullPointerException at org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.(JdbcDataSource.java:251) ... 11 more

Do I use a wrong url or bad dataSource declaration? I can acces that procedure through sql client.

Answer 1

Java was cribbing about the syntax of

(RU', '2011-10-01', 10)

in second line of the trace. I guess you mean

 ('RU', '2011-10-01', 10)

---

Stack Overflow's auto syntax highlighting did help here.

To be less exasperated with embedding queries in a programming language:

1. try out the query in an interpreter (if Sybase has one) before embedding
2. write out the queries in a text editor that supports syntax highlighting for that query language
3. be aware of escape characters for your query language (%hh escapes chars in URLs, \ escapes chars in SQL)
4. use string formatting and multi-line string features supported by your language

-Someone who spent his first four months of his career doing (among other things) SQL and Solr queries in Python and PHP.

Answer 2

Currently the Data Import Handler does not support stored procedure calls. See https://issues.apache.org/jira/browse/SOLR-1262