How to run different MySql-statements with parameters in a loop using PowerShell 2.0?

Question

I am new to Powershell and would like to do the following in a shell script:

1.) read some filenames out of an file with filenames 2.) execute some SQL-statements for each file read

Here's my code:

$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()

This throws following exceptions:

 "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

I have tried different ways and cannot find a solution. I appreciate any help... How can I make the MySql-statements including the parameters (quotes) work?

Maybe I should mention I come from Linux and used Bash-shell. This is my original bash shell script that works fine but pity not at 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

You're creating loads of MySqlDataReader objects which you never dispose. Perhaps those are the issue? Try using the ExecuteNonQuery method instead, since you don't care about the results. — Robert Westerlund
– Robert Westerlund, Commented Mar 14, 2014 at 12:45

mitchimus · Accepted Answer · 2014-04-28 05:43:59Z

Your connection string looks a little messy, try defining your connection string earlier to make things a bit neater, then try what Robert suggested.

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

You could also try initiating the connection from inside your loop

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()
}

I have used the above to insert wmi information from each PC in our fleet. It could be easily adapted to your needs. If it doesn't work, can you please post your error.

If you want multiple queries to be run, execute the command and just don't close the connection. You'd then create a new System.Data.SqlClient.SqlCommand object repeat the process, ie.

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()
}

Collectives™ on Stack Overflow

How to run different MySql-statements with parameters in a loop using PowerShell 2.0?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related