title: SQLite access using Powershell and PSSqlite - SELECT query fails
body: <p>I am trying to write a Powershell script to write data to a SQLite database and to read from it. I'm using the <a href="https://github.com/RamblingCookieMonster/PSSQLite" rel="nofollow noreferrer">PSSQLite</a> module to do this.  The script works on one server, but not on the other. Both machines are Windows 2012 R2. </p>
  
  <p>Both machines look like this:</p>
  
  <pre><code>PS C:\&gt; $PSVersionTable
  
  Name                           Value
  ----                           -----
  PSVersion                      5.0.10586.117
  PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
  BuildVersion                   10.0.10586.117
  CLRVersion                     4.0.30319.42000
  WSManStackVersion              3.0
  PSRemotingProtocolVersion      2.3
  SerializationVersion           1.1.0.1
  
  
  PS C:\&gt; Import-Module PSSQLite
  PS C:\&gt; Get-Module PSSQLite
  
  ModuleType Version    Name                                ExportedCommands
  ---------- -------    ----                                ----------------
  Script     1.0.3      PSSQLite                            {Invoke-SQLiteBulkCopy, Invoke-SqliteQuery, New-SQLiteConn...
  
  PS C:\&gt;
  </code></pre>
  
  <p>I have a short Powershell script to create a SQLite database, create a table within it, write to the table, and then read from the table. The script looks like this:</p>
  
  <pre><code>Import-Module PSSQLite
  $secretsDatabase = ".\secrets.db"
  
  if (Test-Path $secretsDatabase) {
      Write-Host "$secretsDatabase  already exists"
  } else {
     $create_query = "create table secrets (tid string, password string, username string)"
     $cq_result  = Invoke-SqliteQuery -DataSource $secretsDatabase -Query $create_query 
  
     $add_secrets_query = "insert into secrets (tid, password, username) values ('xx1', 'somepassword', 'bob@example.com')"
     $as_result = Invoke-SqliteQuery -DataSource $secretsDatabase -Query $add_secrets_query
  }
  
  $secretsQuery = "select tid, password, username from secrets limit 1"
  
  $result = Invoke-SqliteQuery -DataSource $secretsDatabase -Query $secretsQuery
  Write-Host $result
  </code></pre>
  
  <p>When I run this Powershell script on one server, the output looks like this:</p>
  
  <pre><code>PS C:\&gt; C:\sqlite_test.ps1
  .\secrets.db  already exists
  @{tid=xx1; password=somepassword; username=bob@example.com}
  
  PS C:\&gt;
  </code></pre>
  
  <p>This is the expected output. </p>
  
  <p>When I run the same script on the other server, the output looks like this:</p>
  
  <pre><code>PS C:\&gt; C:\sqlite_test.ps1
  .\secrets.db  already exists
  Unable to find type [DBNullScrubber].
  At C:\Program Files\WindowsPowerShell\Modules\PSSQLite\1.0.3\Invoke-SqliteQuery.ps1:518 char:25
  +                         [DBNullScrubber]::DataRowToPSObject($row)
  +                         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo          : InvalidOperation: (DBNullScrubber:TypeName) [], RuntimeException
      + FullyQualifiedErrorId : TypeNotFound
  
  
  PS C:\&gt; 
  </code></pre>
  
  <p>On both systems, the SQL queries to create a table and to insert a row works. This is evidenced by using the command line utility on both machines and getting this result:</p>
  
  <pre><code>C:\&gt; sqlite3 secrets.db
  SQLite version 3.19.3 2017-06-08 14:26:16
  Enter ".help" for usage hints.
  sqlite&gt; .dump
  PRAGMA foreign_keys=OFF;
  BEGIN TRANSACTION;
  CREATE TABLE secrets (tid string, password string, username string);
  INSERT INTO secrets VALUES('xx1','somepassword','bob@example.com');
  COMMIT;
  sqlite&gt;
  </code></pre>
  
  <p>However the SELECT query on one machine fails because of the "[DBNullScrubber]" error.  How can I correct this problem on the one machine?</p>
  
  <p>------ UPDATE: August 13, 2017  more analysis -----------------</p>
  
  <p>I did some code spelunking at the GitHub repo for this module, into the file </p>
  
  <p><a href="https://raw.githubusercontent.com/RamblingCookieMonster/PSSQLite/master/PSSQLite/Invoke-SqliteQuery.ps1" rel="nofollow noreferrer">Invoke-SqliteQuery.ps1</a></p>
  
  <p>I went into my Powershell on the bad server and ran this code snippet from that file:</p>
  
  <pre><code>$cSharp = @'
                  using System;
                  using System.Data;
                  using System.Management.Automation;
  
                  public class DBNullScrubber
                  {
                      public static PSObject DataRowToPSObject(DataRow row)
                      {
                          PSObject psObject = new PSObject();
  
                          if (row != null &amp;&amp; (row.RowState &amp; DataRowState.Detached) != DataRowState.Detached)
                          {
                              foreach (DataColumn column in row.Table.Columns)
                              {
                                  Object value = null;
                                  if (!row.IsNull(column))
                                  {
                                      value = row[column];
                                  }
  
                                  psObject.Properties.Add(new PSNoteProperty(column.ColumnName, value));
                              }
                          }
  
                          return psObject;
                      }
                  }
  '@
  </code></pre>
  
  <p>This snippet defines the DBNullScrubber class. After that, I went to my command line on the bad server and ran:</p>
  
  <pre><code>PS C:\&gt;  Add-Type -TypeDefinition $cSharp -ReferencedAssemblies 'System.Data','System.Xml'
  Add-Type : (0) : Warning as Error: Invalid search path 'D:\Program Files\IBM\SQLLIB\LIB' specified in 'LIB environment variable' -- 'The system cannot find 
  the path specified. '
  (1) :                 using System;
  At line:1 char:2
  +  Add-Type -TypeDefinition $cSharp -ReferencedAssemblies 'System.Data' ...
  +  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo          : InvalidData: (error CS1668: W...th specified. ':CompilerError) [Add-Type], Exception
      + FullyQualifiedErrorId : SOURCE_CODE_ERROR,Microsoft.PowerShell.Commands.AddTypeCommand
  
  Add-Type : Cannot add type. Compilation errors occurred.
  At line:1 char:2
  +  Add-Type -TypeDefinition $cSharp -ReferencedAssemblies 'System.Data' ...
  +  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo          : InvalidData: (:) [Add-Type], InvalidOperationException
      + FullyQualifiedErrorId : COMPILER_ERRORS,Microsoft.PowerShell.Commands.AddTypeCommand
  
  
  PS C:&gt; 
  </code></pre>
  
  <p>I ran this on the good server and there were no errors.</p>
  
  <p>At this point I don't know what to do. How can I fix this?</p>
  
  <p>-----------------  Update Aug 13, 2017 - 12:11 ----------------</p>
  
  <p>I printed out $env:Lib. There was nothing defined on the good machine. On the bad machine it said:</p>
  
  <pre><code>PS C:\&gt; $env:Lib
  ;D:\Program Files\IBM\SQLLIB\LIB
  PS C:\&gt;
  </code></pre>