NHibernate with ODBC - GenericADOException: Could not Insert

Question

I have written a small program to insert some records into an oracle database. If I make a direct connection to oracle, the program runs fine. However, if I try to connect via ODBC the program does not work. It is able to perform a SELECT statement, but not an INSERT statement.

When I was developing the program, I was using these settings in my NHibernate configuration:

<property name="connection.provider">
    NHibernate.Connection.DriverConnectionProvider
</property>
<property name="dialect">NHibernate.Dialect.Oracle10gDialect</property>
<property name="connection.connection_string">
    Data Source=192.168.1.43:1521/xxxx;User ID=xxxx;Password=xxxx
</property>
<property name="connection.driver_class">
    NHibernate.Driver.OracleClientDriver
</property>
<property name="hbm2ddl.keywords">auto-quote</property>

With these settings the program works.

For production, I am using these settings:

<property name="connection.provider">
    NHibernate.Connection.DriverConnectionProvider
</property>
<property name="dialect">NHibernate.Dialect.Oracle10gDialect</property>
<property name="connection.connection_string">
    DSN=TRUST;Server=oracle;UID=xxxx;PWD=xxxx
</property>
<property name="connection.driver_class">
    NHibernate.Driver.OdbcDriver
</property>
<property name="hbm2ddl.keywords">auto-quote</property>

Using the production settings, I get this exception when I try to insert data:

NHibernate.Exceptions.GenericADOException: could not insert: [Domain.Phoenix.StructureAssign#35159][SQL: INSERT INTO STRUCTURE_ASSIGN (CLIENT_ID, STRUCTURE, NAME_CODE, START_DATE, FINISH_DATE, MEMO_NUMBER, ALTERNATE, ALTERNATE_NAME_CODE, ADMIN_NAME, AUDIT_NAME, VALID_DATE, AUDIT_ACTION, DIRECTOR_CLASS, STAT_SEQ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)] ---> System.Data.Odbc.OdbcException
   at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
   at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()
   at NHibernate.AdoNet.AbstractBatcher.ExecuteNonQuery(IDbCommand cmd)
   at NHibernate.AdoNet.NonBatchingBatcher.AddToBatch(IExpectation expectation)
   at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Boolean[] notNull, Int32 j, SqlCommandInfo sql, Object obj, ISessionImplementor session)
   --- End of inner exception stack trace ---
   at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Boolean[] notNull, Int32 j, SqlCommandInfo sql, Object obj, ISessionImplementor session)
   at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Object obj, ISessionImplementor session)
   at NHibernate.Impl.StatelessSessionImpl.Insert(String entityName, Object entity)
   at NHibernate.Impl.StatelessSessionImpl.Insert(Object entity)
   at Domain.Repositories.StatelessRepository.Add[T](T model) in c:\Users\Owner\Dev\\Domain\Repositories\StatelessRepository.cs:line 112
   at PatchImport.Program.InsertBeneficiaries() in c:\Users\Owner\Dev\PatchImport\Program.cs:line 232
   at PatchImport.Program.Main(String[] args) in c:\Users\Owner\Dev\PatchImport\Program.cs:line 60

The inner exception has no message attached to it.

I have looked at the SQL generated by NHibernate. Using the development settings, SQL generated for the input looks like this:

NHibernate: select STRUCTURE_SEQ.nextval from dual
NHibernate: INSERT INTO STRUCTURE_ASSIGN 
    (CLIENT_ID, STRUCTURE, NAME_CODE, START_DATE, STAT_SEQ) 
    VALUES (:p0, :p1, :p2, :p3, :p4);
    :p0 = 'MAST17' [Type: String (6)],
    :p1 = 'BENEFICIARY' [Type: String (11)],
    :p2 = 27845 [Type: Int64 (0)],
    :p3 = 28/10/2008 00:00:00 [Type: DateTime (0)],
    :p4 = 42830 [Type: Int32 (0)]

However, with production settings the generated SQL looks like this:

NHibernate: select STRUCTURE_SEQ.nextval from dual
NHibernate: INSERT INTO STRUCTURE_ASSIGN 
    (CLIENT_ID, STRUCTURE, NAME_CODE, START_DATE, STAT_SEQ) 
    VALUES (?, ?, ?, ?, ?);
    p0 = 'SCAR03' [Type: String (6)],
    p1 = 'BENEFICIARY' [Type: String (11)],
    p2 = 21525 [Type: Int64 (0)],
    p3 = 07/11/1977 00:00:00[Type: DateTime (0)],
    p4 = 35159 [Type: Int32 (0)]

Nothing else in the code has changed - only the configuration files.

Does anyone have any idea what is going wrong here? I suspect that it is either the use of sequences to generate the ID for the table (the STAT_SEQ column) , or whatever is causing the parameters in the second set of SQL to be ? rather than p0,p1 etc.

