从 solr 调用存储过程 [英] calling stored procedure from solr
问题描述
我正在编写一个通过 solr 3.4 进行搜索的应用程序.为了填充 solr 的索引,我使用 dataimporthandler 和 com.microsoft.sqlserver.jdbc.SQLServerDriver 类从 MS SQL 数据库中获取数据.
I’m writing an application that searches by solr 3.4. To fill the index of solr I use the dataimporthandler and the com.microsoft.sqlserver.jdbc.SQLServerDriver class to get the data from the MS SQL database.
现在我正在尝试在数据库上调用存储过程,但 solr 日志总是返回错误:
Now I’m trying to call a stored procedure on Database but solr log always return 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
我尝试了不同的版本来调用存储过程.这里的查询:
The I’ve tried diferent versions to call the stored procedure. Here the queries:
<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>
有谁知道如何从 solr 调用存储过程?或者有人知道我应该去哪里寻找原因吗?
Does anyone know how to call a stored procedure from solr? Or does anyone know where I should search the cause?
非常感谢您的回答!
推荐答案
对我来说它是这样工作的:
For me it works like this:
<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>
但这仅在存储过程包含简单的 SELECT 查询时才有效.
But this only works if the stored procedure contains a simple SELECT query.
如果我在将返回结果的查询之前声明了一些变量或临时表,那么导入将失败,并给出与您相同的异常.
If I declare some variables or temporrary tables before the query that will return the results then the import fails, giving me the same exception that you get.
后期编辑
我设法让它在存储过程中处理更复杂的查询.通过添加SET NOCOUNT ON;
在存储过程的开头.
I managed to make it work with more complex queries in the Stored Procedure. by adding
SET NOCOUNT ON;
at the beginning of the stored procedure.
这篇关于从 solr 调用存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!