Invoke-SqlQuery -ConnectionName ConName -Query "INSERT INTO table (column) VALUES ('value');"

Invoke-SqlQuery : Die Tabelle 0 kann nicht gefunden werden.
At line:1 char:5

• Invoke-SqlQuery -ConnectionName Postfix -Query "INSERT INTO table ...
  

• ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  

  • CategoryInfo : OperationStopped: (:) [Invoke-SqlQuery], IndexOutOfRangeException
  • FullyQualifiedErrorId : System.IndexOutOfRangeException,Invoke-SqlQuery

This happens for any database, table and column I tried with. The value is inserted correctly in the table but the Cmdlet still throws an exception. SELECT or DELETE statements work without any issues.

Please let me know what kind of details would help to identify the root cause.

How can I get the Last-Inserted-ID after inserting a row?
Invoke-SQLUpdate returns only an integer (modified rows)
I am using the MySQL Provider

The MSSQL provider does not expose parameters for setting Encrypt and TrustServerCertificate - as opposed to the MySQL and PostgreSQL provider which support encryption.

When executing against MySQL in a container (version 8), SimplySql is failing on the Invoke-SqlQuery command is failing on $ds.fill($da) method with the error "The given key was not present in the dictionary". I attempted to debug, but wasn't able to find out much more than this.

Invoke-SqlBulkCopy -SourceConnectionName $src
#where the variable 'src' does not exist

Most of the cmdlets reference the internal function "TestConnectionName" which has a mandatory parameter of -ConnectionName. So add a [ValidateNotNullOrEmpty()] attribute to any -ConnectionName to avoid a confusing situation for end users.

When using SimplySql against an Azure PaaS PostgreSQL server fails when creating a database. The database does get created, but the command fails with

NotSpecified: Exception calling "ExecuteNonQuery" with "0" argument(s): "Exception while reading from stream" 
At /usr/local/share/powershell/Modules/SimplySql/1.7.0/Cmdlets/Invoke-SqlUpdate.ps1:44 char:19 
+             Try { Write-Output $cmd.ExecuteNonQuery() } 
+                   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
at Invoke-SqlUpdate, /usr/local/share/powershell/Modules/SimplySql/1.7.0/Cmdlets/Invoke-SqlUpdate.ps1: line 44 
at <ScriptBlock>, /etc/octopus/Work/20210223230801-288694-57/Script.ps1: line 118 
at <ScriptBlock>, <No file>: line 1 
at <ScriptBlock>, /etc/octopus/Work/20210223230801-288694-57/Octopus.FunctionAppenderContext.ps1: line 185 
at <ScriptBlock>, /etc/octopus/Work/20210223230801-288694-57/Bootstrap.Octopus.FunctionAppenderContext.ps1: line 3057 
at <ScriptBlock>, <No file>: line 1 
at <ScriptBlock>, <No file>: line 1 

The SqlBulkCopy class by defaults uses the option "Keep Identity" -- which requires alter table permissions. There are additional options that, if not specified, require alter table permissions. See Microsoft Documentation: Bulk Insert: Security.

Possible options, provide option flags to include (comma delimited list)?

Open-MySqlConnection : The term 'Open-MySqlConnection' is not recognized as the name of a cmdlet, function, script

Hello.
i think close-sqlconnection doesnt close connection on server, sleep status shows. Im using mariadb. its ok? good that script do not dublicate conections like many scripts in php.

Cannot Invoke-SQLBulkCopy from a PSObject instead of a source db.

Source: Script Porwershell

I am querying a information in a MySQL database with charset utf8. When I use the column data, the words with accents show wrong character, for exemplo, the name Maurício it is show like MaurÃ-cio.

How may I query the correct name from MySQL Database with charset utf8?

When using -Notify, any Write-Progress with Id= 0 (default identity) get overwritten. However, you may want to track a larger progress and then have the bulk progress be a child.

• Allow the ability to specify the ParentId (and calculate the Id as ParentId + 1) [bugfix]
• Allow the ability to specify your own notification scriptblock [enhancement]

Hello,

Thank you so much for providing the SimplySQL module. My initial experiments worked well and it will be a useful resource.

I am having a problem though which, since you seem to be actively maintaining the module, may be of interest. My objective here is to get some data from a MariaDB instance running on a Raspberry Pi 4 and show the output in a WPF grid. At the moment, I'm running my code from a Windows 10 Pro machine (x64) against the database on the RPi.

