使用powershell对mysql运行mysql存储过程脚本

h79rfbju  于 2021-06-19  发布在  Mysql
关注(0)|答案(1)|浏览(438)

我想在MySQL5.6服务器上执行一个mysql存储过程,这是一个qa环境。这是通过azure管道实现连续交付的一部分。
例如:我的 .sql 文件有多个存储过程,mysql workbench成功地执行了这些过程,但我希望通过powershell执行的是同一个文件。
.sql内容:

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脚本代码:

[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引发以下错误:
警告:执行ExecuteOnQuery时出错
我认为执行分隔符//的问题。

smdnsysy

smdnsysy1#

我已经解决了以下更改的问题-1)$mysqlcommand=new object mysql.data.mysqlclient.mysqlscript 2)$mysqlcommand.query=$sql 3)$mysqlcommand.execute(),最后它被执行并存储在我的数据库中

相关问题