Public/Invoke-PdqSqlQuery.ps1
|
<#
.SYNOPSIS Executes a SQL query against a PDQ database. .DESCRIPTION This function is best for on-the-fly queries. If you're writing a script that has multiple queries, I recommend setting up your own Try/Finally block. .INPUTS None. .OUTPUTS See the relevant SimplySql help articles. .LINK Invoke-SqlQuery .LINK Invoke-SqlScalar .LINK Invoke-SqlUpdate .EXAMPLE Invoke-PdqSqlQuery -Product 'Deploy' -Query "SELECT name, tbl_name FROM SQLITE_MASTER WHERE Type = 'trigger';" Retrieve a list of database triggers from PDQ Deploy. .EXAMPLE Invoke-PdqSqlQuery -QueryType 'Scalar' -Product 'Inventory' -Query "SELECT COUNT(*) FROM Collections;" Count the number of Collections in PDQ Inventory. #> function Invoke-PdqSqlQuery { [CmdletBinding()] param ( [Parameter(Mandatory = $true)] # The SQL query you would like to execute. # PDQ uses SQLite databases. [String]$Query, [ValidateSet('Query', 'Scalar', 'Update')] # Determines how data will be returned. See SimplySql's help for more information. [String]$QueryType = 'Query', [Parameter(Mandatory = $true)] [ValidateSet('Deploy', 'Inventory')] # The PDQ application you would like to execute this function against. [String]$Product, # Outputs a DataTable object instead of an array of DataRow objects. # See the help for Invoke-SqlQuery for more information. [Switch]$AsDataTable, # Values to substitute in a parameterized query. # See the help for Invoke-SqlQuery, Invoke-SqlScalar, and Invoke-SqlUpdate for more information. # https://sqlite.org/lang_expr.html#varparam [Hashtable]$Parameters, # Outputs a stream of PSCustomObjects instead of an array of DataRow objects. # See the help for Invoke-SqlQuery for more information. [Switch]$Stream, # The path to the currently active database will be retrieved by default. # You can use this parameter if you wish to run this function against a different database. [String]$DatabasePath ) $Params = @{ 'Query' = $Query 'ConnectionName' = $Product 'Parameters' = $Parameters } try { $CloseConnection = Open-PdqSqlConnection -Product $Product -DatabasePath $DatabasePath switch ( $QueryType ) { 'Query' { $Params += @{ 'AsDataTable' = $AsDataTable 'Stream' = $Stream } Invoke-SqlQuery @Params } 'Scalar' { Invoke-SqlScalar @Params } 'Update' { Invoke-SqlUpdate @Params } } } finally { Close-PdqSqlConnection -Product $Product -CloseConnection $CloseConnection } } |