# . .\MySQL_Functions.ps1
function Execute-MySQLNonQuery($conn, [string]$query) {
$command = $conn.CreateCommand() # Create command object
$command.CommandText = $query # Load query into object
$RowsInserted = $command.ExecuteNonQuery() # Execute command
$command.Dispose() # Dispose of command object
if ($RowsInserted) {
return $RowInserted
} else {
return $false
}
}
[void][system.reflection.Assembly]::LoadWithPartialName(“MySql.Data”)
$server=”FTPServer”
$dbusername=”DbUserName”
$dbpassword=’DbPassWord’
$dbname=”DbName”
$table=”DbTableName”
# Number of days back to import. Run the $filedate line at a command prompt and replace $count with a negative number to get your answer.
$count=-16
$today=[DateTime]::Today.ToString(“yyyy-MM-dd”)
While($count -ne “0”) {
$filedate=[DateTime]::Today.AddDays($count).ToString(“ddMMyyyy”)
$SourcePath = “C:\Test_SQL\”
# $csvfile=”$SourcePath$filedate.csv”
$csvfile=”SQL_$filedate.csv”
$connStr =”server=” + $server + “;Persist Security Info=false;user id=” + $dbusername + “;pwd=” + $dbpassword + “;database=” + $dbname + “;”
$conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr)
$conn.Open()
$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand
$cmd.Connection = $conn
# $cmd.CommandText = “truncate table ” + $dbname + $table
# $cmd.ExecuteNonQuery()
# $clear = “DELETE FROM $table WHERE TBL_Date LIKE ‘$today%'”
# Execute-MySQLNonQuery $conn $clear
$filedate=[DateTime]::Today.AddDays($count).ToString(“yyyy-MM-dd”)
foreach ($i in Import-Csv $csvfile) {
$i=
# $query = “INSERT INTO $table (TBL_Date,Name,Status) VALUES (‘”+$filedate+”‘,'”+$i.Name+”‘,'”+$i.Status+”‘)”
$query = “INSERT INTO $table (TBL_Date,HostName,JobName,JobCreated,LastRunTime,RetentionPeriod,NextRunTime,IsScheduled,LastResult,Status) VALUES (‘”+$filedate+”‘,'”+$i.HostName+”‘,'”+$i.JobName+”‘,'”+$i.JobCreated+”‘,'”+$i.LastRunTime+”‘,'”+$i.Retention+”‘,'”+$i.NextRunTime+”‘,'”+$i.IsScheduled+”‘,'”+$i.LastResult+”‘,'”+$i.Status+”‘)”
$Rows = Execute-MySQLNonQuery $conn $query
Write-Host $Rows ” Inserted Into Database”
}
$count++
}
$conn.Close()