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 ( [ValidateSet('Query', 'Scalar', 'Update')] # Determines how data will be returned. See SimplySql's help for more information. [String]$QueryType = 'Query', [ValidateSet('Deploy', 'Inventory')] # The PDQ application you would like to execute this function against. [String]$Product, # 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, [Parameter(Mandatory = $true)] # The SQL query you would like to execute. # PDQ uses SQLite databases. [String]$Query ) Try { $CloseConnection = Open-PdqSqlConnection -Product $Product -DatabasePath $DatabasePath switch ( $QueryType ) { 'Query' { Invoke-SqlQuery -Query $Query -ConnectionName $Product Break } 'Scalar' { Invoke-SqlScalar -Query $Query -ConnectionName $Product Break } 'Update' { Invoke-SqlUpdate -Query $Query -ConnectionName $Product Break } } } Finally { Close-PdqSqlConnection -Product $Product -CloseConnection $CloseConnection } } |