Re: Solr date variable resolver is not working with MySql

[Alexandre Rafalovitch]

Some reason, you email is complete unreadable with a lot of nbsp
instead of spaces. Maybe it is trying to send as broken HTML?

You may want to try to reformat the message and resend.

Regards,
    Alex.
----
Solr Analyzers, Tokenizers, Filters, URPs and even a newsletter:
http://www.solr-start.com/


On 10 June 2015 at 22:43, abhijit bashetti
<bashettiabhi...@rediffmail.com> wrote:
>
> I have used Solr 3.3 version as Data Import Handler(DIH) with Oracle.Its 
> working fine for me.&nbsp;
> Now I am trying the same with Mysql.With the change in database, I have 
> changed the query used in data-config.xml for MySql.
> The query has variables which are passed url in http.The same thing works 
> fine in Oracle with variable resolver but not in MySql.
> The query is :
> &nbsp; &nbsp; SELECT DISTINCT doc.document_id ,&nbsp; &nbsp; &nbsp; 
> doc.first_version_id,&nbsp; &nbsp; &nbsp; doc.acl_id, &nbsp; &nbsp; &nbsp; 
> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; 
> fol.folder_id &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 
> &nbsp;&nbsp;&nbsp; &nbsp; FROM ds_document_c doc,&nbsp; &nbsp; &nbsp; 
> ds_folder fol&nbsp; &nbsp; WHERE doc.cabinet_id = 
> ${dataimporter.request.cabinetId}&nbsp; &nbsp; &nbsp; AND fol.folder_id = 
> doc.document_folder_id&nbsp; &nbsp; &nbsp; AND 
> doc.index_state_modification_date &gt;= 
> to_date('${dataimporter.request.lastIndexDate}', 'DD/MM/YYYY HH24:MI:SS')
> and the Url &nbsp;is :&nbsp;
> &nbsp; &nbsp; 
> localhost:8983/solr/dataimport?command=full-import&amp;clean=true&amp;commit=true&amp;cabinetId=17083360&amp;lastIndexDate='24/05/2015
>  00:00:00'
> Solr is building the query as below :&nbsp;&nbsp;
> &nbsp; &nbsp; SELECT DISTINCT doc.document_id ,&nbsp; &nbsp; &nbsp; 
> doc.first_version_id,&nbsp; &nbsp; &nbsp; doc.acl_id, &nbsp; &nbsp; &nbsp; 
> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; 
> fol.folder_id &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 
> &nbsp;&nbsp;&nbsp; &nbsp; FROM ds_document_c doc,&nbsp; &nbsp; &nbsp; 
> ds_folder fol&nbsp; &nbsp; WHERE doc.cabinet_id = 24&nbsp; &nbsp; AND 
> fol.folder_id = doc.document_folder_id&nbsp; &nbsp; AND 
> doc.index_state_modification_date &gt;= to_date('[?, '28/05/2015 11:13:50']', 
> 'DD/MM/YYYY HH24:MI:SS')
> I am not able to figure it our why the date variable is not resloved properly 
> in this case.
> Because of `to_date('[?, '28/05/2015 11:13:50']'` is not in a proper MySql 
> syntax, I am getting MySql Syntax error.&nbsp;
> I get the following error&nbsp;
> &nbsp; &nbsp; You have an error in your SQL syntax; check the manual that 
> corresponds to your MySQL server version for the right syntax to use near 
> '[?, '28/05/2015 11:13:50'], 'DD/MM/YYYY HH24:MI:SS')))' at line 1
>
> Anyone knows where is the problem? Why is the variable resolver not working 
> as expected?
> Note : to_date is function written by us in MySql.
> I have checked out the solr code from svn... trying it by adding logs to 
> it...but the logs are not reflected and i m not able to move it.
> I am not very sure very it is wrong ...but just a wild guess that something 
> is wrong at variableResolverImpl.replaceTokens or at the 
> TemplateString.fillTokens...
> I will keep on it but if you know/get a chance to look at it, it would be of 
> great help from your end..
> Regards,Abhijit