function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
DaveIngramDaveIngram 

Dataloader to SQL database error Cannot pass NULL HELP!

Hi All,
 
i think i posted this somewhere else but i cant find it!
 
I have successfully conntecd our SQL server to salesforce.com using hte dataloader. it works great sending all the data unless on field is blank (null) then the whole job crashes;
 
this is the error i am getting:
Code:
(DatabaseContext.java:204) - Error creating PreparedStatement for the database configuration insertHangtagOrder. Error replacing parameter: FirstName with value: null of type: null. Sql error: [Microsoft][SQLServer 2000 Driver for JDBC]The specified SQL type is not supported by this driver.. 
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]The specified SQL type is not supported by this driver. 
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source) 
at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source) 
at com.microsoft.jdbc.base.BasePreparedStatement.validateSqlType(Unknown Source) 
at com.microsoft.jdbc.base.BasePreparedStatement.setObjectInternal(Unknown Source) 
at com.microsoft.jdbc.base.BasePreparedStatement.setObject(Unknown Source) 
at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:165) 
at com.salesforce.lexiloader.dao.database.DatabaseContext.setSqlParamValues(DatabaseContext.java:197) 
at com.salesforce.lexiloader.dao.database.DatabaseWriter.writeRowList(DatabaseWriter.java:143) 
at com.salesforce.lexiloader.action.visitor.QueryVisitor.writeExtraction(QueryVisitor.java:185) 
at com.salesforce.lexiloader.action.visitor.QueryVisitor.visit(QueryVisitor.java:111) 
at com.salesforce.lexiloader.action.ExtractAction.execute(ExtractAction.java:108) 
at com.salesforce.lexiloader.controller.Controller.executeAction(Controller.java:126) 
at com.salesforce.lexiloader.process.ProcessRunner.run(ProcessRunner.java:136) 
at com.salesforce.lexiloader.process.ProcessRunner.main(ProcessRunner.java:228)

 
This job works great when all the feilds contain data and crashes every time there is a null.
 
any ideas? Salesforce suppost says they cant help coz its custom code...
 
Thanks

Dave
Best Answer chosen by Admin (Salesforce Developers) 
DiceManDiceMan
I switched from the microsoft driver to the jtds (open source) driver, and now my process is working.
 
Their homepage is http://jtds.sourceforge.net/. Its a type 4 driver, so no installation is neceessary, just make sure you reference the jar file in the classpath. Also their implementation of named instances is a little quirky. Make sure you read the documentation about their connection string format.
 
Hope this helps.
 
DJH

All Answers

mspohnmspohn
Hi Dave,

What version of the Data Loader are you using?

Thanks,
Markus

Markus Spohn
Director Product Management, Integration
Salesforce.com
DaveIngramDaveIngram

Hi,

 

I have tried Apex Dataloader 9.0 and appexcahnge dataloader 8.0 both have the same error..

 

any ideas?

 

Dave
DiceManDiceMan
Just ran into this one myself. I'll post here when (if?) I come up with the solution.
DiceManDiceMan
I switched from the microsoft driver to the jtds (open source) driver, and now my process is working.
 
Their homepage is http://jtds.sourceforge.net/. Its a type 4 driver, so no installation is neceessary, just make sure you reference the jar file in the classpath. Also their implementation of named instances is a little quirky. Make sure you read the documentation about their connection string format.
 
Hope this helps.
 
DJH
This was selected as the best answer
RaphlRaphl

Please,

Could you send me both files: process-conf.xml and database-conf.xml?

It would be very helpful to continue with my installation.

Thanks a lot and regards,

Rafael Velasco

 
DiceManDiceMan

This is kind of a bare minimum template which should work.

Process.conf

Code:

<bean id="TestExtractToDb" class="com.salesforce.lexiloader.process.ProcessRunner" singleton="false">
 <description>Test direct database connection. Salesforce.com -> My Database</description>
 <property name="name" value="Test extract" />
 <property name="configOverrideMap">
  <map>
   <entry key="sfdc.entity" value="Account" />
   <entry key="process.operation" value="extract" />
   <entry key="process.mappingFile" value="Test.sdl" />
   <entry key="dataAccess.name" value="updateCustomer" />
   <entry key="dataAccess.type" value="databaseWrite" />
   <entry key="sfdc.extractionSOQL" value="SELECT Id, SOMEFIELD__c FROM Account" />
  </map>
 </property>
</bean>


 
Test.sdl

Code:

Id=database_id
SOMEFIELD__c=update_field


 
Database.conf

Code:

<bean id="Customer" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
 <property name="driverClassName" value="net.sourceforge.jtds.jdbc.Driver" />
 <property name="url" value="jdbc:jtds:sqlserver://THESERVER:THEPORT/THEDATABASE;instance=THEINSTANCE" />
 <property name="username" value="" />
 <property name="password" value="" />
</bean>
<bean id="updateCustomer" class="com.salesforce.lexiloader.dao.database.DatabaseConfig" singleton="true">
 <property name="sqlConfig" ref="updateCustomerSql" />
 <property name="dataSource" ref="Customer" />
</bean>
<bean id="updateCustomerSql"
     class="com.salesforce.lexiloader.dao.database.SqlConfig" singleton="true">
    <property name="sqlString">
        <value>
UPDATE 
  dbo.THESQLTABLE
SET 
  THESQLCOLUMN = @update_field@
WHERE
  CustomerId = @database_id@
       </value>
</property>


 Don't forget to cross your fingers. Let us know how you get on.
DaveIngramDaveIngram

Thanks Diceman,

 

our process is working perfectly now!! thanks so much for your help!

 

Dave
DiceManDiceMan
Happy to be of service. Let me know if there's anything else I can do.