Excellent, thanks Avlesh and Noble.
-Jay
On Mon, Aug 3, 2009 at 9:28 PM, Avlesh Singh <avl...@gmail.com> wrote:
> >
> > datasource.getData("update mytable ...."); //though the name is getData()
> > it can execute update commands also
> >
> Even when the dataSource is "readOnly", Noble?
>
> Cheers
> Avlesh
>
> 2009/8/4 Noble Paul നോബിള് नोब्ळ् <noble.p...@corp.aol.com>
>
> > 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<avl...@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 <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
> > >>
> > >
> >
> >
> >
> > --
> > -----------------------------------------------------
> > Noble Paul | Principal Engineer| AOL | http://aol.com
> >
>
