# Invoke-SqliteQuery



### Invoke-SqliteQuery <a href="#invoke-sqlitequery" id="invoke-sqlitequery"></a>

### SYNOPSIS <a href="#synopsis" id="synopsis"></a>

Runs a SQL script against a SQLite database.

### SYNTAX <a href="#syntax" id="syntax"></a>

```
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 <a href="#description" id="description"></a>

Runs a SQL script against a SQLite database.

Paramaterized queries are supported.

Help details below borrowed from Invoke-Sqlcmd, may be inaccurate here.

### EXAMPLES <a href="#examples" id="examples"></a>

#### EXAMPLE 1 <a href="#example-1" id="example-1"></a>

```
#
```

```
# 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 PM
```

#### EXAMPLE 2 <a href="#example-2" id="example-2"></a>

```
Invoke-SqliteQuery -DataSource C:\NAMES.SQLite -Query "SELECT * FROM NAMES" -AppendDataSource
```

```
fullname       surname givenname BirthDate             Database       
   --------       ------- --------- ---------             --------       
   Cookie Monster Cookie  Monster   3/14/2012 12:55:55 PM C:\Names.SQLite

# Append Database column (path) to each result
```

#### EXAMPLE 3 <a href="#example-3" id="example-3"></a>

```
Invoke-SqliteQuery -DataSource C:\Names.SQLite -InputFile C:\Query.sql
```

```
# Invoke SQL from an input file
```

#### EXAMPLE 4 <a href="#example-4" id="example-4"></a>

```
$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 STATS
```

#### EXAMPLE 5 <a href="#example-5" id="example-5"></a>

```
$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 Monster
```

### PARAMETERS <a href="#parameters" id="parameters"></a>

#### DataSource <a href="#datasource" id="datasource"></a>

```
-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?  false
```

#### Query <a href="#query" id="query"></a>

```
-Query <String>
   Specifies a query to be run.
   
   Required?                    true
   Position?                    2
   Default value                
   Accept pipeline input?       true (ByPropertyName)
   Accept wildcard characters?  false
```

#### InputFile <a href="#inputfile" id="inputfile"></a>

```
-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?  false
```

#### QueryTimeout <a href="#querytimeout" id="querytimeout"></a>

```
-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?  false
```

#### As <a href="#as" id="as"></a>

```
-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?  false
```

#### SqlParameters <a href="#sqlparameters" id="sqlparameters"></a>

```
-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?  false
```

#### AppendDataSource <a href="#appenddatasource" id="appenddatasource"></a>

```
-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?  false
```

#### AssemblyPath <a href="#assemblypath" id="assemblypath"></a>

```
-AssemblyPath <String>
   
   Required?                    false
   Position?                    7
   Default value                $SQLiteAssembly
   Accept pipeline input?       false
   Accept wildcard characters?  false
```

#### SQLiteConnection <a href="#sqliteconnection" id="sqliteconnection"></a>

```
-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?  false
```

#### CommonParameters <a href="#commonparameters" id="commonparameters"></a>

This cmdlet supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutVariable, -OutBuffer, -PipelineVariable, -Verbose, -WarningAction, and -WarningVariable. For more information, see [about\_CommonParameters](http://go.microsoft.com/fwlink/?LinkID=113216).


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://manuals.blusapphire.io/blugenie/full-function-list/invoke-sqlitequery.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.