Powershell to SQlite - Need help with Parameters as a variable

[SubScrubber]

I'm trying to make a function that will throw PS object to SQLite db

Still work in progress (lots of tuning needed) but I'm stuck on 1 point:
Passing the Invoke-SQLUpdate -Parameters as a Hashtable

See below:

$DataSource = Get-Process | Select ID,Name -First 5

$DBCols = @()
$Columns = @()
$ColumnValues = @()
$UpdateParams = @{}
$DataSource | Get-Member -MemberType NoteProperty | ForEach-Object {
    $DataType = if ($_.Definition -like "string*") { "Text" } elseif 
                ($_.Definition -like "int*") { "INTEGER" }

    $row = "$($_.Name) $($DataType)"
    $Cols = "$($_.Name)"
    $ColVals = "@$($_.Name)"
    $X = '$_.'
    $UpdateParm = @{"$($_.Name)" = "$($X)$($_.Name)"}

    $DBCols += $row 
    $Columns += $Cols
    $ColumnValues += $ColVals
    $UpdateParams += $UpdateParm
}
$DBColsString = $DBCols | Join-String -Separator ' ,'
$DBColNames = $Columns | Join-String -Separator ', '
$DBColValues = $ColumnValues | Join-String -Separator ', '
$DBColsString
$DBColNames
$DBColValues


Open-SQLiteConnection -ConnectionName "Problem"

Invoke-SqlUpdate -ConnectionName "Problem" -Query "CREATE TABLE TestTable ($DBColsString)" 

$InsertQuery = "INSERT INTO TestTable ($DBColNames) VALUES ($DBColValues)"

[int]$recordsInserted = 0
$DataSource |
    ForEach-Object {
        $recordsInserted += Invoke-SqlUpdate -ConnectionName "Problem" -Query $InsertQuery -Parameters $UpdateParams
        # $recordsInserted += Invoke-SqlUpdate -ConnectionName "Problem" -Query $InsertQuery -Parameters @{Id = $_.Id; Name = $_.Name}
    }

Write-Host "Insert $recordsInserted"

Invoke-SqlQuery -ConnectionName "Problem" -Query "SELECT * FROM TestTable" 

Close-SqlConnection

<#
To delete table data and test again

Invoke-SqlUpdate -ConnectionName "Problem" -Query "DELETE FROM TestTable" 

#>

The commented line for "Invoke-SqlUpdate" works fine, but the $UpdateParams hashtable results in:

Id Name
-- ----
 0 $_.Name
 0 $_.Name
 0 $_.Name
 0 $_.Name
 0 $_.Name

The hashable $UpdateParams looks like this:

Name                           Value
----                           -----
Id                             $_.Id
Name                           $_.Name

Any ideas on how to pass the update params as a variable?

[SubScrubber]

I'm using the SQLite example from the SimplySQL Wiki with Get-Process

[mithrandyr]

the cmdlet parameter Parameters only accept a single hashtable... if you think of a hashtable like a 2 column list.. the first column is the key and the second is the value... the key maps to your sql variable and the value -- you get the point.

So I'm not sure why you are precreating the updateparms hashtable... you should just loop through your datasource and then create the hashtable directly. i.e., what you did and then commented out.

If you really want to create the variable then you can do this:

#open an inmemory db connection
Open-SQLiteConnection

#create a table
Invoke-SqlUpdate -Query "CREATE TABLE computer_process (id int, name text, path text)" | Out-Null
$query = "INSERT INTO computer_process (id, name, path) VALUES (@id, @name, @path)"
Get-Process |
    Select-Object -First 15 |
    ForEach-Object {
        $updateParams = @{
            id = $_.Id
            name = $_.ProcessName
            path = $_.Path
        }
        Invoke-SqlUpdate -Query $query -Parameters $updateParams
    } |
    Out-Null

# count rows
"Number of Rows: "
Invoke-SqlScalar "SELECT COUNT(1) FROM computer_process"

#show data
"Data from computer_process:"
Invoke-SqlQuery "SELECT * FROM computer_process"

#drop connection
Close-SqlConnection

[mithrandyr]

Your example is a little complicated (I suspect in order to handle other scenarios), so I'll reframe to show a simpler version that hopefully will make clear how to leverage the module to do this. (that said v2 will make this easier... and i'm almost done testing the changes).

#open an inmemory db connection
Open-SQLiteConnection

#create a table
Invoke-SqlUpdate -Query "CREATE TABLE computer_process (id int, name text, path text)" | Out-Null

#get processes to store
$data = Get-Process | Select-Object -first 15 Id, ProcessName, Path

#store data
$query = "INSERT INTO computer_process (id, name, path) VALUES (@id, @name, @path)"
foreach($d in $data) {
    Invoke-SqlUpdate -Query $query -Parameters @{id = $d.id; name = $d.processname; path = $d.path} | Out-Null
}

# count rows
"Number of Rows: "
Invoke-SqlScalar "SELECT COUNT(1) FROM computer_process"

#show data
"Data from computer_process:"
Invoke-SqlQuery "SELECT * FROM computer_process"

#drop connection
Close-SqlConnection

Now if you want to make this short and leverage some pipelining, you can do this:

#open an inmemory db connection
Open-SQLiteConnection

#create a table
Invoke-SqlUpdate -Query "CREATE TABLE computer_process (id int, name text, path text)" | Out-Null
$query = "INSERT INTO computer_process (id, name, path) VALUES (@id, @name, @path)"
Get-Process |
    Select-Object -First 15 |
    ForEach-Object {
        Invoke-SqlUpdate -Query $query -Parameters @{id = $_.id; name = $_.processname; path = $_.path}
    } |
    Out-Null

# count rows
"Number of Rows: "
Invoke-SqlScalar "SELECT COUNT(1) FROM computer_process"

#show data
"Data from computer_process:"
Invoke-SqlQuery "SELECT * FROM computer_process"

#drop connection
Close-SqlConnection

[mithrandyr]

With the Version 2.0 update, you can now pass objects directly to Invoke-SqlUpdate and they will be used to supply parameters.
A couple of caveats, many SQL Providers treat parameters as case sensitive, such that 'alpha' and 'Alpha' are different parameters, thus you will need to make sure your SQL query parameter names match (casing) the property names on the object being piped in.