Invoke-SqliteQuery
Invoke-SqliteQuery
SYNOPSIS
Runs a SQL script against a SQLite database.
SYNTAX
Invoke-SqliteQuery [-DataSource] <String[]> [-Query] <String> [[-QueryTimeout] <Int32>] [[-As] <String>] [[-SqlParameters] <IDictionary>] [[-AppendDataSource]] [[-AssemblyPath] <String>] [<CommonParameters>]
Invoke-SqliteQuery [-DataSource] <String[]> [-InputFile] <String> [[-QueryTimeout] <Int32>] [[-As] <String>] [[-SqlParameters] <IDictionary>] [[-AppendDataSource]] [[-AssemblyPath] <String>] [<CommonParameters>]
Invoke-SqliteQuery [-Query] <String> [[-QueryTimeout] <Int32>] [[-As] <String>] [[-SqlParameters] <IDictionary>] [[-AppendDataSource]] [[-AssemblyPath] <String>] [-SQLiteConnection] <SQLiteConnection>
[<CommonParameters>]
Invoke-SqliteQuery [-InputFile] <String> [[-QueryTimeout] <Int32>] [[-As] <String>] [[-SqlParameters] <IDictionary>] [[-AppendDataSource]] [[-AssemblyPath] <String>] [-SQLiteConnection] <SQLiteConnection>
[<CommonParameters>]DESCRIPTION
Runs a SQL script against a SQLite database.
Paramaterized queries are supported.
Help details below borrowed from Invoke-Sqlcmd, may be inaccurate here.
EXAMPLES
EXAMPLE 1
## First, we create a database and a table
$Query = "CREATE TABLE NAMES (fullname VARCHAR(20) PRIMARY KEY, surname TEXT, givenname TEXT, BirthDate DATETIME)"
$Database = "C:\Names.SQLite"
Invoke-SqliteQuery -Query $Query -DataSource $Database
# We have a database, and a table, let's view the table info
Invoke-SqliteQuery -DataSource $Database -Query "PRAGMA table_info(NAMES)"
cid name type notnull dflt_value pk
--- ---- ---- ------- ---------- --
0 fullname VARCHAR(20) 0 1
1 surname TEXT 0 0
2 givenname TEXT 0 0
3 BirthDate DATETIME 0 0
# Insert some data, use parameters for the fullname and birthdate
$query = "INSERT INTO NAMES (fullname, surname, givenname, birthdate) VALUES (@full, 'Cookie', 'Monster', @BD)"
Invoke-SqliteQuery -DataSource $Database -Query $query -SqlParameters @{
full = "Cookie Monster"
BD = (get-date).addyears(-3)
}
# Check to see if we inserted the data:
Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM NAMES"
fullname surname givenname BirthDate
-------- ------- --------- ---------
Cookie Monster Cookie Monster 3/14/2012 12:27:13 PM
# Insert another entry with too many characters in the fullname.
# Illustrate that SQLite data types may be misleading:
Invoke-SqliteQuery -DataSource $Database -Query $query -SqlParameters @{
full = "Cookie Monster$('!' * 20)"
BD = (get-date).addyears(-3)
}
Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM NAMES"
fullname surname givenname BirthDate
-------- ------- --------- ---------
Cookie Monster Cookie Monster 3/14/2012 12:27:13 PM
Cookie Monster![...]! Cookie Monster 3/14/2012 12:29:32 PMEXAMPLE 2
Invoke-SqliteQuery -DataSource C:\NAMES.SQLite -Query "SELECT * FROM NAMES" -AppendDataSourcefullname surname givenname BirthDate Database
-------- ------- --------- --------- --------
Cookie Monster Cookie Monster 3/14/2012 12:55:55 PM C:\Names.SQLite
# Append Database column (path) to each resultEXAMPLE 3
Invoke-SqliteQuery -DataSource C:\Names.SQLite -InputFile C:\Query.sql# Invoke SQL from an input fileEXAMPLE 4
$Connection = New-SQLiteConnection -DataSource :MEMORY:Invoke-SqliteQuery -SQLiteConnection $Connection -Query "CREATE TABLE OrdersToNames (OrderID INT PRIMARY KEY, fullname TEXT);"
Invoke-SqliteQuery -SQLiteConnection $Connection -Query "INSERT INTO OrdersToNames (OrderID, fullname) VALUES (1,'Cookie Monster');"
Invoke-SqliteQuery -SQLiteConnection $Connection -Query "PRAGMA STATS"
# Execute a query against an existing SQLiteConnection
# Create a connection to a SQLite data source in memory
# Create a table in the memory based datasource, verify it exists with PRAGMA STATSEXAMPLE 5
$Connection = New-SQLiteConnection -DataSource :MEMORY:Invoke-SqliteQuery -SQLiteConnection $Connection -Query "CREATE TABLE OrdersToNames (OrderID INT PRIMARY KEY, fullname TEXT);"
Invoke-SqliteQuery -SQLiteConnection $Connection -Query "INSERT INTO OrdersToNames (OrderID, fullname) VALUES (1,'Cookie Monster');"
Invoke-SqliteQuery -SQLiteConnection $Connection -Query "INSERT INTO OrdersToNames (OrderID) VALUES (2);"
# We now have two entries, only one has a fullname. Despite this, the following command returns both; very un-PowerShell!
Invoke-SqliteQuery -SQLiteConnection $Connection -Query "SELECT * FROM OrdersToNames" -As DataRow | Where{$_.fullname}
OrderID fullname
------- --------
1 Cookie Monster
2
# Using the default -As PSObject, we can get PowerShell-esque behavior:
Invoke-SqliteQuery -SQLiteConnection $Connection -Query "SELECT * FROM OrdersToNames" | Where{$_.fullname}
OrderID fullname
------- --------
1 Cookie MonsterPARAMETERS
DataSource
-DataSource <String[]>
Path to one or more SQLite data sources to query
Required? true
Position? 1
Default value
Accept pipeline input? true (ByValue, ByPropertyName)
Accept wildcard characters? falseQuery
-Query <String>
Specifies a query to be run.
Required? true
Position? 2
Default value
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? falseInputFile
-InputFile <String>
Specifies a file to be used as the query input to Invoke-SqliteQuery. Specify the full path to the file.
Required? true
Position? 2
Default value
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? falseQueryTimeout
-QueryTimeout <Int32>
Specifies the number of seconds before the queries time out.
Required? false
Position? 3
Default value 600
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? falseAs
-As <String>
Specifies output type - DataSet, DataTable, array of DataRow, PSObject or Single Value
PSObject output introduces overhead but adds flexibility for working with results: http://powershell.org/wp/forums/topic/dealing-with-dbnull/
Required? false
Position? 4
Default value PSObject
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? falseSqlParameters
-SqlParameters <IDictionary>
Hashtable of parameters for parameterized SQL queries. http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/
Limited support for conversions to SQLite friendly formats is supported.
For example, if you pass in a .NET DateTime, we convert it to a string that SQLite will recognize as a datetime
Example:
-Query "SELECT ServerName FROM tblServerInfo WHERE ServerName LIKE @ServerName"
-SqlParameters @{"ServerName = "c-is-hyperv-1"}
Required? false
Position? 5
Default value
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? falseAppendDataSource
-AppendDataSource [<SwitchParameter>]
If specified, append the SQLite data source path to PSObject or DataRow output
Required? false
Position? 6
Default value False
Accept pipeline input? false
Accept wildcard characters? falseAssemblyPath
-AssemblyPath <String>
Required? false
Position? 7
Default value $SQLiteAssembly
Accept pipeline input? false
Accept wildcard characters? falseSQLiteConnection
-SQLiteConnection <SQLiteConnection>
An existing SQLiteConnection to use. We do not close this connection upon completed query.
Required? true
Position? 8
Default value
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? falseCommonParameters
This cmdlet supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutVariable, -OutBuffer, -PipelineVariable, -Verbose, -WarningAction, and -WarningVariable. For more information, see about_CommonParameters.
Last updated