Geek Answers Handbook

Calling a stored procedure from solr

I am writing an application that is looking for solr 3.4. To populate the solr index, I use dataimporthandler and the class com.microsoft.sqlserver.jdbc.SQLServerDriver to retrieve data from the MS SQL database.

Now I am trying to call a stored procedure in the database, but the solr log always returns errors:

Full Import failed:org.apache.solr.handler.dataimport.DataImportHandlerException: Unable to execute query: exec dbo.h_getThumbnails @h = '52' Processing Document # 48 at org.apache.solr.handler.dataimport.DataImportHandlerException.wrapAndThrow(DataImportHandlerException.java:72) at org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.<init>(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:59) at org.apache.solr.handler.dataimport.CachedSqlEntityProcessor.getAllNonCachedRows(CachedSqlEntityProcessor.java:69) at org.apache.solr.handler.dataimport.EntityProcessorBase.getSimpleCacheData(EntityProcessorBase.java:259) at org.apache.solr.handler.dataimport.CachedSqlEntityProcessor.nextRow(CachedSqlEntityProcessor.java:58) at org.apache.solr.handler.dataimport.EntityProcessorWrapper.nextRow(EntityProcessorWrapper.java:238) at org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:596) at org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:622) at org.apache.solr.handler.dataimport.DocBuilder.doFullDump(DocBuilder.java:268) at org.apache.solr.handler.dataimport.DocBuilder.execute(DocBuilder.java:187) at org.apache.solr.handler.dataimport.DataImporter.doFullImport(DataImporter.java:359) at org.apache.solr.handler.dataimport.DataImporter.runCmd(DataImporter.java:427) at org.apache.solr.handler.dataimport.DataImporter$1.run(DataImporter.java:408) Caused by: java.lang.NullPointerException at org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.<init>(JdbcDataSource.java:251) ... 14 more

Ive tried different versions for calling a stored procedure. Here are the requests:

 <entity name="PicturePath" processor="CachedSqlEntityProcessor" query="exec dbo.h_getThumbnails @h = 4 "> </entity> <entity name="PicturePath" processor="CachedSqlEntityProcessor" query="call dbo.h_getThumbnails @h = 4 "> </entity> <entity name="PicturePath" processor="CachedSqlEntityProcessor" query=" dbo.h_getThumbnails @h = 4 "> </entity>

Does anyone know how to call a stored procedure from solr? Or does anyone know where I should look for a reason?

Thanks so much for all your answers!

+8
sql sql-server-2008 stored-procedures solr
source share
2 answers

For me, this works as follows:

 <dataSource name="ds-1" type="JdbcDataSource" driver="com.microsoft.sqlserver.jdbc.SQLServerDriver" url="jdbc:sqlserver://10.0.2.47;databaseName=dbname" user="username" password="password" responseBuffering="adaptive" batchSize="0" autoCommit="false" /> <entity name="item" dataSource="ds-1" query="[sp_StuffDataImportHandler]"></entity>

But this only works if the stored procedure contains a simple SELECT query.

If I declare some variables or temporary tables before the query that returns the results, the import will fail, giving me the same exception that you will get.

LATER EDIT

I managed to get it to work with more complex queries in the stored procedure. adding SET NOCOUNT ON; at the beginning of the stored procedure.

+13
source share

You need to call the stored procedure using the following syntax

 <entity name="PicturePath" processor="CachedSqlEntityProcessor" query="[h_getThumbnails] '4'" "> </entity>
0
source share

More articles:


All Articles