Here is the code for querying the database, which I am running from a script:

Import-Module -Name SimplySql
$sql = "SELECT * FROM Brokers;"
Open-MySqlConnection -Server '192.168.100.4' -Database 'Finances' -UserName 'sebastian' -Password 'XXXXX' $myData = Invoke-SqlQuery -Query $sql
Close-SqlConnection

[I'm aware that passing the username/password is deprecated, but it's a lot easier for troubleshooting.]

Running that results in an error:

Exception calling "Fill" with "1" argument(s): "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT * FROM Brokers' at line 1" At C:\Program Files\WindowsPowerShell\Modules\SimplySql\1.6.2\Providers\MySql\provider.ps1:42 char:13 + $da.Fill($ds) + ~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : MySqlException

The curious thing is that if I run each line one by one in the PowerShell console, then it all works fine. I only get an error when running the code as a script.

I've tried another module, MySQL by Adam Bertram (https://mcpmag.com/articles/2016/03/02/querying-mysql-databases.aspx). That does work in script form. Initially I had the same error as above with the MySQL module until I put the $sql assignment in double-quotes. Using single-quotes resulted in the error.

With SimplySQL though the error occurs despite the $sql assignment being in double-quotes, as per the code above. It doesn't make any difference if I don't use a variable and just have the double-quoted string as an argument to Invoke-SqlQuery.

The behaviour is similar whether using PowerShell 5 or 7. For PowerShell 7, the error occurs using the script. It also occurs if, when entering the commands line by line, I use the variable in the Invoke-SqlQuery line ($myData = Invoke-SqlQuery -Query $sql) but it works fine with $myData = Invoke-SqlQuery -Query "SELECT * FROM Brokers".

Using the Debugger in the PowerShell 5 ISE (with ISESteroids), I can step through and see that all is working until Invoke-SqlQuery.ps1 calls GetDataSet on line 75. That then calls into provider.ps1 and the exception is thrown on line 42 therein.

I hope this will give enough detail to help you troubleshoot. Please let me know if more would be helpful.

Thank you

Since .Net 4.6 Token based authentication has been present in the SqlClient library SqlConnection.AccessToken. The primary use case seems to be for access to SQL resources in Azure. Automation/Functions/VMs/WebApps being able to connect to SQL Azure by leveraging their Managed Service Identity (MSI) means there is no need to manage secrets for connecting to SQL Azure. Allowing this in SimplySql is important for Automation/Function/VM scenarios.
SQL Azure Azure Active Directory Authentication.

If the rollback takes longer than the command connection timeout, undo-sqltransaction will return with an error. However, the SimplySql connection will continue to show their being a transaction (HasTransaction = True); even when there is not. Also, the connection can get closed during this time.

Detect when an error has occurred with undo (or complete) -sqltransaction and clean up the SimplySql connection -- remove the transaction and make sure the connection is still open, reopen if needed.

For testing, use short connection timeout (less than 5 seconds) and then within a transaction create a temporary table and insert 10k rows into it, then rollback.

Hello

Thanks you for your very usefull module! :)

It could be interesting to add a new source provider, the CSV allowing the ability to do SQL request on a CSV data.

i don't know if it's hard to add it but there is a way using Microsoft’s ACE provider, like in this article

Actually i'm using a script to import a CSV in SQLite db and then use your module to request the SQLlite db.

as i have some db to request and csv file to request : one module to rules them all, etc, etc... :)

Tonic8

If there is an existing transaction (with the MSSQL provider) and invoke-sqlbulkcopy is used, an error is thrown because the bulkcopy class is not expecting this.

Suggestion: modify the code to check for an existing transaction and use it if it exists.

We know the parameter -ArgumentList to transfer variables into a script block. Now I don't want to make a new connection every time in the script block. But I also don't know the variables in which the DB connection is stored. The parameter -ConnectionName does not work here either. How can a connection be passed on to a script block?

`Invoke-SqlQuery -Query "SELECT * FROM packageversions"

Start-Job -ScriptBlock {
Invoke-SqlQuery -Query "SELECT * FROM packageversions"
#ls c:
} | Wait-Job | Receive-Job
`

I'm trying to open a connection to MySQL database using the following cmdlet:
Open-MySqlConnection -Server '(host)' -Credential $SQLCred -Database '(db name)'
I have tried the same but with a connection string but both attempts resulted in the following error:

