Execute multiple SQL statements with NHibernate

Question

I get an error when trying to execute the following SQL statements in NHibernate. They work fine in TOAD. The environment is Oracle, C#, .NET 4 and NHibernate

StringBuilder sb = new StringBuilder();
//some logic to select data from table1 in the where 
//clause of the following statement
sb.Append(" insert into temp_table select * from table1 where ....; ");     
sb.Append(" select t1.col1, t2.col2 from temp_table t1 join table2 t2 on t1.col1 = t2.col2 ");

IQuery query = Session.GetISession().CreateSQLQuery(sb.ToString()).SetResultTransformer(Transformers.AliasToBean(typeof(Class1)));

return query.List<Class1>();

Error that I get.

ORA-00911: invalid character 
[GenericADOException: could not execute query....

If I copy the sql generated by NHibernate in toad the same query works.

Is should be possible (well it is with MsSQL server) to send multiple statements within a CreatSQLQuery e.g. see http://stackoverflow.com/questions/13417976/nhibernate-insert-with-identity-insert-on/13419567#13419567 — Rippo, Mar 23 '14 at 09:49

giammin · Accepted Answer · 2014-03-21T17:40:17.027

0

AFAIK you cannot do this in nhibernate:

you should first do the insert:

Session.GetISession().CreateSQLQuery("insert ....").ExecuteUpdate();

and then do the select:

IQuery query = Session.GetISession().CreateSQLQuery("select ...".SetResultTransformer(Transformers.AliasToBean(typeof(Class1)));    
query.List<Class1>();

or better try to use a storedprocedure

edited Mar 21 '14 at 17:40

answered Mar 21 '14 at 17:14

giammin

18,620
8
71
89

I got the errors `could not execute native bulk manipulation query` , `{"ORA-00942: table or view does not exist"}` for the first insert statement.It is temp table I created in Oracle – gmail user Mar 21 '14 at 17:33
the insert query is working but the `query` throws an exception of `'query.ReturnTypes' threw an exception of type 'System.NotSupportedException'` – gmail user Mar 21 '14 at 18:00

MoB · Answer 2 · 2014-03-24T13:00:34.360

you can do that if you created two different methods, each one opens and closes session independently, and call one after the other. But in this specific example as you select and insert into a temp table, its easier i think if you did that directly on DB (in a stored procedure)

So, you may use a stored procedure and call it from you DAO layer, where the stored procedure has both insert and select... In your DAO layer, you call your stored procedure like the following:

    session.CreateSQLQuery("exec [dbo].[MY_STORED_PROCEDURE] :param1, :param2")
   .setParameter("param1", p1, NHibernateUtil.(dataType of p1)
   .setParameter("param2", p2, NHibernateUtil.(dataType of p2)
   .setResultTransformer...

Your stored procedure would look like this: (just an example):

     declare @table table(col1 int, col2 datetime, col3 nvarchar(50)...) 
     --here u add whatever columns you will be selecting...

then simply do:

     insert into @table
     select * from table1

then you do your select:

    select t1.col1, t2.col2 from @table t1
    join table2 t2 on t2.col1 = t2.col2

then save you stored procedure and just call it as in my example. Hope this helps

@gmailuser: I have given you HOW to do that, under MSSQL or whatever, it is a basic select/insert. That is not your problem though. Your problem is how to execute the 2 procedures in NHibernate... In any case, you must have enough clue to do that after being shown how to do it. — MoB, Mar 24 '14 at 20:11

Execute multiple SQL statements with NHibernate

2 Answers2

Linked