Bug 638 - invalid format string when loading into a datatable records from the sqlite db where they have datetime fields with null values.
Summary: invalid format string when loading into a datatable records from the sqlite d...
Status: RESOLVED FIXED
Alias: None
Product: Class Libraries
Classification: Mono
Component: Mono.Data.Sqlite ()
Version: unspecified
Hardware: Macintosh Mac OS
: Normal normal
Target Milestone: Untriaged
Assignee: Rolf Bjarne Kvinge [MSFT]
URL:
Depends on:
Blocks:
 
Reported: 2011-09-05 09:32 UTC by Loukas
Modified: 2012-03-06 19:29 UTC (History)
4 users (show)

Tags: monotouch
Is this bug a regression?: ---
Last known good build:

Notice (2018-05-24): bugzilla.xamarin.com is now in read-only mode.

Please join us on Visual Studio Developer Community and in the Xamarin and Mono organizations on GitHub to continue tracking issues. Bugzilla will remain available for reference in read-only mode. We will continue to work on open Bugzilla bugs, copy them to the new locations as needed for follow-up, and add the new items under Related Links.

Our sincere thanks to everyone who has contributed on this bug tracker over the years. Thanks also for your understanding as we make these adjustments and improvements for the future.


Please create a new report on GitHub or Developer Community with your current version information, steps to reproduce, and relevant error messages or log files if you are hitting an issue that looks similar to this resolved bug and you do not yet see a matching new report.

Related Links:
Status:
RESOLVED FIXED

Description Loukas 2011-09-05 09:32:31 UTC
The exception is thrown when i try to load into a datatable records from the sqlite db where they have datetime fields with null values.

sample code

public DataTable LoadQueryDataTable(string szCommand)
{
  SqliteCommand cmd = null;
  SqliteDataAdapter da = null;

  cmd = this.Connection.CreateCommand();
  cmd.CommandText = szCommand;
  cmd.CommandType = CommandType.Text;

  da = new SqliteDataAdapter(szCommand, Connection);
  da.SelectCommand = cmd;

  DataTable dtGet = new DataTable();
  da.Fill(dtGet);//////Exception is thrown here!!!!!!!!!

  return dtGet;

}

i have also tried this with the same results

public DataTable LoadQueryDataTable(string szCommand)
{
  SqliteCommand cmd = null;
  SqliteDataAdapter da = null;

  cmd = this.Connection.CreateCommand();
  cmd.CommandText = szCommand;
  cmd.CommandType = CommandType.Text;

  SqliteDataReader dr = cmd.ExecuteReader();

  DataTable dtGet = new DataTable();
  dtGet.Load(dr); /////Exception is thrown here!!!!!!!!!
  dr.Close();

  return dtGet;

}

