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, Apr 22 '16 at 21:59

score 0 · Answer 1 · edited May 23 '17 at 11:59

0

Check if is by sending null on your first parameter:

Here

For deal with null or optional parameters.

edited May 23 '17 at 11:59

Community

1
1

answered Nov 06 '13 at 21:02

Najera

2,772
3
24
49

No null values are being sent. The error message posted above shows what values are being sent. – jm_dealogic Nov 06 '13 at 22:20
Are you using MsSql2008Dialect? – Najera Nov 06 '13 at 22:47
I updated the original post to add the NHibernate settings. We are using ExtendedSybaseAse15Dialect when connecting to the Sybase server. – jm_dealogic Nov 07 '13 at 13:59
Looks like Dialect problem by using parameter prefix, try to use parameter by position like here: http://stackoverflow.com/questions/1031789/how-can-c-sharp-nullable-value-typed-values-be-set-on-nhibernate-named-iquery-pa – Najera Nov 07 '13 at 19:15
I tried using position instead and still receive the same error "Must declare variable '@p0'" – jm_dealogic Nov 07 '13 at 22:14
Try this is valid, sorry if i couldnt help :( --> EXECUTE sUDI_COMPS 'Y', :pInvestorId, :pReleaseStatus, :pAccountName, :pLastChangedBy, :pEventId, :pTargetId – Najera Nov 08 '13 at 15:18
This was also tried earlier on to no avail. This is a very frustrating issue. Thanks for the help though. – jm_dealogic Nov 08 '13 at 16:45

score 0 · Answer 2 · answered Apr 25 '16 at 15:10

0

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

answered Apr 25 '16 at 15:10

Malcolm

1,168
12
23

Calling Named Query in NHibernate and passing parameters to Sybase Server

2 Answers2