nhibernate with Sybase asa9 with named parameters

Question

Trying to get NHibernate working with Sybase ASA9 and having trouble with named parameters throwing an error:

Invalid index 0 for this AsaParameterCollection with Count=0.

I've tried with the NHibernate ODBC driver and currently the ASA driver. I can do straight query without parameters so I know connection is happening. It appears that the parameter is not being passed correctly to ASA but I'm stuck on figuring out if I'm doing something wrong or if this is just an issue with NH and ASA. Took a bit of head scratching to get this far so maybe this will also help someone in the future. The value is correct and is visible in the last snippet in the generated sql, and the query ends in equals question mark which appears to be valid Sybase, just not clear where this is breaking down.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
  <session-factory>
    <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
    <property name="dialect">NHibernate.Dialect.SybaseASA9Dialect</property>
    <property name="connection.driver_class">NHibernate.Driver.SybaseAsaClientDriver</property>
    <property name="connection.connection_string">Uid=;Pwd=;Dsn=</property>
    <property name="hbm2ddl.keywords">none</property><!--could cause problems? update needed when going to sybase driver instead of odbc-->
    <property name="show_sql">true</property>
    <!-- mapping files -->
    <mapping resource="domain.table.hbm.xml" assembly="Label3700" />-->
  </session-factory>
</hibernate-configuration>

mapping

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
               assembly="Label3700"
               namespace="Label3700.Domain">

<class name="menu_item" table="micros.mi_def">
     <id name="mi_seq">
       <column name="mi_seq" sql-type="integer" not-null="true"/>
       <generator class="identity" />
     </id>
     <property name="obj_num" />
     <property name="name_1" />
     <property name="fam_grp_seq" />
   </class>
</hibernate-mapping>

class

public class menu_item
{
    public virtual int mi_seq { get; set; }
    public virtual int obj_num { get; set; }
    public virtual string name_1 { get; set; }
    public virtual int fam_grp_seq { get; set; }
}

query

IList<menu_item> mi = session.CreateQuery("FROM menu_item m where m.fam_grp_seq = :famGrpSeq")
.SetParameter("famGrpSeq", familyGroupSeq)
.List<menu_item>();

error

could not execute query
[ select menu_item0_.mi_seq as mi1_1_, menu_item0_.obj_num as obj2_1_,menu_item0_.name_1 as name3_1_, menu_item0_.fam_grp_seq as fam4_1_ from micros.mi_def menu_item0_ where menu_item0_.fam_grp_seq=? ]
Name:famGrpSeq - Value:44
[SQL: select menu_item0_.mi_seq as mi1_1_, menu_item0_.obj_num as obj2_1_,menu_item0_.name_1 as name3_1_, menu_item0_.fam_grp_seq as fam4_1_ from micros.mi_def menu_item0_ where menu_item0_.fam_grp_seq=?]

score 0 · Answer 1 · answered Dec 08 '15 at 20:36

You need to create a custom connectiondriver because the default connection driver uses the wrong settings. upgrading to newer versions of NHibernate might eliminate the problem.

In my connection driver for sybase I have overriden these properties:

public class SqlAnywhereConnectionDriver : ReflectionBasedDriver
{
    //other code removed…
    public override bool UseNamedPrefixInSql
    {
        get { return true; }
    }

    public override bool UseNamedPrefixInParameter
    {
        get { return true; }
    }

    public override string NamedPrefix
    {
        get { return ":"; }
    }
}

nhibernate with Sybase asa9 with named parameters

1 Answers1