Don't have an account?

Browse by Topic

+ Start a Discussion

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.

April 2, 2007
·
Answer
·
Like
0
·
Follow
2

castles
Here is the process-conf.xml: Code:
<bean id="databaseAccountExtractProcess" class="com.salesforce.lexiloader.process.ProcessRunner" singleton="false"> <description>DatabaseAccountExtract job gets account info from salesforce and updates or inserts info into database."</description> <property name="name" value="databaseAccountExtract"/> <property name="configOverrideMap"> <map> <entry key="sfdc.debugMessages" value="TRUE"/> <entry key="sfdc.debugMessagesFile" value="h:\prod\daily\salesforce\account\dataloader\sfdcSoapTrace.log"/> <entry key="sfdc.endpoint" value="https://www.salesforce.com"/> <entry key="sfdc.proxyHost" value="proxy.bok.com"/> <entry key="sfdc.proxyPort" value="80"/> <entry key="sfdc.username" value="integration_user@bokf.com"/>  <entry key="sfdc.password" value=""/> <entry key="sfdc.timeoutSecs" value="600"/> <entry key="sfdc.loadBatchSize" value="200"/> <entry key="sfdc.entity" value="Account"/> <entry key="sfdc.extractionRequestSize" value="500"/>  <entry key="sfdc.extractionSOQL" value="Select Id FROM Account"/> <entry key="process.operation" value="extract"/> <entry key="process.mappingFile" value="C:\Program Files\salesforce.com\AppExchange Data Loader 8.0\Conf_Account\accountExtractToDbMap.sdl"/> <entry key="dataAccess.type" value="databaseWrite"/> <entry key="dataAccess.name" value="insertAccount"/> </map> </property> </bean>

April 2, 2007
·
Like
0
·
Dislike
0

Ron Hess
don't know much about the dataloader,
but it does look like your config file does not have a password in it.

April 2, 2007
·
Like
0
·
Dislike
0

castles

Yes. I removed the password. The error is related (somewhere) to the values passed from SF to our RDBMS. Could you look at it again?

The sdl file has:

# SalesforceFieldName=OracleFieldName

id=ID

April 3, 2007
·
Like
0
·
Dislike
0

Ron Hess
sorry, configuring the loader against your RDBMS is beyond my current experience.

perhaps someone more expert in this tool will jump in.

April 4, 2007
·
Like
0
·
Dislike
0

DaveIngram

Castles,

Did you ever solve this issue, this is exactly where i am stuck!!

please help!

Dave

July 10, 2007
·
Like
0
·
Dislike
0

castles

Dave, I never found a resolution to the problem. For now, I'm okay using text files to export and import to and from SalesForce. Best wishes finding a solution.

Brian

July 10, 2007
·
Like
0
·
Dislike
0

DaveIngram

Brian,

thanks for the reply,

i will press forward and hopefully find a solution!

thanks

Dave

July 10, 2007
·
Like
0
·
Dislike
0

DaveIngram

Hi All,

i was able to make this work by correcting the Mapping file to be correct. also all entreis in database-conf are refering to the Server field names not the SFDC field names.

Now i have a problem with null values that causes the process to crash:

2007-07-12 04:17:23,323 ERROR [OrdersSQL_Extract] database.DatabaseContext setSqlParamValues (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)
2007-07-12 04:17:23,354 INFO [OrdersSQL_Extract] progress.NihilistProgressAdapter setSubTask (NihilistProgressAdapter.java:68) - Processed 3 of 3 total records. Rate: 229000 records per hour. Estimated time to complete: 0 minutes and 0 seconds. There are 0 successes and 3 errors.
2007-07-12 04:17:23,354 INFO [OrdersSQL_Extract] progress.NihilistProgressAdapter doneSuccess (NihilistProgressAdapter.java:55) - The extract has fully completed. There were 0 successful extracts and 3 errors.

Any ideas?

Dave

July 12, 2007
·
Like
0
·
Dislike
0

smoovbcaltex
I ran into this error as well while doing some data migration work. As a sanity check I made my extract process as simple as possible, with just one parameter, and I still got the error. The error didn't go away until I changed the name of the parameter in the database-conf.xml and my .sdl mapping file. After I got the simplified version working, I added more parameters and kept trying it out and eventually added all the parameters that were in my original extract process and it worked.

So as far as I can tell here, once you get this "Error getting value for SQL parameter" error for a parameter, you're pretty much doomed to keep getting it until you change the parameter name.

I have little idea why just changing parameter names seems to be a solution to this error; if any one has any ideas I'd be interested to hear them.

August 14, 2008
·
Like
0
·
Dislike
0

PSS_BSB
SQL parameter: Id

I am getting the same issue.

May 5, 2009
·
Like
0
·
Dislike
0

Jeff_Blumenthal
I renamed the field to something else and that worked, but I was not satisfied with that. I found out that in my map file there was a space after the field name. After I removed the space it worked.

HTH

September 7, 2010
·
Like
2
·
Dislike
0

Jerun Jose
I had the same error message when I was trying to extract data from MS Access DB.

Realized that the problem was that I was using the wrong tag. Got my issue fixed by using

        <property name="columnNames">
           <list>
               <value>MyName</value>
           </list>
       </property>
