+ Start a Discussion
castlescastles 

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.
castlescastles
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"/>
                <!-- password specified below is invalid, please generate one using the encrypt.bat utility -->
                <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, Name, Type, ParentId, Phone, AccountNumber, Website, Sic, AnnualRevenue, NumberOfEmployees, TickerSymbol, Oracle_Id__c FROM Account"-->
                <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>

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

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

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

perhaps someone more expert in this tool will jump in.

DaveIngramDaveIngram

Castles,

 

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

 

please help!

Dave

castlescastles

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

DaveIngramDaveIngram

Brian,

 

thanks for the reply,

 

i will press forward and hopefully find a solution!

 

thanks


Dave

DaveIngramDaveIngram

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

smoovbcaltexsmoovbcaltex

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.

PSS_BSBPSS_BSB

SQL parameter: Id

 

I am getting the same issue.

Jeff_BlumenthalJeff_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

 

Jerun JoseJerun 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.

 

Merugu SrinivasMerugu 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.

GoForceGoGoForceGo

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.

 

 

 

 

CillaCilla
For me, the sqlparams names had to match the Oracle DB column names.
Tanumay DasTanumay 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
Dan ForestDan 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