0

I have the following code which runs a query against a SQL Server DB. I've read these links

http://jenikya.com/blog/2009/02/sqlexception-select-top-number.html

MS SQL Exception: Incorrect syntax near '@P0'

but i still can't see where/why i'm getting the '@P0' issue. I've wrapped the TOP parameter in brackets.

private String DEP_AMC_QUERY = "SELECT TOP(1) ((fund_amc-reinsurance_premium)/1000)

as dep_amc "+ "FROM Hedging_Staging.dbo.:table "+ "WHERE internal_fund_code_identifier=:fund "+ "AND load_id=:load_id;";

public BigDecimal getDepAmcValue(String fund,Long load_id,String table){

    NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(getDataSource());

    //Set up parameters
    MapSqlParameterSource namedParameters = new MapSqlParameterSource("fund",fund);
    namedParameters.addValue("load_id",load_id);
    namedParameters.addValue("table",table);
    MapUtils.debugPrint(System.out,"params", namedParameters.getValues());

    //Execute query
    return jdbcTemplate.queryForObject(DEP_AMC_QUERY,namedParameters,BigDecimal.class);
}

The console and exception message is

13:11:12,871 INFO  [ReinsuredFundAssetProcessor] looking up dep_amc value for AXX in AI_IFL_Policy table.

params = 
{
    table = AI_IFL_Policy java.lang.String
    fund = AXX java.lang.String
    load_id = 4356 java.lang.Long
} 
13:11:12,909 ERROR [AbstractStep] Encountered an error executing the step
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select top(1) ((fund_amc-reinsurance_premium)/1000) as dep_amc from Hedging_Staging.dbo.? WHERE internal_fund_code_identifier=? AND load_id=?;]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'.
    at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:98)

Any ideas?

Community
  • 1
  • 1
emeraldjava
  • 10,894
  • 26
  • 97
  • 170
  • 1
    Placeholders are only allowed in the where clause, not for table names. Hence the first ? (`table` in your case) isn't going to be replaced by the actual underlying prepared statement. – M. Deinum Apr 26 '14 at 12:24
  • guess i'll have to refactor to use a StringBuilder/jdbc query that isn't backed by a prepared statement. – emeraldjava Apr 26 '14 at 12:35
  • No, you could replace the `:table` yourself and pass the resulting sql into a `(NamedParameter)JdbcTemplate`. – M. Deinum Apr 26 '14 at 12:37

0 Answers0