+ Start a Discussion
Jerun JoseJerun Jose 

Command Line Data Loader with MS Access - Invalid Fetch Size error

Hi,

 

I am trying to pull data from MS Access DB and insert into Salesforce. I was having trouble in getting the configuration done.

 

I tried to make it very simple, and created a database TestDBForCLI.mdb with one table TestTable. This table has only two columns and currently only 1 row of data. I am still getting the error message:

  Sql error: Invalid Fetch Size.

 

Process-conf.xml

<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
    <bean id="AccessPullProcess"
          class="com.salesforce.dataloader.process.ProcessRunner"
          singleton="false">
        <description>AccountMaster job gets the Customer record updates from ERP (Oracle financials) and uploads them to salesforce using 'upsert'.</description>
        <property name="name" value="AccessPullProcess"/>
        <property name="configOverrideMap">
            <map>
	<entry key="sfdc.debugMessages" value="true"/>
                <entry key="sfdc.debugMessagesFile" value="c:\dataloader\samples\status\accountMasterSoapTrace.log"/>
                <entry key="sfdc.endpoint" value="https://login.salesforce.com"/>
                <entry key="sfdc.username" value="myUserName"/>
                <entry key="sfdc.password" value="myPassword"/>
	<entry key="sfdc.proxyHost" value="myProxy"/>
        	<entry key="sfdc.proxyPort" value="8000"/>
	<entry key="sfdc.timeoutSecs" value="600"/>
                <entry key="sfdc.loadBatchSize" value="200"/>
                <entry key="sfdc.entity" value="Account"/>
                <entry key="process.operation" value="insert"/>
                <entry key="process.mappingFile" value="C:\Documents and Settings\jerun\Desktop\Developer\DataLoader\Jerun\AccMap.sdl"/>
	<entry key="dataAccess.name" value="queryAcc"/>
                <entry key="dataAccess.type" value="databaseRead"/>
                <entry key="process.initialLastRunDate" value="2005-12-01T00:00:00.000-0800"/>
            </map>
        </property>
    </bean>
</beans>

 

Database-conf.xml

<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
	<bean id="queryAcc" class="com.salesforce.dataloader.dao.database.DatabaseConfig" singleton="true">
		<property name="sqlConfig" ref="AccountPull"/>
		<property name="dataSource" ref="connectionParams"/>
	</bean>
	<bean id="connectionParams" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
		<property name="driverClassName" value="sun.jdbc.odbc.JdbcOdbcDriver"/>
		<property name="url" value="jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\Documents and Settings\jerun\Desktop\Developer\DataLoader\Jerun\TestDBForCLI.mdb"/>
		<property name="username" value=""/>
		<property name="password" value=""/>
		<property name="defaultAutoCommit" value="true"/>
	</bean>
	<bean id="AccountPull" class="com.salesforce.dataloader.dao.database.SqlConfig" singleton="true">
		<property name="sqlString">
			<value>select MyName from TestTable</value>
		</property>
		<property name="columnNames">
			<list>
        		<value>MyName</value>				
			</list>
		</property>
	</bean>
</beans>

 

And I got the error message as :

 

2011-09-26 21:25:09,734 ERROR [AccessPullProcess] database.DatabaseReader setupQuery (DatabaseReader.java:146) - Database error encountered during setup of the database configuration: queryAcc.  Sql error: Invalid Fetch Size.
java.sql.SQLException: Invalid Fetch Size
    at sun.jdbc.odbc.JdbcOdbcStatement.setFetchSize(JdbcOdbcStatement.java:826)
    at org.apache.commons.dbcp.DelegatingStatement.setFetchSize(DelegatingStatement.java:276)
    at com.salesforce.dataloader.dao.database.DatabaseReader.setupQuery(DatabaseReader.java:140)
    at com.salesforce.dataloader.dao.database.DatabaseReader.open(DatabaseReader.java:109)
    at com.salesforce.dataloader.dao.database.DatabaseReader.open(DatabaseReader.java:97)
    at com.salesforce.dataloader.util.DAORowUtil.calculateTotalRows(DAORowUtil.java:59)
    at com.salesforce.dataloader.dao.database.DatabaseReader.getTotalRows(DatabaseReader.java:209)
    at com.salesforce.dataloader.action.AbstractLoadAction.<init>(AbstractLoadAction.java:82)
    at com.salesforce.dataloader.action.BasePartnerLoadAction.<init>(BasePartnerLoadAction.java:44)
    at com.salesforce.dataloader.action.InsertAction.<init>(InsertAction.java:44)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:494)
    at com.salesforce.dataloader.action.OperationInfo.instantiateAction(OperationInfo.java:66)
    at com.salesforce.dataloader.controller.Controller.executeAction(Controller.java:104)
    at com.salesforce.dataloader.process.ProcessRunner.run(ProcessRunner.java:124)
    at com.salesforce.dataloader.process.ProcessRunner.run(ProcessRunner.java:75)
    at com.salesforce.dataloader.process.ProcessRunner.main(ProcessRunner.java:227)
