Sign Up ›
  • PSS_BSB
  • NEWBIE
  • 25 Points
  • Member since 2009
  • Chatter
    Feed
  • 1
    Best Answers
  • 0
    Likes Received
  • 0
    Likes Given
  • 1
    Questions
  • 5
    Replies
SOLVED
3 replies

I need an example of an Update from the command line Dataloader.

Hello,

 

I already have a process for extracting salesforce tables into SQL nightly. I am now trying to update salesforce from my SQL database and I can't find a single example.

 

My latest test attempt looks like this:

 

database-conf.xml

 

<bean id="databaseUpdateVOD"
      class="com.salesforce.lexiloader.dao.database.DatabaseConfig"
      singleton="true">
    <property name="sqlConfig" ref="databaseUpdateVODSql"/>
    <property name="dataSource" ref="dbDataSource"/>
</bean>

<bean id="databaseUpdateVODSql"
      class="com.salesforce.lexiloader.dao.database.SqlConfig" singleton="true">
    <property name="sqlString">
        <value>
            SELECT Id, Credentials_vod__c from Biovail_Salesforce_Com..Account 

        </value>
    </property>
    <property name="sqlParams">
        <map>
<entry key="Id"   value="java.lang.String"/>
<entry key="Credentials_vod__c"   value="java.lang.String"/>
        </map>
    </property>
</bean>

 

process-conf.xml

 

    <bean id="databaseUpdateVODProcess"
          class="com.salesforce.lexiloader.process.ProcessRunner"
          singleton="false">
        <description>databaseUpdateVOD job updates account info into salesforce info from database."</description>
        <property name="name" value="databaseUpdateVOD"/>
        <property name="configOverrideMap">
            <map>
                <entry key="sfdc.debugMessages" value="false"/>
                <entry key="sfdc.timeoutSecs" value="600"/>
                <entry key="sfdc.loadBatchSize" value="200"/>
                <entry key="sfdc.entity" value="Account"/>
                <entry key="sfdc.externalIdField" value="Id"/>
                <entry key="sfdc.extractionRequestSize" value="500"/>
                <entry key="process.operation" value="update"/>
                <entry key="process.mappingFile" value="C:\VOD\accountExtractMap.sdl"/>
                <entry key="process.outputError" value="C:\VOD\accountErrors.csv"/>
                <entry key="process.outputSuccess" value="C:\VOD\accountSuccess.csv"/>
                <entry key="dataAccess.type" value="databaseRead"/>
                <entry key="dataAccess.name" value="databaseUpdateVOD"/>
            </map>
        </property>
    </bean>

 

What am I missing?

SOLVED
2 replies

Data Loader issue while trying to connect with sandbox

I am trying to connect Data Loader9 with sandbox environment. But the user id and password doesnt work. The same credential works fine to access the sandboz env.

The DataLoader connects fine with production and I am able to export data from it.

 

I have raised a ticket with salesforce but I am still waiting for their response.


Appreciate any help.

 

 

 

SOLVED
3 replies

I need an example of an Update from the command line Dataloader.

Hello,

 

I already have a process for extracting salesforce tables into SQL nightly. I am now trying to update salesforce from my SQL database and I can't find a single example.

 

My latest test attempt looks like this:

 

database-conf.xml

 

<bean id="databaseUpdateVOD"
      class="com.salesforce.lexiloader.dao.database.DatabaseConfig"
      singleton="true">
    <property name="sqlConfig" ref="databaseUpdateVODSql"/>
    <property name="dataSource" ref="dbDataSource"/>
</bean>

<bean id="databaseUpdateVODSql"
      class="com.salesforce.lexiloader.dao.database.SqlConfig" singleton="true">
    <property name="sqlString">
        <value>
            SELECT Id, Credentials_vod__c from Biovail_Salesforce_Com..Account 

        </value>
    </property>
    <property name="sqlParams">
        <map>
<entry key="Id"   value="java.lang.String"/>
<entry key="Credentials_vod__c"   value="java.lang.String"/>
        </map>
    </property>
</bean>

 