Why on earth would you use ODBC with a database that has a supported native driver? — Diego Mijelshon, Apr 26 '13 at 16:48
@DiegoMijelshon They're all running through ODBC at the client site. We tried using the native driver, but we couldn't get it to work at their site. We're not in a position to go fiddling around too much on their systems, so it would be easier for us to get the program to run through ODBC like their existing stuff is. — Oliver, Apr 26 '13 at 16:51

score 1 · Answer 1 · edited May 23 '17 at 11:57

The NHibernate OdbcDriver uses positional parameters. From looking at DriverBase you can see that even though it is using "?" within the SQL statement NHibernate names the parameters p0, p1, p2, etc... Unfortunately the Oracle ODBC driver doesn't like this as can be evidenced from the response to this question.

The easiest way to resolve your issue is to create a custom dialect based on DriverBase but with changes I have included below. This dialect will generate parameters with empty strings for names, which from my research is the format the Oracle ODBC driver requires.

Please note that I'm not 100% certain that this is the correct format, I've seen some examples where the parameter name is formatted ?0, ?1, ?2, etc... But if this is the case you can just amend ToParameterName to return StringHelper.SqlParameter + index instead.

Also please note that you cannot just inherit from DriverBase and override the necessary properties because one of the required changes is to a static property.

I'd also suggest creating a ticket on JIRA because this behaviour is definitely a bug.

public class OracleOdbcDriver : IDriver, ISqlParameterFormatter
{
  ....
  public bool UseNamedPrefixInSql
  {
    get { return false; }
  }

  public bool UseNamedPrefixInParameter
  {
    get { return false; }
  }

  public string NamedPrefix
  {
    get { return string.Empty; }
  }
  ....
  private static string ToParameterName(int index)
  {
    return String.Empty;
  }
  ....
}

Many thanks for your help. I didn't think to look into changing the driver! I will investigate and work out the exact format to use. — Oliver, Apr 26 '13 at 20:39
I spent a few hours trying this with various different parameter names, but unfortunately none seem to work. I might have to shelve it for now and try a different method. — Oliver, Apr 29 '13 at 13:15

score 0 · Accepted Answer · answered Apr 29 '13 at 16:07

Wow, this sure was a tough one to pin down.

As far as I can tell, the bug is in this line:

p2 = 21525 [Type: Int64 (0)],

In particular, if I change the type of NameCode to be an int rather than a long, the insert is successful. Somehow, ODBC is stumbling on 64 bit integers. It breaks without any kind of descriptive error message too, which is very frustrating. As this problem only appears when using ODBC rather than when running through the normal OracleClientDriver, it must be a bug in ODBC rather than a problem with my database configuration.

I also tried using ODBC directly:

var queryString = "insert into STRUCTURE_ASSIGN (CLIENT_ID, \"STRUCTURE\", NAME_CODE, STAT_SEQ, "
    + "START_DATE, FINISH_DATE, MEMO_NUMBER, ALTERNATE, ALTERNATE_NAME_CODE, ADMIN_NAME, "
    + "AUDIT_NAME, VALID_DATE, AUDIT_ACTION, DIRECTOR_CLASS) "
    + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?);";

using (var odbcConnection = new OdbcConnection(connectionString))
{
    OdbcCommand com = new OdbcCommand(queryString, odbcConnection);

    com.Parameters.AddWithValue("?", "aaa");
    com.Parameters.AddWithValue("?", "aaa");
    com.Parameters.AddWithValue("?", 1234);
    com.Parameters.AddWithValue("?", 100000);

    com.Parameters.AddWithValue("?", DateTime.Now);
    com.Parameters.AddWithValue("?", DateTime.Now);
    com.Parameters.AddWithValue("?", 1234);
    com.Parameters.AddWithValue("?", 'N');
    com.Parameters.AddWithValue("?", 1234);
    com.Parameters.AddWithValue("?", "TRUST");

    com.Parameters.AddWithValue("?", "TRUST");
    com.Parameters.AddWithValue("?", DateTime.Now);
    com.Parameters.AddWithValue("?", 'C');
    com.Parameters.AddWithValue("?", 'A');

    odbcConnection.Open();
    var rd = com.ExecuteNonQuery();
    odbcConnection.Close();
}

The above code works. However, if you change one of the lines like this:

com.Parameters.AddWithValue("?", (long)1234);

ODBC throws the same unhelpful error message than NHibernate wraps, so this is not an error in NHibernate.

Summary

If you are experiencing a similar problem, try changing any long variables to int. You might also want to try switching the type of other fields.

NHibernate with ODBC - GenericADOException: Could not Insert

2 Answers2

Summary