MS SQL Exception: Incorrect syntax near '@P0'

I'm querying a DB using MS SQL and for some reason I get the following error: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0' even though this 'P0' isn't anywhere in my syntax...

I've read that someone has had a same issue but they were using a stored proc, something which I am not using so I don't see how his solution will work for me. (His solution being asomething about adding braces {} around the procedure call.

Anyways, below I have pasted the relevant code. Really hope someone can help me with this, getting quite frustrated.

PreparedStatement stmt = null;
Connection conn = null;    

String sqlQuery = "SELECT TOP ? \n"+
                              "z.bankAccountNo, \n"+
                              "z.statementNo, \n"+
                              "z.transactionDate, \n"+
                              "z.description, \n"+
                              "z.amount, \n"+
                              "z.guid \n"+
                              "FROM \n"+
                              "( \n"+
                              "select  \n"+
                              "ROW_NUMBER() OVER (ORDER BY x.transactionDate, x.statementNo) AS RowNumber, \n"+
                              "x.transactionDate, \n"+
                              "x.statementNo, \n"+
                              "x.description, \n"+
                              "x.amount, \n"+
                              "x.bankAccountNo, \n"+
                              "x.guid \n"+
                       开发者_StackOverflow社区       "FROM \n"+
                              "( \n"+
                              "SELECT  \n"+
                              "a.bankAccountNo,  \n"+
                              "a.statementNo,  \n"+
                              "a.transactionDate, \n"+
                              "a.description,  \n"+
                              "a.amount,  \n"+
                              "a.guid  \n"+
                              "FROM BankTransactions as a  \n"+
                              "LEFT OUTER JOIN BankTransactionCategories as b  \n"+
                              "ON a.category = b.categoryCode  \n"+
                              "WHERE b.categoryCode is null \n"+
                              ") as x \n"+
                              ") as z \n"+
                              "WHERE (z.RowNumber >= ?)";

stmt = conn.prepareStatement(sqlQuery);
stmt.setInt(1, RowCountToDisplay);
stmt.setInt(2, StartIndex);
ResultSet rs = null;
try{
    rs = stmt.executeQuery();
} catch (Exception Error){
    System.out.println("Error: "+Error);
}

SQL Server requires you to place parenthesis around the argument to top if you pass in a variable:

SELECT TOP (?)

In our application we have extended a depraceted SQLServerDialect. After change to SQLServer2008Dialect the problem disappeared.

Upgraded hibernate to version 5.x and came across this issue. Had to update "hibernate.dialect" configuration from org.hibernate.dialect.SQLServerDialect to org.hibernate.dialect.SQLServer2012Dialect. Fixed the issue!

Hibernate Doc Reference: https://docs.jboss.org/hibernate/orm/3.6/reference/en-US/html/session-configuration.html#configuration-programmatic

Hibernate Jira issue: https://hibernate.atlassian.net/browse/HHH-10032

It can also be caused by a syntax error in your SQL as was the case for me

select * from drivel d where exists (select * from drivel where d.id = drivel.id and drivel.start_date < '2015-02-05' AND '2015-02-05' < drivel.end_date) OR exists (select * from drivel where d.id = drivel.id and drivel.start_date < '2015-02-05' AND '2015-02-05' < drivel.end_date) OR exists (select * from drivel where d.id = drivel.id and '2015-02-05' < drivel.start_date and drivel.end_date < '2015-02-05'

gave the message

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P5'

the problem was actually the balancing ')' missing at the end, namely, correct version is

select * from drivel d where exists (select * from drivel where d.id = drivel.id and drivel.start_date < '2015-02-05' AND '2015-02-05' < drivel.end_date) OR exists (select * from drivel where d.id = drivel.id and drivel.start_date < '2015-02-05' AND '2015-02-05' < drivel.end_date) OR exists (select * from drivel where d.id = drivel.id and '2015-02-05' < drivel.start_date and drivel.end_date < '2015-02-05')

If you are using custom data source, adding property :

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.SQLServer2012Dialect

in application.properties will not work.

You have to add the property as properties map in your data source bean:

  @Bean
public LocalContainerEntityManagerFactoryBean sqlServerEntityManagerFactory() {
    HashMap<String, String> properties = new HashMap<>();
    properties.put("hibernate.dialect", "org.hibernate.dialect.SQLServer2012Dialect");

    LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();
    factoryBean.setDataSource(sqlServerDataSource());
    factoryBean.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
    factoryBean.setJpaPropertyMap(properties);
    return factoryBean;
}

If you are using Hibernate within a spring-boot app, you may set hibernate.dialect with following configuration property:

spring.jpa.database-platform=org.hibernate.dialect.SQLServer2008Dialect

Call the Procedure in the below way

@Override
public List<Rep_Holdings> getHoldingsReport(
        int pid
)
{
    List<Rep_Holdings> holdings = null;

    Session sess = sFac.getCurrentSession();
    if (sess != null && pid > 0)
    {
        @SuppressWarnings(
            "rawtypes"
        )
        Query query = sess.createSQLQuery(
                "{CALL GetHoldingsforPF(:pid)}").addEntity(Rep_Holdings.class);
        query.setParameter("pid", pid);

        @SuppressWarnings(
            "rawtypes"
        )
        List result = query.getResultList();
        if (result != null)
        {
            if (result.size() > 0)
            {
                holdings = new ArrayList<Rep_Holdings>();
                for (int i = 0; i < result.size(); i++)
                {
                    Rep_Holdings holding = (Rep_Holdings) result.get(i);
                    holdings.add(holding);
                }
            }
        }
    }

    return holdings;
}

The same procedure in SQL Server

ALTER PROCEDURE [dbo].[GetHoldingsforPF]
    @pid int
AS
BEGIN
SET NOCOUNT ON;
        -- Insert statements for procedure here

        declare @totalPFInv decimal(15,2);

    set @totalPFInv =  ( select  sum(totalInvestment) from Holdings where pid = @pid );

    Select hid,
           pid,
           scCode,
           numUnits,
           avgPPU,
           adjPPU,
           totalInvestment,
           cast ( (totalInvestment/@totalPFInv)*100 as decimal(10,1)) as perPF,
           totalDiv,
           cast ( (totalDiv/totalInvestment)*100 as decimal(10,1)) as divY
           from Holdings
           where pid = @pid
END