Couple of things -
1. Your dataSource is probably in readOnly mode. It is possible to fire
updates, by specifying readOnly="false" in your dataSource.
2. What you are trying achieve, is typically done using a "select for
update". For MySql, here's the documentation -
http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html
3. You don't need to create a separate "entity" for firing updates.
Writing a database procedure might be a good idea. In that case your query
will simply be <entity name="mainEntity" query="call MyProcedure();" .../>.
All the heavy lifting can be done by this query.
Moreover, update queries, only return the number of rows affected and not a
resultSet. DIH expects one and hence the exception.
Cheers
Avlesh
On Tue, Aug 4, 2009 at 1:49 AM, Jay Hill <jayallenh...@gmail.com> wrote:
> Is it possible for the DataImportHandler to update records in the table it
> is querying? For example, say I have a query like this in my entity:
>
> query="select field1, field2, from someTable where hasBeenIndexed=false"
>
> Is there a way I can mark each record processed by updating the
> hasBeenIndexed field? Here's a config I tried:
>
> <?xml version="1.0"?>
> <dataConfig>
> <dataSource
> type="JdbcDataSource"
> driver="com.mysql.jdbc.Driver"
> url="jdbc:mysql://localhost:3306/solrhacks"
> user="user"
> password="pass"/>
>
> <document name="testingDIHupdate">
> <entity name="mainEntity"
> pk="id"
> query="select id, name from tableToIndex where
> hasBeenIndexed=0">
> <field column="id" template="dihTestUpdate-${main.id}"/>
> <field column="name" name="name"/>
>
> <entity name="updateEntity"
> pk="id"
> query="update tableToIndex set hasBeenIndexed=1 where
> id=${mainEntity.id}">
> </entity>
> </entity>
> </document>
> </dataConfig>
>
> It does update the first record, but then an Exception is thrown:
> Aug 3, 2009 1:15:24 PM org.apache.solr.handler.dataimport.DocBuilder
> buildDocument
> SEVERE: Exception while processing: mainEntity document :
> SolrInputDocument[{id=id(1.0)={1}, name=name(1.0)={John Jones}}]
> org.apache.solr.handler.dataimport.DataImportHandlerException: Unable to
> execute query: update tableToIndex set hasBeenIndexed=1 where id=1
> Processing Document # 1
> at
>
> org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.<init>(JdbcDataSource.java:250)
> at
>
> org.apache.solr.handler.dataimport.JdbcDataSource.getData(JdbcDataSource.java:207)
> at
>
> org.apache.solr.handler.dataimport.JdbcDataSource.getData(JdbcDataSource.java:40)
> 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:344)
> at
>
> org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:370)
> at
>
> org.apache.solr.handler.dataimport.DocBuilder.doFullDump(DocBuilder.java:225)
> at
> org.apache.solr.handler.dataimport.DocBuilder.execute(DocBuilder.java:167)
> at
>
> org.apache.solr.handler.dataimport.DataImporter.doFullImport(DataImporter.java:333)
> at
>
> org.apache.solr.handler.dataimport.DataImporter.runCmd(DataImporter.java:393)
> at
>
> org.apache.solr.handler.dataimport.DataImporter$1.run(DataImporter.java:372)
> Caused by: java.lang.NullPointerException
> at
>
> org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.<init>(JdbcDataSource.java:248)
> ... 12 more
>
>
> -Jay
>
