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?