ramblingcookiemonster / pssqlite Goto Github PK

I tried installing PSSQLlite 1.1.0 but there is a conflict as can be seen below:

PackageManagement\Install-Package : The following commands are already available 
on this system:'Out-DataTable'. This module 'PSSQLite' may override the existing 
commands. If you still want to install this module 'PSSQLite', use -AllowClobber 
parameter.
At C:\Program 
Files\WindowsPowerShell\Modules\PowerShellGet\2.2.5\PSModule.psm1:9711 char:34
+ ... talledPackages = PackageManagement\Install-Package @PSBoundParameters
+                      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (Microsoft.Power....InstallPacka 
   ge:InstallPackage) [Install-Package], Exception
    + FullyQualifiedErrorId : CommandAlreadyAvailable,Validate-ModuleCommandAlre 
   adyAvailable,Microsoft.PowerShell.PackageManagement.Cmdlets.InstallPackage

I can install 1.0.3 just fine, the issue seems introduced in 1.1.0

You should fix Line 8 of PSSQLite.psm1

if ($PSEdition -like 'core')

The Powershell Core x64 Global Variable $PSEdition on Linux delivers "core" instead of "Core".

Because you asking explicit for "-eq", this will result in loading the wrong DLLs:

Exception calling "Open" with "0" argument(s): "Unable to load shared library 'SQLite.Interop.dll' or one of its dependencies. In order to help diagnose loading problems, consider setting the LD_DEBUG environment variable: libSQLite.Interop.dll: cannot open shared object file: No such file or directory"

Here is a repro script:

Import-Module -Name 'PSSQLite' -Force

$DataSource = '.\NonAlphanum.sqlite'

$Query = @"
    CREATE TABLE ALPHANUM (
        'Alfa-Bravo' INTEGER,
        'Charlie=Delta' INTEGER,
        'Echo#Foxtrot' INTEGER,
        'Echo@Foxtrot' INTEGER,
        'Echo👻Foxtrot' INTEGER)
"@

$NonAlphanum = 0..100 | ForEach-Object {
    [pscustomobject]@{
        'Alfa-Bravo' = Get-Random
        'Charlie=Delta' = Get-Random
        'Echo#Foxtrot' = Get-Random
        'Echo@Foxtrot' = Get-Random
        'Echo👻Foxtrot' = Get-Random    
    }
}

Invoke-SqliteQuery -DataSource $DataSource -Query $Query

$DataTable = $NonAlphanum | Out-DataTable

Invoke-SQLiteBulkCopy -DataTable $DataTable -DataSource $DataSource -Table 'ALPHANUM' -Force

It fails with this error:

Rolled back due to error:
Exception calling "ExecuteNonQuery" with "0" argument(s): "SQL logic error or missing database
near "-": syntax error"
At C:\Path\To\PSSQLite\Invoke-SqliteBulkCopy.ps1:280 char:29
+                             Throw "Rolled back due to error:`n$_"
+                             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (Rolled back due...: syntax error":String) [], RuntimeException
    + FullyQualifiedErrorId : Rolled back due to error:
Exception calling "ExecuteNonQuery" with "0" argument(s): "SQL logic error or missing database
near "-": syntax error"

I think I've fixed it, by creating parameters, where every non-alphanumeric character in column's name is converted to it's charcode string and then everything is joined with underscore.

For example, the column Echo👻Foxtrot, with "ghost in the PowerShell" (which consists of two surrogate pairs) will have parameter named Echo_55357_56443_Foxtrot.

I've also added some escaping with single quotes (') where necessary.

I'll do a proper pull-request soon.

When variable has a ' in it, it is not escaped and thus the SQL insert is failing. Proper escape is required

Invoke-SqliteQuery is failing if we have used Rank(), or in fact any analytic function in view queries. Below is the error that I am getting.

"database disk image is malformed
malformed database schema (UserCommentsSummary) - near "(": syntax error"
At line:24 char:16"

