You need to sign in to do that
Don't have an account?
![castles castles](https://dfc-org-production.my.site.com/img/userprofile/default_profile_45_v2.png)
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.
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.
but it does look like your config file does not have a password in it.
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
perhaps someone more expert in this tool will jump in.
Castles,
Did you ever solve this issue, this is exactly where i am stuck!!
please help!
Dave
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
Brian,
thanks for the reply,
i will press forward and hopefully find a solution!
thanks
Dave
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
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.
SQL parameter: Id
I am getting the same issue.
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
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
earlier.
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.
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.
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
From Process-conf.xml
From Database-config.xml SDL File
Thanks.
Dan