Bug 2128 - Support Mono.Data.Sqlite DataTable metadata
Summary: Support Mono.Data.Sqlite DataTable metadata
Status: ASSIGNED
Alias: None
Product: Android
Classification: Xamarin
Component: BCL Class Libraries ()
Version: 2.0
Hardware: PC Windows
: Normal normal
Target Milestone: ---
Assignee: Atsushi Eno
URL:
: 5462 ()
Depends on:
Blocks:
 
Reported: 2011-11-21 15:47 UTC by Jonathan Pobst
Modified: 2016-12-01 10:43 UTC (History)
10 users (show)

Tags: XATriaged
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 for Bug 2128 on Developer Community or GitHub if you have new information to add and do not yet see a matching new report.

If the latest results still closely match this report, you can use the original description:

  • Export the original title and description: Developer Community HTML or GitHub Markdown
  • Copy the title and description into the new report. Adjust them to be up-to-date if needed.
  • Add your new information.

In special cases on GitHub you might also want the comments: GitHub Markdown with public comments

Related Links:
Status:
ASSIGNED

Description Jonathan Pobst 2011-11-21 15:47:59 UTC
From https://bugzilla.novell.com/show_bug.cgi?id=677096.

Problem: Android 2.2 includes enough in libsqlite.so to make many uses of
Mono.Data.Sqlite work, but things break when trying to use Mono.Data.Sqlite +
System.Data.DataTable, because the sqlite3_column_origin_name() function is
missing.

See also:

http://lists.ximian.com/mailman/private/monodroid/2011-February/003302.html

It might be useful to fix the DataTable Load & Fill methods so that
sqlite3_column_origin_name() is avoided; the above email suggests:


  SqliteDataReader reader = mycommand.ExecuteReader();

  // Add all the columns.
  for (int i = 0; i < reader.FieldCount; i++)
  {
    DataColumn col = new DataColumn();
    col.DataType = reader.GetFieldType(i);
    col.ColumnName = reader.GetName(i);
    dt.Columns.Add(col);
  }

  while (reader.Read())
  {
    DataRow row = dt.NewRow();
    for (int i = 0; i < reader.FieldCount; i++)
    {
      // Ignore Null fields.
      if (reader.IsDBNull(i)) continue;

      if (reader.GetFieldType(i) == typeof(String))
      {
        row[dt.Columns[i].ColumnName] = reader.GetString(i);
      }
      else if (reader.GetFieldType(i) == typeof(Int16))
      {
        row[dt.Columns[i].ColumnName] = reader.GetInt16(i);
      }
      else if (reader.GetFieldType(i) == typeof(Int32))
      {
        row[dt.Columns[i].ColumnName] = reader.GetInt32(i);
      }
      else if (reader.GetFieldType(i) == typeof(Int64))
      {
        row[dt.Columns[i].ColumnName] = reader.GetInt64(i);
      }
      else if (reader.GetFieldType(i) == typeof(Boolean))
      {
        row[dt.Columns[i].ColumnName] = reader.GetBoolean(i); ;
      }
      else if (reader.GetFieldType(i) == typeof(Byte))
      {
        row[dt.Columns[i].ColumnName] = reader.GetByte(i);
      }
      else if (reader.GetFieldType(i) == typeof(Char))
      {
        row[dt.Columns[i].ColumnName] = reader.GetChar(i);
      }
      else if (reader.GetFieldType(i) == typeof(DateTime))
      {
        row[dt.Columns[i].ColumnName] = reader.GetDateTime(i);
      }
      else if (reader.GetFieldType(i) == typeof(Decimal))
      {
        row[dt.Columns[i].ColumnName] = reader.GetDecimal(i);
      }
      else if (reader.GetFieldType(i) == typeof(Double))
      {
        row[dt.Columns[i].ColumnName] = reader.GetDouble(i);
      }
      else if (reader.GetFieldType(i) == typeof(float))
      {
        row[dt.Columns[i].ColumnName] = reader.GetFloat(i);
      }
      else if (reader.GetFieldType(i) == typeof(Guid))
      {
        row[dt.Columns[i].ColumnName] = reader.GetGuid(i);
      }
    }

    dt.Rows.Add(row);
  }

  reader.Close();

---

Alternatively, it might be possible to use this bit of information (from Dragos
Pop): use  the SQL Command:

    PRAGMA table_info(<table name>)

