我是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
发布于 2014-04-28 13:30:55
你的连接字符串看起来有点凌乱,试着早点定义你的连接字符串,让事情看起来更整洁一点,然后试试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()
}
https://stackoverflow.com/questions/22404492
复制相似问题