Get-SQL.ps1

if (-not $Global:DbSessions ) { $Global:DbSessions = @{}  } #I have supressed warnings about global variables, this needs to be accessible inside and outside the module.

function Get-SQL {
    [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSAvoidUsingPlainTextForPassword','',Justification='The Credential parameter accepts a SQL cred or a PS cred but not a string')]
    <#
      .Synopsis
        Queries an ODBC, SQLite or SQL Server database
      .Description
        Get-SQL queries SQL databases using either ODBC, the ADO driver for SQLite 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 explicitly; -MsSQLserver forces the use of
        the native SQL Server driver, -lite allows the file name of a SQLite Database to be used
        and -Excel or -Access allow a file name to be used without converting it into an ODBC connection string.

        Multiple named sessions may be open concurrently, and the global variable $DbSessions holds objects
        for each until Get-SQL is run with -Close. Note that you can run a query and make and/or close
        the connection in a single command. However, if you pipe the output into a command like
        Select-Object -First 2 then when Get-SQL is stopped by the downstream command it is unable to
        close the connection.

        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.

        Argument completers fill in names of ODBC connections, databases, tables, and columns where needed.
      .Parameter SQL
        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.
      .Parameter Connection
        An ODBC connection string or an Access, Excel, or SQLite 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» ".
      .Parameter Excel
        Specifies that the string in -Connection is an Excel file path to be converted into a connection string.
      .Parameter Access
        Specifies that the string in -Connection is an Access file path to be converted into a connection string.
      .Parameter Lite
        Specifies the SQLite driver should be used and the string in -Connection may be the path to a SQLite file.
      .Parameter MsSQLserver
        Specifies the SQL Native client should be used and string in -Connection may be the name of a SQL Server.
      .Parameter Session
        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
      .Parameter ForceNew
        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.
      .Parameter ChangeDB
        For SQL server and ODBC sources which support it (like MySQL) switches to a different database at the same server.
      .Parameter Close
        Closes a database connection. Note this is run in the "end" phase of the command. If Get-SQL is stopped by another command
        in the pipeline (for example Select-object -first ) then it may not close the connection, so although this command can be
        combined with a select query, care is needed to ensure it is not defeated by another command in the same pipeline.
      .Parameter Table
        Specifies a table to select or delete from or to update.
      .Parameter Where
        If specified, applies a SQL WHERE condition to the selected table. -Where specifies the field and the text in -SQL supplies the condition.
      .Parameter GT
        Used with -Where specifies the > operator should be used, with the operand for the condition found in -SQL.
      .Parameter GE
        Used with -Where specifies the >= operator should be used, with the operand for the condition found in -SQL.
      .Parameter EQ
        Used with -Where specifies the = operator should be used, with the operand for the condition found in -SQL.
      .Parameter NE
        Used with -Where specifies the <> operator should be used, with the operand for the condition found in -SQL.
      .Parameter LE
        Used with -Where specifies the <= operator should be used, with the operand for the condition found in -SQL.
      .Parameter LT
        Used with -Where specifies the < operator should be used, with the operand for the condition found in -SQL.
      .Parameter Like
        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 "%".
      .Parameter NotLike
        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 "%".
      .Parameter Select
        If Select is omitted, -Table TableName will result in "SELECT * FROM TableName".
        Select specifies field-names (or other text) to use in place of "*".
      .Parameter Distinct
        Specifies that "SELECT DISTINCT ..." should be used in place of "SELECT ...".
      .Parameter OrderBy
        Specifies fields to be used in a SQL ORDER BY clause added at the end of the query.
      .Parameter Delete
        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.
      .Parameter Set
        If specified, changes the query from a Select to a Update -Set Specifies the field(s) to be updated.
        -Set requires a WHERE clause.
      .Parameter Values
        If -Set is specified, -Values contains the new value(s) for the fields being updated.
      .Parameter Insert
        Specifies a table to insert into. The SQL parameter should contain a hash table or PSObject which holding the data to be inserted.
      .Parameter DateFormat
        Allows the format applied to Dates to be inserted to be changed if a service requires does not follow standard conventions.
      .Parameter GridView
        If specified, sends the output to gridview instead of the PowerShell console.
      .Parameter GroupBy
        If specified, adds a group by clause to a select query; in this case the SELECT clause needs to contain fields suitable for grouping.
      .Parameter Describe
        Returns a description of the specified table - note that some ODBC providers don't support this.
      .Parameter ShowTables
        If specified, returns a list of tables in the current database - note that some ODBC providers don't support this.
      .Parameter Paste
        If specified, takes an SQL statement from the clipboard.
        Line breaks and any text before SELECT , UPDATE or DELETE will be removed.
      .Parameter Quiet
        If specified, suppresses printing of the console message saying how many rows were returned.
      .Parameter OutputVariable
         Behaves like the common parameters errorVariable, warningvariable etc.to pass back a table object instead of an array of data rows.
      .Example
        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
        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
        Get-Sql -showtables *dataitem
        Gives a list of tables on the default connection that end with "dataitem"
      .Example
        Get-SQL -Session f1 -Excel  -Connection C:\Users\James\OneDrive\Public\F1\f1Results.xlsx -showtables
        Creates a new connection named F1 to an Excel file, and shows the tables available.
      .Example
        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
        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 explicitly 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
        "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
        Get-Sql -paste -gridview
        Runs the query currently in the clipboard against the default existing and outputs to the Gridview
      .Example
        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
        [void](Get-sql $sql  -OutputVariable Table)
        PowerShell unpacks Datatable objects into rows; so anything which needs a DataTable object cannot 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' as the parameter value)
        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.
    #>
    [CmdletBinding(DefaultParameterSetName='Describe',SupportsShouldProcess=$true,ConfirmImpact="High")]
    param   (
        [parameter(Position=0, ValueFromPipeLine=$true)]
        $SQL,
        [parameter(Position=1)][ValidateNotNullOrEmpty()]
        [string]$Connection  = $global:DefaultDBConnection ,
        [ValidateNotNullOrEmpty()]
        [string]$Session = "Default",
        [parameter(Position=2)]
        [alias('Use')]
        [string]$ChangeDB,
        [alias('Renew')]
        [switch]$ForceNew  ,
        [parameter(ParameterSetName="Paste")]
        [parameter(ParameterSetName="Describe")]
        [parameter(ParameterSetName="Select")]
        [parameter(ParameterSetName="SelectWhere")]
        [alias('g')][switch]$GridView,
        [parameter(ParameterSetName="Describe")]
        [alias('d')][string]$Describe,
        [parameter(ParameterSetName="ShowTables" , Mandatory=$true)]
        [switch]$ShowTables,
        [parameter(ParameterSetName="Paste"      , Mandatory=$true)]
        [switch]$Paste,
        [parameter(ParameterSetName="UpdateWhere", Mandatory=$true, ValueFromPipelineByPropertyName=$true)]
        [parameter(ParameterSetName="DeleteWhere", Mandatory=$true, ValueFromPipelineByPropertyName=$true)]
        [parameter(ParameterSetName="SelectWhere", Mandatory=$true, ValueFromPipelineByPropertyName=$true)]
        [parameter(ParameterSetName="Update"     , Mandatory=$false)]
        [parameter(ParameterSetName="Delete"     , Mandatory=$false)]
        [parameter(ParameterSetName="Select"     , Mandatory=$false)]
        [alias('from','update','T')][string]$Table,
        #region Parameters for queries with a WHERE clause
        [parameter(ParameterSetName="UpdateWhere", Mandatory=$true, ValueFromPipelineByPropertyName=$true)]
        [parameter(ParameterSetName="DeleteWhere", Mandatory=$true, ValueFromPipelineByPropertyName=$true)]
        [parameter(ParameterSetName="SelectWhere", Mandatory=$true, ValueFromPipelineByPropertyName=$true)]
        [string]$Where,
        [parameter(ParameterSetName="UpdateWhere")]
        [parameter(ParameterSetName="DeleteWhere")]
        [parameter(ParameterSetName="SelectWhere")]
        [switch]$GT,
        [parameter(ParameterSetName="UpdateWhere")]
        [parameter(ParameterSetName="DeleteWhere")]
        [parameter(ParameterSetName="SelectWhere")]
        [switch]$GE,
        [parameter(ParameterSetName="UpdateWhere")]
        [parameter(ParameterSetName="DeleteWhere")]
        [parameter(ParameterSetName="SelectWhere")]
        [switch]$EQ,
        [parameter(ParameterSetName="UpdateWhere")]
        [parameter(ParameterSetName="DeleteWhere")]
        [parameter(ParameterSetName="SelectWhere")]
        [switch]$NE,
        [parameter(ParameterSetName="UpdateWhere")]
        [parameter(ParameterSetName="DeleteWhere")]
        [parameter(ParameterSetName="SelectWhere")]
        [switch]$LE,
        [parameter(ParameterSetName="UpdateWhere")]
        [parameter(ParameterSetName="DeleteWhere")]
        [parameter(ParameterSetName="SelectWhere")]
        [switch]$LT,
        [parameter(ParameterSetName="UpdateWhere")]
        [parameter(ParameterSetName="DeleteWhere")]
        [parameter(ParameterSetName="SelectWhere")]
        [switch]$Like,
        [parameter(ParameterSetName="UpdateWhere")]
        [parameter(ParameterSetName="DeleteWhere")]
        [parameter(ParameterSetName="SelectWhere")]
        [switch]$NotLike,
        #endregion
        #Parameters for SELECT Queries
        [parameter(ParameterSetName="Select")]
        [parameter(ParameterSetName="SelectWhere")]
        [alias('Property')][string[]]$Select,
        [parameter(ParameterSetName="Select")]
        [parameter(ParameterSetName="SelectWhere")]
        [switch]$Distinct,
        [parameter(ParameterSetName="Select")]
        [parameter(ParameterSetName="SelectWhere")]
        [string[]]$OrderBy,
        [parameter(ParameterSetName="Select")]
        [parameter(ParameterSetName="SelectWhere")]
        [String[]]$GroupBy,
        #Parameters for Delete queries
        [parameter(ParameterSetName="DeleteWhere", Mandatory=$true)]
        [parameter(ParameterSetName="Delete"     , Mandatory=$true)]
        [switch]$Delete,
        #Parameters for Update queries
        [parameter(ParameterSetName="UpdateWhere", Mandatory=$true)]
        [parameter(ParameterSetName="Update"     , Mandatory=$true)]
        [string[]]$Set,
        [parameter(ParameterSetName="UpdateWhere", Mandatory=$true,Position=1)]
        [parameter(ParameterSetName="Update"     , Mandatory=$true,Position=1)]
        [Object[]]$Values,
        #Parameters for INSERT Queries
        [parameter(ParameterSetName="Insert"     , Mandatory=$true)]
        [alias('into')][string]$Insert,
        [parameter(ParameterSetName="Insert")]
        [parameter(ParameterSetName="Update")]
        [parameter(ParameterSetName="UpdateWhere")]
        [parameter(ParameterSetName="DeleteWhere")]
        [parameter(ParameterSetName="SelectWhere")]
        [String]$DateFormat   = "'\''yyyy'-'MM'-'dd HH':'mm':'ss'\''",
        [parameter(ParameterSetName="Paste")]
        [parameter(ParameterSetName="Describe")]
        [parameter(ParameterSetName="Select")]
        [parameter(ParameterSetName="SelectWhere")]
        [Alias("Q","Qu")]
        [switch]$Quiet,
        [switch]$MsSQLserver,
        [switch]$MySQL,
        [switch]$Lite,
        [switch]$Access,
        [switch]$Excel,
        $CredForMsSQL,
        [String]$OutputVariable,
        [alias('TimeOut')]
        [int]$QueryTimeOut,
        [switch]$Close
    )
    begin   {
        #Prepare  session, if needed, and leave it in the global variable DBSessions - a hash table with Name and connection object
        #If the function was invoked with an Alias of "DB" and there is session named "DB" switch to using that session
        if   (("Default" -eq $Session) -and $Global:DbSessions[$MyInvocation.InvocationName]) {$Session = $MyInvocation.InvocationName}

        #if the session doesn't exist or we're told to force a new session, then create and open a session
        if   (     ($ForceNew) -or (  -not  $Global:DbSessions[$session]) ) {  #-and -not $Close
            if     ($Lite -and $PSVersionTable.PSVersion.Major -gt 5 -and $IsMacOS ) {
                Add-Type -Path (Join-Path $PSScriptRoot "osx\System.Data.SQLite.dll"       )
            }
            elseif ($Lite -and $PSVersionTable.PSVersion.Major -gt 5 -and $linux )   {
                Add-Type -Path (Join-Path $PSScriptRoot "linux-x64\System.Data.SQLite.dll" )
            }
            elseif ($lite -and -not [System.Environment]::Is64BitProcess) {
                Add-Type -Path (Join-Path $PSScriptRoot "win-x86\System.Data.SQLite.dll"   )
            }
            elseif ($lite ) {
                Add-Type -Path (Join-Path $PSScriptRoot "win-x64\System.Data.SQLite.dll"   )
            }
            #Catch -force to refresh instead of replace the current connection (e.g. Server has timed out )
            if    (($ForceNew)   -and       $Global:DbSessions[$session] -and -not $PSBoundParameters.ContainsKey('Connection'))  {
                if     ($Global:DbSessions[$session].GetType().name -eq "SqlConnection"    ) {$MsSQLserver = $true}
                elseif ($Global:DbSessions[$session].GetType().name -eq "SQLiteConnection" ) {$Lite        = $true}
                elseif ($Global:DbSessions[$session].GetType().name -eq "MySqlConnection"  ) {$MySQL       = $true}
            }
            #If -MySQL switch is used check we have the MySQL objects
            if     ($MySQL) {
                    $t = 'MySql.Data.MySqlClient.MySqlConnection' -as [Type]
                    if (-not $t) {throw 'Native MySQL was requested by MSSQL objects have not been load (use Add-Type -path <<path>>\MySql.Data.Dll)' ; return}
                    else {$dllVerForMySql = $t.Assembly.FullName -replace '^.*version=([\d\.]+).*$','$1'}
            }
            if     ($CredForMsSQL -and ($Access -or $Excel -or $lite -or $mysql)) {Write-Warning '-CredForMsSQL Ignored'}
            if     ($CredForMsSQL -and -not $MsSQLserver)                         {$MsSQLserver = $true}
            #If -MSSQLServer  switch is used assume connection is a server if there is no = sign in the connection string
            if     ($MsSQLserver  -and  $Connection                  -and  $connection -notmatch "=") {
                $Connection =   "server=$Connection;timeout=60"
                if (-not $CredForMsSQL){$Connection = "server=$Connection;trusted_connection=true;timeout=60"}
            }
            #If -Lite switch is used assume connection is a file if there is no = sign in the connection string, check it exists and build the connection string
            if     ($Lite         -and  $Connection                  -and  $connection -notmatch "=") {
                if (Test-Path -Path     $Connection)  {
                                        $Connection  = "Data Source=" + (Resolve-Path -Path $Connection -ErrorAction SilentlyContinue).Path + ";"
                }
                else { Write-Warning -Message "Can't create database connection: could not find $Connection" ; return}
            }
            #If the -Excel or Access switches are used, then the connection parameter is the path to a file, so check it exists and build the connection string
            if     ($Excel)               {
              if   (Test-Path     -Path $Connection)  {
                  $Connection  = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DriverId=790;ReadOnly=0;Dbq=" +
                                 (Resolve-Path -Path $Connection -ErrorAction SilentlyContinue).Path + ";"
              }
              else { Write-Warning -Message "Can't create database connection: could not find $Connection" ; return}
            }
            if     ($Access)              {
              if (Test-Path -Path  $Connection)  {
                     $Connection  = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq="+
                                    (Resolve-Path -Path $Connection -ErrorAction SilentlyContinue).Path + ";"
              }
              else { Write-Warning -Message "Can't create database connection: could not find $Connection" ; return}
            }
            if     (-not $Connection)     { Write-Warning -Message "A connection was needed but -Connection was not provided."; break}
            Write-Verbose -Message "Connection String is '$connection'"
            #Use different types for SQL server, SQLite and ODBC. They (and the logic) are almost interchangable.
            if     ($CredForMsSQL -is [pscredential]) {
                $u=([pscredential]$CredForMsSQL).UserName
                $p=([pscredential]$CredForMsSQL).Password
                $p.MakeReadOnly()
                $CredForMsSQL = [System.Data.SqlClient.SqlCredential]::new($u,$p)
            }
            if     ($CredForMsSQL -and $CredForMsSQL -isnot [System.Data.SqlClient.SqlCredential]) {throw 'Invalid SQL Credential'}
            elseif ($CredForMsSQL)        { $Global:DbSessions[$Session] = New-Object -TypeName System.Data.SqlClient.SqlConnection    -ArgumentList $Connection, $CredForMsSQL }
            elseif ($MsSQLserver)         { $Global:DbSessions[$Session] = New-Object -TypeName System.Data.SqlClient.SqlConnection    -ArgumentList $Connection }
            elseif ($Lite)                { $Global:DbSessions[$Session] = New-Object -TypeName System.Data.SQLite.SQLiteConnection    -ArgumentList $Connection }
            elseif ($MySQL)               { $Global:DbSessions[$Session] = New-Object -TypeName MySql.Data.MySqlClient.MySqlConnection -ArgumentList $Connection }
            else                          { $Global:DbSessions[$Session] = New-Object -TypeName System.Data.Odbc.OdbcConnection        -ArgumentList $Connection
                                            $Global:DbSessions[$Session].ConnectionTimeout = 30
            }
            #Open our connection. NB, if 32 bit office is installed Excel, Access ETC have 32 bit ODBC drivers which need 32 bit Powershell not 64 bit.
            try                           { $Global:DbSessions[$Session].open() }
            catch                         { Write-Warning -Message "Error opening connection to '$Connection'"
                                            if (($Access -or $Excel) -and [System.Environment]::Is64BitProcess) {
                                                Write-Warning -Message  "This is 64-bit PowerShell, If Office is 32-bit you need to use 32 bit-PowerShell"}
                                            $Global:DbSessions[$Session] = $null
                                            break
            }
            if ($MySQL -and [version]::new(8,0,28) -lt $Global:DbSessions[$Session].ServerVersion -and [version]::new(8,0,30) -gt $dllVerForMySql) {
                Write-Warning "The combinaton of .NET driver and Server version may need  'Set Names utf8MB4' to avoid errors about character set 'utf8MB4'"
                # V8 non-odbc drivers need .net newer than 5 so Windows PowerShell, native drivers and new servers are a bad combo
            }
            #Create an alias which matches the connection name.
            if  ("Default" -eq $Session)  { $Global:DefaultDBConnection = $Connection }
            else                          { New-Alias -Name $Session -Value Get-SQL -Scope Global -Force}
        }
        if         ($ChangeDB)            { $Global:DbSessions[$Session].ChangeDatabase($ChangeDB) } #This method to change DB won't work with every provider
        if   (     ($Paste) -and (Get-Command -Name 'Get-Clipboard' -ErrorAction SilentlyContinue))  {
            #You could use [windows.clipboard]::GetText() - be warned this may not work in the older releases of the standard shell
            #For older versions of PowerShell I have a Get-Clipboard function which wraps this
            $SQL = (Get-Clipboard) -replace "^.*?(?=select|update|delete)","" -replace "[\n|\r]+"," "
        }
    }
    process {
        #If $table is specified make sure $SQL isn't empty otherwise we won't get to Select * from $Table; also make sure conditions allow for it to be zero!
        if   ($Table -and $null -eq $SQL) { $SQL = "   "}
        if   ($SQL.SQL)                   { $SQL = $SQL.SQL}
        if                    ($Describe) { #support -Describe [tablename] to descibe a table
            if ($Global:DbSessions[$Session].driver -match "SQORA" ) { #Oracle is special ...
                Get-SQL -Session $Session -Quiet -SQL  ("select COLUMN_NAME, data_type as TYPE_NAME, data_length AS COLUMN_SIZE " +
                                                        " from user_tab_cols where table_name = '$Describe' order by COLUMN_NAME")
            }
            else  { #Remove any [] around the table name - because that's how .GetSchema() works ...
                $Describe = $Describe -replace "\[(.*)\]",'$1'
                # For some drivers .GetSchema() can get the columns for a single table. But the Excel driver can't, so get all columns and filter.
                if     ($Global:DbSessions[$Session].Driver -match "ACEODBC.DLL" ) {
                        $columns = $Global:DbSessions[$Session].GetSchema("Columns") | Where-Object {$_.TABLE_NAME -eq $Describe }
                }
                elseif ($Global:DbSessions[$Session].gettype().name -eq "SqlConnection" ) {#SQL server uses slightly differnet syntax
                        $columns = $Global:DbSessions[$Session].GetSchema("Columns", @("%","%",$Describe,"%"))
                }
                else {  $columns = $Global:DbSessions[$Session].GetSchema("Columns", @("","",$Describe)) }
                if ($GridView) {$columns | Out-GridView -Title "Table $Describe"}
                else           {$columns | Select-Object -Property @{n="COLUMN_NAME";e={if ($_.Column_Name -match "\W") {"[$($_.Column_Name)]"} else {$_.column_Name} }},
                                                                TYPE_NAME, COLUMN_SIZE, IS_NULLABLE
                }
            }
        }
        elseif              ($Showtables) { #ODBC method to get tables won't work with every provider, but nor will executing "show tables". $SQL param becomes a filter
            if   ($Global:DbSessions[$Session].driver -match "SQORA" ) {#Oracle is special ...
                (Get-SQL  -Session $Session -Quiet -SQL  "select OBJECT_NAME from user_objects where object_type  IN ('VIEW','TABLE'); ").object_name |
                    Where-Object {$_ -like "$SQL*"}
            }
            else {$Global:DbSessions[$Session].GetSchema("Tables") | Where-Object {$Global:DbSessions[$Session].DataSource -ne "Access" -or $_.TABLE_TYPE -ne "SYSTEM TABLE"} |
                ForEach-Object {
                    if     ($_.TABLE_NAME -like "$SQL*" -and $_.TABLE_NAME -match "\W") {"[" + $_.TABLE_NAME + "]"}
                    elseif ($_.TABLE_NAME -like "$SQL*")                                {      $_.TABLE_NAME      }
                } | Sort-Object
            }
        }
        elseif           ($null -ne $SQL) { #$SQL holds any SQL which we can't (or don't want to( assemble from the cmdline, a whole statement or final clause
          ForEach        ($s     in $SQL) { #More than one statement/clause can be passed
            if ($Delete -or $Set -and -not $Table) { Write-Warning -Message "You must specifiy a table and where condition to use -Delete or -Set" ; return }
            if                            ($Table) { #If $Table was specified, build a Select, Delete or Update query
                #Support -table [tablename] -Where [ColumnName] -eq 99 and similar syntax.
                # -eq -ne and other operators are *switches*. The operand for = (etc.) is in $SQL so only Operator is allowed. Too complex to enforce this in Param() block!
                $opCount        =  (($Like, $EQ, $NE , $LT , $GT , $GE, $LE, $NotLike) -eq $true).Count
                #Can't have multiple operators, and operator requires -Where to be specified and a value in -SQL (-SQL usually implied in cmdline)
                if ((($opCount) -gt 1) -or  (($opCount -eq 1) -and -not $Where ) -or ($Where -and  "   " -eq  $s )) {
                    Write-Warning -Message  "You can't specify a where condition like that"
                    return
                }
                if  (($opCount) -eq 1) { #If we have an operator, column and value in $s turn $s into the condition (add the column name after)
                    #if the operand for -eq etc is a date format it for SQL
                    if ($s -is [datetime])  {
                        $s = $s.tostring($DateFormat)  #Default format has "'" this works for Excel inserts and SQL server.
                        if ($Global:DbSessions[$Session].Driver -eq "ACEODBC.DLL") {       #For Excel where needs # not quotes as date markers
                            $s = $s -replace "'","#"
                        }
                    } #if the operand for -eq etc is not a number or isn't wrrapped in quotes. Wrap it in quotes and double up the ' character
                    elseif (($s -notmatch "^\d+\.?\d*$") -and ($s -notmatch "^'.*'$"))
                                      {$s = "'" + ($s -replace "(?<!')'(?!')","''") +"'"  }
                    if          ($EQ) {$s =   " =  $s "            }
                    if          ($NE) {$s =   " <> $s "            }
                    if          ($GE) {$s =   " >= $s "            }
                    if          ($LE) {$s =   " <= $s "            }
                    if          ($GT) {$s =   " >  $s "            }
                    if          ($LT) {$s =   " <  $s "            }
                    if       (($Like)  -or ($NotLike) ) {   #for the like operators replace * wildcard with SQL % wildcard
                                       $s = $s -replace "\*","%"   }
                    if        ($Like) {$s =     " like $s "        }
                    if     ($NotLike) {$s = " not like $s "        }
                    #At the end of this $s holds the condition but not the column name
               }
                if           ($Delete) { #Support Delete queries  -Table [tableName] -Delete -where [Column] -eq [Value]
                    #A careless -Delete could wipe out a table - so insist on either -where [columnName] and a condition, or "Where blah blah" in $SQL
                  if ((($Where) -and $s) -or ($s -match "where\s+\w+")) {
                    if ($Where)     {$s = "DELETE FROM $Table WHERE $Where " + $S }
                    else            {$s = "DELETE FROM $Table "              + $S }
                  }
                  else {Write-Warning -Message "You must specifiy a where condition to use -Delete"; return }
               }
                elseif          ($Set) {
                  #Support update ... set queries -Table [tableName] -Set [Columns] -Values [values] -Where [Column] -EQ [Value]
                  #Don't allow set to modify all the rows (same logic as Delete)
                  if ( (  $Where  -and $s) -or ($s -match "where\s+\w+")) {
                    #We have a list of columns in Set and values for them need the same number of each - then build the set clause, wrapping text values in ''
                    if ($Set.Count  -ne  $Values.Count)          {Write-Warning -Message "Must have the same number of columns to set as values to set them to"; return }
                    $setList = ""
                    for  ($i = 0; $i  -lt  $set.count; $i++) {
                        if  (   $Values[$i] -is [datetime])  {
                            if ($Global:DbSessions[$Session].gettype().name -match "SQLiteConnection") {
                                    $Vi = [int]($Values[$i].Subtract([datetime]::UnixEpoch).TotalSeconds)
                            }
                            else {  $Vi = $Values[$i].tostring($DateFormat)}     #Default format has "'" this works for Excel, Access and SQL server.
                            $SetList = $SetList + $Set[$i] + "= " + $vi +"  ,"
                        }
                        #  Wrap text in ' and escape ' char
                        elseif ($Values[$i] -notmatch "^[\d\.]*$") {$SetList = $SetList + $Set[$i] + "='" + ($Values[$i] -replace "'","''") +"' ," }
                        else                                       {$SetList = $SetList + $Set[$i] + "= " +  $Values[$i] +"  ," }
                    }
                    #will have an extra "," at the end.
                    $setList = $setList -replace ",$",""
                    if   ($Where)   {$s = "UPDATE $Table SET $setList WHERE $Where " + $s }
                    else            {$s = "UPDATE $Table SET $setList "              + $s }
                }
                  else                {Write-Warning -Message "You must specifiy a where condition to use -Set"   ; return }
               }
                else                   {#If we're not updating or deleting and -Table was passed we must be selecting ....
                    if   (   $Select) {$SelectClause = ($Select -join ", ") + " FROM $Table " }
                    else              {$SelectClause =                      " * FROM $Table " }
                    if   (    $Where) {$SelectClause = $SelectClause   +      "WHERE $Where " } #note we need to have the "what" part of SQL. but SQL could be @("=10",">73") we'll run 2 queries
                    if   ( $Distinct) {$s = "SELECT DISTINCT "         +  $SelectClause + $s  }
                    else              {$s = "SELECT "                  +  $SelectClause + $s  }
                    if   (  $GroupBy) {$s = $s +    " GROUP BY "       + ($GroupBy -join ", ")}
                    if   (  $OrderBy) {$s = $s +    " ORDER BY "       + ($OrderBy -join ", ")}
                }
            }
            elseif                       ($Insert) {
            #Support -insert [IntoTableName] @{hashtable of fields and values}
                if     ($s -is [Hashtable]) {$index = $s.keys}
                elseif ($s -is [psobject] ) {$index = (Get-Member -InputObject $s -MemberType NoteProperty).Name }
                else                        { Write-Warning -Message "Can't build an Insert statement from $s. Pass a hashtable or a PSObject" ; return}
                $fieldsPart     = "  "
                $valuesPart     = "  "
                foreach ($name in $index) {
                    $fieldsPart = $fieldsPart  + $name + " , "
                    $v          = $s.$name
                    if     ($Global:DbSessions[$Session].gettype().name -match "SQLiteConnection") {
                        if ($v -is [datetime] )  {$v = [int]($v.Subtract([datetime]::UnixEpoch).TotalSeconds)}
                        if ($v -is [Boolean]  )  {$v = [int]$v}
                    }
                    #$DateFormat defaults to the standard date format which SQL dialects support, but it can be overridden for special cases
                    if     ($v -is [datetime] )  {$valuesPart = $valuesPart +         $v.tostring($DateFormat) + " , " }
                    elseif ($v -is [int]    -or
                            $v -is [float]  -or
                            $v -is [boolean]  )  {$valuesPart = $valuesPart +         $v.tostring()         +    " , " }
                    elseif ($v -match "^\d+$" )  {$valuesPart = $valuesPart +         $v.tostring()         +    " , " }
                    else                         {$valuesPart = $valuesPart +  "'" + ($v -replace "'","''") +   "' , " }
                }
                $s  = ("INSERT INTO {0} ({1}) VALUES ({2})" -f $Insert,($fieldsPart -replace ",\s*$",""),($valuesPart -replace ",\s*$",""))
                $s = $s -replace ",\s*,",", null ," -replace "(?<=[(,])\s*''\s*(?=[),])"," null " -replace ",\s*\)",", null)"
            }
            Write-Verbose -Message $s
            #Choose suitable data adapter object based on session type.
            if     ($Global:DbSessions[$Session].gettype().name -match "MySqlConnection" ) { #Test this first or it will match on SQLConnection which is for MS SQL Server
               $da = New-Object    -TypeName MySql.Data.MySqlClient.MySqlDataAdapter -ArgumentList (
                        New-Object -TypeName MySql.Data.MySqlClient.MySqlCommand     -ArgumentList $s,$Global:DbSessions[$Session] )
            }
            elseif ($Global:DbSessions[$Session].gettype().name -match "SqlConnection" )  {
               $da = New-Object    -TypeName System.Data.SqlClient.SqlDataAdapter    -ArgumentList (
                        New-Object -TypeName System.Data.SqlClient.SqlCommand        -ArgumentList $s,$Global:DbSessions[$Session] )
            }
            elseif ($Global:DbSessions[$Session].gettype().name -match "SQLiteConnection" ) {
               $da = New-Object    -TypeName System.Data.SQLite.SQLiteDataAdapter    -ArgumentList (
                        New-Object -TypeName System.Data.SQLite.SQLiteCommand        -ArgumentList $s,$Global:DbSessions[$Session] )
            }
            else  {
               $da = New-Object    -TypeName System.Data.Odbc.OdbcDataAdapter        -ArgumentList (
                        New-Object -TypeName System.Data.Odbc.OdbcCommand            -ArgumentList $s,$Global:DbSessions[$Session])
            }
            if ($QueryTimeOut -and $da.SelectCommand) {$da.SelectCommand.CommandTimeout = $QueryTimeOut}
            if ($QueryTimeOut -and $da.InsertCommand) {$da.InsertCommand.CommandTimeout = $QueryTimeOut}
            if ($QueryTimeOut -and $da.UpdateCommand) {$da.UpdateCommand.CommandTimeout = $QueryTimeOut}
            $dt       = New-Object -TypeName System.Data.DataTable
            #And finally we get to execute the SQL Statement.
            try  { if ((-not ($Set -or $Delete -or ($Insert -and $ConfirmPreference -ne "high"))) -or ($PSCmdlet.ShouldProcess("$Session database", $s)) ) {
                   $rows  = $da.fill($dt)
                   if (-not ($Quiet -or $Delete -or $Set -or $Insert)) {Write-Host -Object ("" + [int]$rows + " row(s) returned")}
            }}
            catch {
               if($S)               { #if we get an error and -SQL was passed show the final SQL statement.
                  $e=$Global:error[0]
                  throw ( New-Object -TypeName "System.Management.Automation.ErrorRecord" `
                                     -ArgumentList (($e.exception.message -replace "^(.*])\s*","`$1`n") + "`n `n>>>  $S `n `n" ), $e.FullyQualifiedErrorId ,"ParserError" ,$e.TargetObject)
               }
               else                   { throw }
              }
            if   (($GridView) -and (($PSVersionTable.PSVersion.Major -GE 3)-or ($host.name -match "ISE" )) ) {$dt | Out-GridView -Title $s}
            else  {$dt}
            if ($OutputVariable) {Set-Variable -Scope 2 -Name $OutputVariable -Value $dt -Visibility Public}
            }
        }
        elseif              (-not $Close -and -not $Quiet) { #If $SQL, $table, $describe or $showtimes weren't included either we're opening a new connection, or we're checking or closing an existing one.
            $Global:DbSessions[$Session]
        }
    }
    end     {
        if ($Close -and $Global:DbSessions[$Session]) {
            $Global:DbSessions[$Session].close()
            $Global:DbSessions[$Session].dispose()
            $Global:DbSessions.Remove($Session)
            Remove-Item -Path (Join-Path -Path "Alias:\" -ChildPath $Session) -ErrorAction SilentlyContinue
        }
    }
}

function Hide-GetSQL {
<#
    .Synopsis
        Allows a command line with quote marks to passed into Get-SQL, can be used simply as ¬
    .Example
        ¬ select host,user from mysql.user
        Sends the command "select host,user from mysql.user" to the default ODBC session

#>
  Get-Sql -sql ($MyInvocation.line.substring($MyInvocation.OffsetInLine)) | Format-Table -AutoSize
}
Set-Alias -Name ¬ -Value Hide-GetSQL