Using SQLite from PowerShell on Windows 7x64?

Question

I am having difficulty loading System.Data.SQLite.dll from PowerShell on Windows 7 x64.

# x64 [void][System.Reflection.Assembly]::LoadFrom("C:\projects\PSScripts\lib\System.Data.SQLite.x64.DLL") # x86 #[void][System.Reflection.Assembly]::LoadFrom("C:\projects\PSScripts\lib\System.Data.SQLite.DLL") $conn = New-Object -TypeName System.Data.SQLite.SQLiteConnection $conn.ConnectionString = "Data Source=C:\temp\PSData.db" $conn.Open() $command = $conn.CreateCommand() $command.CommandText = "select DATETIME('NOW') as now, 'Bar' as Foo" $adapter = New-Object -TypeName System.Data.SQLite.SQLiteDataAdapter $command $dataset = New-Object System.Data.DataSet [void]$adapter.Fill($dataset) 

Trying to open a connection with x64 build results:

Aborting the “Open” call with “0” argument (s): “An attempt was made to load a program with the wrong format. (Exception from HRESULT: 0x8007000B)”

Trying to load x86 build results:

Raising a LoadFrom exception with argument "1": "Failed to load file or assembly 'file: /// C: \ projects \ PSScripts \ lib \ System.Data.SQLite.DLL' or one of its dependencies. An attempt was made Download the program in the wrong format. "

Any thoughts or ideas?

Answer 1

Is it possible that your x64 binary is corrupted? I can successfully use add-type for a recently downloaded copy of system.data.sqlite.dll using the code below, and I can create an instance of all related objects. I can also open the database without errors and successfully execute the query. Try this technique (essentially using Add-Type instead of LoadFrom) with your db and let me know.

Sample code for the SQLite PowerShell module:

 function Add-SqliteAssembly { # determine bitness (32 vs. 64) of current PowerShell session # I'm assuming bitness = system architecture here, which won't work on IA64, but who cares switch ( [intptr]::Size ) { 4 { $binarch = 'x86' } 8 { $binarch = 'x64' } } $modPath = $MyInvocation.MyCommand.Module.ModuleBase $SQLiteBinName = 'System.Data.SQLite.dll' $SQLiteBinPath = "$modPath\$binarch\$SQLiteBinName" Add-Type -Path $SQLiteBinPath } 

To use this module, save it in the sqlite.psm1 file and place it somewhere in the module path . Then place the two System.Data.SQLite.dll that you downloaded into subfolders, each of which is located in the corresponding folder (x86 or x64). Then in PowerShell type:

 Import-Module sqlite 

And then to actually load the assembly:

 Add-SqliteAssembly 

Now your source code (minus loadfrom stuff) should work.

Answer 2

You need to make sure that the System.Data.SQLite.dll file is correct for both versions (32 or 64) and .Net. For Win 7 x64, the default .Net is set to 3.5. Powershell will use .Net 3.5, even if you have installed 4.0 (or higher) clients. You can use .NET 4.0 in Powershell if you go through other hoops (discussed elsewhere).

Download the ADO SQLite package from:
http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

Locate Precompiled binaries for 64-bit Windows (.NET Framework 3.5 sp1) . Select the appropriate zip (with or without VC ++ runtime). Do not let 2008 (or 2010) in the file name fool you. This is a link to the version of VC ++ used for compilation. The project is relevant. The version at the time of this writing is 1.0.79.0 dated January 28, 2012. Unfortunately, the help file does not come with this package. The useful SQLite.Net.chm file is located in a separate source package in the docs \ folder.

Once the zip is extracted (no installation or registry fix required), specify your script file system in the dll using:

Add-Type -Path "<drive> <path> \ System.Data.SQLite.dll"

Example:
Add-Type -Path "C: \ sql \ sqliteFx35x64 \ System.Data.SQLite.dll"

The rest of the test code should work

Answer 3

I would try to create the YourApp.exe.config file in the application home directory ( powershell.exe.config in the powershell.exe.config directory, if it is PowerShell, remember that the location is different for x86 and x64):

 <?xml version="1.0"?> <configuration> <runtime> <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1"> <probing privatePath="C:\projects\PSScripts\lib"/> </assemblyBinding> </runtime> </configuration> 

If this does not help, double-check that all SQLite DLLs are installed correctly. See the manual (I myself know little about SQLite ADO.NET).