Using NHibernate with SAP Advantage Database Server 12

Question

I'm currently trying to make NHibernate work with SAP's Advantage Database Server 12. Unfortunately, there is not much information around this database server in the internet, but in this case, I have to use it to access an existing database.

So I tried to set up a simple test project first which adds "Students" in a Student table, following this: https://www.tutorialspoint.com/nhibernate/nhibernate_basic_orm.htm

As for the ADS, I found a driver and dialect here: https://groups.google.com/forum/#!topic/nhibernate-development/s7HLs-aEXDk

I've set up a table and this code:

private void button1_Click(object sender, EventArgs e)
        {
            var cfg = new Configuration();
            cfg.DataBaseIntegration(x =>
            {
                x.ConnectionString = "data source=C:\\Users\\xxx\\Downloads;ServerType=local; TableType=ADT; Shared=True; LockMode=COMPATIBLE";
                x.Driver<AdvantageDatabaseServerDriver>();
                x.Dialect<AdvantageDatabaseServer8Dialect>();
                x.LogSqlInConsole = true;
            });

            cfg.AddAssembly(Assembly.GetExecutingAssembly());
            var sefact = cfg.BuildSessionFactory();

            using (var session = sefact.OpenSession())
            {
                using (var tx = session.BeginTransaction())
                {
                    //perform database logic 
                    tx.Commit();
                }
                Console.ReadLine();
            }

            using (var session = sefact.OpenSession())
            {
                using (var tx = session.BeginTransaction())
                {
                    var student1 = new Students
                    {
                        ID = 1,
                        FirstMidName = "Allan",
                        LastName = "Bommer"
                    };

                    var student2 = new Students
                    {
                        ID = 2,
                        FirstMidName = "Jerry",
                        LastName = "Lewis"
                    };

                    session.Save(student1);
                    session.Save(student2);
                    tx.Commit();
                }
                Console.ReadLine();
            }
        }

But it keeps throwing this exception at the first write attempt:

NHibernate: INSERT INTO C:\Users\ad\Downloads\REFERENCE\Students.adt (LastName, FirstMidName) VALUES (?, ?); select NEWIDSTRING() from system.iota;p0 = 'Glenn' [Type: String (-1:0:0)], p1 = 'Allan' [Type: String (-1:0:0)]
Exception thrown: 'Advantage.Data.Provider.AdsException' in NHibernate.dll

Is there anything wrong with the dialect maybe? I really appreciate any help on this, thank you!

Answer 1

From the comments the problem is this:

NHibernate with the grammar you are using emits this code:

INSERT INTO
  C:\Users\ad\Downloads\REFERENCE\Students.adt
(
    LastName
  , FirstMidName
)
VALUES
(
    ?
  , ?
);

select NEWIDSTRING() from system.iota;

Apparently the ASP.NET driver for ADS can not handle this form of parameter passing correctly.

The knowledge base item you linked to suggest to change the SQL to use named parameters instead:

INSERT INTO
  C:\Users\ad\Downloads\REFERENCE\Students.adt
(
    LastName
  , FirstMidName
)
VALUES
(
    :p0
  , :p1
);

select NEWIDSTRING() from system.iota;

I don't know enough of NHibernate to tell you how this can be achieved.