STACK TRACE

  at System.DateTime.ParseExact (System.String s, System.String[] formats, IFormatProvider provider, DateTimeStyles style) [0x0005c] in /Developer/MonoTouch/Source/mono/mcs/class/corlib/System/DateTime.cs:1761 
  at Mono.Data.Sqlite.SqliteConvert.ToDateTime (System.String dateText) [0x00041] in /Developer/MonoTouch/Source/mono/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLiteConvert.cs:163 
  at Mono.Data.Sqlite.SqliteConvert.ToDateTime (IntPtr ptr, Int32 len) [0x00000] in /Developer/MonoTouch/Source/mono/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLiteConvert.cs:217 
  at Mono.Data.Sqlite.SQLite3.GetDateTime (Mono.Data.Sqlite.SqliteStatement stmt, Int32 index) [0x00000] in /Developer/MonoTouch/Source/mono/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLite3.cs:588 
  at Mono.Data.Sqlite.SQLite3.GetValue (Mono.Data.Sqlite.SqliteStatement stmt, Int32 index, Mono.Data.Sqlite.SQLiteType typ) [0x000d3] in /Developer/MonoTouch/Source/mono/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLite3.cs:919 
  at Mono.Data.Sqlite.SqliteDataReader.GetValue (Int32 i) [0x00033] in /Developer/MonoTouch/Source/mono/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLiteDataReader.cs:796 
  at Mono.Data.Sqlite.SqliteDataReader.get_Item (Int32 i) [0x00000] in /Developer/MonoTouch/Source/mono/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLiteDataReader.cs:1023 
  at System.Data.Common.DbDataAdapter.FillFromReader (System.Data.DataTable table, IDataReader reader, Int32 start, Int32 length, System.Int32[] mapping, LoadOption loadOption) [0x0003e] in /Developer/MonoTouch/Source/mono/mcs/class/System.Data/System.Data.Common/DbDataAdapter.cs:365 
  at System.Data.DataTable.Load (IDataReader reader, LoadOption loadOption) [0x0002f] in /Developer/MonoTouch/Source/mono/mcs/class/System.Data/System.Data/DataTable.cs:2855 
  at System.Data.DataTable.Load (IDataReader reader) [0x00011] in /Developer/MonoTouch/Source/mono/mcs/class/System.Data/System.Data/DataTable.cs:2836 
  at gr.entersoft.esmobile.DBHandler.LoadQueryDataTable (System.String szCommand) [0x00038] in /Users/stavrosmenegos/Projects/ESMobile/SFA/gr.entersoft.esmobile/gr.entersoft.esmobile/Handlers/DBHandler.cs:382
Comment 1 Miguel de Icaza [MSFT] 2012-01-03 15:47:00 UTC
Resetting priority since Blocker is now reserved for regressions.
Comment 2 Miguel de Icaza [MSFT] 2012-01-09 15:48:43 UTC
Rolf, do you mind taking a look at this?
Comment 3 Rolf Bjarne Kvinge [MSFT] 2012-01-10 08:34:15 UTC
I have not been able to reproduce this error, I tried the following code:

using (var conn = new SqliteConnection ()) {
  conn.ConnectionString = "URI=file://./test.db, version=3";
  conn.Open ();
  var cmd = (SqliteCommand) conn.CreateCommand ();
  cmd.CommandText = @"
  CREATE TABLE IF NOT EXISTS TestNullableDateTime (nullable datetime NULL, dummy int);
  INSERT INTO TestNullableDateTime (nullable, dummy) VALUES (NULL, 2);
  ";
  cmd.ExecuteNonQuery ();

  var query = "SELECT * FROM TestNullableDateTime;";
  cmd = (SqliteCommand) conn.CreateCommand ();
  cmd.CommandText = query;
  cmd.CommandType = CommandType.Text;

  var da = new SqliteDataAdapter (query, conn);
  da.SelectCommand = cmd;

  var dt = new DataTable ();
  da.Fill (dt);
}

can you provide a complete sample that fails for you?
Comment 4 Jim Goff 2012-03-04 20:17:45 UTC
It's impossible to read the main table in Apple's Photo Library (Photos.sqlite) because the 13th & 14th fields are both defined as type TIMESTAMP.  This explicit use of type TIMESTAMP is new to IOS 5, as it is to Sqlite3.
You will encounter this if you attempt to use Mono.Data.Sqlite to read  Media/PhotoData/Photos.sqlite.  I have written a standalone console mode Mono program which creates a one record sqlite3 data table.  It will choke when it attempts to reead the field of type TIMESTAMP.

I will attempt to paste the source code below. In the meantime if you know a Sqlite work-around, I would appreciate if you could email it to me.

Thanks,
Jim Goff

------------
using System;
using System.IO;
using Mono.Data.Sqlite;