Line |
100 | Try { $conn.Open() }
| ~~~~~~~~~~~~
| Exception calling "Open" with "0" argument(s): "Unable to connect to any of the specified MySQL
| hosts."

Additional information:
PSVersion 7.1.3
PSEdition Core
GitCommitId 7.1.3
OS Linux 3.10.0-1160.21.1.el7.x86_64 #1 SMP Tue Mar 16 18:28:22 UTC 2021
Platform Unix
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1
WSManStackVersion 3.0

SimplySQL version: 1.8.0

Expose this via a new property on the results of Show-SqlConnection OR new command Get-SqlTransaction.

Windows 10
PowerShell 7.1.0-preview.4

Import-Module -Name SimplySql

$param  = @{
    ServiceName = 'xepdb1'
    UserName    = 'sys'
    Password    = 'password'
}

Open-OracleConnection @param

WARNING: You are using -UserName and -Password, these options are deprecated and will be removed in the future.  Please consider using -Credential.

An error has occurred that was not properly handled. Additional information is shown below. The PowerShell process will exit.
Unhandled exception. System.TypeLoadException: Could not load type 'System.Security.Principal.WindowsImpersonationContext' from assembly 'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.
   at OracleInternal.ConnectionPool.PoolManager`3.CreateNewPRThreadFunc(Object state)
   at System.Threading.QueueUserWorkItemCallback.<>c.<.cctor>b__6_0(QueueUserWorkItemCallback quwi)
   at System.Threading.ExecutionContext.RunForThreadPoolUnsafe[TState](ExecutionContext executionContext, Action`1 callback, TState& state)
   at System.Threading.QueueUserWorkItemCallback.Execute()
   at System.Threading.ThreadPoolWorkQueue.Dispatch()
   at System.Threading._ThreadPoolWaitCallback.PerformWaitCallback()

This worked when creating the connection string my self
DATA SOURCE=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xe)));User Id=sys;DBA Privilege=sysdba;

user id 'sys' worked, but 'system' did not.

If I run this command:
Open-MySqlConnection -Server $x.server -Database $x.database -Credential $x.Credential -ConnectionName "development"
I get no active connections

If I remove the -ConnectionName property it works and the connection name is "default"

Am I missing something? Sometimes I need to have multiple db connections open at once. I am assuming that is accomplished by giving each one a unique ConnectionName?

Powershell version 5.1
SimplySQL version 1.7.0

When trying to bulk copy data from oracle to mssql i get the specified cast is not valid, but i can't figure out what field is causing it (i first create the table by doing an select into from openrowsets and the bulk copying it with simplysql)

Error message: "Conversion failed when converting the nvarchar value 'invoke-sqlbulkcopy : exception calling "writetoserver" with "1" argument(s): "specified cast is not valid."' to data type int."

This is a table that doesnt work (Oracle)
field1 number
field2 number(15,0)
field3 number (15,0)
field4 number (15,0)
field5 varchar2(10)
field6 number(19,4)
field7 number
field8 varchar2(10)

And this is the table in mssql
field1 float
field2 numeric(15,0)
field3 numeric(15,0)
field4 numeric(15,0)
field5 nvarchar(10)
field6 numeri(19,4)
field7 float
field8 nvarchar(10)

I am trying to run a MySql update query to update some a single column to a new value and running into issues:

Any thoughts here

P.S. I have tried quoting the string "Closed" and that did not work either

Sometimes it is useful to see how a query will look without actually executing it. I would love to see the ability to prepare a query integrated.

In my mind this could be accomplished on Invoke-* by adding SupportsShouldProcess and making -WhatIf dump out the query to the console.

It looks from the code that this should work, but it doesn't so I'm probably missing something...

$SimplySql = Import-Module SimplySql -PassThru

$SqlConnectionParams = @{
    Server          = 'server'
    Database        = 'database'
    Credential      = [pscredential]::new( 'read_only', ( ConvertTo-SecureString -String 'read_only' -AsPlainText -Force ) )
}

Open-MySqlConnection @SqlConnectionParams -ConnectionName 'Test'

$Command = & $SimplySql { $Script:Connections.Test.GetCommand( 'SELECT @Test', 30, @{ Test = 'Hello World' } ) }
$Command.Prepare()

Have you done any testing or work to make this compatible with PowerShell 6 on linux? I get the following error when I try to connect initially.

