0

Setup:

  • Oracle 11g Enterprise Edition Release 11.2.0.1.0 64bit Production
  • Toad For Oracle 12.1.0.22
  • Nhibernate 4.0.0.4000
  • Fluent Nhibernate 2.0.1.0
  • Projects develop under C# 4.5.1

Fluent Configuration:

var configuration = Fluently.Configure()
                    .Database(OracleManageDataClientConfiguration.Oracle10
                        .ConnectionString("my-connection-string")
                        .IsolationLevel(IsolationLevel.ReadCommitted).ShowSql())
                    .Mappings(x => x.FluentMappings.AddFromAssembly(my-assembly)
                        .Convention.Add<CustomIdentityHiloConvetion>())
                    .ExposeConfiguration(buildSchema)
                    .BuildConfiguration();

Fluent Convention:

public class CustomIdentityHiloConvetion : IIdConvention
{
    public void Apply(IIdentityInstance instance)
    {
        instance.GeneratedBy.HiLo("NEXTHIVALUETABLE", "NEXTHIVALUECOLUMN", 10, builder => 
            builder.AddParam("where", string.Format("{0} = '{1}'", "TABLENAME", instance.EntityType.Name.ToUpperInvariant())));
    }
}

Table layout

Generated SQL statement from Nhibernate: select NEXTHIVALUECOLUMN from NEXTHIVALUETABLE where TABLENAME = 'MYSAMPLETABLE' for update

It throws error that it could not read a hi value from table NEXTHIVALUETABLE using the statement. I execute the said statement directly in Toad and running perfectly fine and SQLite table.

I disassembled (using resharper) the Nhibernate assembly with command.ExecuteReader statement from TableGenerator class (line 193) from Nhibernate.Id namespace return no records thus it throws this exception.

Quick code from DoWorkInCurrentTransaction method:

do
{
    IDbCommand command = conn.CreateCommand();
    IDataReader rs = (IDataReader) null;
    command.CommandText = this.query; // select NEXTHIVALUECOLUMN from NEXTHIVALUETABLE where TABLENAME = 'MYSAMPLETABLE' for update
    command.CommandType = CommandType.Text;
    command.Transaction - transaction;
    PersistentIdGeneratorParmsNames.SqlStatementLogger.LogCommand("Reading high value: ", command, FormatStyle.Basic);

    try
    {
        rs = command.ExecuteReader();
        if(!rs.Read())
        {
            string message = !string.IsNullOrEmpty(this.whereClause) ? string.Format("could not read a hi value from table '{0}' using the where class ({1})- you need to populate the table"....//more statements);
            TableGenerator.log.Error((object)message);
            throw new IdentifierGeneratorException(message);
        }

        num1 = Convert.ToInt64(this.columnType.Get(rs, 0));
    }
    catch(Exception ex)
    {
        //more statements
    }
    //more statements
    ...
    ...
    ...     
}
altyne
  • 159
  • 3
  • 14
  • I observed if I make all uppercase the statement (SELECT NEXTHIVALUECOLUMN FROM NEXTHIVALUETABLE WHERE TABLENAME = 'MYSAMPLETABLE' FOR UPDATE) goes in. How to set in configuration make all SQL stamentments uppercase? – altyne Sep 29 '15 at 10:22

1 Answers1

0
instance.GeneratedBy.HiLo("NEXTHIVALUETABLE", "NEXTHIVALUECOLUMN", 10, builder => 
            builder.AddParam("WHERE", string.Format("{0} = '{1}'", "TABLENAME", instance.EntityType.Name.ToUpperInvariant())));

I make uppercase for WHERE and it fixes the problem. Very weird issue.

altyne
  • 159
  • 3
  • 14