MS SQL Exception: Incorrect syntax near '@P0'
JavaSqlJdbcJava Problem Overview
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"+
"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);
}
Thanks in advance!
Java Solutions
Solution 1 - Java
SQL Server requires you to place parenthesis around the argument to top
if you pass in a variable:
SELECT TOP (?)
Solution 2 - Java
In our application we have extended a depraceted SQLServerDialect
. After change to SQLServer2008Dialect
the problem disappeared.
Solution 3 - Java
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
Solution 4 - Java
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')
Solution 5 - Java
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;
}
Solution 6 - Java
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
Solution 7 - Java
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