Solr templated sql print nothing in dataimport

Question

I have the following dataimport configuration:

<?xml version="1.0" encoding="UTF-8" ?>
<dataConfig>
<dataSource driver="net.ucanaccess.jdbc.UcanaccessDriver" type="JdbcDataSource" url="jdbc:ucanaccess://C:/feqh/main.mdb;memory=false" />

<document>
    <entity name="Book" 
            query="select bkid AS id, bkid AS BookID,bk AS BookTitle, betaka AS BookInfo, cat as cat from 0bok">
                   <field column="id" name="id"/>
                   <field column="BookID" name="BookID"/>
                   <field column="BookTitle" name="BookTitle"/>
                   <field column="cat" name="cat"/>    
        <entity name="Category"
                query="select name as CatName, catord as CatWeight, Lvl as CatLevel from 0cat where id = ${Book.cat}">
                    <field column="CatName" name="CatName"/>
                    <field column="CatWeight" name="CatWeight"/>
                    <field column="CatLevel" name="CatLevel"/>
        </entity>

    </entity>
  </document>
</dataConfig>

This dataimport is failed due to the following error from the log:

Unable to execute query: select name as CatName, catord as CatWeight, Lvl as CatLevel from 0cat where id = Processing Document # 1

When I replace ${Book.cat} with any fixed number such as 128, the import works fine.

So it seems that ${Book.cat} does not printout any value. The database that I import data from is MS Access database mdb using ucanaccess version 2.0.9. I'm using Solr 4.9.0 on Java8. How could I solve this issue?

Answer 1

For unknown reason I found that the column name should be written in Upper case in the template, so ${Book.cat} should be ${Book.CAT} I said unknown because I'm sure that the column name in the database is written lower case cat.