Error converting get-process result into sqlite

[n0rc0d3]

Just starting to experiment with your module and encountered some issue

I tried your "computer inventory" example and I have no errors, so it looks like the module is properly installed,

then I tried to use Get-Process

$process = get-process
$process | ConvertTo-MySQLiteDB -Path c:\temp\test_process.db -TableName Process -TypeName myProcess -force

I get multiple occurrences of this error:

Exception calling "ExecuteNonQuery" with "0" argument(s): "SQL logic error
cannot start a transaction within a transaction"
At C:\Program Files\WindowsPowerShell\Modules\mySQLite\0.9.2\functions\Invoke-MySQLiteQuery.ps1:95 char:25
+                         [void]$cmd.ExecuteNonQuery()
+                         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SQLiteException

at a first glance it looks like the database is created, tables are created and 1 process details is saved inside the table.

P.S. I don't really need to get processes into SQLite it's just something that I noticed. not sure if it's due to the number of properties or some specific type.

I tried also with Get-ADUser, if I just leave the default properties, it gets imported into sqlite. If I do the get-aduser with -properties * and try to put it into SQLite it fails with a different error (compared to the process example)

Exception calling "ExecuteNonQuery" with "0" argument(s): "SQL logic error
near "-": syntax error"

and not even the tables get created (so possibly a different issue)

regards

[jdhitsolutions]

I get the error under Windows PowerShell but not when running in PowerShell 7 with the process example.

I also get the same AD error.

PS C:\> get-aduser artd -Properties * | ConvertTo-MySQLiteDB -Path .\art2.db -TableName user -TypeName aduser
Exception calling "ExecuteNonQuery" with "0" argument(s): "SQL logic error
near "-": syntax error"
At C:\Program Files\WindowsPowerShell\Modules\mySQLite\0.9.2\functions\New-MySQLiteDBTable.ps1:119 char:21
+                     [void]$cmd.ExecuteNonQuery()
+                     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SQLiteException

I suspect the AD User is a different problem. I will move this problem to a separate issue.

[jdhitsolutions]

The AD Problem is now under Issue #14

[jdhitsolutions]

In Windows PowerShell, I see a constraint error due to duplicate process names.

Invoke-MySQLiteQuery : Exception calling "ExecuteNonQuery" with "0" argument(s): "constraint failed
UNIQUE constraint failed: Process.Name"

Curious as to why this doesn't happen in PowerShell 7.

[jdhitsolutions]

I get the same error in PowerShell 7.

[jdhitsolutions]

Not sure how I can really fix this unless I can let the user specify the property to use as the index. Although a selected object appears to work.

Get-Process | Select ID,Name,WS,Path,Company,Handles | ConvertTo-MySQLiteDB -Path d:\temp\test_process8.db -TableName Process -TypeName myProcess -force -verbose -ErrorAction stop 

[jdhitsolutions]

It looks like specifying the primary key solves this problem.

[jdhitsolutions]

There appears to be an issue with the Modules property. If I exclude it, there are no errors.

Get-Process  | Where {$_.name -notmatch "^(system|idle)$"} -ov w | Select * -exclude Modules | ConvertTo-MySQLiteDB -Path d:\temp\allproc.db -TableName Process -TypeName myProcess -Primary ID

[jdhitsolutions]

Digging deeper. this doesn't always cause a problem.

ps -id $pid | ConvertTo-MySQLiteDB -Path d:\temp\pid.db -TableName process

Ran with no errors. Not sure if it is a size thing or something else in how it is getting serialized.

[jdhitsolutions]

I think this is the culprit. https://stackoverflow.com/questions/27761453/how-to-properly-escape-single-quotes-in-sqlite-insert-statement-ios

In my testing, I found that some process modules have an apostrophe in the name which is exposed when converting objects to clixml.

  <Props>
          <S N="Comments">
          This packages provides a low-level .NET (ECMA-335) metadata reader and writer. It's geared for performance and is the ideal choice for building higher-level libraries that intend to provide their own object model, such as compilers._x000D__x000A__x000D__x000A_
          Commonly Used Types:

The It's is the problem and breaks the Insert query.

[n0rc0d3]

Thanks for checking the issue, Jeff. so based on the SO it looks like there are two ways to address it, either replace the apostrophe with a different type of quote character or leverage a different sqlite internal command to pass the attributes (do you have access to those through the way you access the sqlite library?)

[jdhitsolutions]

I have code that checks the imported clixml and escapes the single quote. I have to test another scenario that I realized might also come into play.

[jdhitsolutions]

I have published v0.10.0 to the PowerShell Gallery. Update the module and try again.

[n0rc0d3]

I have updated the module and tried, I was still getting the error until I specified "-Primary ID" as you showed in https://github.com/jdhitsolutions/MySQLite/issues/13#issuecomment-1242203465

Now it seems to be working fine. I will do more tests when possible. Thanks for fixing this.

[jdhitsolutions]

I think this is going to be a documentation issue. Database tables have to have a unique primary key.