process-conf.xml

 

    <bean id="databaseUpdateVODProcess"
          class="com.salesforce.lexiloader.process.ProcessRunner"
          singleton="false">
        <description>databaseUpdateVOD job updates account info into salesforce info from database."</description>
        <property name="name" value="databaseUpdateVOD"/>
        <property name="configOverrideMap">
            <map>
                <entry key="sfdc.debugMessages" value="false"/>
                <entry key="sfdc.timeoutSecs" value="600"/>
                <entry key="sfdc.loadBatchSize" value="200"/>
                <entry key="sfdc.entity" value="Account"/>
                <entry key="sfdc.externalIdField" value="Id"/>
                <entry key="sfdc.extractionRequestSize" value="500"/>
                <entry key="process.operation" value="update"/>
                <entry key="process.mappingFile" value="C:\VOD\accountExtractMap.sdl"/>
                <entry key="process.outputError" value="C:\VOD\accountErrors.csv"/>
                <entry key="process.outputSuccess" value="C:\VOD\accountSuccess.csv"/>
                <entry key="dataAccess.type" value="databaseRead"/>
                <entry key="dataAccess.name" value="databaseUpdateVOD"/>
            </map>
        </property>
    </bean>

 

What am I missing?

SOLVED
2 replies

No such column 'Id' on entity

We have a customer using our packaged app and we keep getting the exception message:

 



Visualforce Page: /apex/tw__ApexPage


System.Exception: No such column 'Id' on entity 'ns__CustomObject__c'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.


Class.ns.MyController.refreshList: line 1473, column 24

Class.ns.MyController.ControllerStart: line 212, column 9

Class.ns.MyController.<init>: line 171, column 13

Class.ns.MyExtension.<init>: line 16, column 9

External entry point

 

This is a VF page that gets added to the Contact page layout. It calls a method refreshList in the constructor that has a simple soql query.

 

So I can get rid of the error by not calling that soql query in the constructor. By why in the world would it say No such column Id?? Doesn't everything have an Id column?

 

It's only one customer (and one user) that hit this so I'm curious what they did. Any thoughts?

 

Thanks,

DSL

 

  • April 28, 2009
  • Like
  • 0
17 replies

Data Loader Error getting value for SQL parameter

Hi, I'm getting this error when trying to run databaseAccountExtractProcess bean.  I've included the process-conf.xml and database-conf.xml in the source.  Would appreciate your help to fix this.Code:
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
<bean id="dbDataSource"
      class="org.apache.commons.dbcp.BasicDataSource"
      destroy-method="close">
    <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
    <property name="url" value="jdbc:oracle:thin:@BTCFINAPORA2:1521:FINprod"/>
    <property name="username" value="brian"/>
    <property name="password" value="brian"/>
</bean>
<bean id="queryAccountAll"
      class="com.salesforce.lexiloader.dao.database.DatabaseConfig"
      singleton="true">
    <property name="sqlConfig" ref="queryAccountAllSql"/>
    <property name="dataSource" ref="dbDataSource"/>
</bean>
<bean id="queryAccount"
      class="com.salesforce.lexiloader.dao.database.DatabaseConfig"
      singleton="true">
    <property name="sqlConfig" ref="queryAccountSql"/>
    <property name="dataSource" ref="dbDataSource"/>
</bean>
<bean id="insertAccount"
      class="com.salesforce.lexiloader.dao.database.DatabaseConfig"
      singleton="true">
    <property name="sqlConfig" ref="insertAccountSql"/>
    <property name="dataSource" ref="dbDataSource"/>
</bean>
<bean id="updateAccount"
      class="com.salesforce.lexiloader.dao.database.DatabaseConfig"
      singleton="true">
    <property name="sqlConfig" ref="updateAccountSql"/>
    <property name="dataSource" ref="dbDataSource"/>
</bean>
<bean id="deleteAccountAll"
      class="com.salesforce.lexiloader.dao.database.DatabaseConfig"
      singleton="true">
    <property name="sqlConfig" ref="deleteAccountAllSql"/>
    <property name="dataSource" ref="dbDataSource"/>
