|
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. 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.
(08 May '12, 20:23)
waynemystir
|
|
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 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 The second problem, which pertains to your Criteria query, is that the 12.0.1 .NET provider inadvertently tries to bind string hostvars to My thanks to the infrastructure team for the diagnosis and the fix to the .NET provider. |
|
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? 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.
(04 May '12, 09:21)
waynemystir
Replies hidden
Then you're already on very shaky ground. The SQL Anywhere 12 dialect in NH 3.3.0 will issue SQL syntax that ASA version 9 does not support, and the server will give you syntax errors.
(04 May '12, 09:27)
Glenn Paulley
And how do you recommend that I handle this?
(04 May '12, 09:32)
waynemystir
Replies hidden
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.
(04 May '12, 10:45)
Glenn Paulley
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:
(04 May '12, 10:45)
waynemystir
Ahhh, let's see. I don't know which features ASA 9 supports and which ones they don't support. And I do not typcially develop against Sybase. Can you please provide a driver and dialect that are compatiable?
(04 May '12, 10:54)
waynemystir
Replies hidden
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.
(04 May '12, 17:24)
Glenn Paulley
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.
(04 May '12, 18:16)
waynemystir
|