namespace ConsoleTest
{
	class MainClass
	{
		public static void Main (string[] args)
		{
			
			string fqp="jTestDb.db3";
			CreateSQLiteDatabase(fqp);
			InsertData(fqp);
			
			//  reading data which fails on Date2			
			SqliteConnection sqlCon = new SqliteConnection ("Data Source = "+fqp);
			sqlCon.Open ();
			SqliteCommand sqlCom = new SqliteCommand (sqlCon);
			sqlCom.CommandText = "SELECT * FROM CUSTOMERS";
			SqliteDataReader dbReader = sqlCom.ExecuteReader ();
						
			dbReader.Read ();
		    	string id= Convert.ToString (dbReader["ID"]);
			string fName = Convert.ToString (dbReader["FirstName"]);
                       
			// this date is written as an INTEGER & can be read correctly 
			string date1= Convert.ToString (dbReader["Date1"]);
			
			Console.WriteLine ("id="+id);
			Console.WriteLine ("firstName="+fName);
			Console.WriteLine ("date1="+date1); 
			Console.WriteLine ("Date = "+FixDate(Convert.ToInt64(date1)));
  	             
			// this date is written as type TIMESTAMP, it CANNOT be read!!
			string date2= Convert.ToString (dbReader["Date2"]);
			Console.WriteLine ("date2="+date2);			 
			
			dbReader.Close();
			sqlCon.Close();			            			
			Console.ReadKey();							
		}
		
		private static string FixDate(long x){
		DateTime obj = new DateTime(2000,12,31,20, 0, 0);	
		return obj.AddSeconds(x).ToString("s")+"Z";							
		}
		
		
		private static void CreateSQLiteDatabase (string databaseFile)
		{
											
					File.Delete(databaseFile);						
					SqliteConnection.CreateFile (databaseFile); // Create the database
					
					// Connect to the database
		using (SqliteConnection sqlCon = new SqliteConnection (String.Format ("Data Source = {0};", databaseFile)))
					{						
						sqlCon.Open ();						
						using (SqliteCommand sqlCom = new SqliteCommand (sqlCon))
					{
						sqlCom.CommandText = "CREATE TABLE Customers (ID INTEGER PRIMARY KEY,
						FirstName VARCHAR(20), LastName VARCHAR(20), Date1 INTEGER, Date2 TIMESTAMP)";
							sqlCom.ExecuteNonQuery ();
						}
						
						sqlCon.Close ();					
					}
					
					Console.WriteLine("Database created!");								
		
		}
		private static void InsertData (string databaseFile)
		{				
				// Connect to database
				using (SqliteConnection sqlCon = new SqliteConnection (String.Format ("Data Source = {0}", databaseFile)))
				{
					
					sqlCon.Open ();
					
					using (SqliteCommand sqlCom = new SqliteCommand (sqlCon))
					{
						
						sqlCom.CommandText = "INSERT INTO Customers (FirstName,
						LastName, Date1, Date2) VALUES (@firstName, @lastName, @date1, @date2)";
						sqlCom.Parameters.Add (new SqliteParameter ("@firstName", "John"));
						sqlCom.Parameters.Add (new SqliteParameter ("@lastName", "Smith"));
						sqlCom.Parameters.Add (new SqliteParameter ("@date1", 352221095));
						sqlCom.Parameters.Add (new SqliteParameter ("@date2", 352221095));
						
						sqlCom.ExecuteNonQuery ();
					
					}
					//end using sqlCom
					
					sqlCon.Close ();
				
				}
				//end using sqlCon
				
				Console.WriteLine( "Customer data inserted.");						
			
		}//end void InsertData
		
		
}
}
Comment 5 Rolf Bjarne Kvinge [MSFT] 2012-03-06 09:02:58 UTC
Thanks for the test case, I can reproduce this now.
Comment 6 Rolf Bjarne Kvinge [MSFT] 2012-03-06 19:29:32 UTC
Fixed in mono (master: 257b82a2, 2.10: 1e35d53d, mobile-master: dceef113, monotouch: eeda7f87).

The fix will be included in the MonoTouch 5.3 beta.

Note that the fix adds another date type, so you will need to tell Sqlite that you want that date type to be used. This is done in the connection string like this: "DateTimeFormat=UnixEpoch". Until then you can fetch the value as an int and then do the proper calculations:

new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc).AddSeconds (dbReader.GetInt32 ("Date2"))