Though I imagine that would require parsing textual output, so it might not be
an improvement.
Comment 2 Jonathan Pobst 2012-03-28 17:12:06 UTC
In case anyone else happens on this code, here's a complete method to fill a datatable:

private static void FillDatatable (DbCommand command, DataTable dt)
{
	var reader = command.ExecuteReader ();
	var len = reader.FieldCount;

	// Create the DataTable columns
	for (int i = 0; i < len; i++)
		dt.Columns.Add (reader.GetName (i), reader.GetFieldType (i));

	dt.BeginLoadData ();

	var values = new object[len];

	// Add data rows
	while (reader.Read ()) {
		for (int i = 0; i < len; i++)
			values[i] = reader[i];

		dt.Rows.Add (values);
	}

	dt.EndLoadData ();

	reader.Close ();
	reader.Dispose ();
}
Comment 3 hr 2012-04-30 09:56:15 UTC
Hello,

is there any progress on this issue? With the above workaround we would need to change the adaptater created by visual studio. Other threads propose to compile sqlite with the SQLITE_ENABLE_COLUMN_METADATA compiler switch to get the adapter working. Why is it not provided by Xamarin?

Thanks for your support.
Regards
Hendrik.
Comment 4 Jonathan Pobst 2012-04-30 10:35:58 UTC
The sqlite binary ships on the Android device by the device vendor, not with Mono for Android.  Therefore, Xamarin has no control over the sqlite binary.
Comment 5 hr 2012-05-07 09:41:03 UTC
Hello,

in my opinion this is a behavior the wrapper should handle properly or it should not be included into the wrapper. (If the wrapper provides a method or class then the wrapper is responsible that it is working.) 

I made some changes here https://github.com/hruemmler/mono/commit/34bce75f5806e520cee321e452fec06403860d76 that got the adapter working with a little test application. Maybe this is something that could be included into the wrapper or is there someting badly wrong  or missing.

Thanks for your support.
Regards
Hendrik.
Comment 6 Jonathan Pryor 2012-05-24 15:12:47 UTC
*** Bug 5165 has been marked as a duplicate of this bug. ***
Comment 7 Goncalo Oliveira 2012-06-22 07:23:28 UTC
Don't know if this helps anyone or if it is completely unrelated, but I was having this problem with Android 4.0.3, packed with SQLite 3.7.5, so, not that old.

The sqlite documentation says the following:

SQLITE_ENABLE_COLUMN_METADATA

When this C-preprocessor macro is defined, SQLite includes some additional APIs that provide convenient access to meta-data about tables and queries. The APIs that are enabled by this option are:

sqlite3_column_database_name()
sqlite3_column_database_name16()
sqlite3_column_table_name()
sqlite3_column_table_name16()
sqlite3_column_origin_name()
sqlite3_column_origin_name16()
sqlite3_table_column_metadata()

Adding the compilation flag to the project solved the issue.
Comment 8 Mikayla Hutchinson [MSFT] 2013-05-15 20:18:22 UTC
See also bug 327.
Comment 9 Mikayla Hutchinson [MSFT] 2013-05-17 17:20:27 UTC
I fixed the crash (tracked as bug 327) by omitting the metadata when sqlite3_column_origin_name is missing, the same way as is done in Xamarin.iOS.

The table will be missing metadata, so I'm leaving this bug open, as it contains a number of better fallback proposals.
Comment 10 Fleischer 2014-01-05 00:10:43 UTC
Small improvement to the workaround:
When you use dt.LoadDataRow(values, LoadOption.PreserveChanges) instead of dt.Rows.Add(values) the DataAdapter.Update method will recognize the changes->Update, or new lines->Insert. This will prevent using the non implemented sqlite3_column_origin_name method for the update method.
Comment 11 Rolf Bjarne Kvinge [MSFT] 2015-04-16 10:33:56 UTC
*** Bug 5462 has been marked as a duplicate of this bug. ***
Comment 12 Marek Habersack 2016-09-01 12:20:56 UTC
@atsushi, can you check the status of this bug against our bundled sqlite in master and cycle8? Perhaps the issue is gone with the inclusion of sqlite in XA?
Comment 13 Elbes 2016-12-01 10:43:29 UTC
Issue not gone, I'm still getting the error when trying to use SqliteDataAdapter and SqliteCommandBuilder to create InsertCommand, UpdateCommand and DeleteCommand dynamically to insert DataSets in the sqlite's database. Someone knows further information about how to retrieve metadata from Android's Sqlite tables?