in the SQLConfig bean. I was using

<property name="sqlParams">

earlier.

June 14, 2011
·
Like
0
·
Dislike
0

Merugu Srinivas
Hi ,

Please help me on the below issue

My process and data base confile are ok,since i am able to load other columns into data base

Please suggest if changes i have to make in Process or database conf

[DMSDistributorTeam] database.DatabaseContext setSqlParamValues(DatabaseContext.java:175) - Error getting value for SQL parameter
: EMAIL_NAME. Please make sure that the value exists in the configuration fileor is passed in. Database configuration: InsDMSDistributorTeam.
2012-07-17 11:59:10,315 INFO [DMSDistributorTeam] progress.NihilistProgressAdap
ter doneSuccess (NihilistProgressAdapter.java:60) - The operation has fully comp
leted. There were 0 successful extractions and 20 errors.

July 19, 2012
·
Like
0
·
Dislike
0

GoForceGo
Error getting value for SQL parameter..... Please make sure that the value exists in the configuration file or is passed in.

I was getting this error. Turns out I had empty values for this field in SFDC tables. When I fixed that, it worked...The field was __r.Name and the underlying object was null. Seems like you can't export such fields.

March 18, 2013
·
Like
1
·
Dislike
0

Cilla
For me, the sqlparams names had to match the Oracle DB column names.

October 12, 2015
·
Like
0
·
Dislike
0

Tanumay Das
Hello Dave

I think i might have solved it.There are 2 main things causing this error. Firstly if there is no data in any perticuler SFDC field which is to be inserted in oracle And the second thing is related to mapping.sdl and the sqlParams tag. I got the same problem what you guys have met erlier. And adter fiddeling arround I realized that The right side of the Mapping.sdl file is to be used as the replacement vars.ie. <@var@>. So " Error getting value for SQL parameter: Id. Please make sure that the value exists in the configuration file or is passed in" means That the parameter "Id" is in the right side of the mapping file and the engine is trying to replace the parameter with the extracted value but in param the field name has changed or the mapping has different value(may be id or ID). So the conclution is the mapping file Destination Field , @var@ and the entries in sqlParam must be same(case sensative) to overcome this problem.

Regards
Tanumay

February 9, 2017
·
Like
0
·
Dislike
0

Dan Forest
I am new to sfdc/data loader and having the same issue on the OPP_QTY parameter. I tried renaming the sql param, but that did not solve the issue. Below are the key values from process-conf.xml, database-config.xml and the sdl mapping file. This was all working until I added this additional Opportunity field TotalOpportunityQuantity. If I run the query from the data loader GUI app, it works fine and returns a float value.

From Process-conf.xml
<entry key="sfdc.extractionSOQL" value="Select Account.Id, Account.Name, Id, Name, Opportunity_Number__c, Amount, TotalOpportunityQuantity, CurrencyIsoCode, LastModifiedDate FROM Opportunity Where Name like 'MTKOPC%' And StageName like '6%' And HasOpportunityLineItem = True And LastModifiedDate = LAST_N_DAYS:7 LIMIT 1"/>

From Database-config.xml
<bean id="insertOppHeaderSql" class="com.salesforce.dataloader.dao.database.SqlConfig" singleton="true"> <property name="sqlString"> <value> IF NOT EXISTS(SELECT 1 FROM [dbo].[SFOppHeaderToSLX] WHERE OPP_ID = @opp_id@) INSERT INTO dbo.SFOppHeaderToSLX ( ACCT_ID, ACCT_NAME, OPP_ID, OPP_NAME, OPP_NUM, OPP_AMT, OPP_QTY, OPP_CURRENCY, OPP_DATE) VALUES (@acct_id@, @acct_name@, @opp_id@, @opp_name@, @opp_num@, @opp_amt@, @opp_qty@, @opp_currency@, @opp_date@) </value> </property> <property name="sqlParams"> <map> <entry key="acct_id" value="java.lang.String"/> <entry key="acct_name" value="java.lang.String"/> <entry key="opp_id" value="java.lang.String"/> <entry key="opp_name" value="java.lang.String"/> <entry key="opp_num" value="java.lang.String"/> <entry key="opp_amt" value="java.lang.Float"/> <entry key="opp_qty" value="java.lang.Float"/> <entry key="opp_currency" value="java.lang.String"/> <entry key="opp_date" value="java.lang.String"/> </map> </property> </bean>
SDL File

# Opp Insert Mapping values for query from Salesforce (left) and insert/update to SQL Server (right) # SalesforceFieldName=SQLFieldName Account.Id=acct_id Account.Name=acct_name Id=opp_id Name=opp_name Opportunity_Number__c=opp_num Amount=opp_amt TotalOpportunityQuantity=opp_qty CurrencyIsoCode=opp_currency LastModifiedDate=opp_date
Thanks.

Dan

November 3, 2017
·
Like
0
·
Dislike
0

You need to sign in to do that.

Need an account? Sign Up

Have an account? Sign In

Dismiss

Browse by Topic

Welcome to Support!

Show

sorted by

Data Loader Error getting value for SQL parameter

You need to sign in to do that.