Description:
I've just updated a project from version 6.3.6 to 6.4.3 and I've started to get the following exception when trying to execute a stored proceedure:
System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.String'
at MySql.Data.MySqlClient.StoredProcedure.GetAndFixParameter(String spName, DataRow param, Boolean realAsFloat, MySqlParameter returnParameter)
at MySql.Data.MySqlClient.StoredProcedure.CheckParameters(String spName)
at MySql.Data.MySqlClient.StoredProcedure.Resolve(Boolean preparing)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
at Adfero.Photos.Photos.CreatePhoto(String username, Photo photo, Stream fileData, Boolean skipSolr) in C:\Users\james.toyer\Documents\Development\PhotoLibrary\PhotoLibrary\Photos.cs:line 129
The stored function worked fine until the upgrade. I've included the C# and SQL code here
C#:
int createdId = photo.ID;
string access = photo.Access.ToString();
// Write to database.
using (MySqlConnection conn = GetMySqlConnection())
{
MySqlCommand cmd = new MySqlCommand("create_photo", conn) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("in_photo_id", updatedPhoto.ID);
cmd.Parameters.AddWithValue("in_access", updatedPhoto.Access.ToString());
cmd.Parameters.AddWithValue("in_collection", updatedPhoto.Collection);
cmd.Parameters.AddWithValue("in_title", updatedPhoto.Title);
cmd.Parameters.AddWithValue("in_tags", updatedPhoto.Tags);
cmd.Parameters.AddWithValue("in_description", updatedPhoto.Description);
cmd.Parameters.AddWithValue("in_usage_instructions", updatedPhoto.UsageInstructions);
cmd.Parameters.AddWithValue("in_width", updatedPhoto.Width);
cmd.Parameters.AddWithValue("in_height", updatedPhoto.Height);
cmd.Parameters.AddWithValue("in_source_type", updatedPhoto.SourceType);
cmd.Parameters.AddWithValue("in_source", updatedPhoto.Source);
cmd.Parameters.AddWithValue("in_source_url", updatedPhoto.SourceUrl);
cmd.Parameters.AddWithValue("in_copyright", updatedPhoto.Copyright);
cmd.Parameters.AddWithValue("in_focal_point_x", updatedPhoto.FocalPointX);
cmd.Parameters.AddWithValue("in_focal_point_y", updatedPhoto.FocalPointY);
cmd.Parameters.AddWithValue("in_codec_extensions", updatedPhoto.CodecExtensions);
cmd.Parameters.AddWithValue("in_codec_name", updatedPhoto.CodecName);
cmd.Parameters.AddWithValue("in_codec_mime_types", updatedPhoto.CodecMimeTypes);
cmd.Parameters.AddWithValue("in_created", updatedPhoto.Created);
cmd.Parameters.AddWithValue("in_modifier_username", username);
MySqlParameter newIdParam = cmd.Parameters.Add(new MySqlParameter("@new_id", MySqlDbType.UInt32) { Direction = System.Data.ParameterDirection.ReturnValue });
MySqlCommand selectCmd = new MySqlCommand("get_updated_photo", conn) { CommandType = CommandType.StoredProcedure };
selectCmd.Parameters.AddWithValue("in_photo_id", photo.ID);
conn.Open();
using (var trans = conn.BeginTransaction())
{
cmd.Transaction = trans;
selectCmd.Transaction = trans;
cmd.ExecuteNonQuery();
if (updatedPhoto.ID == 0)
{
if (newIdParam.Value != DBNull.Value)
createdId = Convert.ToInt32(newIdParam.Value);
updatedPhoto.ID = createdId;
}
selectCmd.Parameters["in_photo_id"].Value = updatedPhoto.ID;
using (MySqlDataReader r = selectCmd.ExecuteReader())
{
if (!r.HasRows || !r.Read())
{
LogManager.GetCurrentClassLogger().Warn("Failed getting created dates/user details from database after creating db meta data.");
}
updatedPhoto.Created = r.GetDateTime("created");
updatedPhoto.Modified = r.GetDateTime("modified");
updatedPhoto.CreatedUsername = r.GetString("created_username");
updatedPhoto.ModifiedUsername = r.GetString("modified_username");
}
trans.Commit();
}
}
SQL Function:
DELIMITER $$
CREATE DEFINER=`root`@`%` FUNCTION `create_photo`(
`in_photo_id` INT,
`in_access` ENUM('public','private'),
`in_collection` VARCHAR(45),
`in_title` VARCHAR(255),
`in_tags` TEXT,
`in_description` TEXT,
`in_usage_instructions` TEXT,
`in_width` SMALLINT UNSIGNED,
`in_height` SMALLINT UNSIGNED,
`in_source_type` VARCHAR(45),
`in_source` TEXT,
`in_source_url` VARCHAR(1023),
`in_copyright` TEXT,
`in_focal_point_x` SMALLINT UNSIGNED,
`in_focal_point_y` SMALLINT UNSIGNED,
`in_codec_extensions` VARCHAR(45),
`in_codec_name` VARCHAR(45),
`in_codec_mime_types` VARCHAR(255),
`in_created` DATETIME,
`in_modifier_username` VARCHAR(255)) RETURNS int(11)
MODIFIES SQL DATA
BEGIN
INSERT INTO `photo` (`photo_id`,`access`,`collection`,`title`,`tags`,`description`,`usage_instructions`,`width`,`height`,`source_type`,`source`,`source_url`,
`copyright`,`focal_point_x`,`focal_point_y`,`codec_extensions`,`codec_name`,`codec_mime_types`,`created`,`created_user_id`,`modified_user_id`)
VALUES
(`in_photo_id`,`in_access`,`in_collection`,`in_title`,`in_tags`,`in_description`,`in_usage_instructions`,`in_width`,`in_height`,`in_source_type`,
`in_source`,`in_source_url`,`in_copyright`,`in_focal_point_x`,`in_focal_point_y`,`in_codec_extensions`,`in_codec_name`,`in_codec_mime_types`, `in_created`,
(SELECT `user_id` FROM `user` WHERE `user`.`username` = `in_modifier_username`),
(SELECT `user_id` FROM `user` WHERE `user`.`username` = `in_modifier_username`));
RETURN LAST_INSERT_ID();
END
Permissions for executing user:
GRANT EXECUTE ON FUNCTION `photo_library`.`create_photo` TO 'photos_user'@'%';
How to repeat:
The code in the description should provide enough to recreate.