PS /> Open-MySqlConnection -ConnectionName some-mysql -Server 127.0.0.1 -UserName root -Password password
Exception calling "Open" with "0" argument(s): "The type initializer for 'MySql.Data.MySqlClient.Replication.ReplicationManager' threw an exception."
At /root/.local/share/powershell/Modules/SimplySql/1.2.0/Providers/MySql/config.ps1:76 char:11
+     Try { $conn.Open() }
+           ~~~~~~~~~~~~
+ CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : TypeInitializationException

Synopsis

I'm trying to use the Invoke-SqlUpdate command in the SimplySql PowerShell module to create a MySQL Stored Procedure. The query I have authored works perfectly fine when I copy/paste it into Adminer. However, when I try to use SimplySql, the query fails. The following error message is returned by the MySQL database engine.

Invoke-SqlUpdate: Exception calling "ExecuteNonQuery" with "0" argument(s): "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER //
CREATE PROCEDURE cbtnuggets.sp_addPerson (IN firstName varchar(30)' at line 1"

My suggestion would be to add an extra automated test, to ensure that insertion of stored procedures and functions works correctly. If I can find time to do this, I will submit a pull request to the project.

Reproduction Steps

Invoke-SqlUpdate -Query @'
DELIMITER //

CREATE PROCEDURE cbtnuggets.sp_addPerson (IN firstName varchar(30), IN lastName varchar(30))
  BEGIN
    INSERT INTO cbtnuggets.people (firstName, lastName) VALUES (@firstName, @lastName);
  END//
'@

Hi Mitharandyr.

I have tried to use Invoke-SqlUpdate with -Parameters to insert into an Oracle-DB.
I always get the exception ORA-00936. Is there a problem with the Parameters-Option?
Thanks a lot!
Tobias

Ausnahme beim Aufrufen von "ExecuteScalar" mit 0 Argument(en): "ORA-00936: Ausdruck fehlt"
In C:\Program Files\WindowsPowerShell\Modules\SimplySql\1.6.2\Classes.ps1:38 Zeichen:22
Try { return $cmd.ExecuteScalar() }
~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : OracleException

AWS RDS IAM role authentication for MySQL requires the use of the mysql_clear_password plugin. The included version of MySql.data.dll does not contain the method/plugin to make this work. I was able to get the authentication to work by replacing the MySql.data.dll from the MySql.Data NuGet Package (I used the version from the net452 folder when testing).

NotSpecified: Exception calling "Open" with "0" argument(s): "Authentication method 'mysql_clear_password' not supported by any of the available plugins." 

Closing a database connection doesn't close the session on the database server. Tested with MSSQL, MySQL, Oracle and PostgreSQL. (PowerShell and database servers all latest versions)

I installed simplysql module but it appears it is missing. I tried the same on several PCs.
All PCs are PS > 5 , windows 10. Please see verbose logs below.
Any clue?

PS C:\WINDOWS\system32> Install-Module -Name SimplySql -Verbose
VERBOSE: Using the provider 'PowerShellGet' for searching packages.
VERBOSE: The -Repository parameter was not specified. PowerShellGet will use all of the registered repositories.
VERBOSE: Getting the provider object for the PackageManagement Provider 'NuGet'.
VERBOSE: The specified Location is 'https://www.powershellgallery.com/api/v2' and PackageManagementProvider is 'NuGet'.
VERBOSE: Searching repository 'https://www.powershellgallery.com/api/v2/FindPackagesById()?id='SimplySql'' for ''.
VERBOSE: Total package yield:'1' for the specified package 'SimplySql'.
VERBOSE: Performing the operation "Install-Module" on target "Version '1.6.2' of module 'SimplySql'".

Untrusted repository
You are installing the modules from an untrusted repository. If you trust this repository, change its
InstallationPolicy value by running the Set-PSRepository cmdlet. Are you sure you want to install the modules from
'PSGallery'?
[Y] Yes [A] Yes to All [N] No [L] No to All [S] Suspend [?] Help (default is "N"): a
VERBOSE: The installation scope is specified to be 'AllUsers'.
VERBOSE: The specified module will be installed in 'C:\Program Files\WindowsPowerShell\Modules'.
VERBOSE: The specified Location is 'NuGet' and PackageManagementProvider is 'NuGet'.
VERBOSE: Downloading module 'SimplySql' with version '1.6.2' from the repository
'https://www.powershellgallery.com/api/v2'.
VERBOSE: Searching repository 'https://www.powershellgallery.com/api/v2/FindPackagesById()?id='SimplySql'' for ''.
VERBOSE: InstallPackage' - name='SimplySql',
version='1.6.2',destination='C:\Users\Win10user\AppData\Local\Temp\2064638562'
VERBOSE: DownloadPackage' - name='SimplySql',
version='1.6.2',destination='C:\Users\Win10user\AppData\Local\Temp\2064638562\SimplySql\SimplySql.nupkg',
uri='https://www.powershellgallery.com/api/v2/package/SimplySql/1.6.2'
VERBOSE: Downloading 'https://www.powershellgallery.com/api/v2/package/SimplySql/1.6.2'.
VERBOSE: Completed downloading 'https://www.powershellgallery.com/api/v2/package/SimplySql/1.6.2'.
VERBOSE: Completed downloading 'SimplySql'.
VERBOSE: Hash for package 'SimplySql' does not match hash provided from the server.
VERBOSE: InstallPackageLocal' - name='SimplySql',
version='1.6.2',destination='C:\Users\Win10user\AppData\Local\Temp\2064638562'
VERBOSE: Catalog file 'SimplySql.cat' is not found in the contents of the module 'SimplySql' being installed.
VERBOSE: Module 'SimplySql' was installed successfully to path 'C:\Program
Files\WindowsPowerShell\Modules\SimplySql\1.6.2'.

When I run a sql script with multiple queries I recover the last table in a DataTable which belong to a DataSet.
DataSet seems to hold all the dataTable with the results of each query in the sql script twice.
Sadly the Relations is not implemented.

I go around with copying the dataTables in a fresh DataSet but it's bit sad.

Having an issue, I built a tool in Powershell ISE using SimplySQL and, though it works the ISE, when I try to run it in powershell.exe, it throws an error:

New-Object : Cannot find type [System.Collections.Generic.Queue[SqlMessage]]: verify that the assembly containing this
type is loaded.
At line:32 char:16
+         return & $origNewObject @psBoundParameters
+                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidType: (:) [New-Object], PSArgumentException
    + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand

I've looked at the other submitted issue that is similar (but different) and pre-emptively run this command for you:

Import-Module SimplySql -Force
Get-Module SimplySql | Select-Object ModuleType, Version, Name
(Get-Module SimplySql).ExportedCommands.Keys | Where-Object { $_ -like "open*"}

Output:

ModuleType Version Name
---------- ------- ----
    Script 1.6.2   SimplySql
Open-MySqlConnection
Open-OracleConnection
Open-PostGreConnection
Open-SqlConnection
Open-SQLiteConnection

I've tried a number of things to try to get it to work (here's a non-comprehensive list):

