Which command(s) will populate a database with an existing .sql file in Powershell

Question

Im writing a script in powershell which is supposed to connect to a MySQL server, create a new database, and then run query commands against that database. I have a premade .sql file with all of the commands to be executed, however there seems to be a problem with my code where it never actually executes the .sql file. Any help would be appreciated. My code is below.

[void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")

#Creates a connection to the server
$connStr ="server=mySql;Persist Security Info=false;user id=" + "username" + ";pwd=" + "pass" + ";"
$conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr)

#Open connection
$conn.Open()

#Drops database if it currently exists
$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand
$cmd.Connection  = $conn
$cmd.CommandText = "DROP DATABASE IF EXISTS " + $dbname
$cmd.ExecuteNonQuery()

#Create the new database
$cmd.CommandText = 'CREATE SCHEMA `' + $dbname + '`'
$cmd.ExecuteNonQuery()

#Load the sql file
$sql = (Get-Content C:\"path to sql file") -join "'r'n"
$cmd.CommandText = $sql

#Close connection
$conn.Close()

Shay Levy · Accepted Answer · 2012-05-16 20:39:59Z

1

Aren't you missing a $cmd.ExecuteNonQuery() call ? And it looks like you're joining the file lines with the literal "'r'n". You need to use the back tick character instead (e.g "`r`n"). I would use the Out-String cmdlet to convert the file content to one string:

#Load the sql file
$sql = Get-Content C:\"path to sql file" | Out-String
$cmd.CommandText = $sql
$cmd.ExecuteNonQuery()

edited May 16, 2012 at 20:39

answered May 16, 2012 at 20:34

Shay Levy

128k34 gold badges193 silver badges210 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

G3TH Over a year ago

Thank you for your very prompt response. I was under the assumption that I did not need the ExecuteNonQuery call since I am in fact running query's. If I place that call I get the error of no database selected. Sorry I am very new to this so I may be making some fundamental flaws. Also, my sql file contains ~20+ query's is that a problem when converting to one string?

Shay Levy Over a year ago

My answer is based on your code and what seems to me to be missing or wrong. Does any of the previous commands worked (dropping or creating the db)? Is the file content (queries) needs to be run one line at a time?

G3TH Over a year ago

Yes the code that I have above will create and drop the database as expected. The sql file itself contains the commands to Create tables for my database. An example would be: CREATE TABLE IF NOT EXISTS billable_items_checkoffs ( ID int(11) NOT NULL auto_increment, FL int(11) default NULL, UC varchar(50) default NULL etc.

G3TH Over a year ago

Thank you Shay for your help debugging my code. As it turns out both -join "rn" and Out-String work. The problem I had actually came from my sql script. It contained comments which caused the file to not be read in proper.

Collectives™ on Stack Overflow

Which command(s) will populate a database with an existing .sql file in Powershell

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related