</bean>
<bean id="queryAccountAllSql"
      class="com.salesforce.lexiloader.dao.database.SqlConfig" singleton="true">
    <property name="sqlString">
        <value>
            SELECT ACCOUNT_NAME, BUSINESS_PHONE, SFDC_ACCOUNT_ID, ACCOUNT_EXT_ID, ANNUAL_REVENUE, LAST_UPDATED, ACCOUNT_NUMBER
            FROM TableOwner.Accounts
        </value>
    </property>
    <property name="columnNames">
        <list>
            <value>account_name</value>
            <value>business_phone</value>
            <value>sfdc_account_id</value>
            <value>account_ext_id</value>
            <value>annual_revenue</value>
            <value>last_updated</value>
            <value>account_number</value>
        </list>
    </property>
</bean>
<bean id="queryAccountSql"
      class="com.salesforce.lexiloader.dao.database.SqlConfig" singleton="true">
    <property name="sqlString">
        <value>
            SELECT ACCOUNT_NAME, BUSINESS_PHONE, ACCOUNT_EXT_ID, ANNUAL_REVENUE, LAST_UPDATED, ACCOUNT_NUMBER
            FROM TableOwner.Accounts
            WHERE LAST_UPDATED > @process.lastRunDate@
        </value>
    </property>
    <property name="columnNames">
        <list>
            <value>account_name</value>
            <value>business_phone</value>
            <value>account_ext_id</value>
            <value>annual_revenue</value>
            <value>last_updated</value>
            <value>account_number</value>
        </list>
    </property>
    <property name="sqlParams">
        <map>
            <entry key="process.lastRunDate" value="java.sql.Timestamp"/>
        </map>
    </property>
</bean>
<!--- this is for updating Customers in Oracle based on SFDC Accounts -->
<bean id="updateAccountSql"
      class="com.salesforce.lexiloader.dao.database.SqlConfig" singleton="true">
    <property name="sqlString">
        <value>
            update TableOwner.Accounts accounts
               set accounts.account_name = @account_name@,
                   accounts.business_phone = @business_phone@,
                   accounts.sfdc_account_id = @sfdc_account_id@,
                   accounts.annual_revenue = @annual_revenue@,
                   accounts.account_number = @account_number@
            where
                   accounts.ACCOUNT_EXT_ID = @account_ext_id@
        </value>
    </property>
    <property name="sqlParams">
        <map>
            <entry key="account_name"    value="java.lang.String"/>
            <entry key="business_phone"  value="java.lang.String"/>
            <entry key="sfdc_account_id" value="java.lang.String"/>
            <entry key="annual_revenue"  value="java.lang.Double"/>
            <entry key="account_ext_id"  value="java.lang.String"/>
            <entry key="account_number"  value="java.lang.String"/>
        </map>
    </property>
</bean>
<bean id="XXXinsertAccountSql"
      class="com.salesforce.lexiloader.dao.database.SqlConfig" singleton="true">
    <property name="sqlString">
        <value>
            INSERT INTO TableOwner.Accounts (
               ACCOUNT_NAME, BUSINESS_PHONE, SFDC_ACCOUNT_ID, ANNUAL_REVENUE, ACCOUNT_EXT_ID, ACCOUNT_NUMBER)
            VALUES (@account_name@, @business_phone@, @sfdc_account_id@, @annual_revenue@, @account_ext_id@, @account_number@)
        </value>
    </property>
    <property name="sqlParams">
        <map>
            <entry key="account_name"    value="java.lang.String"/>
            <entry key="business_phone"  value="java.lang.String"/>
            <entry key="sfdc_account_id" value="java.lang.String"/>
            <entry key="annual_revenue"  value="java.lang.Double"/>
            <entry key="account_ext_id"  value="java.lang.String"/>
            <entry key="account_number"  value="java.lang.String"/>
        </map>
    </property>
</bean>
<bean id="insertAccountSql"
      class="com.salesforce.lexiloader.dao.database.SqlConfig" singleton="true">
    <property name="sqlString">
        <value>
            INSERT INTO brian.sfdc_de_account (
               ID)
            VALUES (@Id@)
        </value>
    </property>
    <property name="sqlParams">
        <map>
            <entry key="Id" value="java.lang.String"/>
        </map>
    </property>
