CODEWITHSUNDEEP
sqlnhibernatehql
zszep
zszep

Reputation: 4483

NHibernate Antlr.Runtime.MismatchedTokenException when updating table

I am getting this strange error Antlr.Runtime.MismatchedTokenException when running my update query against ms sql server. I am effectively trying to update a table from the sum query result of another table. I tried executing the sql directy in a sql console and it's working without any problems.

   var sql = new StringBuilder();

    sql.Append("UPDATE s SET s.pokriveno = l.pokriveno,");
    sql.Append("s.zatvoreno = CASE WHEN s.Pokriveno = s.Duguje + s.Potrazuje THEN 1 ELSE 0 END ");
    sql.Append("FROM Stavka s ");
    sql.Append("LEFT JOIN ");
    sql.Append("(");
    sql.Append("    SELECT id, SUM(pokriveno) pokriveno FROM ");
    sql.Append("    (");
    sql.Append("        SELECT l.Stavka1Id id, SUM(l.iznos1) pokriveno ");
    sql.Append("        FROM Link l  ");
    sql.Append("        JOIN stavka s ON l.Stavka1Id = s.Id ");
    sql.Append("        JOIN Dokument d ON s.DokumentId = d.id ");
    sql.Append("        WHERE d.godina = :year ");
    sql.Append("        GROUP BY Stavka1Id ");
    sql.Append("        UNION ALL ");
    sql.Append("        SELECT l.Stavka2Id id, SUM(l.iznos2) pokriveno ");
    sql.Append("        FROM Link l ");
    sql.Append("        JOIN stavka s ON l.Stavka2Id = s.Id ");
    sql.Append("        JOIN Dokument d ON s.DokumentId = d.id ");
    sql.Append("        WHERE d.godina = :year ");
    sql.Append("        GROUP BY Stavka2Id ");
    sql.Append("    ) tmp GROUP BY id ");
    sql.Append(") l ");
    sql.Append("ON s.Id = l.id");

    Debug.WriteLine(sql);
    var query = session.CreateQuery(sql.ToString());
    query.SetParameter("year", year);
    int count = query.ExecuteUpdate();

Anyone a clue what's happening?

Upvotes: 1

Views: 746

Answers (1)

Radim Köhler
Radim Köhler

Reputation: 123891

The issue here is, that you are using API for manipulating HQL / DML queries, while passing the native SQL statement. See:

Instead of the .CreateQuery(hql) expecting hql, we have to use .CreateSQLQuery(sql) expecting the native SQL to be passed

var query = session
     //.CreateQuery(sql.ToString())
     .CreateSQLQuery(sql.ToString())
     ;
query.SetParameter("year", year);
int count = query.ExecuteUpdate();

Also see the usage of CreateQuery:

Upvotes: 2

Related Questions