mysql - 如何使用 PowerShell 2.0 在循环中运行带参数的不同 MySql 语句？

Question

我是 Powershell 的新手，想在 shell 脚本中执行以下操作:

1.) 从具有文件名的文件中读取一些文件名2.) 为每个读取的文件执行一些 SQL 语句

这是我的代码:

$server= "localhost"
$username= "root"
$password= "pass1234"
$database= "RD"
[void][system.reflection.Assembly]::LoadFrom("C:\Program Files (x86)\MySQL
 \MySQLConnector Net 6.8.3\Assemblies\v2.0\MySql.Data.dll")
$myconnection = New-Object MySql.Data.MySqlClient.MySqlConnection
$myconnection.ConnectionString = "server=$server;user id=$username;password=$password;
 database=$database;pooling=false;Allow Zero Datetime=True;"
$myconnection.Open()

#Params for MySqls:
$OUTPath="c:/RD/BAN-OUT/tmp/distinct_"
$OUTPath1="c:/RD/BAN-OUT/tmp/load_"
$INPath="c:/RD/BAN-OUT/"

#File-list:
$RDS= get-content c:\RD\BV-OUT\test.txt

foreach ($RD in $RDS)
{
 echo $RD
 $bv_out_distinct="$OUTPath$RD"
 $bv_out_load_new_bvs="$OUTPath1$RD"
 $RDDATEI="$INPath$RD"

 $sql = New-Object MySql.Data.MySqlClient.MySqlCommand
 $sql.Connection = $myconnection
 $sql.CommandText = "LOAD DATA LOCAL INFILE '$RDDATEI' INTO TABLE bv_tmp_all FIELDS TERMINATED BY ';'"
 $sql.ExecuteReader()

 $sql = New-Object MySql.Data.MySqlClient.MySqlCommand
 $sql.Connection = $myconnection
 $sql.CommandText = "SELECT * FROM bv_tmp_all GROUP BY BAN INTO OUTFILE 
                    '$bv_out_distinct' FIELDS TERMINATED BY ';'"
 $sql.ExecuteReader()

 $sql = New-Object MySql.Data.MySqlClient.MySqlCommand  
 $sql.Connection = $myconnection
 $sql.CommandText = "LOAD DATA LOCAL INFILE '$bv_out_distinct' INTO TABLE  
  bv_tmp_distinct FIELDS TERMINATED BY ';'"
 $sql.ExecuteReader()

 $sql = New-Object MySql.Data.MySqlClient.MySqlCommand
 $sql.Connection = $myconnection
 $sql.CommandText = "UPDATE bv, bv_tmp_distinct SET 
 bv.RD=bv_tmp_distinct.RD,bv.LEV21=bv_tmp_distinct.LEV21 where 
                bv.BAN=bv_tmp_distinct.BAN"
 $sql.ExecuteReader()

 $sql = New-Object MySql.Data.MySqlClient.MySqlCommand
 $sql.Connection = $myconnection
 $sql.CommandText = "SELECT * FROM bv_tmp_distinct WHERE BAN NOT IN (SELECT BAN FROM bv) INTO OUTFILE '$bv_out_load_new_bvs' FIELDS TERMINATED BY ';'"
 $sql.ExecuteReader()

 $sql = New-Object MySql.Data.MySqlClient.MySqlCommand
 $sql.Connection = $myconnection
 $sql.CommandText = "LOAD DATA LOCAL INFILE '$bv_out_load_new_bvs' INTO TABLE bv FIELDS TERMINATED BY ';'"
 $sql.ExecuteReader()

 $sql = New-Object MySql.Data.MySqlClient.MySqlCommand
 $sql.Connection = $myconnection
 $sql.CommandText = "TRUNCATE TABLE bv_tmp_distinct"
 $sql.ExecuteReader()

 $sql = New-Object MySql.Data.MySqlClient.MySqlCommand
 $sql.Connection = $myconnection
 $sql.CommandText = "TRUNCATE TABLE bv_tmp_all"
 $sql.ExecuteReader()
}
$myconnection.Close()

这会引发以下异常:

 "Connection must be valid and open." at C:\Users\Admin\Documents\ps_rd_update.ps1:69 Zeichen:19
+ $sql.ExecuteReader <<<< ()
+ CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException

我尝试了不同的方法，但找不到解决方案。感谢您的帮助...如何使包含参数(引号)的 MySql 语句起作用？

也许我应该提到我来自 Linux 并使用 Bash-shell。这是我原来的 bash shell 脚本，可以正常工作，但遗憾的是不能在 Windows 上运行 :( ...:

#!/bin/bash
OUTPath="/home/bono/RD/BV-OUT/tmp/distinct_"
OUTPath1="/home/bono/RD/BV-OUT/tmp/load_"
INPath="/home/bono/RD/BV-OUT/"
RD=" "

while read line; do
    RD="$line"
    bv_out_distinct="$OUTPath$RD"
    bv_out_load_new_bvs="$OUTPath1$RD"
    RDDATEI="$INPath$RD"
    echo $RDDATEI
    if ! test -f "$RDDATEI"; then
       echo error: file does not exist: $RDDATEI
       continue
    fi
###MYSQLs:
mysql -u root -D RD --local-infile << EOF
LOAD DATA LOCAL INFILE '$RDDATEI' INTO TABLE bv_tmp_all FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n';
SELECT * FROM bv_tmp_all GROUP BY BAN INTO OUTFILE '$bv_out_distinct' FIELDS TERMINATED BY ';';
LOAD DATA LOCAL INFILE '$bv_out_distinct' INTO TABLE bv_tmp_distinct FIELDS TERMINATED BY ';';
UPDATE bv, bv_tmp_distinct SET bv.RD=bv_tmp_distinct.RD,bv.LEV21=bv_tmp_distinct.LEV21 where bv.BAN=bv_tmp_distinct.BAN;
SELECT * FROM bv_tmp_distinct WHERE BAN NOT IN (SELECT BAN FROM bv) INTO OUTFILE  '$bv_out_load_new_bvs' FIELDS TERMINATED BY ';';
LOAD DATA LOCAL INFILE '$bv_out_load_new_bvs' INTO TABLE bv FIELDS TERMINATED BY ';';
TRUNCATE TABLE bv_tmp_distinct;
TRUNCATE TABLE bv_tmp_all;
EOF
##### EOSQL

done < /home/bono/RD/BAN-OUT/test

Answer 1

您的连接字符串看起来有点乱，请尝试早点定义您的连接字符串以使事情变得更整洁，然后尝试 Robert 的建议。

  $connectionstring = "server=$server;user id=$username;password=$password"

您也可以尝试从循环内部启动连接

foreach ($item in $list){
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = $connectionstring
    $SqlConnection.Open()
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = "INSERT INTO BLAH VALUES ('$PARAM','$PARAM2')"
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.ExecuteNonQuery()
    $SqlConnection.Close()
}

我已经使用上面的方法从我们车队中的每台 PC 插入 wmi 信息。它可以很容易地适应您的需要。如果它不起作用，请您发布您的错误。

如果您希望运行多个查询，请执行命令并且不要关闭连接。然后，您将创建一个新的 System.Data.SqlClient.SqlCommand 对象，重复该过程，即。

foreach ($item in $list){
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = $connectionstring
    $SqlConnection.Open()

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = "INSERT INTO BLAH 2 VALUES ('$PARAM','$PARAM2')"
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.ExecuteNonQuery()

    $SqlCmd1 = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd1.CommandText = "INSERT INTO STILLBLAH VALUES ('$PARAM','$PARAM2')"
    $SqlCmd1.Connection = $SqlConnection
    $SqlCmd1.ExecuteNonQuery()

    $SqlCmd2 = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd2.CommandText = "INSERT INTO BLAH VALUES ('$PARAM','$PARAM2')"
    $SqlCmd2.Connection = $SqlConnection
    $SqlCmd2.ExecuteNonQuery()

    $SqlConnection.Close()
}