• adding the assembly directly via add-type -path
• adding the assembly via add-type -name
• checking the $env:profile and setting the .exe to have the same results for $env:profile
• compared all the assemblies loaded between the 2, found the 21 differences and loaded those
• tried to copy the content of Classes.ps1 in the module folder straight into SimplySql.psm1 (over the line where it dot-sources that classes file).

Willing to troubleshoot if you have any thoughts, not sure why ISE works and not .exe; otherwise I will have to find another way to connect to the db. Thanks :)

oh, also:

PS C:\Windows\system32> $PSVersionTable

Name                           Value
----                           -----
PSVersion                      5.1.18362.628
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.18362.628
CLRVersion                     4.0.30319.42000
WSManStackVersion              3.0
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1

Hi, regarding #1 - any thoughts about if powershell core and linux/macos will be supported?

Edit: my mistake, no issues at all - last example with the ` quotes are working flawlessly. Thank you for this great module!!

Hi,
quotes for DB or the columns in the query string are not working.

working:
$query = 'SELECT time_opening, order_number FROM MyDB.MyTable ORDER BY time_opening DESC, Id ASC LIMIT 100;'
not working:
$query = 'SELECT "time_opening", order_number FROM MyDB.MyTable ORDER BY time_opening DESC, Id ASC LIMIT 100;'
$query = 'SELECT "time_opening", order_number FROM MyDB.MyTable ORDER BY time_opening DESC, Id ASC LIMIT 100;'
$query = 'SELECT ""time_opening"", order_number FROM MyDB.MyTable ORDER BY time_opening DESC, Id ASC LIMIT 100;'
$query = 'SELECT "time_opening", order_number FROM MyDB.MyTable ORDER BY time_opening DESC, Id ASC LIMIT 100;'
$query = 'SELECT time_opening, country, order_number FROM MyDB.MyTable ORDER BY time_opening DESC, Id ASC LIMIT 100;'

The last command is a valid SQL query (automatically generated by HeidiSQL)
What I am doing wrong? Especially column or table names with spaces - i want to quote

Thanks.

Immediately after upgrading the DB engine to MySQL 8.29, the simplysql throws this error:
Exception calling "Fill" with "1" argument(s): "Character set 'utf8mb3' is not supported by .Net Framework."
At C:\Program Files\WindowsPowerShell\Modules\SimplySql\Providers\MySql\provider.ps1:42 char:13

•         $da.Fill($ds)
  

•         ~~~~~~~~~~~~~
  

  • CategoryInfo : NotSpecified: (:) [], MethodInvocationException
  • FullyQualifiedErrorId : NotSupportedException

It looks like simplysql is using an older version of the .NET driver. How do I point it to the updated version of the driver? We have to run 8.0.29 for compliance reasons.

Hi
Not an issue just some snippet of code I wanted to share.
Sometime you get an error like error at or near ".". I have 100 "." in my query, not really helpful (:
Now you can run Get-SqlQueryError after getting an error from Invoke-SqlQuery and it will show you the last query and mark the error location in red so you know which "." it is:

Probably need some refining and testing (tested it only on PostgreSQL) but maybe it can help someone

function Get-SqlQueryError {
    [CmdletBinding()]
    param (
        [Parameter(ValueFromPipeLine)]
        [System.Management.Automation.ErrorRecord]$ErrorRecord
    )
    if (!$ErrorRecord) {
        foreach ($ErrorI in $Error) {
            if ($ErrorI.Exception.Message -like '*Exception calling "Fill"*') {
                $ErrorRecord = $ErrorI
                Break
            }
        }
    }

    $Query = $ErrorRecord.Exception.InnerException.Statement.SQL
    $Position = $ErrorRecord.Exception.InnerException.Position

    $Start = $Query.Substring(0, $Position -1)
    $End = $Query.Substring($Position, $Query.Length - $Position)

    'Error: {0}' -f $ErrorRecord.Exception.Message
    'Position: {0}' -f $Position
    "{0}`e[41m{1}`e[0m{2}" -f $Start, $Query[$Position - 1], $End
}

At least in the mysql config.ps1 it accepts string based usernames and passwords. It would not be too difficult to support pscredentials possibly?

I ask because I am trying to minimize the occurrence of clear text passwords in code. I currently read secure strings from file and then convert to plain text. Skipping that step could be more PowerShelly. Internally you would still have to convert but should not need to store in memory?

Summary

I'd like to report an issue encountered with SimplySql 1.8.0 and PowerShell Core 7.2.0 on Ubuntu that caused me quite a few hours of confusion until I found a workaround.

What is expected to happen?

What is expected is if one installs the simplysql module via PowerShell Core in Ubuntu, it works out of the box, as if one had run the commands in Windows.

From a fresh Ubuntu install do:
$ sudo snap install powershell --classic
powershell 7.2.0 from Microsoft PowerShell✓ installed
$ pwsh
PS /> install-module -name SimplySql

What happens instead?

PS /> show-sqlconnection
show-sqlconnection: The term 'show-sqlconnection' is not recognized as a name of a cmdlet, function, script file, or executable program.
Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

Is there a workaround?

PS /> $test1=(get-installedmodule -name simplysql)
PS /> $test2=(get-command | where-object {$_.Source -eq 'SimplySql'})
PS /> if($test1.count -eq 1 -and $test2.count -lt 21){import-module simplysql}

I'm pretty new to SimplySQL but I can't seem to be able to open a connection to a database file when using the ConnectionName parameter. Here's an example

$FilePath = "C:\Users\inica\Documents\result.db"

Open-SQLiteConnection -FilePath $FilePath -ConnectionName foo
Get-SqlConnection
WARNING: There is no active SQL Connection.

When using the commandlet without the connectionName paramter a connection is opened

$FilePath = "C:\Users\inica\Documents\result.db"

Open-SQLiteConnection -FilePath $FilePath 
Get-SqlConnection



PoolCount         : 0
ConnectionString  : Data Source=C:\Users\inica\Documents\result.db
DataSource        : result
FileName          : C:\Users\inica\Documents\result.db
Database          : main
DefaultTimeout    : 30
BusyTimeout       : 0
WaitTimeout       : 30000
PrepareRetries    : 3
ProgressOps       : 0
ParseViaFramework : False
Flags             : Default
DefaultDbType     : 
DefaultTypeName   : 
VfsName           : 
OwnHandle         : True
ServerVersion     : 3.24.0
LastInsertRowId   : 0
Changes           : 0
AutoCommit        : True
MemoryUsed        : 401202
MemoryHighwater   : 401202
State             : Open
ConnectionTimeout : 15
Site              : 
Container         : 

Is this normal, or am I doing something wrong?

Dear sirs,

we tried to use your module for an oracle DB.

We used the update command with 8 oracle parameters as hash value.
The order of the parameters seems to be incorrect, perhaps randomly.
Maybe the hash parameter should be an [ordered}[hashtable] type?

Thank you very much for your help!
The module is great :-)

kind regards
Klaus.

I am trying to install SimplySQL, but so far, have not been able to use it. Here is Install-Module.
PS C:\Program Files\WindowsPowerShell\Modules> install-module -Name SimplySQL -verbose -Repository PSGallery
VERBOSE: Repository details, Name = 'PSGallery', Location = 'https://www.powershellgallery.com/api/v2'; IsTrusted =
'False'; IsRegistered = 'True'.
VERBOSE: Using the provider 'PowerShellGet' for searching packages.
VERBOSE: Using the specified source names : 'PSGallery'.
VERBOSE: Getting the provider object for the PackageManagement Provider 'NuGet'.
VERBOSE: The specified Location is 'https://www.powershellgallery.com/api/v2' and PackageManagementProvider is 'NuGet'.
VERBOSE: Searching repository 'https://www.powershellgallery.com/api/v2/FindPackagesById()?id='SimplySQL'' for ''.
VERBOSE: Total package yield:'1' for the specified package 'SimplySQL'.
VERBOSE: Skipping installed module SimplySql 1.8.0.

I'm thinking this last line means that it did not install. I was able to find the SimplySql directory from the Admin login, but cannot get it to load in the Program Files directory. This is my first time installing modules, so I am not experienced.

Thank you.

https://dev.mysql.com/doc/connector-net/en/connector-net-programming-bulk-loader.html
This supports bulk loading from a file.

Consider Adding a Connection level attribute (use alternative-loading) and then in Invoke-SqlBulkCopy
write out the information to disk, then process the file into MySQL.

If the duraton of a SqlQuery against Oracle (Version 11) is longer then the CommandTimeout (default 30 sec) the Invoke-SqlQuery command freezes.
Setting the Timeout higher or to 0 (unlimited) it works.
I think the correct behavior is to call an error an not freezing.

Hello,

is it possible to use NULL as value?

Something like this:

$q = 'INSERT INTO tt (id,val1,val2) VALUES(@id,@val1,@val2)'
Invoke-SqlUpdate -Query $q -Parameters @{id=1;val1=$null;val2=10};

val1 appears with the value 0 in the DB instead of NULL. Any way to fix that?

There are no examples of how to use a ldap lookup server for oracle connections.

i need to call this with a program using 32-bit powershell, but it takes 15-30 seconds to import the module, vs only a couple of seconds on a normal 64-bit powershell. i can reproduce the delay in 32-bit ISE. with the -verbose switch, import-module shows the delay happens prior to any cmdlets/functions actually loading.

VERBOSE: Loading module from path 'C:\Program Files (x86)\WindowsPowerShell\Modules\SimplySql\1.6.2\SimplySql.psm1'.

(the delay happens whether i have the module in program files or program files (x86)

the cmdlets all load pretty instantaneously once they start loading. any thoughts or tips?

Hi, thanks for the great module.

After successfully installing it with:

Find-Module "SimplySql" | Install-Module -Scope AllUsers -Force

it fails to import on Server 2016 due to a missing .NET Standard dependency or so I've inferred from this error:

Imgur

It works on my local machine where I have Visual Studio 2017 installed which presumably included this. From googling it appears one might also be able to resolve this by installing the .NET Core SDK, however that's a little much.

Would it be possible for you to fix this entirely on your side/bundle the dependencies with the Module?

Or is there at least a way you recommend for me to get this working, preferably without installing huge SDKs on a Server.

Thank you