< Back
Get-SQL
Post
NAME Get-SQL
SYNOPSIS
Queries an ODBC or SQL Server database
SYNTAX
Get-SQL [[-SQL] <Object>] [[-Connection] <String>] [-Session <String>] [[-ChangeDB] <String>] [-ForceNew]
[-GridView] [-Describe <String>] [-Quiet] [-MsSQLserver] [-Access] [-Excel] [-OutputVariable <String>] [-Close]
[-WhatIf] [-Confirm] [<CommonParameters>]
Get-SQL [[-SQL] <Object>] [[-Connection] <String>] [-Session <String>] [[-ChangeDB] <String>] [-ForceNew]
[-GridView] -Table <String> -Where <String> [-GT] [-GE] [-EQ] [-NE] [-LE] [-LT] [-Like] [-NotLike] [-Select
<String[]>] [-Distinct] [-OrderBy <String[]>] [-GroupBy <String[]>] [-DateFormat <String>] [-Quiet] [-MsSQLserver]
[-Access] [-Excel] [-OutputVariable <String>] [-Close] [-WhatIf] [-Confirm] [<CommonParameters>]
Get-SQL [[-SQL] <Object>] [[-Connection] <String>] [-Session <String>] [[-ChangeDB] <String>] [-ForceNew]
[-GridView] [-Table <String>] [-Select <String[]>] [-Distinct] [-OrderBy <String[]>] [-GroupBy <String[]>]
[-Quiet] [-MsSQLserver] [-Access] [-Excel] [-OutputVariable <String>] [-Close] [-WhatIf] [-Confirm]
[<CommonParameters>]
Get-SQL [[-SQL] <Object>] [[-Connection] <String>] [-Session <String>] [[-ChangeDB] <String>] [-ForceNew]
[-GridView] -Paste [-Quiet] [-MsSQLserver] [-Access] [-Excel] [-OutputVariable <String>] [-Close] [-WhatIf]
[-Confirm] [<CommonParameters>]
Get-SQL [[-SQL] <Object>] [[-Connection] <String>] [-Session <String>] [[-ChangeDB] <String>] [-ForceNew]
-ShowTables [-MsSQLserver] [-Access] [-Excel] [-OutputVariable <String>] [-Close] [-WhatIf] [-Confirm]
[<CommonParameters>]
Get-SQL [[-SQL] <Object>] [[-Connection] <String>] [-Session <String>] [[-ChangeDB] <String>] [-ForceNew] [-Table
<String>] -Delete [-MsSQLserver] [-Access] [-Excel] [-OutputVariable <String>] [-Close] [-WhatIf] [-Confirm]
[<CommonParameters>]
Get-SQL [[-SQL] <Object>] [[-Connection] <String>] [-Session <String>] [[-ChangeDB] <String>] [-ForceNew] [-Table
<String>] -Set <String[]> [-Values] <String[]> [-DateFormat <String>] [-MsSQLserver] [-Access] [-Excel]
[-OutputVariable <String>] [-Close] [-WhatIf] [-Confirm] [<CommonParameters>]
Get-SQL [[-SQL] <Object>] [[-Connection] <String>] [-Session <String>] [[-ChangeDB] <String>] [-ForceNew] -Table
<String> -Where <String> [-GT] [-GE] [-EQ] [-NE] [-LE] [-LT] [-Like] [-NotLike] -Delete [-DateFormat <String>]
[-MsSQLserver] [-Access] [-Excel] [-OutputVariable <String>] [-Close] [-WhatIf] [-Confirm] [<CommonParameters>]
Get-SQL [[-SQL] <Object>] [[-Connection] <String>] [-Session <String>] [[-ChangeDB] <String>] [-ForceNew] -Table
<String> -Where <String> [-GT] [-GE] [-EQ] [-NE] [-LE] [-LT] [-Like] [-NotLike] -Set <String[]> [-Values]
<String[]> [-DateFormat <String>] [-MsSQLserver] [-Access] [-Excel] [-OutputVariable <String>] [-Close] [-WhatIf]
[-Confirm] [<CommonParameters>]
Get-SQL [[-SQL] <Object>] [[-Connection] <String>] [-Session <String>] [[-ChangeDB] <String>] [-ForceNew] -Insert
<String> [-DateFormat <String>] [-MsSQLserver] [-Access] [-Excel] [-OutputVariable <String>] [-Close] [-WhatIf]
[-Confirm] [<CommonParameters>]
DESCRIPTION
Get-SQL queries SQL databases using either ODBC or the native SQL-Server client.
Connections to databases are kept open and reused to avoid the need to make connections for every query,
but the first time the command is run it needs a connection string; this come from $DefaultDBConnection.
(e.g. set in your Profile) rather than being passed as a parameter: if it is set you can run
sql "Select * From Customers"
without any other setup; PowerShell will assume "sql" means "GET-SQL" if there is no other command named SQL.
Get-SQL -Connection allows a connection to be specified explictly; -MsSQLserver forces the use of the native SQL
Server driver,
and -Excel or -Access allow a file name to be used without converting it into an ODBC connection string.
The global variable $DbSessions holds objects for each open connection until Get-SQL is run with -Close.
Get-Sql will also build simple queries; for example
Get-SQL -Table Authors
Will run the "Select * from Authors" and a condition can be specified with
Get-SQL -Table Authors -Where Name -like "*Smith"
Get-SQL -ShowTables will show the available tables, and Get-SQL -Describe Authors will show the design of the
table.
PARAMETERS
-SQL <Object>
A SQL statement. If other parameters (such as -Table, or -Where) are provided, it becomes the end of the SQL
statement.
If no statement is provided, or none can be built from the other parameters, Get-SQL returns information about
the connection.
Required? false
Position? 1
Default value
Accept pipeline input? true (ByValue)
Accept wildcard characters? false
-Connection <String>
An ODBC connection string or an Access or Excel file name or the name of a SQL Server
It can be in the form "DSN=LocallyDefinedDSNName;" or
"Driver={MySQL ODBC 5.1 Driver};SERVER=192.168.1.234;PORT=3306;DATABASE=xstreamline;UID=johnDoe;PWD=password;"
A default connection string can be set in in $DBConnection so that you can just run "Get-SQL " ????SQL Statement????
".
Required? false
Position? 2
Default value $global:DefaultDBConnection
Accept pipeline input? false
Accept wildcard characters? false
-Session <String>
Allows a database connection to be Identified by name: this sets the name used in the global variable
$DBSessions.
In addition an alias is added: for example, if the session is named "F1" you can use the command F1 in place
of Get-SQL -Session F1
Required? false
Position? named
Default value Default
Accept pipeline input? false
Accept wildcard characters? false
-ChangeDB <String>
For SQL server and ODBC sources which support it (like MySQL) switches to a different database at the same
server.
Required? false
Position? 3
Default value
Accept pipeline input? false
Accept wildcard characters? false
-ForceNew [<SwitchParameter>]
If specified, makes a new connection for the default or named session.
If a connection is already established, -ForceNew is required to change the connection string.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-GridView [<SwitchParameter>]
If specified, sends the output to gridview instead of the PowerShell console.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-Describe <String>
Returns a description of the specified table - note that some ODBC providers don't support this.
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-ShowTables [<SwitchParameter>]
If specified, returns a list of tables in the current database - note that some ODBC providers don't support
this.
Required? true
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-Paste [<SwitchParameter>]
If specified, takes an SQL statement from the clipboard.
Line breaks and any text before SELECT , UPDATE or DELETE will be removed
Required? true
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-Table <String>
Specifies a table to select or delete from or to update.
Required? true
Position? named
Default value
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? false
-Where <String>
If specified, applies a SQL WHERE condition to the selected table. -Where specifies the field and the text in
-SQL supplies the condition.
Required? true
Position? named
Default value
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? false
-GT [<SwitchParameter>]
Used with -Where specifies the > operator should be used, with the operand for the condition found in -SQL.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-GE [<SwitchParameter>]
Used with -Where specifies the >= operator should be used, with the operand for the condition found in -SQL.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-EQ [<SwitchParameter>]
Used with -Where specifies the = operator should be used, with the operand for the condition found in -SQL.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-NE [<SwitchParameter>]
Used with -Where specifies the <> operator should be used, with the operand for the condition found in -SQL.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-LE [<SwitchParameter>]
Used with -Where specifies the <= operator should be used, with the operand for the condition found in -SQL.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-LT [<SwitchParameter>]
Used with -Where specifies the < operator should be used, with the operand for the condition found in -SQL.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-Like [<SwitchParameter>]
Used with -Where specifies the Like operator should be used, with the operand for the condition found in -SQL.
"*" in -SQL will be replaced with "%".
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-NotLike [<SwitchParameter>]
Used with -Where specifies the Not Like operator should be used, with the operand for the condition found in
-SQL. "*" in -SQL will be replaced with "%".
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-Select <String[]>
If Select is omitted, -Table TableName will result in "SELECT * FROM TableName";
Select specifies field-names (or other text) to use in place of "*".
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-Distinct [<SwitchParameter>]
Specifies that "SELECT DISTINCT ..." should be used in place of "SELECT ...".
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-OrderBy <String[]>
Specifies fields to be used in a SQL ORDER BY clause added at the end of the query.
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-GroupBy <String[]>
If specified, adds a group by clause to a select query; in this case the SELECT clause needs to contain fields
suitable for grouping.
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-Delete [<SwitchParameter>]
If specified, changes the query from a SELECT to a DELETE. This allows a query to be tested as a SELECT before
adding -Delete to the command.
-Delete requires a WHERE clause and not all ODBC drivers support deletion.
Required? true
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-Set <String[]>
If specified, changes the query from a Select to a Update -Set Specifies the field(s) to be updated.
-Set requires a WHERE clause.
Required? true
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-Values <String[]>
If -Set is specified, -Values contains the new value(s) for the fields being updated.
Required? true
Position? 2
Default value
Accept pipeline input? false
Accept wildcard characters? false
-Insert <String>
Specifies a table to insert into. The SQL parameter should contain a hash table or PSObject which holding the
data to be inserted.
Required? true
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-DateFormat <String>
Allows the format applied to Dates to be inserted to be changed if a service requires does not follow standard
conventions.
Required? false
Position? named
Default value '\\''yyyy'-'MM'-'dd HH':'mm':'ss'\\''
Accept pipeline input? false
Accept wildcard characters? false
-Quiet [<SwitchParameter>]
If specified, surpresses printing of the console message saying how many rows were returned
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-MsSQLserver [<SwitchParameter>]
Specifies the SQL Native client should be used instead of ODBC and string in -Connection is a SQL one.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-Access [<SwitchParameter>]
Specifies that the string in -Connection is an Access file path to be converted into an ODBC connection string.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-Excel [<SwitchParameter>]
Specifies that the string in -Connection is an Excel file path to be converted into an ODBC connection string.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-OutputVariable <String>
Behaves like the common parameters errorVariable, warningvariable etc.to pass back a table object instead of
an array of data rows.
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-Close [<SwitchParameter>]
Closes a database connection.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-WhatIf [<SwitchParameter>]
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-Confirm [<SwitchParameter>]
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Get-SQL -MsSQLserver -Connection "server=lync3\\rtclocal;database=rtcdyn; trusted_connection=true;" -Session
Lync
Creates a new session named "LYNC" to the rtcdyn database on the Rtclocal SQL instance on server Lync
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Get-SQL -Session LR -Connection "DSN=LR" -Quiet -SQL $SQL
Runs the SQL in $SQL - if the Session LR already exists it will be used, otherwise it will be created to the ODBC
source "LR"
Note that a script should always name a its session(s), something else may already have set the defualt session
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Get-Sql -showtables *dataitem
Gives a list of tables on the default connection that end wtih "dataitem"
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>Get-SQL -Session f1 -Excel -Connection C:\\Users\\James\\OneDrive\\Public\\F1\\f1Results.xlsx -showtables
Creates a new connection named F1 to the an Excel file, and shows the tables available.
-------------------------- EXAMPLE 5 --------------------------
PS C:\\>f1 -Insert "[RACES]" @{RaceName = $raceName, RaceDate = $racedate.ToString("yyyy-MM-dd") }
Uses the automatically created alias "f1" which was created in the previous example to insert a row of data into
the "Races" Table
-------------------------- EXAMPLE 6 --------------------------
PS C:\\>Get-SQL -Session F1 -Table "[races]" -Set "[poleDriver]" -Values $PoleDriver -SQL "WHERE RaceDate = $rd"
-Confirm:$false
Updates the races table in the "F1" session, setting the value in the column "PoleDriver" to the contents of
the variable $PoleDriver, in those rows where the RaceDate = $RD. This time the session is explictly specified
(using aliases is OK at the command line but not in scripts especially if the alias is created by a command run in
the script)
Changes normally prompt the user to confirm but here -Confirm:$false prevents it
-------------------------- EXAMPLE 7 --------------------------
PS C:\\>"CREATE USER 'johndoe' IDENTIFIED BY 'password'" , "GRANT ALL PRIVILEGES ON *.* TO 'johndoe'@'%' WITH grant
option" | Get-SQL
Pipes two commands into the default connection, giving a new mySql user full access to all tables in all databases
-------------------------- EXAMPLE 8 --------------------------
PS C:\\>Get-Sql -paste -gridview
Runs the query currently in the windows clipboard against the default existing and outputs to the Gridview
-------------------------- EXAMPLE 9 --------------------------
PS C:\\>SQL -table catalog_dataitem -select dataStatus -distinct -orderBy dataStatus -gridView
Builds the query " SELECT DISTINCT dataStatus FROM catalog_dataitem ORDER BY dataStatus",
runs it against the default existing connection and displays the results in a grid.
-------------------------- EXAMPLE 10 --------------------------
PS C:\\>[void](Get-sql $sql -OutputVariable Table)
PowerShell upacks Datatable objects into rows; so anything which needs a data table object can not get get it with
$table = Get-Sql $sql
because $table will contain an Array of DataRow objects, not a single DataTable.
To get round this Get-SQL has -OutputVariable which behaves like the common parameters errorVariable,
warningvariable etc.
(using the Name of the variable 'Table' not its value '$table'
After running the command the variable in the scope where the command is run contains the DataTable object.
Usually the datarow objects will not be required, so the output can be cast to a void or piped to Out-Null,.
RELATED LINKS
SYNOPSIS
Queries an ODBC or SQL Server database
SYNTAX
Get-SQL [[-SQL] <Object>] [[-Connection] <String>] [-Session <String>] [[-ChangeDB] <String>] [-ForceNew]
[-GridView] [-Describe <String>] [-Quiet] [-MsSQLserver] [-Access] [-Excel] [-OutputVariable <String>] [-Close]
[-WhatIf] [-Confirm] [<CommonParameters>]
Get-SQL [[-SQL] <Object>] [[-Connection] <String>] [-Session <String>] [[-ChangeDB] <String>] [-ForceNew]
[-GridView] -Table <String> -Where <String> [-GT] [-GE] [-EQ] [-NE] [-LE] [-LT] [-Like] [-NotLike] [-Select
<String[]>] [-Distinct] [-OrderBy <String[]>] [-GroupBy <String[]>] [-DateFormat <String>] [-Quiet] [-MsSQLserver]
[-Access] [-Excel] [-OutputVariable <String>] [-Close] [-WhatIf] [-Confirm] [<CommonParameters>]
Get-SQL [[-SQL] <Object>] [[-Connection] <String>] [-Session <String>] [[-ChangeDB] <String>] [-ForceNew]
[-GridView] [-Table <String>] [-Select <String[]>] [-Distinct] [-OrderBy <String[]>] [-GroupBy <String[]>]
[-Quiet] [-MsSQLserver] [-Access] [-Excel] [-OutputVariable <String>] [-Close] [-WhatIf] [-Confirm]
[<CommonParameters>]
Get-SQL [[-SQL] <Object>] [[-Connection] <String>] [-Session <String>] [[-ChangeDB] <String>] [-ForceNew]
[-GridView] -Paste [-Quiet] [-MsSQLserver] [-Access] [-Excel] [-OutputVariable <String>] [-Close] [-WhatIf]
[-Confirm] [<CommonParameters>]
Get-SQL [[-SQL] <Object>] [[-Connection] <String>] [-Session <String>] [[-ChangeDB] <String>] [-ForceNew]
-ShowTables [-MsSQLserver] [-Access] [-Excel] [-OutputVariable <String>] [-Close] [-WhatIf] [-Confirm]
[<CommonParameters>]
Get-SQL [[-SQL] <Object>] [[-Connection] <String>] [-Session <String>] [[-ChangeDB] <String>] [-ForceNew] [-Table
<String>] -Delete [-MsSQLserver] [-Access] [-Excel] [-OutputVariable <String>] [-Close] [-WhatIf] [-Confirm]
[<CommonParameters>]
Get-SQL [[-SQL] <Object>] [[-Connection] <String>] [-Session <String>] [[-ChangeDB] <String>] [-ForceNew] [-Table
<String>] -Set <String[]> [-Values] <String[]> [-DateFormat <String>] [-MsSQLserver] [-Access] [-Excel]
[-OutputVariable <String>] [-Close] [-WhatIf] [-Confirm] [<CommonParameters>]
Get-SQL [[-SQL] <Object>] [[-Connection] <String>] [-Session <String>] [[-ChangeDB] <String>] [-ForceNew] -Table
<String> -Where <String> [-GT] [-GE] [-EQ] [-NE] [-LE] [-LT] [-Like] [-NotLike] -Delete [-DateFormat <String>]
[-MsSQLserver] [-Access] [-Excel] [-OutputVariable <String>] [-Close] [-WhatIf] [-Confirm] [<CommonParameters>]
Get-SQL [[-SQL] <Object>] [[-Connection] <String>] [-Session <String>] [[-ChangeDB] <String>] [-ForceNew] -Table
<String> -Where <String> [-GT] [-GE] [-EQ] [-NE] [-LE] [-LT] [-Like] [-NotLike] -Set <String[]> [-Values]
<String[]> [-DateFormat <String>] [-MsSQLserver] [-Access] [-Excel] [-OutputVariable <String>] [-Close] [-WhatIf]
[-Confirm] [<CommonParameters>]
Get-SQL [[-SQL] <Object>] [[-Connection] <String>] [-Session <String>] [[-ChangeDB] <String>] [-ForceNew] -Insert
<String> [-DateFormat <String>] [-MsSQLserver] [-Access] [-Excel] [-OutputVariable <String>] [-Close] [-WhatIf]
[-Confirm] [<CommonParameters>]
DESCRIPTION
Get-SQL queries SQL databases using either ODBC or the native SQL-Server client.
Connections to databases are kept open and reused to avoid the need to make connections for every query,
but the first time the command is run it needs a connection string; this come from $DefaultDBConnection.
(e.g. set in your Profile) rather than being passed as a parameter: if it is set you can run
sql "Select * From Customers"
without any other setup; PowerShell will assume "sql" means "GET-SQL" if there is no other command named SQL.
Get-SQL -Connection allows a connection to be specified explictly; -MsSQLserver forces the use of the native SQL
Server driver,
and -Excel or -Access allow a file name to be used without converting it into an ODBC connection string.
The global variable $DbSessions holds objects for each open connection until Get-SQL is run with -Close.
Get-Sql will also build simple queries; for example
Get-SQL -Table Authors
Will run the "Select * from Authors" and a condition can be specified with
Get-SQL -Table Authors -Where Name -like "*Smith"
Get-SQL -ShowTables will show the available tables, and Get-SQL -Describe Authors will show the design of the
table.
PARAMETERS
-SQL <Object>
A SQL statement. If other parameters (such as -Table, or -Where) are provided, it becomes the end of the SQL
statement.
If no statement is provided, or none can be built from the other parameters, Get-SQL returns information about
the connection.
Required? false
Position? 1
Default value
Accept pipeline input? true (ByValue)
Accept wildcard characters? false
-Connection <String>
An ODBC connection string or an Access or Excel file name or the name of a SQL Server
It can be in the form "DSN=LocallyDefinedDSNName;" or
"Driver={MySQL ODBC 5.1 Driver};SERVER=192.168.1.234;PORT=3306;DATABASE=xstreamline;UID=johnDoe;PWD=password;"
A default connection string can be set in in $DBConnection so that you can just run "Get-SQL " ????SQL Statement????
".
Required? false
Position? 2
Default value $global:DefaultDBConnection
Accept pipeline input? false
Accept wildcard characters? false
-Session <String>
Allows a database connection to be Identified by name: this sets the name used in the global variable
$DBSessions.
In addition an alias is added: for example, if the session is named "F1" you can use the command F1 in place
of Get-SQL -Session F1
Required? false
Position? named
Default value Default
Accept pipeline input? false
Accept wildcard characters? false
-ChangeDB <String>
For SQL server and ODBC sources which support it (like MySQL) switches to a different database at the same
server.
Required? false
Position? 3
Default value
Accept pipeline input? false
Accept wildcard characters? false
-ForceNew [<SwitchParameter>]
If specified, makes a new connection for the default or named session.
If a connection is already established, -ForceNew is required to change the connection string.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-GridView [<SwitchParameter>]
If specified, sends the output to gridview instead of the PowerShell console.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-Describe <String>
Returns a description of the specified table - note that some ODBC providers don't support this.
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-ShowTables [<SwitchParameter>]
If specified, returns a list of tables in the current database - note that some ODBC providers don't support
this.
Required? true
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-Paste [<SwitchParameter>]
If specified, takes an SQL statement from the clipboard.
Line breaks and any text before SELECT , UPDATE or DELETE will be removed
Required? true
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-Table <String>
Specifies a table to select or delete from or to update.
Required? true
Position? named
Default value
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? false
-Where <String>
If specified, applies a SQL WHERE condition to the selected table. -Where specifies the field and the text in
-SQL supplies the condition.
Required? true
Position? named
Default value
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? false
-GT [<SwitchParameter>]
Used with -Where specifies the > operator should be used, with the operand for the condition found in -SQL.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-GE [<SwitchParameter>]
Used with -Where specifies the >= operator should be used, with the operand for the condition found in -SQL.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-EQ [<SwitchParameter>]
Used with -Where specifies the = operator should be used, with the operand for the condition found in -SQL.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-NE [<SwitchParameter>]
Used with -Where specifies the <> operator should be used, with the operand for the condition found in -SQL.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-LE [<SwitchParameter>]
Used with -Where specifies the <= operator should be used, with the operand for the condition found in -SQL.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-LT [<SwitchParameter>]
Used with -Where specifies the < operator should be used, with the operand for the condition found in -SQL.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-Like [<SwitchParameter>]
Used with -Where specifies the Like operator should be used, with the operand for the condition found in -SQL.
"*" in -SQL will be replaced with "%".
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-NotLike [<SwitchParameter>]
Used with -Where specifies the Not Like operator should be used, with the operand for the condition found in
-SQL. "*" in -SQL will be replaced with "%".
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-Select <String[]>
If Select is omitted, -Table TableName will result in "SELECT * FROM TableName";
Select specifies field-names (or other text) to use in place of "*".
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-Distinct [<SwitchParameter>]
Specifies that "SELECT DISTINCT ..." should be used in place of "SELECT ...".
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-OrderBy <String[]>
Specifies fields to be used in a SQL ORDER BY clause added at the end of the query.
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-GroupBy <String[]>
If specified, adds a group by clause to a select query; in this case the SELECT clause needs to contain fields
suitable for grouping.
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-Delete [<SwitchParameter>]
If specified, changes the query from a SELECT to a DELETE. This allows a query to be tested as a SELECT before
adding -Delete to the command.
-Delete requires a WHERE clause and not all ODBC drivers support deletion.
Required? true
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-Set <String[]>
If specified, changes the query from a Select to a Update -Set Specifies the field(s) to be updated.
-Set requires a WHERE clause.
Required? true
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-Values <String[]>
If -Set is specified, -Values contains the new value(s) for the fields being updated.
Required? true
Position? 2
Default value
Accept pipeline input? false
Accept wildcard characters? false
-Insert <String>
Specifies a table to insert into. The SQL parameter should contain a hash table or PSObject which holding the
data to be inserted.
Required? true
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-DateFormat <String>
Allows the format applied to Dates to be inserted to be changed if a service requires does not follow standard
conventions.
Required? false
Position? named
Default value '\\''yyyy'-'MM'-'dd HH':'mm':'ss'\\''
Accept pipeline input? false
Accept wildcard characters? false
-Quiet [<SwitchParameter>]
If specified, surpresses printing of the console message saying how many rows were returned
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-MsSQLserver [<SwitchParameter>]
Specifies the SQL Native client should be used instead of ODBC and string in -Connection is a SQL one.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-Access [<SwitchParameter>]
Specifies that the string in -Connection is an Access file path to be converted into an ODBC connection string.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-Excel [<SwitchParameter>]
Specifies that the string in -Connection is an Excel file path to be converted into an ODBC connection string.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-OutputVariable <String>
Behaves like the common parameters errorVariable, warningvariable etc.to pass back a table object instead of
an array of data rows.
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-Close [<SwitchParameter>]
Closes a database connection.
Required? false
Position? named
Default value False
Accept pipeline input? false
Accept wildcard characters? false
-WhatIf [<SwitchParameter>]
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
-Confirm [<SwitchParameter>]
Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Get-SQL -MsSQLserver -Connection "server=lync3\\rtclocal;database=rtcdyn; trusted_connection=true;" -Session
Lync
Creates a new session named "LYNC" to the rtcdyn database on the Rtclocal SQL instance on server Lync
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Get-SQL -Session LR -Connection "DSN=LR" -Quiet -SQL $SQL
Runs the SQL in $SQL - if the Session LR already exists it will be used, otherwise it will be created to the ODBC
source "LR"
Note that a script should always name a its session(s), something else may already have set the defualt session
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Get-Sql -showtables *dataitem
Gives a list of tables on the default connection that end wtih "dataitem"
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>Get-SQL -Session f1 -Excel -Connection C:\\Users\\James\\OneDrive\\Public\\F1\\f1Results.xlsx -showtables
Creates a new connection named F1 to the an Excel file, and shows the tables available.
-------------------------- EXAMPLE 5 --------------------------
PS C:\\>f1 -Insert "[RACES]" @{RaceName = $raceName, RaceDate = $racedate.ToString("yyyy-MM-dd") }
Uses the automatically created alias "f1" which was created in the previous example to insert a row of data into
the "Races" Table
-------------------------- EXAMPLE 6 --------------------------
PS C:\\>Get-SQL -Session F1 -Table "[races]" -Set "[poleDriver]" -Values $PoleDriver -SQL "WHERE RaceDate = $rd"
-Confirm:$false
Updates the races table in the "F1" session, setting the value in the column "PoleDriver" to the contents of
the variable $PoleDriver, in those rows where the RaceDate = $RD. This time the session is explictly specified
(using aliases is OK at the command line but not in scripts especially if the alias is created by a command run in
the script)
Changes normally prompt the user to confirm but here -Confirm:$false prevents it
-------------------------- EXAMPLE 7 --------------------------
PS C:\\>"CREATE USER 'johndoe' IDENTIFIED BY 'password'" , "GRANT ALL PRIVILEGES ON *.* TO 'johndoe'@'%' WITH grant
option" | Get-SQL
Pipes two commands into the default connection, giving a new mySql user full access to all tables in all databases
-------------------------- EXAMPLE 8 --------------------------
PS C:\\>Get-Sql -paste -gridview
Runs the query currently in the windows clipboard against the default existing and outputs to the Gridview
-------------------------- EXAMPLE 9 --------------------------
PS C:\\>SQL -table catalog_dataitem -select dataStatus -distinct -orderBy dataStatus -gridView
Builds the query " SELECT DISTINCT dataStatus FROM catalog_dataitem ORDER BY dataStatus",
runs it against the default existing connection and displays the results in a grid.
-------------------------- EXAMPLE 10 --------------------------
PS C:\\>[void](Get-sql $sql -OutputVariable Table)
PowerShell upacks Datatable objects into rows; so anything which needs a data table object can not get get it with
$table = Get-Sql $sql
because $table will contain an Array of DataRow objects, not a single DataTable.
To get round this Get-SQL has -OutputVariable which behaves like the common parameters errorVariable,
warningvariable etc.
(using the Name of the variable 'Table' not its value '$table'
After running the command the variable in the scope where the command is run contains the DataTable object.
Usually the datarow objects will not be required, so the output can be cast to a void or piped to Out-Null,.
RELATED LINKS