使用Powershell针对MySQL运行MySql存储过程脚本 [英] Run a MySql stored procedure Script Against MySQL using Powershell
问题描述
我想在QA环境的MySQL 5.6服务器上执行MySQL存储过程.这是通过Azure固定线实施持续交付的一部分.
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.
例如:我的.sql
文件具有多个存储过程,这些存储过程已由MySQL Workbench成功执行,但是我想通过PowerShell执行的同一文件.
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内容:
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引发以下错误:
PowerShell throws the following error:
警告:ExecuteNonQuery发生错误
WARNING: ERROR occurred while ExecuteNonQuery
我认为执行DELIMITER//时遇到问题.
I think the problem in executing DELIMITER //.
推荐答案
我已经解决了以下更改的问题-1)$ oMYSQLCommand =新对象MySql.Data.MySqlClient.MySqlScript 2)$ oMYSQLCommand.Query = $ sql 3)$ oMYSQLCommand.Execute(),最后它已被执行并将proc存储在我的数据库中. –
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. –
这篇关于使用Powershell针对MySQL运行MySql存储过程脚本的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!