2011-09-26 21:25:09,750 ERROR [AccessPullProcess] util.DAORowUtil calculateTotalRows (DAORowUtil.java:72) - Error Calculating Total Rows
com.salesforce.dataloader.exception.DataAccessObjectInitializationException: Database error encountered during setup of the database configuration: queryAcc.  Sql error: Invalid Fetch Size.
    at com.salesforce.dataloader.dao.database.DatabaseReader.setupQuery(DatabaseReader.java:148)
    at com.salesforce.dataloader.dao.database.DatabaseReader.open(DatabaseReader.java:109)
    at com.salesforce.dataloader.dao.database.DatabaseReader.open(DatabaseReader.java:97)
    at com.salesforce.dataloader.util.DAORowUtil.calculateTotalRows(DAORowUtil.java:59)
    at com.salesforce.dataloader.dao.database.DatabaseReader.getTotalRows(DatabaseReader.java:209)
    at com.salesforce.dataloader.action.AbstractLoadAction.<init>(AbstractLoadAction.java:82)
    at com.salesforce.dataloader.action.BasePartnerLoadAction.<init>(BasePartnerLoadAction.java:44)
    at com.salesforce.dataloader.action.InsertAction.<init>(InsertAction.java:44)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:494)
    at com.salesforce.dataloader.action.OperationInfo.instantiateAction(OperationInfo.java:66)
    at com.salesforce.dataloader.controller.Controller.executeAction(Controller.java:104)
    at com.salesforce.dataloader.process.ProcessRunner.run(ProcessRunner.java:124)
    at com.salesforce.dataloader.process.ProcessRunner.run(ProcessRunner.java:75)
    at com.salesforce.dataloader.process.ProcessRunner.main(ProcessRunner.java:227)
Caused by: java.sql.SQLException: Invalid Fetch Size
    at sun.jdbc.odbc.JdbcOdbcStatement.setFetchSize(JdbcOdbcStatement.java:826)
    at org.apache.commons.dbcp.DelegatingStatement.setFetchSize(DelegatingStatement.java:276)
    at com.salesforce.dataloader.dao.database.DatabaseReader.setupQuery(DatabaseReader.java:140)
    ... 16 more

 

Any pointers on how to fix this ? Does anybody have a working configuration file for MS Access data extraction ?

 

Regards,

 

Jerun Jose

RixyncsRixyncs

Hi Jerun Jose

 

I am also getting the same error...if you find the solution please let me know

 

 

 

thanks,

thirupathi pattipaka

sfdc deve 401 certified

 

 

DuffAlthausDuffAlthaus

Hi Jerun, 
Were you able to resolve the Invalid Fetch size issue?  I'm trying to connect to SQL Server through ODBC and getting this same error.
Thanks,
Duff
 

Jerun JoseJerun Jose
I still havent managed to crack this one, but I have been able to connect to SQL server in the past. I had used a thin driver for the same. The database.conf xml file had configuration bean as below

<bean id="SQLServerOracledbDataSource"
		  class="org.apache.commons.dbcp.BasicDataSource"
		  destroy-method="close">
		<property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
		<property name="url" value="jdbc:sqlserver://Localhost:1433;databaseName=TypeTheSQLServerDBName;"/>
		<property name="username" value="MyDBUsernameInPlainText"/>
		<property name="password" value="MyDBPasswordInPlainText"/>
	</bean>
	<bean id="SQLServerInsertAccount"
		  class="com.salesforce.lexiloader.dao.database.DatabaseConfig"
		  singleton="true">
		<property name="sqlConfig" ref="OracleQueryAccountSqlServer"/>
		<property name="dataSource" ref="SQLServerOracledbDataSource"/>
	</bean>

Hope it helps.