You are viewing a plain text version of this content. The canonical link for it is here.
Posted to solr-user@lucene.apache.org by Ci-man <ci...@yahoo.com> on 2009/04/28 20:53:11 UTC

Unable to import data from database

I am using MS SQL server and want to index a table.
I setup my data-config like this:

<dataConfig>
	<dataSource type="JdbcDataSource" batchSize="25000" 
		autoCommit="true" 
		driver="com.microsoft.sqlserver.jdbc.SQLServerDriver" 
		url="jdbc:sqlserver://localhost:1433;databaseName=MYDB" 
		user="****" password="****"/>

    <document name="products">
        <entity name="item" query="select TOP 50 * from items">
            <field column="item_id" name="id" />
            <field column="itemname" name="name" />
            <field column="itemavgbucost" name="price" />
	    <field column="categoryname" name="cat" />            
            <field column="itemdesc" name="features"  />                
        </entity>
    </document>
</dataConfig>


I am unable to load data from database. I always receive 0 document fetched:
<lst name="statusMessages">
<str name="Time Elapsed">0:0:12.989</str>
<str name="Total Requests made to DataSource">1</str>
<str name="Total Rows Fetched">0</str>
<str name="Total Documents Processed">0</str>
<str name="Total Documents Skipped">0</str>
<str name="Full Dump Started">2009-04-28 14:37:49</str>
</lst>

The query runs in SQL Server query manager and retrieves records. The funny
thing is, even if I purposefully write a wrong query with non-existing
tables I get the same response. What am I doing wrong? How can I tell
whether a query fails or succeeds or if solr is running the query in the
first place?

Any help is appreciated.
Best,
-Ci 


-- 
View this message in context: http://www.nabble.com/Unable-to-import-data-from-database-tp23283852p23283852.html
Sent from the Solr - User mailing list archive at Nabble.com.

Re: Unable to import data from database

Posted by Noble Paul നോബിള്‍ नोब्ळ् <no...@gmail.com>.
I guess this can go in the FAQ section of DIH

On Wed, Apr 29, 2009 at 9:47 PM, Erick Erickson <er...@gmail.com> wrote:
> Thanks for letting us all know the resolution, that may save some other
> poor soul from frustration....
>
> Best
> Erick
>
> On Wed, Apr 29, 2009 at 9:31 AM, Ci-man <ci...@yahoo.com> wrote:
>
>>
>> Found the problem.
>> It is with Microsoft jdbc drivers (jdbc 2.0).
>>
>> With the latest download Microsoft provides two .jar files:
>> sqljdbc.jar
>> sqljdbc4.jar
>>
>> I had copied both into the lib directory. By doing so it used the older
>> drivers (sqljdbc.jar) which do not work with jvm1.6. You get this kind of
>> cryptic message in debug trace:
>>
>> The JDBC Driver version 2.0 does not support JRE 1.4. You must upgrade JRE
>> 1.4 to JRE 5.0 or later when using the JDBC Driver version 2.0. In some
>> cases, you might need to recompile your application because it might not be
>> compatible with JDK 5.0 or later. For more information, see the
>> documentation on Sun Microsystems Web site
>>
>> No further help from MS or boards. I experimented and removed the
>> sqljdbc.jar file from lib directory so that only the sqljdbc4.jar is
>> available. And bingo. Everything is working like a charm.
>>
>> Thanks everyone,
>> -Ci
>>
>> --
>> View this message in context:
>> http://www.nabble.com/Unable-to-import-data-from-database-tp23283852p23295866.html
>> Sent from the Solr - User mailing list archive at Nabble.com.
>>
>>
>



-- 
--Noble Paul

Re: Unable to import data from database

Posted by Erick Erickson <er...@gmail.com>.
Thanks for letting us all know the resolution, that may save some other
poor soul from frustration....

Best
Erick

On Wed, Apr 29, 2009 at 9:31 AM, Ci-man <ci...@yahoo.com> wrote:

>
> Found the problem.
> It is with Microsoft jdbc drivers (jdbc 2.0).
>
> With the latest download Microsoft provides two .jar files:
> sqljdbc.jar
> sqljdbc4.jar
>
> I had copied both into the lib directory. By doing so it used the older
> drivers (sqljdbc.jar) which do not work with jvm1.6. You get this kind of
> cryptic message in debug trace:
>
> The JDBC Driver version 2.0 does not support JRE 1.4. You must upgrade JRE
> 1.4 to JRE 5.0 or later when using the JDBC Driver version 2.0. In some
> cases, you might need to recompile your application because it might not be
> compatible with JDK 5.0 or later. For more information, see the
> documentation on Sun Microsystems Web site
>
> No further help from MS or boards. I experimented and removed the
> sqljdbc.jar file from lib directory so that only the sqljdbc4.jar is
> available. And bingo. Everything is working like a charm.
>
> Thanks everyone,
> -Ci
>
> --
> View this message in context:
> http://www.nabble.com/Unable-to-import-data-from-database-tp23283852p23295866.html
> Sent from the Solr - User mailing list archive at Nabble.com.
>
>

Re: Unable to import data from database

Posted by Ci-man <ci...@yahoo.com>.
Found the problem. 
It is with Microsoft jdbc drivers (jdbc 2.0).

With the latest download Microsoft provides two .jar files:
sqljdbc.jar
sqljdbc4.jar

I had copied both into the lib directory. By doing so it used the older
drivers (sqljdbc.jar) which do not work with jvm1.6. You get this kind of
cryptic message in debug trace:

