[Mono-bugs] [Bug 77268][Wis] New - Sqlite DateTime Parameters Handled Incorrectly

[bugzilla-daemon at bugzilla.ximian.com]

Please do not reply to this email- if you want to comment on the bug, go to the
URL shown below and enter your comments there.

Changed by jhill at arcfocus.com.

http://bugzilla.ximian.com/show_bug.cgi?id=77268

--- shadow/77268	2006-01-15 14:51:02.000000000 -0500
+++ shadow/77268.tmp.1774	2006-01-15 14:51:02.000000000 -0500
@@ -0,0 +1,141 @@
+Bug#: 77268
+Product: Mono: Class Libraries
+Version: 1.1
+OS: All
+OS Details: 
+Status: NEW   
+Resolution: 
+Severity: 
+Priority: Wishlist
+Component: Sys.Data.SqlClient
+AssignedTo: tsenganal at novell.com                            
+ReportedBy: jhill at arcfocus.com               
+QAContact: mono-bugs at ximian.com
+TargetMilestone: ---
+URL: 
+Cc: 
+Summary: Sqlite DateTime Parameters Handled Incorrectly
+
+Description of Problem:
+DbType.DateTime parameters are passed into SqliteCommands as numbers which
+cannot be used for datetime comparison in Sqlite (for instance, using the
+datetime functions provided with sqlite), and, if stored, will not be
+selected back out as DateTime parseable values.
+
+Steps to reproduce the problem:
+1. Create and populate a sqlite database:
+CREATE TABLE `FirstTable` (
+  `DateField` DateTime NOT NULL default ''
+);
+
+2. Create a C# program to insert / access this database:
+using System;
+using System.Data;
+using Mono.Data.SqliteClient;
+
+namespace AppX
+{
+	class X
+	{
+		static void Main(string[] args)
+		{
+			SqliteCommand cmd = new SqliteCommand();
+			SqliteConnection connection = new
+SqliteConnection(@"version=3,URI=file:C:\Temp\x.db");
+			connection.Open();
+			cmd.Connection = connection;
+			cmd.CommandText = "INSERT INTO FirstTable VALUES (:ADate);";
+			cmd.CommandType = CommandType.Text;
+
+			SqliteParameter p = new SqliteParameter(":ADate", DbType.DateTime);
+			p.Direction = ParameterDirection.Input;
+			p.Value = DateTime.Now;
+			cmd.Parameters.Add( p );
+			
+			cmd.ExecuteNonQuery();
+
+			connection.Close();
+		
+			DataSet ds = new DataSet();
+			DataTable dt = ds.Tables.Add();	
+			dt.Columns.Add("DateField", typeof(DateTime));
+			//dt.Columns.Add("DateField");
+
+			cmd = new SqliteCommand();
+			connection.Open();
+			cmd.Connection = connection;
+			cmd.CommandText = "SELECT * FROM FirstTable;";
+			cmd.CommandType = CommandType.Text;
+			
+			SqliteDataAdapter da = new SqliteDataAdapter(cmd);
+			da.TableMappings.Add("Table",ds.Tables[0].TableName);
+			da.Fill(ds);
+
+			Console.WriteLine(ds.Tables[0].Rows[0][0].ToString());
+		}
+	}
+}
+
+3. Build x.cs:
+mcs x.cs -r:Mono.Data.SqliteClient -r:System.Data
+
+4. Execute x.exe
+mono x.exe
+
+
+Actual Results:
+Unhandled Exception: System.ArgumentException: System.InvalidCastException:
+Inva
+lid cast from Int64 to DateTime.
+   at System.Int64.System.IConvertible.ToDateTime(IFormatProvider provider)
+   at System.Convert.ToDateTime(Object value)
+   at System.Data.Common.DateTimeStorage.Set(Int32 record, Object value)
+   at System.Data.DataColumn.set_Item(Int32 record, Object value)Couldn't store
+<5985318908758851584> in DateField Column.  Expected type is DateTime.
+   at System.Data.DataColumn.set_Item(Int32 record, Object value)
+   at System.Data.DataTable.NewRecordFromArray(Object[] value)
+   at System.Data.DataTable.LoadDataRow(Object[] values, Boolean
+fAcceptChanges)
+
+   at System.Data.Common.SchemaMapping.LoadDataRow(Boolean clearDataValues,
+Bool
+ean acceptChanges)
+   at System.Data.Common.DbDataAdapter.FillLoadDataRow(SchemaMapping mapping)
+   at System.Data.Common.DbDataAdapter.FillFromReader(Object data, String
+srcTab
+le, IDataReader dataReader, Int32 startRecord, Int32 maxRecords, DataColumn
+pare
+ntChapterColumn, Object parentChapterValue)
+   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
+srcTable, ID
+ataReader dataReader, Int32 startRecord, Int32 maxRecords)
+   at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
+startR
+ecord, Int32 maxRecords, String srcTable, IDbCommand command,
+CommandBehavior be
+havior)
+   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord,
+Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior
+behavior)
+
+   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
+   at AppX.X.Main(String[] args)
+
+Expected Results:
+2006-01-15 18:37:17
+(The current DateTime)
+
+How often does this happen? 
+Always
+
+Additional Information:
+The above example fails because it attempts to load an Int64 value into a
+DataColumn that has been specified as DateTime.  Replacing:
+  dt.Columns.Add("DateField", typeof(DateTime));
+with the commented line:
+  dt.Columns.Add("DateField");
+will load the column as an Int64 value, rather than a DateTime.
+
+My guess would be that the best way to pass DateTime values into sqlite
+would be as strings of the format "yyyy-MM-dd HH:mm:ss" ; however, perhaps
+there is a more correct/efficient/precise way to do this.