powershell与MySQL交互_使用Powershell针对MySQL运行MySql存储过程脚本

I want to execute a MySQL stored procedure on a MySQL 5.6 server that is a QA environment. This is part of a Continuous Delivery implementation via Azure piplines.

For example: My .sql file has multiple stored procedures which are executed successfully by MySQL Workbench, but the same file I want to execute via PowerShell.

.sql content:

DROP procedure IF EXISTS `test`;

DELIMITER //

CREATE PROCEDURE `test` ()

BEGIN

select * from organisations;

END

//

DELIMITER ;

DROP procedure IF EXISTS `test1`;

DELIMITER //

CREATE PROCEDURE `test1` ()

BEGIN

select * from organisations;

END

//

DELIMITER ;

PowerShell script code:

[System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")

[string]$sMySQLUserName = 'xxx'

[string]$sMySQLPW = 'xxx'

[string]$sMySQLDB = 'xxx'

[string]$sMySQLHost = 'localhost'

[string]$sConnectionString = "server="+$sMySQLHost+";port=3306;uid=" + $sMySQLUserName + ";pwd=" + $sMySQLPW + ";database="+$sMySQLDB

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

$Error.Clear()

try

{

$oConnection.Open()

write-host "Connection opened"

}

catch

{

write-warning ("Could not open a connection to Database $sMySQLDB on Host $sMySQLHost. Error: "+$Error[0].ToString())

}

#$oTransAction=$oConnection.BeginTransaction()

$oMYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand

$oMYSQLCommand.Connection=$oConnection

#$oMYSQLCommand.Transaction=$oMYSQLTransaction

#$sql = Get-Content D:\Release\ConsoleDev\SqlScript\testprocedure.sql

$sql = [io.file]::ReadAllText('D:\Release\ConsoleDev\SqlScript\testprocedure - Copy.sql')

$oMYSQLCommand.CommandText = $sql

write-host $sql

try

{

$iRowsAffected=$oMYSQLCommand.executeNonQuery()

}

catch

{

write-warning ("ERROR occured while ExecuteNonQuery")

}

# Do some Inserts or updates here and commit your changes

finally

{

$oConnection.Close()

write-host "Closing Connection"

}

PowerShell throws the following error:

WARNING: ERROR occurred while ExecuteNonQuery

I think the problem in executing DELIMITER //.

解决方案

I have resolved the issues with below changes - 1)$oMYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlScript 2) $oMYSQLCommand.Query = $sql 3) $oMYSQLCommand.Execute() and finally it has been executed and stored proc in my DB. –