Running Windows 7 x64 and I get the following error when trying to load the module. Trying to load the module by pointing directly to PSSQLite-master\PSSQLite.

Add-Type : Could not load file or assembly
'file:///C:\Users\...\Downloads\PSSQLite-master\PSSQLite\x64\System.Data.SQLite.dll' or one of its dependencies.
Operation is not supported. (Exception from HRESULT: 0x80131515)
At C:\Users\...\Downloads\PSSQLite-master\PSSQLite\PSSQLite.psm1:21 char:26
+     if( -not ($Library = Add-Type -path $SQLiteAssembly -PassThru -ErrorAction s ...
+                          ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Add-Type], FileLoadException
    + FullyQualifiedErrorId : System.IO.FileLoadException,Microsoft.PowerShell.Commands.AddTypeCommand

I've got a database that keeps pulling the same old field in vscode when running it in non-admin mode.
However the added fields are visible when using vscode admin mode. When retrieving the fields in powershell non-admin mode also shows the added fields.
It other words it uses the same old instance of the database, closing the database or deleting the database doesn't seem to alleviate the problem.
This might in part be a vscode cache problem.

To reproduce this bug:
Create a database in your shell and add fields.
Open the database in vsode without admin mode and retrieve data.
Close vscode.
Insert a new field using shell. Data isn't visible in vscode, unless using admin mode.
Insert a new field using vscode and the new field can be retrieved.

In conclusion vscode non-admin mode uses a cached version of the database.

There appears to be no native support for bulk inserts along the lines of System.Data.SqlClient.SqlBulkCopy. Several references indicate using transactions would help with this.

• Implement wrapper function to mimic Invoke-SqlBulkCopy functionality
• To support this, implement transaction support. Example references:
  • Why are inserts slow?
  • SQLite Bulk Insert In C#/.NET
  • Bulk Operations in SQLite and C# with Transaction
• Include transaction support in Invoke-SqliteQuery function

If anyone wants to help, would be appreciated!

When using Invoke-SQLiteBulkCopy with an open SQLiteConnection, the ".sqlite-journal" file grows larger but never commits the changes to the database.

This is kind of an odd one and I can fork and fix if you want, but in the PSSQLite.psm1 file you're using the alias for Select-Object as part of the function export process (at the end). Under normal circumstances this is totally ok, however I've been banging my head over the last few days trying to figure out why I can't get the PSSQLite module to work as part of a constrained endpoint (JEA) and after doing a lot of debugging the use of the Select alias is not playing nice with a locked down endpoint\shell (SessionType='Empty' and LanguageMode='RestrictedLanguage'). I tried defining Select as a valid alias with VisibleAliases but the only way I could get it to work is if I edited the module and spelled out the alias as Select-Object.

So anyway we can get the Select alias removed and the full cmdlet name put int the PSSQLite module file? I can do it if you want, but it seems like a really small change :)

Works fine in Windows PowerShell 5, doesnt work in 7

Works: CREATE table Customers
Doesnt work: SELECT * from Customers

InvalidOperation: \PowerShell\Modules\PSSQLite\1.0.3\Invoke-SqliteQuery.ps1:518:25
Line |
518 | [DBNullScrubber]::DataRowToPSObject($row)
| ~~~~~~~~~~~~~~~~
| Unable to find type [DBNullScrubber].

There is a bug on line 299 in the Invoke-SqliteQuery.ps1 script.

$InputFile can tested without it having been set. The fix is to test the InputFile parameter using
$PSBoundParameters.ContainsKey('InputFile')).

Running PS7 Core on a Rasperry Pi 3+:

PS /root> $PSVersionTable

Name                           Value
----                           -----
PSVersion                      7.1.3
PSEdition                      Core
GitCommitId                    7.1.3
OS                             Linux 5.10.17-v7+ #1403 SMP Mon Feb 22 11:29:51 GMT 2021
Platform                       Unix
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

PS /root> Get-Module -ListAvailable

