I want to insert a PNG image into an OracleDatabase using OleDb and a C# application. The table looks like this:
CREATE TABLE Plant
(
Id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
Name VARCHAR2(50) NOT NULL,
Image BLOB null,
CONSTRAINT plant_pk PRIMARY KEY (Id)
);
Below is the code:
public static void AddBinaryParameterToCommand(OleDbCommand cmd, string parameterColumn, object parameter)
{
if (cmd != null)
{
if (parameter != null && !parameter.ToString().Equals(""))
{
OleDbParameter blobParameter = new OleDbParameter();
blobParameter.OleDbType = OleDbType.LongVarBinary;
blobParameter.Direction = ParameterDirection.InputOutput;
blobParameter.ParameterName = parameterColumn;
blobParameter.Value = parameter;
cmd.Parameters.Add(blobParameter);
}
else
cmd.Parameters.Add(new OleDbParameter(parameterColumn, DBNull.Value));
}
}
public int InsertPlant(string name, byte[] image)
{
int id = 0;
using (var connection = new OleDbConnection(ConnectionString))
{
var commandGetIdText = @"SELECT MAX(id) FROM PLANT";
connection.Open();
using (var command = new OleDbCommand(commandGetIdText, connection))
{
using (var reader = command.ExecuteReader())
{
reader.Read();
id = int.Parse(reader[0].ToString()) + 1;
}
}
var commandText = string.Format("INSERT INTO PLANT(ID,NAME,IMAGE) VALUES (?, ?, ?)");
using (var command = new OleDbCommand(commandText, connection))
{
Utils.AddParameterToCommand(command, "ID", id);
Utils.AddParameterToCommand(command, "NAME", name);
Utils.AddBinaryParameterToCommand(command, "IMAGE", image);
command.ExecuteNonQuery();
connection.Close();
}
}
return id;
}
private void button_UploadMap_Click(object sender, EventArgs e)
{
var openFileDialog = new OpenFileDialog
{
Multiselect = true
};
var path = string.Empty;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
path = openFileDialog.FileName;
}
else
{
return;
}
byte[] imageArray = File.ReadAllBytes(path);
var palletMapDL = new PalletMapDL("Data Source=192.168.1.21/orcl;Persist Security Info=True; Password=test;User ID=test; Provider=MSDAORA; OLEDB.NET=True; PLSQLRSet=1");
palletMapDL.InsertPlant("Test Plant 01", imageArray);
When execute command.ExecuteNonQuery(); I got this error message:
System.InvalidOperationException: 'Command parameter[2] '' data value could not be converted for reasons other than sign mismatch or data overflow.
OleDbException: 'MSDAORA' failed with no error message available, result code: DB_E_ERRORSOCCURRED(0x80040E21).
Do you know what could be the issue?
Thanks
User contributions licensed under CC BY-SA 3.0