如何通过powershell使用SQL分隔符

Im trying to read in an sql file to be executed by powershell. Everything works fine except that powershell throws an error due to the word delimiter. So my question is how would I go about using a delimiter in my sql file (I can only make minor changes to the sql file, its code must remain relatively unchanged) but loading it correctly through powershell.

我试图读取一个由PowerShell执行的sql文件。一切正常,但powershell因单词分隔符引发错误。所以我的问题是如何在我的sql文件中使用分隔符(我只能对sql文件进行微小的更改,它的代码必须保持相对不变),但是通过powershell正确加载它。

Heres my code from both files and the error I get. Thank you in advance.

继承我的代码来自两个文件和我得到的错误。先感谢您。

Powershell:

$sql = (Get-Content $pwd\NewClient.sql) | Out-String
$cmd.CommandText = "USE $dbname"
$cmd.ExecuteNonQuery()
$cmd.CommandText = $sql
$cmd.ExecuteNonQuery()

SQL:

delimiter //
......
end;//
delimiter ;

Exception calling "ExecuteNonQuery" with "0" argument(s): "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Delimiter // create trigger stop_time_insert after insert on stops' at line 1"

使用“0”参数调用“ExecuteNonQuery”的异常:“您的SQL语法中有错误;请检查与您的MySQL服务器版本对应的手册,以便在插入后使用正确的语法//创建触发器stop_time_insert停在'第1行'

2 个解决方案

#1

Powershell doesn't support the Delimiter keyword, but it's not actually necessary in this case.

Powershell不支持Delimiter关键字,但在这种情况下实际上并不是必需的。

Your

create trigger stop_time_insert after insert on stops 
begin
   //do stuff
end;

block will work without the delimiter, since Powershell treats the entire command as a single item, instead of how the MySQL command line interface treats it as a multiline set.

block将在没有分隔符的情况下工作,因为Powershell将整个命令视为单个项目,而不是MySQL命令行界面如何将其视为多行集合。

So just remove the Delimiter statements and you should be fine. (Tested against local machine here.)

所以只需删除Delimiter语句就可以了。 (在这里测试了本地机器。)

#2

I realize this is a very old thread, but I think it's the only one covering this topic. The answered solution works, but you cannot use the same script when running from a MySQL command prompt. I created a PS function as follows:

我意识到这是一个非常古老的主题,但我认为这是唯一涵盖这一主题的内容。已解决的解决方案有效,但从MySQL命令提示符运行时无法使用相同的脚本。我创建了一个PS函数如下:

function executeScript([MySql.Data.MySqlClient.MySqlConnection]$conn, [String]$filePath)
{
    $fileName = Split-Path $filePath -Leaf
    Write-Host "Executing $fileName script"
    $sqlText = Get-Content $filePath | Out-String
    $sqlText = $sqlText.Replace("DELIMITER","-- DELIMITER")
    $sqlText = $sqlText.Replace('$$',";")
    return Invoke-MySql -sql $sqlText -connection $conn | Out-Null
}

Assume a SQL script as follows:

假设一个SQL脚本如下:

DELIMITER $$
....
END $$
DELIMITER ;

Invoke-MySql is from Mike Shepard's MySQLLib package.

Invoke-MySql来自Mike Shepard的MySQLLib包。

Now the same script file can be executed from both environments.

现在可以从两个环境执行相同的脚本文件。

#1