SQLite query not working using IQ Driver or by referencing SQLite.dll

PascalLaurin

I'm trying to run a SQLite query on an existing database file (created by an application) but I'm failing. The application use version 1.0.82.0 of SQLite.

If I'm trying to use IQ driver I get a FormatException (String was not recognized as a valid DateTime.) I think the latest versions of SQLite changed the way Date and Time is serialized.

Next I tried to reference SQLite.dll directly and write my query like this:

using (var connection = new SQLiteConnection(cnx))
using (var sqlCommand = connection.CreateCommand())
{
	connection.Open();
	sqlCommand.CommandType = CommandType.Text;
	sqlCommand.CommandText = "SELECT * FROM Table";
	var result = sqlCommand.ExecuteScalar();
	SQLiteDataReader rdr = sqlCommand.ExecuteReader();
	connection.Close();
}

but now I get a DllNotFoundException (Unable to load DLL 'SQLite.Interop.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E). This is because where the SQLite.dll sit there are two sub-folders x86 and x64 each with a SQLite.Interop.dll which I guess is a native unmanaged dll.

So I went to change the advance option 'Do not shadow assembly references' and now I get a BadImageFormatException (An attempt was made to load a program with an incorrect format. (Exception from HRESULT: 0x8007000B).

The only way I finally managed to make this work is by using NuGet in the Beta version. Unfortunately, I need to provide queries to other people I work with and not all of them have a Premium license.

Does anybody have the same problem I have? Any idea how to make this work without using NuGet?

JoeAlbahari

The FormatException is probably occuring because non-DateTime data has been written to a DateTime column. SQLite doesn't enforce correctness of types, which is a problem for object/relational mappers such as LINQ to SQL.

A query with a NuGet reference will work with users who don't have a Premium license. LINQPad has a dialog that downloads NuGet packages referenced by a query and this dialog is not disabled for free users. They won't be able to add more NuGet packages to the query, but they can use the ones that have been saved with the query.

Note that a BadImageFormatException is usually triggered by a X86/X64 mismatch.

PascalLaurin

Thanks Joe,

For the record, the same database can be read by version 1.0.82.0 but not the previous version we used 1.0.6?.0.

I'll stick to create SQLite queries using the NuGet package.

Govert

I had the same problem. What worked for me was to add the following line on top of my script (before the assembly that references SQLite is user):
System.Environment.SetEnvironmentVariable("PreLoadSQLite_BaseDirectory", @"C:\..Path..to..lib\");
where C:\..Path..to..lib\ contains the System.Data.Sqlite.dll and has an x86 subdirectory with SQLite.Interop.dll in.

I'm guessing that SQLite is looking for the x86 directory relative to the executing assembly, and not relative to the System.Data.Sqlite.dll. For LinqPad it won't work, since the assembly is compiled elsewhere, and the Shadow Copy option won't copy the required SQLite.Interop.dll file too.