Can not run Insert query against mySQL database using Powershell, but can via the PHPMyAdmin UI

Question

Im trying to run the following query against my mySQL database :

"INSERT INTO present (name) VALUES ('fred');"

I can run this query in the UI in PHPMyAdmin and it created the expected data, however, I can not run it from my PowerShell script. I can get data from the database using my PowerShell script, but can't seem to be able to create any. Any ideas ?

EDIT

Code I use to connect to database :

function ConnectToDatabase([string]$user, [string]$pass, [string]$MySQLHost, [string]$database) {

    Log "--------"
    Log "Connecting to database" 
    # Load MySQL .NET Connector Objects 
    [void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data") 

    # Open Connection 
    $connStr = "server=" + $MySQLHost + ";port=3306;uid=" + $user + ";pwd=" + $pass + ";database="+$database+";Pooling=FALSE" 
    try {
        $conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr) 
        $conn.Open()
    } catch [System.Management.Automation.PSArgumentException] {
        Log "Unable to connect to MySQL server, do you have the MySQL connector installed..?"
        Log $_
        #Exit
    } catch {
        Log "Unable to connect to MySQL server..."
        Log $_.Exception.GetType().FullName
        Log $_.Exception.Message
        #exit
    }
    Log "Connected to MySQL database : $MySQLHost\$database"
    Log "--------"

    return $conn 
}

Running the query

$conn = ConnectToDatabase $user $pass $MySQLHost $database
$query = "INSERT INTO present (name) VALUES ('fred');"
$Command = New-Object MySql.Data.MySqlClient.MySqlCommand $query, $conn

I also had this to GET data from the database but I thought it was redundant to the Insert of data

$dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
$dataSet = New-Object System.Data.DataSet
$recordCount = $dataAdapter.Fill($dataSet, "data")
return $dataSet.Tables[0]

Then please post your Powershell script ;) we can't help you without the code. — René Höhle
– René Höhle, Commented Mar 12, 2018 at 11:39
@Stony amended question. The credentials are fine, this queries work for getting data, but don't seem to be working for inserting — thatOneGuy
– thatOneGuy, Commented Mar 12, 2018 at 11:41
Any error messages? Are you using the same credentials in PhpMyAdmin and PowerShell? — gvee
– gvee, Commented Mar 12, 2018 at 11:43
no error messages in the console. I'm not sure how to catch the error messages ? When I write a query I know will not work, I don't get an errors, perhaps I need to catch them somehow ? And yes, same credentials in both — thatOneGuy
– thatOneGuy, Commented Mar 12, 2018 at 11:45
Can I mention, this was working at one point. And I can really remember the exact time it wasnt. But I remember resetting the auto increment to 1 and I think it was around a similar time it stopped working — thatOneGuy
– thatOneGuy, Commented Mar 12, 2018 at 11:47

Ansgar Wiechers · Accepted Answer · 2018-03-12 12:03:23Z

1

The code you posted creates a MySqlCommand object, but doesn't actually execute the SQL statement. Use the ExecuteNonQuery() method for that:

$conn = ConnectToDatabase $user $pass $MySQLHost $database
$query = "INSERT INTO present (name) VALUES ('fred');"
$Command = New-Object MySql.Data.MySqlClient.MySqlCommand $query, $conn
$Command.ExecuteNonQuery()

answered Mar 12, 2018 at 12:03

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

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

Collectives™ on Stack Overflow

Can not run Insert query against mySQL database using Powershell, but can via the PHPMyAdmin UI

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