ModuleType Version    PreRelease Name                                PSEdition ExportedCommands
---------- -------    ---------- ----                                --------- ----------------
Script     1.1.0                 PSSQLite                            Desk      {Invoke-SqliteBulkCopy, Invoke-SqliteQu…

When I try to run PSSQL Invoke-SqliteQuery -Query $query -DataSource $db, my Command will fail:

     | Exception calling ".ctor" with "1" argument(s): "Unable
     | to load shared library 'SQLite.Interop.dll' or one of its dependencies. In order to help diagnose
     | loading problems, consider setting the LD_DEBUG environment variable: libSQLite.Interop.dll: cannot
     | open shared object file: No such file or directory"!

For me it seems, the SQLite.Interop.dll and System.Data.SQLite.dll seems not compiled for armhf?

It seems, there is a Plaform-indepentend library available: http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

I am having a handful of Windows 10 1909 machines out of 2,500 that seem to dislike Invoke-SqliteQuery when returning data as a PSObject. I get this error:

Unable to find type [DBNullScrubber].
At C:\Program Files\WindowsPowerShell\Modules\PSSQLite\1.1.0\Invoke-SqliteQuery.ps1:536 char:25

The query itself is quite simple and works on almost all endpoints: SELECT Name FROM sqlite_master WHERE type='table' AND Name LIKE '%5%'; It is done from a computer running PS 7.1, but executing via Invoke-Command on computers running PS 5.1.

Short of rewriting my code to return the data in a different format, is there anything I can do to work around this? It looks like others are having this issue as well, but it seems to be reported on Core versions only. I would be the oddball. :)

Thanks,

Don Wyatt

The latest version of the .NET dlls (SQLite.Interop.dll and System.Data.SQLite.dll) is 1.0.105.2. The version currently distributed with the module is 1.0.96.0. What is the best approach to replace the dlls with the newer ones? More importantly, there are several versions of the dlls available for download on the System.Data.SQLite.org site. Which one is the most appropriate one?

Thank you in advance.

BulkCopy should throw if source and target tables' column types are incompatible. It currently will allow you to insert records which will then cause errors upon selection.

When trying to do a bulk insert with a single column, I received the following error. Maybe I'm doing something stupid, but when I add a second column to the same dataset, and to the table, the bulk insert works.

Index operation failed; the array index evaluated to null.
At C:\Program Files\WindowsPowerShell\Modules\PSSQLite\1.0.2\Invoke-SqliteBulkCopy.ps1:342 char:29

• ... $Command.Parameters[$ColumnToParamHash[$Columns[$col]]].V ...

•             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  

  • CategoryInfo : InvalidOperation: (:) [], RuntimeException
  • FullyQualifiedErrorId : NullArrayIndex

Hi Warren,

Today I tried to use the module on PowerShell Core running on a RaspberryPi (2) and it failed with the following error.

New-Object : Exception calling ".ctor" with "1" argument(s): "Unable to load DLL 'SQLite.Interop.dll': The specified module or one of its dependencies could not be found.

It would be great to get this working with .net core and PowerShell core.

Would this be possible?

/Stefan

Hi,

I started using this module and it's been working fine. But when I started using it in Powershell Core v. 6.0.2 I get this error:

Unable to find type [DBNullScrubber].
At C:\Users\Admn\Documents\PowerShell\Modules\PSSQLite\1.0.3\Invoke-SqliteQuery.ps1:518 char:25
+                         [DBNullScrubber]::DataRowToPSObject($row)
+                         ~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidOperation: (DBNullScrubber:TypeName) [], RuntimeException
+ FullyQualifiedErrorId : TypeNotFound

Just thought I should let you know.

The SQLite db I am attempting to get date from has columns that are in a Date/Time format like this 2019-07-02 04:59:18.578 +00:00 . It seems my queries all fail to pull back the data with the error in the subject line. Is there an override or way to convert it to string?

I have tried using all the -As param options, all give same error.

Example of query that fails "Select * From table_name;"

I am iterating through tables to find specific named columns and then getting that data so I can't easily just grab all columns besides the ones with the date/time.