</bean>
<bean id="deleteAccountAllSql"
      class="com.salesforce.lexiloader.dao.database.SqlConfig" singleton="true">
    <property name="sqlString">
        <value>
            DELETE FROM TableOwner.Accounts
        </value>
    </property>
</bean>
</beans>

 
 
2007-04-02 11:35:10,953 INFO  [main] process.ProcessConfig getBeanFactory (ProcessConfig.java:78) - Loading process configuration from config file: C:\Program Files\salesforce.com\AppExchange Data Loader 8.0\bin\..\Conf_account\process-conf.xml
2007-04-02 11:35:11,031 INFO  [main] xml.XmlBeanDefinitionReader loadBeanDefinitions (XmlBeanDefinitionReader.java:163) - Loading XML bean definitions from file [C:\Program Files\salesforce.com\AppExchange Data Loader 8.0\bin\..\Conf_account\process-conf.xml]
2007-04-02 11:35:11,078 INFO  [main] core.CollectionFactory <clinit> (CollectionFactory.java:66) - JDK 1.4+ collections available
2007-04-02 11:35:11,093 INFO  [main] core.CollectionFactory <clinit> (CollectionFactory.java:71) - Commons Collections 3.x available
2007-04-02 11:35:11,187 INFO  [databaseAccountExtract] controller.Controller initConfig (Controller.java:350) - The controller config has been initialized
2007-04-02 11:35:11,203 INFO  [databaseAccountExtract] process.ProcessRunner run (ProcessRunner.java:102) - Initializing process engine
2007-04-02 11:35:11,203 INFO  [databaseAccountExtract] process.ProcessRunner run (ProcessRunner.java:105) - Loading parameters
2007-04-02 11:35:12,390 INFO  [databaseAccountExtract] process.ProcessRunner run (ProcessRunner.java:116) - Logging in to: https://www.salesforce.com
2007-04-02 11:35:13,296 INFO  [databaseAccountExtract] dao.DataAccessObjectFactory getDaoInstance (DataAccessObjectFactory.java:51) - Instantiating data access object: insertAccount of type: databaseWrite
2007-04-02 11:35:13,312 INFO  [databaseAccountExtract] xml.XmlBeanDefinitionReader loadBeanDefinitions (XmlBeanDefinitionReader.java:163) - Loading XML bean definitions from file [C:\Program Files\salesforce.com\AppExchange Data Loader 8.0\bin\..\Conf_account\database-conf.xml]
2007-04-02 11:35:13,375 INFO  [databaseAccountExtract] process.ProcessRunner run (ProcessRunner.java:121) - Checking the data access object connection
2007-04-02 11:35:14,187 INFO  [databaseAccountExtract] process.ProcessRunner run (ProcessRunner.java:126) - Setting field types
2007-04-02 11:35:15,359 INFO  [databaseAccountExtract] process.ProcessRunner run (ProcessRunner.java:130) - Setting object reference types
2007-04-02 11:35:23,312 INFO  [databaseAccountExtract] process.ProcessRunner run (ProcessRunner.java:134) - Creating Map
2007-04-02 11:35:23,312 INFO  [databaseAccountExtract] action.ActionFactory getActionInstance (ActionFactory.java:64) - Instantiating operation: extract
2007-04-02 11:35:23,328 INFO  [databaseAccountExtract] controller.Controller executeAction (Controller.java:130) - executing operation: extract
2007-04-02 11:35:27,343 FATAL [databaseAccountExtract] database.DatabaseContext setSqlParamValues (DatabaseContext.java:183) - Error getting value for SQL parameter: Id.  Please make sure that the value exists in the configuration file or is passed in.  Database configuration: insertAccount.
2007-04-02 11:35:27,343 INFO  [databaseAccountExtract] progress.NihilistProgressAdapter setSubTask (NihilistProgressAdapter.java:68) - Processed 500 of 126019 total records. Rate: 120000000 records per hour. Estimated time to complete: 0 minutes and 0 seconds.  There are 0 successes and 500 errors.
2007-04-02 11:35:28,343 FATAL [databaseAccountExtract] database.DatabaseContext setSqlParamValues (DatabaseContext.java:183) - Error getting value for SQL parameter: Id.  Please make sure that the value exists in the configuration file or is passed in.  Database configuration: insertAccount.