I am trying to get transactions to work using PowerShell and MySQL. My code is based on https://michlstechblog.info/blog/powershell-some-examples-to-use-a-mysql-database/:
[string]$sMySQLUserName = 'user';
[string]$sMySQLPW = 'passw0rd';
[string]$sMySQLDB = 'mydatabase';
[string]$sMySQLHost = localhost';
[string]$sConnectionString = "server="+$sMySQLHost+";port=3306;uid=" + $sMySQLUserName + ";pwd=" + $sMySQLPW + ";database="+$sMySQLDB;
$mySqlTempTableDefinition = 'create table some_temp_table (
sku varchar(50) primary key,
thing float null
)';
$oConnection = New-Object MySql.Data.MySqlClient.MySqlConnection($sConnectionString);
$oConnection.Open();
$oMYSQLTransaction=$oConnection.BeginTransaction();
$oMYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand;
$oMYSQLCommand.Connection=$oConnection;
$oMYSQLCommand.Transaction=$oMYSQLTransaction;
$oMYSQLCommand.CommandText = "SET autocommit = 0";
$iRows=$oMYSQLCommand.executeNonQuery();
$oMYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand
$oMYSQLCommand.Connection=$oConnection;
$oMYSQLCommand.CommandText = $mySqlTempTableDefinition;
$oMYSQLCommand.Transaction=$oMYSQLTransaction;
$iRows=$oMYSQLCommand.executeNonQuery();
$oMySQLTransaction.Rollback();
$oMySqlCommand.Connection.Close();
The first time I run it, the table is created and persisted in the database, which is not the expected result, since I never commit the transaction. Of course, this means that a subsequent run fails because the table already exists. How do I correctly implement this?
