cancel
Showing results for 
Search instead for 
Did you mean: 

NHibernate issue with parameters

Former Member
8,333

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

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member

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.

Former Member
0 Kudos

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.

Former Member

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.

Former Member
0 Kudos

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?

Former Member
0 Kudos

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.

Former Member
0 Kudos

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.

Former Member
0 Kudos

And how do you recommend that I handle this?

Former Member
0 Kudos

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.

Former Member
0 Kudos

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:

Former Member
0 Kudos

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?

Former Member
0 Kudos

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.

Former Member
0 Kudos

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.