on 2012 May 04 12:21 AM
Hi
I have recently begun using NHibernate. I am using NHibernate-3.3.0.GA, SybaseSQLAnywhere12Driver, SybaseSQLAnywhere12Dialect, and iAnywhere.Data.SQLAnywhere.v4.0. Everything seems to work just fine until I try to use the Criteria functionality on a property of type String. When I do so, I get the below error message. No error occurs when I use the Criteria functionality on an Int property.
2012-05-03 08:48:49,697 ERROR Could not execute query: select counterpar0_.ID as ID6_, counterpar0_.CounterpartyCode as CounterpartyCode6_, counterpar0_.CounterpartyName as CounterpartyName6_ from Counterparty counterpar0_ where counterpar0_.CounterpartyCode=? iAnywhere.Data.SQLAnywhere.SAException (0x80004005): Communication error at iAnywhere.Data.SQLAnywhere.SACommand._ExecuteReader(CommandBehavior commandBehavior, Boolean isExecuteScalar, Boolean isBeginExecuteReader) at iAnywhere.Data.SQLAnywhere.SACommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd) in C:\\OpenSource\\NHibernate-3.3.0.GA-src\\src\\NHibernate\\AdoNet\\AbstractBatcher.cs:line 223
It doesn't appear that anything is amiss on the NHibernate end. I took the simple HelloNHibernate example and modified it to include a Criteria query that involved string hostvars:
static void LoadEmployeesFromDatabase() { using (ISession session = OpenSession()) { string searchString1 = "Tobin%"; string searchString2 = "%Pierre%"; IQuery query = session.CreateQuery( "from Employee as emp where emp.name like ? or emp.name like ? order by emp.name asc"); query.SetMaxResults(20); query.SetString(0, searchString1); query.SetString(1, searchString2); query.SetFirstResult(3); IList<Employee> foundEmployees = query.List<Employee>(); Console.WriteLine("\\n{0} employees found:", foundEmployees.Count); foreach (Employee employee in foundEmployees) Console.WriteLine(employee.SayHello()); } }
and using a 12.0.1.3725 server the application works as expected. However, if I take the same .NET assembly (compiled with Visual Studio 2010) and try to use an ASA version 9.0.2.4012 server, then I can neither INSERT a new Employee row, nor list the rows that are already there (I added some thru DBISQLC).
I believe the problem may be due to an incompatibility between something in either the .NET provider (or possibly the 12.0.1 ODBC driver) and the ASA 9.0.2 server - unfortunate but, on the other hand, hardly surprising since ASA version 9 first shipped in July 2003, and 9.0.2 shipped in 2004.
This will be further investigated by the .NET provider Engineering team.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Many thanks Glenn for thoroughly looking into this. I have postponed our use of NHibernate until this is resolved (or more likely until our DBAs upgrade the DB, which I'm told will be soon). So this is no longer an urgent issue. But I would definitely like to start using NHibernate ASAP so please do let us know the outcome from the .NET provider Engineering team.
Thanks again.
There were two separate issues. The first is dialect-related; I had forgotten that the SQLAnywhere12Dialect enables
public override bool SupportsInsertSelectIdentity { get { return true; } }
through inheritance but this means for an INSERT
NHibernate will generate something like
INSERT INTO foo VALUES ( :p0, :p1 ); SELECT @@IDENTITY;
but ASA Version 9 does not support the use of host variables in a SQL batch.
The SybaseASA9Dialect that ships with the NH3.3.0 GA distribution doesn't enable SupportsInsertSelectIdentity
so it doesn't suffer from this problem. The SybaseASA9Dialect dialect also will work better with IQ 12.7 because that dialect does not issue FOR UPDATE BY LOCK
syntax which the SybaseSQLAnywhere12 dialect can issue that ASA 9 servers don't support.
The second problem, which pertains to your Criteria query, is that the 12.0.1 .NET provider inadvertently tries to bind string hostvars to NVARCHAR
types, but ASA 9 does not support NCHAR
types. A fix has been made to the .NET provider to use CHAR
types instead with ASA9; this fix to the .NET provider will be available in 12.0.1 builds 3731 and higher, and to 11.0.1 builds 2812 and higher.
My thanks to the infrastructure team for the diagnosis and the fix to the .NET provider.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
The "Communication error" is suspicious. Does the server go down when this error occurs?
What version and build of the SQL Anywhere .NET driver and SA server are you using?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Glenn,
I can confirm that the server does not go down, or otherwise cause communication errors:
iAnywhere.Data.SQLAnywhere.v4.0 v4.0.30319
SA: IQ 12.7 includes Sql Anywhere 9
In fact, if you run the same query but with a first criteria on an int, then the error message "Host variable :p1 was not initialized" is returned. And the log clearly indicates that it is sending both :p0 and :p1.
You have a couple of options. You can tailor the SA 12 dialect to remove the features that ASA 9 doesn't support, such as FOR UPDATE BY LOCK syntax when LockMode is used. Removing LockMode functionality, however, has implications for correct concurrency control that are impossible to test with a single client.
Another possibility is to use one of the other (generic) dialects that ship with the distribution. Or, re-work an older SQL Anywhere 9 dialect from an older NHibernate distribution (version 2.1) and see if it will work with NHibernate 3.3.0.
I receive the same error when trying with the following configuration:
NHibernate.Driver.SybaseSQLAnywhereDriver iAnywhere.Data.SQLAnywhere SybaseSQLAnywhere10Dialect or SybaseASA9Dialect
Below is the exception that is returned when I run the Criteria query with two parameters. The first parameter is of type Int. NH, the driver, and Sybase all seem to recognize it without problem. It is the second parameter of type String that generates the error message:
NHibernate.Exceptions.GenericADOException was unhandled Message=could not execute query [ SELECT this_.ID as ID1_0_, this_.CounterpartyCode as Counterp2_1_0_, this_.CounterpartyName as Counterp3_1_0_ FROM Counterparty this_ WHERE this_.ID = :p0 and this_.CounterpartyCode = :p1 ] Name:cp0 - Value:1 Name:cp1 - Value:PERRY [SQL: SELECT this_.ID as ID1_0_, this_.CounterpartyCode as Counterp2_1_0_, this_.CounterpartyName as Counterp3_1_0_ FROM Counterparty this_ WHERE this_.ID = :p0 and this_.CounterpartyCode = :p1] Source=NHibernate SqlString=SELECT this_.ID as ID1_0_, this_.CounterpartyCode as Counterp2_1_0_, this_.CounterpartyName as Counterp3_1_0_ FROM Counterparty this_ WHERE this_.ID = :p0 and this_.CounterpartyCode = :p1 StackTrace: at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) in C:OpenSourceNHibernate-3.3.0.CR1-srcsrcNHibernateLoaderLoader.cs:line 1472 at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) in C:OpenSourceNHibernate-3.3.0.CR1-srcsrcNHibernateLoaderLoader.cs:line 1467 at NHibernate.Loader.Criteria.CriteriaLoader.List(ISessionImplementor session) in C:OpenSourceNHibernate-3.3.0.CR1-srcsrcNHibernateImplSessionImpl.cs:line 1965 at NHibernate.Impl.CriteriaImpl.List(IList results) in C:OpenSourceNHibernate-3.3.0.CR1-srcsrcNHibernateImplCriteriaImpl.cs:line 265 at NHibernate.Impl.CriteriaImpl.ListT in C:OpenSourceNHibernate-3.3.0.CR1-srcsrcNHibernateImplCriteriaImpl.cs:line 276 at SwapExtractDAL.InitialiseData.LoadCounterpartiesFromDB() in SwapExtractDALInitialiseData.cs:line 22 at SwapExtractGenerator_01.Program.Main(String[] args) in C:DevelopmentTestSwapExtractGenerator_01SwapExtractGenerator_01Program.cs:line 20 InnerException: iAnywhere.Data.SQLAnywhere.SAException Message=Host variable 'p1' is not initialized, -- (df_Heap.cxx 2303) Source=SQL Anywhere .NET Data Provider ErrorCode=-2147467259 NativeError=-1001036 StackTrace: at iAnywhere.Data.SQLAnywhere.SACommand._ExecuteReader(CommandBehavior commandBehavior, Boolean isExecuteScalar, Boolean isBeginExecuteReader) at iAnywhere.Data.SQLAnywhere.SACommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd) in C:OpenSourceNHibernate-3.3.0.CR1-srcsrcNHibernateAdoNetAbstractBatcher.cs:line 229 at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean autoDiscoverTypes, Boolean callable, RowSelection selection, ISessionImplementor session) in C:OpenSourceNHibernate-3.3.0.CR1-srcsrcNHibernateLoaderLoader.cs:line 1224 at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in C:OpenSourceNHibernate-3.3.0.CR1-srcsrcNHibernateLoaderLoader.cs:line 421 at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in C:OpenSourceNHibernate-3.3.0.CR1-srcsrcNHibernateLoaderLoader.cs:line 251 at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in C:OpenSourceNHibernate-3.3.0.CR1-srcsrcNHibernateLoaderLoader.cs:line 1564 InnerException:
As long as you stay away from data types introduced since version 9 (ie. TIMESTAMP WITH TIME ZONE), don't use the OFFSET clause for pagination and don't use LockMode the version 12 dialect should probably work with version 9. Alternatively, you can use the ASA9 dialect that ships with the NHibernate 3.3.0 distribution.
It is as yet unclear to me where the problem with string parms lies. There have been problems with the Criteria API in the past - see
http://iablog.sybase.com/paulley/2010/03/nhibernate-the-criteria-api-and-the-sqlanywhere11dialect/
but I have failed to find anything related in the NHibernate forums as yet. I will try to repro your issue with a simple example and see what happens.
Many thanks Glenn. It is quite possible that I am missing something very simple due to my experience with both NH and Sybase. So if you are able to reproduce the error, that would remove this possibility.
I initially drew the same conclusion as you, that version 12 dialect would likely work for me, with certain restrictions. It didn't turn out to be the case.
I made a feeble attempt to write a dialect. And I specifically set RegisterColumnType(DbType.String,... with all the varchar types under the sun. Produces the same result.
User | Count |
---|---|
71 | |
11 | |
11 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.