Hello, latha1119.
Here are the answers to your questions:
1)What kind of driver do you use?I'm using the official Microsoft SQL Server 2005 JDBC Driver 1.2
(which is compatible with Microsoft SQL Server 2000)
2)We use SQL Server 2005 so I don't know the default compatibility level for SQL Server2000.The default database compatibility level for SQL Server 2000 is 80, which accepts
to TOP keyword in SELECT statements. But as I said, we are using a database with
compatibility level 65. This is an old database here in the company where I work
that was converted from SQL Server 7, I suppose. So, it came with the compatibility
level unchanged, I think. As I also said we tried changing the compatibility level
to 80, but all sort of strange errors appeared in the applications. So, we reverted
to the old configuration and the errors went away.
3)Can you post the generated SQL?Unfortunately I can't right now. I noticed that JBoss 5.1.0.GA (that I'm migrating for)
doesn't log the Hibernate messages in the server.log file anymore. I suppose there
should be a way to achieve this, but I haven't tried to find out how yet. Nevertheless,
I'm sure about the genereted SQL the Hibernate genereted in this case. If you open the
SQL Query Analyzer in SQL Server and execute this following query in a database with
compatibility level 80:
SELECT TOP 2 1
the query will work and will return one row with one column with the value 1. But if
you execute the same query in a database that has compatibility level 65, for example,
you will get this error message in the SQL Query Analyzer:
Code:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '2'.
It clearly doesn't understand the TOP keyword. Now compare this message error with the
relevant parts of the error message in the server.log in JBoss that I showed you in
the beginning of this thread:
Code:
08:19:49,875 WARN [JDBCExceptionReporter] SQL Error: 170, SQLState: S0001
08:19:49,906 ERROR [JDBCExceptionReporter] Line 1: Incorrect syntax near '2'.
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Line 1: Incorrect syntax near '2'.
Now you know Hibernate is generating a SQL with the TOP keyword, which didn't happen
in Hibernate 3.2.4.sp1.
4)can you get rid of the method getSingleResult() and that does not help you?
or may be let me ask you this.. can you change the Java end of your app?If I get rid of the method
getSingleResult() and replace it with
getResultList()I'm almost sure the error will disappear. But now I'll have to deal with the
result list just to get one value or one record in a case I know there's only
one result. I'll have to change all code the calls getSingleResult(). This in
without doubt an ugly workaround that will make the code apper non intuitive and
longer that it would should be.
Marcos
Login
FAQ
Search
