One more idea. If your data fits into heap you can join data by DIH. see Example 2 in http://wiki.apache.org/solr/DataImportHandler#CachedSqlEntityProcessor
but be aware that where="xid=x.id" were broken in trunk around r1201784. see https://issues.apache.org/jira/browse/SOLR-2933 but it works in 3.x. Regards On Sat, Jan 14, 2012 at 12:45 AM, Mikhail Khludnev < mkhlud...@griddynamics.com> wrote: > Hello, > > I'm afraid you can only vote > https://issues.apache.org/jira/browse/SOLR-1262 > > Regards > > > On Fri, Jan 13, 2012 at 11:16 PM, geeky2 <gee...@hotmail.com> wrote: > >> >> hello all, >> >> >> some of my records contain bad data i the orb_itm_id column. >> >> example: >> >> select * from prtxtps_prt_summ where orb_itm_id like '''%'; >> >> prd_gro_id spp_id orb_itm_id ds_tx rnk_no >> 0022 335 ' LONG. (TERMINAL ATTACH )' LONG. (TERMINAL >> ATTACH) 0 >> 0042 596 ', FAN MOTOR CAPACITOR S TRAP 0 >> >> >> this is causing the indexing process to fail on the bad records - with a >> sql >> syntax error >> >> >> is there a way i can trap for this and cleans the "'" before the sql is >> constructed? >> >> mark >> >> <dataConfig> >> <dataSource driver="com.informix.jdbc.IfxDriver" >> url="jdbc:informix-sqli:// >> hawaii.sears.com:1525/lis0d000d:informixserver=ifmx00289_tcp" >> user="pdrp" password="parts234" batchSize="10"/> >> <document> >> <entity transformer="TemplateTransformer" name="core1-parts" >> query="select *, 1 as item_type from prtxtps_prt_summ"> >> <field column ="id" name="id" >> >> template="${core1-parts.prd_gro_id},${core1-parts.spp_id},${core1-parts.orb_itm_id}"/> >> <field column="orb_itm_id" name="itemNo"/> >> <field column="prd_gro_id" name="groupId"/> >> <field column="ds_tx" name="itemDesc"/> >> <field column="spp_id" name="supplierId"/> >> <field column="rnk_no" name="rankNo"/> >> <field column="item_type" name="itemType"/> >> <entity name="partAttributes" query="SELECT pa.uom_hi, >> pa.att_val_hi, pa.uom_low, pa.att_val_low, a.att_nm FROM prtxtpa_att_val >> pa, >> prtxtat_att a WHERE pa.att_id = a.att_id and pa.orb_itm_id = >> '${core1-parts.orb_itm_id}' and pa.spp_id = '${core1-parts.spp_id}' and >> pa.prd_gro_id = '${core1-parts.prd_gro_id}' and pa.att_val_hi is not >> NULL"> >> <field column="uom_hi" name="partAttrib_uomHigh"/> >> <field column="att_val_hi" name="partAttrib_highValue"/> >> <field column="uom_low" name="partAttrib_uomLow"/> >> <field column="att_val_low" name="partAttrib_lowValue"/> >> <field column="att_nm" name="partAttrib_attributeName"/> >> </entity> >> </entity> >> </document> >> </dataConfig> >> >> >> >> >> >> 2012-01-13 12:27:38,912 SEVERE >> [org.apache.solr.handler.dataimport.DataImporter] (Thread-27) Full Import >> failed:org.apache.solr.handler.dataimport.DataImportHandlerException: >> Unable >> to execute query: SELECT pa.uom_hi, pa.att_val_hi, pa.uom_low, >> pa.att_val_low, a.att_nm FROM prtxtpa_att_val pa, prtxtat_att a WHERE >> pa.att_id = a.att_id and pa.orb_itm_id = '' LONG. (TERMINAL ATTACH' and >> pa.spp_id = '335' and pa.prd_gro_id = '0022' and pa.att_val_hi is not NULL >> Processing Document # 119 >> 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.SqlEntityProcessor.nextRow(SqlEntityProcessor.java:73) >> at >> >> org.apache.solr.handler.dataimport.EntityProcessorWrapper.nextRow(EntityProcessorWrapper.java:238) >> at >> >> org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:591) >> at >> >> org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:617) >> at >> >> org.apache.solr.handler.dataimport.DocBuilder.doFullDump(DocBuilder.java:267) >> at >> org.apache.solr.handler.dataimport.DocBuilder.execute(DocBuilder.java:186) >> 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.sql.SQLException: A syntax error has occurred. >> >> >> >> >> >> >> >> -- >> View this message in context: >> http://lucene.472066.n3.nabble.com/linking-query-in-DIH-fails-with-sql-syntax-error-when-specific-fields-contain-bad-data-tp3657482p3657482.html >> Sent from the Solr - User mailing list archive at Nabble.com. >> > > > > -- > Sincerely yours > Mikhail Khludnev > Lucid Certified > Apache Lucene/Solr Developer > Grid Dynamics > > <http://www.griddynamics.com> > <mkhlud...@griddynamics.com> > > -- Sincerely yours Mikhail Khludnev Lucid Certified Apache Lucene/Solr Developer Grid Dynamics <http://www.griddynamics.com> <mkhlud...@griddynamics.com>
