使用 Powershell 和 PSSqlite 访问 SQLite - SELECT 查询失败 [英] SQLite access using Powershell and PSSqlite - SELECT query fails
问题描述
我正在尝试编写一个 Powershell 脚本来将数据写入 SQLite 数据库并从中读取数据.我正在使用 PSSQLite 模块来执行此操作.该脚本适用于一台服务器，但不适用于另一台服务器.两台机器都是Windows 2012 R2.
两台机器都是这样的:
PS C:\>$PS版本表名称值---- -----PS版本 5.0.10586.117PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}构建版本 10.0.10586.117CLR 版本 4.0.30319.42000WSManStackVersion 3.0PSRemotingProtocolVersion 2.3序列化版本 1.1.0.1PS C:\>导入模块 PSSQLitePS C:\>获取模块 PSSQLiteModuleType 版本名称 ExportedCommands---------- ------- ---- ----------------脚本 1.0.3 PSSQLite {Invoke-SQLiteBulkCopy、Invoke-SqliteQuery、New-SQLiteConn...PS C:\>我有一个简短的 Powershell 脚本来创建 SQLite 数据库，在其中创建一个表，写入该表，然后从该表中读取.脚本如下所示:
导入模块 PSSQLite$secretsDatabase = ".\secrets.db"如果(测试路径 $secretsDatabase){写主机$secretsDatabase 已经存在"}其他{$create_query = "创建表机密(tid 字符串、密码字符串、用户名字符串)"$cq_result = Invoke-SqliteQuery -DataSource $secretsDatabase -Query $create_query$add_secrets_query = "插入秘密 (tid, password, username) 值 ('xx1', 'somepassword', 'bob@example.com')"$as_result = Invoke-SqliteQuery -DataSource $secretsDatabase -Query $add_secrets_query}$secretsQuery = "选择 tid, password, username from secrets limit 1"$result = Invoke-SqliteQuery -DataSource $secretsDatabase -Query $secretsQuery写主机 $result当我在一台服务器上运行这个 Powershell 脚本时，输出如下所示:
PS C:\>C:\sqlite_test.ps1.\secrets.db 已经存在@{tid=xx1;密码=一些密码；用户名=bob@example.com}PS C:\>这是预期的输出.
当我在另一台服务器上运行相同的脚本时，输出如下所示:
PS C:\>C:\sqlite_test.ps1.\secrets.db 已经存在无法找到类型 [DBNullScrubber].在 C:\Program Files\WindowsPowerShell\Modules\PSSQLite\1.0.3\Invoke-SqliteQuery.ps1:518 char:25+ [DBNullScrubber]::DataRowToPSObject($row)+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+ CategoryInfo : InvalidOperation: (DBNullScrubber:TypeName) [], RuntimeException+ FullQualifiedErrorId : TypeNotFoundPS C:\>在两个系统上，用于创建表和插入行的 SQL 查询都有效.这可以通过在两台机器上使用命令行实用程序并得到以下结果来证明:
C:\>sqlite3 secrets.dbSQLite 版本 3.19.3 2017-06-08 14:26:16输入.help"以获得使用提示.sqlite>.dumpPRAGMA foreign_keys=OFF;开始交易；CREATE TABLE secrets (tid string, password string, username string);INSERT INTO secrets VALUES('xx1','somepassword','bob@example.com');提交；sqlite>然而，由于[DBNullScrubber]"错误，在一台机器上的 SELECT 查询失败.如何在一台机器上解决这个问题?
------ 更新:2017 年 8 月 13 日更多分析 -----------------
我在 GitHub 存储库中为此模块做了一些代码探索，放入文件
我在坏服务器上进入我的 Powershell 并从该文件运行此代码片段:
$cSharp = @'使用系统；使用 System.Data;使用 System.Management.Automation;公共类 DBNullScrubber{public static PSObject DataRowToPSObject(DataRow row){PSObject psObject = new PSObject();if (row != null && (row.RowState & DataRowState.Detached) != DataRowState.Detached){foreach(row.Table.Columns 中的 DataColumn 列){对象值 = 空；if (!row.IsNull(column)){值 = 行 [列];}psObject.Properties.Add(new PSNoteProperty(column.ColumnName, value));}}返回 psObject；}}'@此代码段定义了 DBNullScrubber 类.之后，我转到坏服务器上的命令行并运行:
PS C:\>添加类型 -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指定的路径.'(1):使用系统；在行:1 字符:2+ 添加类型 -TypeDefinition $cSharp -ReferencedAssemblies 'System.Data' ...+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+ CategoryInfo : InvalidData: (error CS1668: W...th specified.':CompilerError) [Add-Type], Exception+ FullQualifiedErrorId : SOURCE_CODE_ERROR,Microsoft.PowerShell.Commands.AddTypeCommand添加类型:无法添加类型.出现编译错误.在行:1 字符:2+ 添加类型 -TypeDefinition $cSharp -ReferencedAssemblies 'System.Data' ...+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+ CategoryInfo : InvalidData: (:) [Add-Type], InvalidOperationException+ FullQualifiedErrorId : COMPILER_ERRORS,Microsoft.PowerShell.Commands.AddTypeCommandPS C:>我在好的服务器上运行了这个，没有错误.
此时我不知道该怎么办.我该如何解决这个问题?
----------------- 2017 年 8 月 13 日更新 - 12:11 ----------------
我打印了 $env:Lib.好机器上没有任何定义.在坏机器上它说:
PS C:\>$env:Lib;D:\Program Files\IBM\SQLLIB\LIBPS C:\>运行以下命令删除 Lib 环境变量.
Remove-Item $env:Lib更好的是，保存旧值，运行脚本，然后恢复旧值.
导入模块 PSSQLite$secretsDatabase = ".\secrets.db"$envlib = $env:Lib删除项目 env:Lib如果(测试路径 $secretsDatabase){写主机$secretsDatabase 已经存在"}其他{$create_query = "创建表机密(tid 字符串、密码字符串、用户名字符串)"$cq_result = Invoke-SqliteQuery -DataSource $secretsDatabase -Query $create_query$add_secrets_query = "插入秘密 (tid, password, username) 值 ('xx1', 'somepassword', 'bob@example.com')"$as_result = Invoke-SqliteQuery -DataSource $secretsDatabase -Query $add_secrets_query}$secretsQuery = "选择 tid, password, username from secrets limit 1"$result = Invoke-SqliteQuery -DataSource $secretsDatabase -Query $secretsQuery写主机 $result$env:Lib = $envlibI am trying to write a Powershell script to write data to a SQLite database and to read from it. I'm using the PSSQLite module to do this. The script works on one server, but not on the other. Both machines are Windows 2012 R2.
Both machines look like this:
PS C:\> $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:\> Import-Module PSSQLite
PS C:\> Get-Module PSSQLite
ModuleType Version Name ExportedCommands
---------- ------- ---- ----------------
Script 1.0.3 PSSQLite {Invoke-SQLiteBulkCopy, Invoke-SqliteQuery, New-SQLiteConn...
PS C:\>
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:
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
When I run this Powershell script on one server, the output looks like this:
PS C:\> C:\sqlite_test.ps1
.\secrets.db already exists
@{tid=xx1; password=somepassword; username=bob@example.com}
PS C:\>
This is the expected output.
When I run the same script on the other server, the output looks like this:
PS C:\> 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:\>
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:
C:\> sqlite3 secrets.db
SQLite version 3.19.3 2017-06-08 14:26:16
Enter ".help" for usage hints.
sqlite> .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>
However the SELECT query on one machine fails because of the "[DBNullScrubber]" error. How can I correct this problem on the one machine?
------ UPDATE: August 13, 2017 more analysis -----------------
I did some code spelunking at the GitHub repo for this module, into the file
I went into my Powershell on the bad server and ran this code snippet from that file:
$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 && (row.RowState & 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;
}
}
'@
This snippet defines the DBNullScrubber class. After that, I went to my command line on the bad server and ran:
PS C:\> 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:>
I ran this on the good server and there were no errors.
At this point I don't know what to do. How can I fix this?
----------------- Update Aug 13, 2017 - 12:11 ----------------
I printed out $env:Lib. There was nothing defined on the good machine. On the bad machine it said:
PS C:\> $env:Lib
;D:\Program Files\IBM\SQLLIB\LIB
PS C:\>
Run the following to delete the Lib environment variable.
Remove-Item $env:Lib
Better yet, save the old value, run the script, and then restore the old value.
Import-Module PSSQLite
$secretsDatabase = ".\secrets.db"
$envlib = $env:Lib
Remove-Item env:Lib
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
$env:Lib = $envlib
这篇关于使用 Powershell 和 PSSqlite 访问 SQLite - SELECT 查询失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
