Insert statement is normal insert statement to a table that contains at least one timestamp field.
insert into track (path, ..., dateAdded) values ('somefile', ..., '')
The insert succeeds, but the next select for that row fails. Database contains date 1988-12-30. DateTime.MinValue is 0001-01-01 00:00:00. I think empty string should either be written as null or minimum value.
Exception stack trace is:
String was not recognized as a valid DateTime. stack: at System.DateTimeParse.ParseExactMultiple(String s, String[] formats, DateTimeFormatInfo dtfi, DateTimeStyles style)
at System.DateTime.ParseExact(String s, String[] formats, IFormatProvider provider, DateTimeStyles style)
at System.Data.SQLite.SQLiteConvert.ToDateTime(String dateText)
at System.Data.SQLite.SQLiteConvert.ToDateTime(IntPtr ptr, Int32 len)
at System.Data.SQLite.SQLite3.GetDateTime(SQLiteStatement stmt, Int32 index)
at System.Data.SQLite.SQLite3.GetValue(SQLiteStatement stmt, Int32 index, SQLiteType typ)
at System.Data.SQLite.SQLiteDataReader.GetValue(Int32 i)
at System.Data.SQLite.SQLiteDataReader.GetValues(Object[] values)
at System.Data.ProviderBase.DataReaderContainer.CommonLanguageSubsetDataReader.GetValues(Object[] values)
at System.Data.ProviderBase.SchemaMapping.LoadDataRow()
at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)
at System.Data.DataTable.Load(IDataReader reader)
at SQLite.NET.SQLiteClient.Execute(String query)
This is the code I'm using to execute SQL statements:
DataTable table = new DataTable();
using (SQLiteCommand cmd = _connection.CreateCommand())
{
cmd.CommandText = query;
//SQLiteDataReader reader = cmdExecuteReader();
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
table.Load(reader);
reader.Close();
}
}
<hr /><i>mistachkin added on 2011-09-30 20:55:10 UTC:</i><br />
Currently, the SQLite3.GetValue method attempts to convert empty strings to the specified type of the underlying database column; however, this will obviously fail for all value types.
In theory, I could "fix" this behavior; however, there would be a high risk for breaking compatibility with applications that may rely on the current behavior.
The workaround for this issue is to use NULL instead of an empty string as NULL is handled correctly by the SQLite3.GetValue method for all types.
<hr /><i>mistachkin added on 2011-09-30 20:57:39 UTC:</i><br />
I'm still unclear on one point: Where is the value '1988-12-30' coming from in your example?
<hr /><i>mistachkin added on 2011-09-30 21:02:41 UTC:</i><br />
See check-in [e81a7d474c] for unit tests that demonstrate the current behavior.
<hr /><i>anonymous added on 2011-09-30 21:49:02 UTC:</i><br />
I looked at the tests for current behavior and tried to execute commands from the tests in my app:
CREATE TABLE t1(x INTEGER, y DATETIME);
INSERT INTO t1 (x, y) VALUES(1, NULL)
SELECT x, y FROM t1 ORDER BY x
This worked fine and I got empty string for "y" column.
INSERT INTO t1 (x, y) VALUES(1, '') -- executes OK (at this point I looked at the database file using SQLite Expert - it showed this: http://imageshack.us/photo/my-images/18/testlh.png/)
SELECT x, y FROM t1 ORDER BY x -- error (String was not recognized as a valid DateTime - same call stack as in first message).
I'm not sure what tests in your environment show but this doesn't seem like correct behavior. If I am going to put empty string value to datetime column, I would expect it to be filled with NULL. For me, it's not a very big deal to change empty string to null in insert statements in the app. But, it's not good that I can actually put the "bad" data in the DB but then wrapper fails on reading it. So maybe the fix should be done on read rather than write. But I'm just guessing here, you'll know the best :)
<hr /><i>mistachkin added on 2011-10-02 01:25:12 UTC:</i><br />
The NULL value and empty string are *very* different values and receive different handling by the current code.
<hr /><i>anonymous added on 2011-10-04 09:43:00 UTC:</i><br />
Of course null and empty string are very different. But sorry, I don't see the reasoning behind the decission in "working as designed". Inserting empty in datetime field leads to unhanded exception on read (select). At least the exception should not be left unhandled?
That said, I have looked at the code and method in SQLite class:
internal override object GetValue(SQLiteStatement stmt, int index, SQLiteType typ)
could maybe return DateTime.MinValue in case of convert failure? According to MSDN, that's the value of unitialized DateTime variable.
<hr /><i>mistachkin added on 2011-10-04 09:51:38 UTC:</i><br />
To be fair, this is not a new behavior. The current handling of empty strings by the DateTime conversion code has been there for quite some time and changing it right now seems too risky (i.e. too likely to break existing applications).
|
