MySQL Powershell "ExecuteNonQuery" Exception Error

by DARKOCEAN   Last Updated January 12, 2018 23:06 PM

I have a MySQL database with over 100 SQL files, which are used to build or update the database. These files create the tables, create functions and stored procedures etc.

Most of the files work fine, using the following syntax in a Powershell script, which simply loops over each file one at a time and executes the query against the database:

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

    $databaseConnectionString = 

        "server=" + $databaseHost + ";" +
        "uid=" + $databaseUsername + ";" +
        "pwd=" + $databasePassword + ";" +
        "database=" + $databaseTarget + ";" +
        "Pooling=FALSE"

    $databaseConnection = New-Object MySql.Data.MySqlClient.MySqlConnection($databaseConnectionString) 
    $databaseConnection.Open() 
    $databaseCommand = $databaseConnection.CreateCommand()
    $databaseCommand.CommandText = $querySyntax
    $databaseCommand.ExecuteNonQuery()
    $databaseCommand.Dispose()

On the majority of the SQL files, they are successful, apart from the files that create a stored procedure that either has a INSERT or UPDATE command within it. For info, creating stored procedures that simply query the data, works fine.

The full error I get is:

Error: Exception calling "ExecuteNonQuery" with "0" argument(s): "Fatal error encountered during command execution."

Here is a sample of one of the procedures that fails to create:

CREATE PROCEDURE Reference_RouteStatus_Insert (

    IN paramRouteStatus VARCHAR(120),

    OUT paramInsertStatus VARCHAR(100),
    OUT paramRouteStatusID INT(11)

)
BEGIN

    DECLARE varRouteStatusID INT(11);

    SET varRouteStatusID = (

        SELECT
            RouteStatusID

        FROM
            Reference_RouteStatuses

        WHERE
            RouteStatusValue = paramRouteStatus

    );

    IF varRouteStatusID IS NULL THEN

        INSERT INTO Reference_RouteStatuses (

            RouteStatusValue

        )

        VALUES (

            paramRouteStatus

        );

        SET paramInsertStatus = "Route Status record inserted";

        SET paramRouteStatusID = LAST_INSERT_ID();

    ELSE

        SET paramInsertStatus = "Route Status record returned";

        SET paramRouteStatusID = varRouteStatusID;

    END IF;

END;

I've also made the same script using Python for the Mac platform, which works without errors.

Any reason why some of these files fail to work when using Powershell?



Related Questions


MySQL - Service Name in Windows

Updated January 15, 2018 23:06 PM

'mysql' Recognized by cmd but not PowerShell?

Updated August 10, 2017 05:06 AM

SA login history in sql

Updated May 04, 2015 21:02 PM