MySQL USE command not working from PowerShell

Question

Here is a snippet from a PowerShell script:

$sql.CommandText = "CREATE DATABASE IF NOT EXISTS ``$dbName``;"
$rowsInserted = $sql.ExecuteNonQuery()
$sql.CommandText = "USE ``$dbName``;"
$rowsInserted = $sql.ExecuteNonQuery()

The CREATE DATABASE command works fine. The USE command does not. I get no error message from the Powershell ISE. $rowsInserted is 1 when I execute the CREATE DATABASE command and 0 when I execute the USE command.

I am running the ISE as an administrator. I have created the database outside of PowerShell and executed the USE command first. Still doesn't work. The database name is as simple as "test".

All the commands work from MySQL Workbench and from a command window.

Any help would be much appreciated.

• Richard: What version of PowerShell? What MySQL driver/package? Also, how are you creating/setting up $sql?
• Shadow: 1) how do you know that use stayement is not working? 2) prefix your table and column references with the database name and you don't even need use use (pun intended)
• user810702: 1) $rowsInserted return value is 0 - This may not be determinative? 2) PS Desktop 5.1, Mysql 9.3.
• Shadow: I don't think a return value of 0 would indicate error for a use statement as it does not create or delete any rows or data structures.. Try changing to a non-existing database and see what happens. Also, try to issue sql statements without qualifying the database and see what happens.
• Shadow: Also, use statement is moat beneficial for human users, not for scripts. In a script I would qualify all table names with the database name

Answer 1

When you use a database connection via ADO.NET or the MySQL Connector, each SQL command executes in its own session context unless you explicitly keep the same open connection.

However, even with the same connection, the USE statement is ignored by the connector because the connector does not persist a change in default database context through USE. Instead, the "default database" must be specified in the connection string.

When you do this in PowerShell:

$sql.CommandText = "CREATE DATABASE IF NOT EXISTS `$dbName`;"
$rowsInserted = $sql.ExecuteNonQuery()

This works because CREATE DATABASE is a DDL command (Data Definition Language). It returns 1 row affected, meaning successfully executed.

But :

$sql.CommandText = "USE `$dbName`;"
$rowsInserted = $sql.ExecuteNonQuery()

This doesn’t "fail" — but it also doesn’t do what you expect. $rowsInserted is 0 because USE doesn’t affect any rows. More importantly: USE has no effect on the connection object.

As a solution Specify the database name in your connection string when you need to work in a specific database.

$connectionString = "server=localhost;uid=root;pwd=yourPassword;database=$dbName"
$sql = New-Object MySql.Data.MySqlClient.MySqlCommand
$sql.Connection = New-Object MySql.Data.MySqlClient.MySqlConnection($connectionString)
$sql.Connection.Open()

Now you can execute commands directly within that database context, without needing USE.

Answer 2

My misunderstanding. The return value of ExecuteNonQuery appears to be the number of rows inserted. The USE command does not insert any rows, so the return value is zero.

• StackLyft: The USE command returning 0 is expected behavior - it doesn't modify rows, just changes the database context. Verify It Worked powershell$sql.CommandText = "SELECT DATABASE();" $reader = $sql.ExecuteReader() if ($reader.Read()) { Write-Host "Current database: $($reader.GetString(0))" } $reader.Close() Better Alternative Specify the database in your connection string: powershell$connectionString = "Server=$server;Database=$dbName;Uid=$user;Pwd=$password;"