A nhibernate bug.

I. BACKGROUND

We are now doing projects that use NHibernate to implement the data access layer.

When accessing data, some database tables are deterministic: There are explicit table names, field names. This is done in the usual way: establish a database table-to-class mapping, and use HQL to read and write the database.

But there is data access, the database tables that are targeted are indeterminate, and in the run phase, determine which fields of the database tables are accessed. Database tables and fields are not sure, naturally there is no way to suggest o-r mapping, have to construct SQL statement.

Now that we have used nhibernate, we still use nhibernate when we use SQL to access the database. The main point is to use the session it manages, as well as support for paging queries: You can specify the start line and the total number of rows that you want.

Because of greed, this is a cheap problem.

Second, error

Using SQL instead of HQL to access the database, plus the range of access (start or total), sometimes there are strange problems: there are fields that have values in the database, but they can't be read.

For example, there is a database table, and the definition of the table is roughly: MyTable (Id, Name, Frompoint).

Now you need to query the first 10 records, using the following SQL statement:

Select Id, Name, frompoint from MyTable

After creating the SQL query, set the query scope:

... var query = session. Createsqlquery (SQL); query. Setfirstresult (0); query. Setmaxresults (10);..

For the execution result, it is expected that each record has three fields. But in fact, only the first two fields are returned, the third field, the value of Frompoint, is not returned.

To view the NHibernate log, the resulting SQL is:

Select ID, name from (select ID, name, frompoint from MyTable) where rownum<=10

This is really confusing.

Third, Reason

Search on the Internet, can not find the reason. Had to sacrifice the last killer: tracking source code.

The conclusion is that NHibernate has a problem parsing SQL statements, resulting in filtering out columns that should not be filtered out.

Specifically, the following code is in the method Extractcolumnoraliasnames of the class NHibernate.Dialect.Dialect:

if (token. Startswithcaseinsensitive ("select")) continue;if (token. Startswithcaseinsensitive ("distinct")) continue;if (token. Startswithcaseinsensitive (",")) Continue;if (token. Startswithcaseinsensitive ("from"));

The intent of this code is not to use SQL reserved words such as SELECT, DISTINCT, etc. as columns, and once the from reserved word is encountered, it means that the column ends.

The problem is that it uses startswith, rather than the whole word, to hurt the fields that begin with these keywords. And once there are fields starting with from, the subsequent fields are filtered out.

Iv. Ways of

The problem method Extractcolumnoraliasnames is static internal, there is no chance of modification. We had to detour: We added the rownum filter conditions by ourselves.

I was going to use nhibernate to implement cross-database, and I've been telling my little friends to avoid using database management system-specific SQL syntax such as Oracle, SQL Server, and so on.

A lot of programmers will instinctively think of a solution: since it is open source, and the cause of the error found, take over to modify, compile a new version with OK. I do not agree with this, on the one hand, this will be out of the NHibernate mainline version of the development, on the other hand, also to the components of the reference inconvenience: we are using Spring.net management Nhibernate,build a version of their own, to set up a heap of meta-information, think about the big head.

V. Scope

As far as I know, the bug appears as follows:

1. Use native SQL to access the database;
2. Set the Firstresult, maxresults and other query results range;
3. The earliest introduction version is unknown, in the latest version, judging from the source code, this problem still exists;
4. We are using Oracle database and other database management systems are unknown.



A nhibernate bug.