Mono on OS X: System.Data.SQLite does not work

Question

I am planning to do a project using Mono and SQLite as the database. Development is to be done primarily on a Mac. I have successfully setup Mono and tested System.Data.SQLite(managed dll). Simple test applications work perfectly.

However when I tried to use DataTable in my code it throws a runtime exception. Following is the code snippet:

public static void Main (string[] args)
{
    string connectionString = "Data Source=emp.db";

    try {
        using (SQLiteConnection conn = new SQLiteConnection(connectionString))
        {
            string query = "SELECT firstname, lastname FROM employees";

            using (SQLiteCommand comm = new SQLiteCommand(query, conn))
            {
                conn.Open();

                comm.CommandText = query;

                using (SQLiteDataReader reader = comm.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string firstname = reader.GetString(0);
                        string lastname  = reader.GetString(1);
                        Console.WriteLine("Name: " + firstname + " " + lastname);
                    }

                    DataTable dt = new DataTable();
                    dt.Load(reader);  // line 39 where problem occurs
                }
            }
        }
    } catch (Exception e) {
        Console.WriteLine(e);
    }
}

The above code get built successfully but does not run on my development machine and gives the following output on the terminal.

The output when I run the application is as below:

Name: John Doe
Name: Eric Smith
System.EntryPointNotFoundException: sqlite3_column_origin_name
  at (wrapper managed-to-native) System.Data.SQLite.UnsafeNativeMethods:sqlite3_column_origin_name (intptr,int)
  at System.Data.SQLite.SQLite3.ColumnOriginalName (System.Data.SQLite.SQLiteStatement stmt, Int32 index) [0x00000] in <filename unknown>:0 
  at System.Data.SQLite.SQLiteDataReader.GetSchemaTable (Boolean wantUniqueInfo, Boolean wantDefaultValue) [0x00000] in <filename unknown>:0 
  at System.Data.SQLite.SQLiteDataReader.GetSchemaTable () [0x00000] in <filename unknown>:0 
  at System.Data.Common.DataAdapter.BuildSchema (IDataReader reader, System.Data.DataTable table, SchemaType schemaType, MissingSchemaAction missingSchAction, MissingMappingAction missingMapAction, System.Data.Common.DataTableMappingCollection dtMapping) [0x0003b] in /private/tmp/monobuild/build/BUILD/mono-2.10.6/mcs/class/System.Data/System.Data.Common/DataAdapter.cs:284 
  at System.Data.DataTable.Load (IDataReader reader, LoadOption loadOption) [0x0001f] in /private/tmp/monobuild/build/BUILD/mono-2.10.6/mcs/class/System.Data/System.Data/DataTable.cs:2853 
  at System.Data.DataTable.Load (IDataReader reader) [0x00011] in /private/tmp/monobuild/build/BUILD/mono-2.10.6/mcs/class/System.Data/System.Data/DataTable.cs:2838 
  at SQLiteApp.MainClass.Main (System.String[] args) [0x00086] in /Users/nayaabkhan/Projects/SQLiteApp/SQLiteApp/Main.cs:37

I was surprised to see that the same above executable worked perfectly under windows, but was failing on my development machine.

Searching on the internet, I found that this problem has to do something with the sqlite dynamic library and must be compiled with SQLITE_ENABLE_COLUMN_METADATA. I've tried to compile sqlite with the SQLITE_ENABLE_COLUMN_METADATA as well. I don't know where to put the compiled libsqlite3.0.dylib.

Any help will be appreciated.

Answer 1

You need to put the dylib anywhere mono can find it.

You can find out where mono looks for native libraries by doing this:

export MONO_LOG_LEVEL=debug
export MONO_LOG_MASK=dll
mono yourprogram.exe

and verbose lookup output will be printed to the terminal. On my system mono looks first in the directory where the executable is, so putting the dylib there is probably the easiest. Then mono asks the system to find the dylib (by trying to open it without a path). The system typically looks in /usr/lib and maybe a few other places (this is of course system-dependent), but in any case you can add a path for the system to look in by setting LD_LIBRARY_PATH to that path. In this case you'll do this:

export LD_LIBRARY_PATH=/path/to/dylib:$LD_LIBRARY_PATH
mono yourprogram.exe