Hi all,
I'm trying to use SQLite 3 with NHibernate, and am getting an error when saving an object with a native ID.

The error is to do with not being able to find table 'hibernate_unique_key', even though I can see the SQL which has created and inserted into the table.

Viewing the database shows that there is, a record in the 'hibernate_unique_key', created when the database was created.

Any thoughts,

Thanks,

James

Hibernate version: NHibernate 1.0.2.0

Mapping documents:
<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE hibernate-mapping
PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
<class name="Evaluator.Project, Evaluator.Classes" table="Projects">
<id name="ID" column="id" type="integer">
<generator class="native" />
</id>
<property name="Name" />
</class>
</hibernate-mapping>


Name and version of the database you are using:
SQLite 3

The generated SQL (show_sql=true):
drop table Projects
Unsuccessful: no such table: Projects
drop table hibernate_unique_key
Unsuccessful: no such table: hibernate_unique_key
create table Projects (
id INTEGER not null,
Name TEXT,
primary key (id)
)
create table hibernate_unique_key (
next_hi INTEGER
)
insert into hibernate_unique_key values ( 1 )
'ACHIIVE_evaluator.exe': Loaded 'c:\p4\achiive\ach-001 - achiive evaluator\prototype\prototype\bin\hashcodeprovider.dll', No symbols loaded.
NHibernate.Id.TableGenerator: 2006-07-30 09:16:58,688 [4004] ERROR NHibernate.Id.TableGenerator - could not read a hi value
Finisar.SQLite.SQLiteException: no such table: hibernate_unique_key
at Finisar.SQLite.sqlite3.Throw()
at Finisar.SQLite.sqlite3.CheckOK()
at Finisar.SQLite.sqlite3.compile(String zSql)
at Finisar.SQLite.OneSQLStatement.Compile()
at Finisar.SQLite.SQLiteDataReader.ExecuteFirstStep()
at Finisar.SQLite.SQLiteDataReader.EnsureInitialization()
at Finisar.SQLite.SQLiteDataReader.Read()
at NHibernate.Id.TableGenerator.Generate(ISessionImplementor session, Object obj)
NHibernate.Util.ADOExceptionReporter: 2006-07-30 09:16:58,719 [4004] WARN NHibernate.Util.ADOExceptionReporter - Finisar.SQLite.SQLiteException: no such table: hibernate_unique_key
at Finisar.SQLite.sqlite3.Throw()
at Finisar.SQLite.sqlite3.CheckOK()
at Finisar.SQLite.sqlite3.compile(String zSql)
at Finisar.SQLite.OneSQLStatement.Compile()
at Finisar.SQLite.SQLiteDataReader.ExecuteFirstStep()
at Finisar.SQLite.SQLiteDataReader.EnsureInitialization()
at Finisar.SQLite.SQLiteDataReader.Read()
at NHibernate.Id.TableGenerator.Generate(ISessionImplementor session, Object obj)
at NHibernate.Id.TableHiLoGenerator.Generate(ISessionImplementor session, Object obj)
at NHibernate.Impl.SessionImpl.SaveWithGeneratedIdentifier(Object obj, CascadingAction action, Object anything)
NHibernate.Util.ADOExceptionReporter: 2006-07-30 09:16:58,735 [4004] ERROR NHibernate.Util.ADOExceptionReporter - no such table: hibernate_unique_key
Could not save object

Found the problem.

It seems that having 'New=True' in the connection string was causing the problem.

Setting 'New=true' for the database creation, then resetting it to 'false' and reconfiguring NHibernate before actually adding data, solved it.

Probably a bug in the SQLite dialect - maybe someone can put me right on this!

This is a great article thanks for sharing this informative information.. I will visit your blog regularly for some latest post.