The JDBC Driver version 2.0 does not support JRE 1.4. You must upgrade JRE
1.4 to JRE 5.0 or later when using the JDBC Driver version 2.0. In some
cases, you might need to recompile your application because it might not be
compatible with JDK 5.0 or later. For more information, see the
documentation on Sun Microsystems Web site

No further help from MS or boards. I experimented and removed the
sqljdbc.jar file from lib directory so that only the sqljdbc4.jar is
available. And bingo. Everything is working like a charm.

Thanks everyone,
-Ci

-- 
View this message in context: http://www.nabble.com/Unable-to-import-data-from-database-tp23283852p23295866.html
Sent from the Solr - User mailing list archive at Nabble.com.

Re: Unable to import data from database

Posted by Ci-man <ci...@yahoo.com>.
Thanks.
I found the interactive debugger .

solr/admin/dataimport.jsp

and I am seeing exceptions in Java that I can dig into
-- 
View this message in context: http://www.nabble.com/Unable-to-import-data-from-database-tp23283852p23295859.html
Sent from the Solr - User mailing list archive at Nabble.com.

Re: Unable to import data from database

Posted by Noble Paul നോബിള്‍ नोब्ळ् <no...@gmail.com>.
forgot to mention DIH has a debug mode

2009/4/29 Noble Paul നോബിള്‍  नोब्ळ् <no...@gmail.com>:
> just put it on debugger and you will know if the query is indeed
> fetching any rows
>
> On Wed, Apr 29, 2009 at 2:59 AM, Ci-man <ci...@yahoo.com> wrote:
>>
>> Thanks for your question.
>> Yes all the fields are defined in the schema; I am using the default schema
>> and mapping between the DB fields and schema fields.
>>
>> My biggest problem is that it appears that no records are being pulled as
>> "Total Rows Fetched" always zero.
>>
>>
>>
>>
>> Did you define all the fields that you used in schema.xml?
>>
>>
>> --
>> View this message in context: http://www.nabble.com/Unable-to-import-data-from-database-tp23283852p23286437.html
>> Sent from the Solr - User mailing list archive at Nabble.com.
>>
>>
>
>
>
> --
> --Noble Paul
>



-- 
--Noble Paul

Re: Unable to import data from database

Posted by Noble Paul നോബിള്‍ नोब्ळ् <no...@gmail.com>.
just put it on debugger and you will know if the query is indeed
fetching any rows

On Wed, Apr 29, 2009 at 2:59 AM, Ci-man <ci...@yahoo.com> wrote:
>
> Thanks for your question.
> Yes all the fields are defined in the schema; I am using the default schema
> and mapping between the DB fields and schema fields.
>
> My biggest problem is that it appears that no records are being pulled as
> "Total Rows Fetched" always zero.
>
>
>
>
> Did you define all the fields that you used in schema.xml?
>
>
> --
> View this message in context: http://www.nabble.com/Unable-to-import-data-from-database-tp23283852p23286437.html
> Sent from the Solr - User mailing list archive at Nabble.com.
>
>



-- 
--Noble Paul

Re: Unable to import data from database

Posted by Ci-man <ci...@yahoo.com>.
Thanks for your question.
Yes all the fields are defined in the schema; I am using the default schema
and mapping between the DB fields and schema fields.

My biggest problem is that it appears that no records are being pulled as
"Total Rows Fetched" always zero.




Did you define all the fields that you used in schema.xml?


-- 
View this message in context: http://www.nabble.com/Unable-to-import-data-from-database-tp23283852p23286437.html
Sent from the Solr - User mailing list archive at Nabble.com.

Re: Unable to import data from database

Posted by ahammad <ah...@gmail.com>.
Did you define all the fields that you used in schema.xml?



Ci-man wrote:
> 
> I am using MS SQL server and want to index a table.
> I setup my data-config like this:
> 
> <dataConfig>
> 	<dataSource type="JdbcDataSource" batchSize="25000" 
> 		autoCommit="true" 
> 		driver="com.microsoft.sqlserver.jdbc.SQLServerDriver" 
> 		url="jdbc:sqlserver://localhost:1433;databaseName=MYDB" 
> 		user="****" password="****"/>
> 
>     <document name="products">
>         <entity name="item" query="select TOP 50 * from items">
>             <field column="item_id" name="id" />
>             <field column="itemname" name="name" />
>             <field column="itemavgbucost" name="price" />
> 	    <field column="categoryname" name="cat" />            
>             <field column="itemdesc" name="features"  />                
>         </entity>
>     </document>
> </dataConfig>
> 
> 
> I am unable to load data from database. I always receive 0 document
> fetched:
> <lst name="statusMessages">
> <str name="Time Elapsed">0:0:12.989</str>
> <str name="Total Requests made to DataSource">1</str>
> <str name="Total Rows Fetched">0</str>
> <str name="Total Documents Processed">0</str>
> <str name="Total Documents Skipped">0</str>
> <str name="Full Dump Started">2009-04-28 14:37:49</str>
> </lst>
> 
> The query runs in SQL Server query manager and retrieves records. The
> funny thing is, even if I purposefully write a wrong query with
> non-existing tables I get the same response. What am I doing wrong? How
> can I tell whether a query fails or succeeds or if solr is running the
> query in the first place?
> 
> Any help is appreciated.
> Best,
> -Ci 
> 
> 
> 

-- 
View this message in context: http://www.nabble.com/Unable-to-import-data-from-database-tp23283852p23284381.html
Sent from the Solr - User mailing list archive at Nabble.com.