CODEWITHSUNDEEP
c#nhibernate
mridula
mridula

Reputation: 3281

How to pass schema name in a variable to nHibernate's named query?

I have a named native sql query in one of my nhibernate mapping files as follows:

<sql-query name="GetAllClients">
    <return alias="clientList" class="IBeam.Core.Models.Client"/>
    <![CDATA[
      SELECT a.sname Name, a.scd Id
        FROM :MASTER_USER.smast      a,
        :MASTER_USER.fa_ledmast b,
        :TRAN_USER.fa_subledmast c
      WHERE a.scd = c.subledcd
        AND b.ledgercd = c.ledgercd
        AND b.ledtypecd = 'SDR'
        AND a.catflg  = 'N'
     group by a.scd, a.sname
     order by a.sname
   ]]>
</sql-query>

And I am running this query as from my c# code as follows:

var query = Repository.GetExecutingSession().GetNamedQuery("GetAllClients").SetString("MASTER_USER", "test$master").SetString("TRAN_USER", "test$tran");
var clients = query.List<Models.Client>();

But I get the error: Parameter MASTER_USER does not exist as a named parameter in the query. I have used named queries and passed parameters to it before, but never as a schema name. I think it is considering the whole :MASTER_USER.smast as a table name and is not distinguishing the parameter name. How can I pass the schema name to this query as a parameter?

I found this Link so I think it can be done. But I don't know how to.

Upvotes: 1

Views: 1687

Answers (1)

jbl
jbl

Reputation: 15413

First, your schema specifications can't be SQL parameters. The replacement would need to be performed by the NH engine, not by the SQL Engine. So your schema specifications would need to be in NH placeholders ( with angle brackets )

Then, the Hibernate Link you provided does not allow to pass schema as parameter. It allows to extract default schema from config.

Maybe (not tried) you can try something like this, with a (very rough) interceptor implementation :

public class SchemaSqlInterceptor : EmptyInterceptor, IInterceptor
{

    public string MASTER_USER { get; set; }
    public string TRAN_USER { get; set; }


    NHibernate.SqlCommand.SqlString IInterceptor.OnPrepareStatement(NHibernate.SqlCommand.SqlString sql)
    {
        return sql.Replace("{MASTER_USER}", MASTER_USER).Replace("{TRAN_USER}", TRAN_USER);
    }
}

Then :

    var interceptor = new SchemaSqlInterceptor();
    using (var session = sessionFactory.OpenSession(interceptor))
    {
        interceptor.MASTER_USER = "test$master";
        interceptor.TRAN_USER = "test$tran";
        var query = session.GetNamedQuery("GetAllClients");
        var clients = query.List<Models.Client>();
        session.Close();
    }

your query being :

<sql-query name="GetAllClients">
    <return alias="clientList" class="IBeam.Core.Models.Client"/>
    <![CDATA[
      SELECT a.sname Name, a.scd Id
        FROM {MASTER_USER}.smast      a,
        {MASTER_USER}.fa_ledmast b,
        {TRAN_USER}.fa_subledmast c
      WHERE a.scd = c.subledcd
        AND b.ledgercd = c.ledgercd
        AND b.ledtypecd = 'SDR'
        AND a.catflg  = 'N'
     group by a.scd, a.sname
     order by a.sname
   ]]>
</sql-query>

Upvotes: 3

Related Questions