[nhibernate-1.0.1.0; SQLite ADO.NET 0.21_x86; sqlitedll-3_2_7; .NET 1.0.3705.6018]
Not sure if this is a SQLite bug or what. Anyway, when I manually create a db with DATETIME columns then everything is fine. But when I use SchemaExport with the bundled SQLiteDialect then Save works but Load fails, because the logical DATETIME columns have become SQLite NUMERIC columns, containing numbers not parseable by DateTime.
Strangely, SQLiteDialect maps DateTime to TEXT, not DATETIME or NUMERIC. I guess that's actually an attempt to avoid the NUMERIC mapping, since [1] implies that's what DATETIME becomes by default. However, like I said above, if DATETIME is used without trying to second-guess SQLite then I do in fact get a TEXT affinity which results in the correct behavior (values parseable by DateTime). Repro code and output is below.
So the solution seems to be to replace
Code:
RegisterColumnType(DbType.DateTime, "TEXT");
with
Code:
RegisterColumnType(DbType.DateTime, "DATETIME");
in SQLiteDialect.cs. Don't know about DbType.Time.
Ayende's NHibernate Query Analyzer has apparently avoided this problem by using hard-coded ddl, which also specifies DATETIME and VARCHAR types instead of TEXT.
Anyone seen this before? Anyone want to put this into JIRA? Someone might want to make a note in [2], I dunno. I'm fine with using my own SQLiteDialect replacement at the moment.
cmb
[1]
http://www.sqlite.org/datatype3.html[2]
http://wiki.nhibernate.org/display/NH/SQLiteBTW, how do I specify a different connection string for use only during SchemaExport? SQLite requires you add "New=True" when executing against a db for the first time. After that New=True will delete your db at every execution. Right now I am dumping the ddl to file and executing it manually with New=True, which is somewhat annoying.
BTW again, that ddl doesn't even work unmodified: I have to cut out the DROP statements, since Finisar throws an exception when you try to drop a nonexistant table. I don't suppose you can tell SchemaExport to exclude the drops or something? Not a big deal I guess.
--
Config:
Code:
<session-factory name="NHibernate.Examples">
<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
<property name="connection.driver_class">NHibernate.Driver.SQLiteDriver</property>
<property name="connection.connection_string">Data Source=nhibernate.db;Version=3</property>
<property name="show_sql">false</property>
<property name="dialect">NHibernate.Dialect.SQLiteDialect</property>
<property name="use_outer_join">true</property>
<property name="query.substitutions">true 1, false 0, yes 'Y', no 'N'</property>
<mapping assembly="NHibernate.Examples" />
</session-factory>
Code:
<class name="NHibernate.Examples.QuickStart.User, NHibernate.Examples" table="users">
<id name="Id" column="LogonId" type="String" length="20"><generator class="assigned" /></id>
<property name="UserName" column="Name" type="String" length="40"/>
<property name="Password" type="String" length="20"/>
<property name="EmailAddress" type="String" length="40"/>
<property name="LastLogon" type="DateTime"/>
</class>
--
Code fragments from an entry point class added to NHibernate.Examples:
Code:
private static void CreateDB (string path, string ddl)
{
using (SQLiteConnection cx = new SQLiteConnection("Data Source="+path+";Version=3;New=True"))
using (IDbCommand cmd = cx.CreateCommand())
{
cx.Open();
string[] stmts = ddl.Split('\n');
for (int i = 0; i < stmts.Length; i++)
{
if (stmts[i].ToLower().StartsWith("create")) // skip drops (finisar throws exception)
{
cmd.CommandText = stmts[i];
cmd.ExecuteNonQuery();
}
}
}
}
Code:
if (true)
{
CreateDB("nhibernate.db", "create table users (LogonId VARCHAR(20) primary key, Name VARCHAR(40) not null, Password VARCHAR(20) not null, EmailAddress VARCHAR(40) not null, LastLogon DATETIME not null)");
}
else // get ADOException-->FormatException below...
{
Configuration cfg = new Configuration();
cfg.AddAssembly("NHibernate.Examples");
SchemaExport se = new SchemaExport(cfg);
se.SetOutputFile("setup.sql");
se.Execute(true, false, false, false);
using (StreamReader sr = File.OpenText("setup.sql"))
{
CreateDB("nhibernate.db", sr.ReadToEnd());
}
//executes: "create table users (LogonId TEXT not null, Name TEXT, Password TEXT, EmailAddress TEXT, LastLogon TEXT, primary key (LogonId))"
}
new UserFixture().ValidateQuickStart();
--
Output with manual "LastLogon DATETIME":
Code:
NHibernate :INSERT INTO users (Name, Password, LastLogon, EmailAddress, LogonId) VALUES (@p0, @p1, @p2, @p3, @p4)
NHibernate :SELECT user0_.LogonId as LogonId0_, user0_.Name as Name0_, user0_.Password as Password0_, user0_.LastLogon as LastLogon0_, user0_.EmailAddress as EmailAdd4_0_ FROM users user0_ WHERE user0_.LogonId=@p0
NHibernate :UPDATE users SET Name = @p0, Password = @p1, LastLogon = @p2, EmailAddress = @p3 WHERE LogonId = @p4
NHibernate :SELECT this.LogonId as LogonId0_, this.Name as Name0_, this.Password as Password0_, this.LastLogon as LastLogon0_, this.EmailAddress as EmailAdd4_0_ FROM users this WHERE this.LastLogon > @p0
--
Output with SchemaExport "LastLogon TEXT":
Code:
NHibernate :INSERT INTO users (Name, Password, LastLogon, EmailAddress, LogonId) VALUES (@p0, @p1, @p2, @p3, @p4)
NHibernate :SELECT user0_.LogonId as LogonId0_, user0_.Name as Name0_, user0_.Password as Password0_, user0_.LastLogon as LastLogon0_, user0_.EmailAddress as EmailAdd4_0_ FROM user
s user0_ WHERE user0_.LogonId=@p0
Unhandled Exception: NHibernate.ADOException: could not load: [NHibernate.Examples.QuickStart.User#joe_cool] ---> System.FormatException: String was not recognized as a valid DateTime.
at System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, DateTimeStyles styles)
at System.DateTime.Parse(String s, IFormatProvider provider, DateTimeStyles styles)
at System.DateTime.Parse(String s, IFormatProvider provider)
at System.Convert.ToDateTime(String value, IFormatProvider provider)
at System.String.System.IConvertible.ToDateTime(IFormatProvider provider)
at System.Convert.ToDateTime(Object value)
at NHibernate.Type.DateTimeType.Get(IDataReader rs, Int32 index)
at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name)
at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String[] names, ISessionImplementor session, Object owner)
at NHibernate.Type.AbstractType.Hydrate(IDataReader rs, String[] names, ISessionImplementor session, Object owner)
at NHibernate.Loader.Loader.Hydrate(IDataReader rs, Object id, Object obj, ILoadable persister, ISessionImplementor session, String[][] suffixedPropertyColumns)
at NHibernate.Loader.Loader.LoadFromResultSet(IDataReader rs, Int32 i, Object obj, Key key, String suffix, LockMode lockMode, ILoadable rootPersister, ISessionImplementor session)
at NHibernate.Loader.Loader.InstanceNotYetLoaded(IDataReader dr, Int32 i, ILoadable persister, String suffix, Key key, LockMode lockMode, Key optionalObjectKey, Object optionalObject, IList hydratedObjects, ISessionImplementor session)
at NHibernate.Loader.Loader.GetRow(IDataReader rs, ILoadable[] persisters, String[] suffixes, Key[] keys, Object optionalObject, Key optionalObjectKey, LockMode[] lockModes, IList hydratedObjects, ISessionImplementor session)
at NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, IList hydratedObjects, Object optionalObject, Object optionalId, Key[] keys, Boolean returnProxies)
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies)
at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies)
at NHibernate.Loader.EntityLoader.Load(ISessionImplementor session, Object id, Object optionalObject, Object optionalId)
at NHibernate.Loader.EntityLoader.Load(ISessionImplementor session, Object id, Object optionalObject)
at NHibernate.Persister.EntityPersister.Load(Object id, Object optionalObject, LockMode lockMode, ISessionImplementor session)
--- End of inner exception stack trace ---
at NHibernate.Persister.EntityPersister.Load(Object id, Object optionalObject, LockMode lockMode, ISessionImplementor session)
at NHibernate.Impl.SessionImpl.DoLoad(Type theClass, Object id, Object optionalObject, LockMode lockMode, Boolean checkDeleted)
at NHibernate.Impl.SessionImpl.DoLoadByClass(Type clazz, Object id, Boolean checkDeleted, Boolean allowProxyCreation)
at NHibernate.Impl.SessionImpl.Load(Type clazz, Object id)
at NHibernate.Examples.QuickStart.UserFixture.ValidateQuickStart() in c:\products\thirdparty\nhibernate-1.0.1.0\src\nhibernate.examples\quickstart\userfixture.cs:line 44
at NHibernate.Examples.Class1.Main(String[] args) in c:\products\thirdparty\nhibernate-1.0.1.0\src\nhibernate.examples\class1.cs:line 90
--
Another fragment:
Code:
DataSet ds = ADOHelper.ExecuteDataSet("Data Source=nhibernate.db;Version=3", "SELECT LastLogon FROM users", null);
Console.WriteLine (ds.Tables[0].Rows[0][0].ToString());
--
Output with manual "LastLogon DATETIME":
Code:
12/9/2005 9:28:14 AM
--
Output with SchemaExport "LastLogon TEXT":
Code:
632697176390000000
Login
FAQ
Search
