lucene-solr-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Noble Paul നോബിള്‍ नोब्ळ् <noble.p...@corp.aol.com>
Subject Re: DIH: Any way to make update on db table?
Date Tue, 04 Aug 2009 04:26:28 GMT
If your are writing a Transformer (or any other component) you can get
hold of a dataSource instance .

 datasource =Context#getDataSource(name).
//then you can invoke
datasource.getData("update mytable ....");
//though the name is getData() it can execute update commands also

ensure that you do a
datasource.close();
after you are done

On Tue, Aug 4, 2009 at 9:40 AM, Avlesh Singh<avlesh@gmail.com> wrote:
> 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 <jayallenhill@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
>>
>



-- 
-----------------------------------------------------
Noble Paul | Principal Engineer| AOL | http://aol.com

Mime
View raw message