-1

I am inserting values from PowerShell into MySQL table. I have 2 Insert statements (insertQuery1,insertQuery2) in transaction.

There is COMMIT after insertQuery1 so changes in query_1 is committed but there is no is COMMIT after insertQuery2 yet changes in query_2 are also committed. Can someone please let me know why are changes in query_2 are also committed even when there is no COMMIT statement after it.

If I do not use COMMIT both the queries are not committed.

$oConnection = New-Object MySql.Data.MySqlClient.MySqlConnection($sConnectionString)
try
{

        
        $oConnection.Open()
        $oMYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand

        
        $oMYSQLTransaction = $oConnection.BeginTransaction();
        $oMYSQLCommand.Connection=$oConnection
        $oMYSQLCommand.Transaction = $oMYSQLTransaction;
        
        $insertQuery1 ='INSERT into `temp_schema`.`temp_table` (`temp_col`) VALUES('+'"'+$temp_val1+'"' +')'

        $oMYSQLCommand.CommandText=$insertQuery1 
        $iRowsAffected=$oMYSQLCommand.ExecuteNonQuery()
        
        $oMYSQLTransaction.Commit();

        Start-Sleep -Seconds 10      

        $insertQuery2 ='INSERT into `temp_schema`.`temp_table` (`temp_col`) VALUES('+'"'+$temp_val2+'"' +')'

        $oMYSQLCommand.CommandText=$insertQuery2
        $iRowsAffected=$oMYSQLCommand.ExecuteNonQuery()

}
catch{
    write-warning ("Error: "+$Error[0].ToString())
}
finally{
    $oMySqlCommand.Connection.Close()

}
sql_dummy
  • 715
  • 8
  • 23

1 Answers1

0

Ok, I realized the Transaction is completed after COMMIT and as insertQuery2 is not part of any Transaction it is auto-commited.

sql_dummy
  • 715
  • 8
  • 23