Calling Named Query in NHibernate and passing parameters to Sybase Server

Question

I am trying to call a named query using NHibernate and am receiving errors upon execution. This only happens when connecting to Sybase server and works fine on Microsoft SQL Server.

We are using Sybase ASE 15 and NHibernate 3.3.1.4000.

We do not have access to a Sybase Profiler. My guess is that NHibernate is not translating the query properly when sending to Sybase.

Here is where the parameters are being passed:

NHibernate.IQuery query;

query = NHibernateDataContext.GetNamedQuery("UPDATE_INVESTOR_TARGET");

query.SetParameter("pTargetId", target.Id.TargetId < 1 ? null :(int?) target.Id.TargetId, NHibernate.NHibernateUtil.Int32);
query.SetParameter("pInvestorId", target.InvestorId, NHibernate.NHibernateUtil.String);
query.SetParameter("pReleaseStatus", new TargetReleaseStatusEnumerationStringType().GetValue(target.ReleaseStatus), NHibernate.NHibernateUtil.String);
query.SetParameter("pEventId", target.Id.EventId, NHibernate.NHibernateUtil.Int32);
query.SetParameter("pAccountName", target.InvestorName.Value, NHibernate.NHibernateUtil.String);
query.SetParameter("pLastChangedBy", lastChangedBy, NHibernate.NHibernateUtil.String);

target.Id.TargetId = (int)query.UniqueResult();

trans.CommitChanges();

Here is the NHibernate mapping:

<sql-query name="UPDATE_INVESTOR_TARGET" cacheable="false" >
    <return-scalar column="Returned Value" type="int" />
    EXECUTE sUDI_COMPS
    @pSelectReturnYN = 'Y',
    @pAccountNo = :pInvestorId,
    @pReleaseStatus = :pReleaseStatus,
    @pAccountName = :pAccountName,
    @pLastChangedBy = :pLastChangedBy,
    @pDealNo = :pEventId,
    @pCompsNo = :pTargetId
</sql-query>

The error is received when executing query.UniqueResult().

This is the error:

could not execute query [ EXECUTE sUDI_COMPS @pSelectReturnYN = 'Y', @pAccountNo = @p0, @pReleaseStatus = @p1, @pAccountName = @p2, @pLastChangedBy = @p3, @pDealNo = @p4, @pCompsNo = @p5 ]
Name:pTargetId - Value:1 Name:pInvestorId - Value:1 Name:pReleaseStatus - Value:R Name:pEventId - Value:1 Name:pAccountName - Value:45cda18d-f4da-4342-909b-491918fb4032 Name:pLastChangedBy - Value:jmacri [SQL: EXECUTE sUDI_COMPS @pSelectReturnYN = 'Y', @pAccountNo = @p0, @pReleaseStatus = @p1, @pAccountName = @p2, @pLastChangedBy = @p3, @pDealNo = @p4, @pCompsNo = @p5]

And the Inner Exception:

{"Must declare variable '@p0'.\n"}

It is obvious that the variables are not being declared by NHibernate.

Has anybody experienced this before that may know why this is happening?

Is this an NHibernate issue or a configuration/setup issue?

Any help is greatly appreciated.

Thanks, Joe

UPDATE:

Here are the configuration settings. Note that the Microsoft SQL settings are commented out. The Microsoft SQL settings work when connecting to a Microsoft SQL database. The Sybase settings do not work when connecting to a Sybase SQL server.

 <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
    <session-factory name="ICM">

      <!-- MS SQL 2008 Server -->
      <!--<property name="connection.driver_class">
        Dealogic.DataAccess.Driver.DealogicSqlClientDriver,Dealogic.DataAccess
      </property>
      <property name="dialect">
        NHibernate.Dialect.MsSql2008Dialect
      </property>
      <property name="connection.connection_string_name">default</property>-->

      <!-- Sybase ASE 15 Server -->
      <property name="connection.driver_class">
          Dealogic.DataAccess.Driver.DealogicSybaseAseClientDriver,Dealogic.DataAccess
      </property>
      <property name="dialect">
          Dealogic.DataAccess.Dialect.ExtendedSybaseAse15Dialect,Dealogic.DataAccess
      </property>
      <property name="connection.connection_string_name">default</property>

      <property name="adonet.batch_size">10</property>
      <property name="show_sql">false</property>
      <property name="format_sql">true</property>
      <property name="generate_statistics">false</property>
      <property name="command_timeout">60</property>
      <property name="query.substitutions">true 1, false 0, yes 'Y', no 'N'</property>
    </session-factory>
  </hibernate-configuration>

Did you find a solution to this? I just ran into the same issue. — Malcolm
– Malcolm, Commented Apr 22, 2016 at 21:59

Community · Accepted Answer · 2017-05-23 11:59:35Z

0

Check if is by sending null on your first parameter:

Here

For deal with null or optional parameters.

edited May 23, 2017 at 11:59

CommunityBot

11 silver badge

answered Nov 6, 2013 at 21:02

Najera

2,8793 gold badges30 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

jm_dealogic Over a year ago

No null values are being sent. The error message posted above shows what values are being sent.

Najera Over a year ago

Are you using MsSql2008Dialect?

jm_dealogic Over a year ago

I updated the original post to add the NHibernate settings. We are using ExtendedSybaseAse15Dialect when connecting to the Sybase server.

Najera Over a year ago

Looks like Dialect problem by using parameter prefix, try to use parameter by position like here: stackoverflow.com/questions/1031789/…

jm_dealogic Over a year ago

I tried using position instead and still receive the same error "Must declare variable '@p0'"

|

Malcolm · Accepted Answer · 2016-04-25 15:10:17Z

0

FYI, it works with the ODBC driver, but not with the SybaseASE driver.

answered Apr 25, 2016 at 15:10

Malcolm

1,2211 gold badge14 silver badges26 bronze badges

Collectives™ on Stack Overflow

Calling Named Query in NHibernate and passing parameters to Sybase Server

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related