Object must implement IConvertible, writing to BLOB

  • Thread starter Thread starter Jonaze
  • Start date Start date
J

Jonaze

I have a function with the following code, I have written this function
with the example provided in the documentation provided with
OracleClient.

The called stored procedure functions perfectly, I have tested it
manually with success, but when I try to use the function, I get the
error "Object must implement IConvertible".

I also tested with the code in comment, without success and the same
result. If anyone has an idea...

int fileLength = Convert.ToInt32(_BinaryFile.Length);
byte[] fileBinary = new byte[fileLength];

int status = _BinaryFile.Read(fileBinary, 0, fileLength);

_BinaryFile.Close();

OracleConnection oc = new
OracleConnection(ConfigurationSettings.AppSettings["ConnectionString"]);

oc.Open();
OracleTransaction tx = oc.BeginTransaction();
OracleCommand ocm = oc.CreateCommand ();
ocm.Transaction = tx;
/*

ocm.CommandText = "declare xx blob; begin dbms_lob.createtemporary(xx,
false, 0); :tempblob := xx; end;";
ocm.Parameters.Add(new OracleParameter("tempblob",
OracleType.Blob)).Direction = ParameterDirection.Output;
ocm.ExecuteNonQuery();

OracleLob ol = (OracleLob)ocm.Parameters[0].Value;

ol.BeginBatch(OracleLobOpenMode.ReadWrite);
ol.Write(fileBinary, 0, fileLength);
ol.EndBatch();
*/
ocm.Parameters.Clear();

ocm.CommandText = PUT_FILE; //Constant
ocm.CommandType = CommandType.StoredProcedure;

ocm.Parameters.Add( new OracleParameter("PROCESS_ID",
OracleType.VarChar, 36)).Value = _ProcessID;
ocm.Parameters.Add( new OracleParameter("NSB", OracleType.VarChar,
15)).Value = _NSB;
ocm.Parameters.Add( new OracleParameter("GENERATION_DATE",
OracleType.DateTime)).Value = _Date;
if (_AllPublication)
ocm.Parameters.Add( new OracleParameter("ALLPUBLICATION",
OracleType.Number)).Value = 1;
else
ocm.Parameters.Add( new OracleParameter("ALLPUBLICATION",
OracleType.Number)).Value = 0;
ocm.Parameters.Add( new OracleParameter("FILTERED",
OracleType.Number)).Value = _Filtered;
ocm.Parameters.Add( new OracleParameter("DATE_FROM",
OracleType.DateTime)).Value = _DateFrom;
ocm.Parameters.Add( new OracleParameter("DATE_TO",
OracleType.DateTime)).Value = _DateTo;
ocm.Parameters.Add( new OracleParameter("FILE_FORMAT",
OracleType.VarChar, 3)).Value = _Format;
ocm.Parameters.Add( new OracleParameter("PATH", OracleType.VarChar,
500)).Value = _Path;
ocm.Parameters.Add( new OracleParameter("FILE_NAME",
OracleType.VarChar, 500)).Value = _FileName;
ocm.Parameters.Add( new OracleParameter("FILE_BINARY",
OracleType.Blob)).Value = fileBinary;
//ocm.Parameters.Add( new OracleParameter("FILE_BINARY",
OracleType.Blob)).Value = ol;
ocm.Parameters.Add( new OracleParameter("p_err",
OracleType.Number)).Direction = ParameterDirection.Output;

ocm.ExecuteNonQuery();

tx.Commit();

oc.Close();
 
The data type specified on the OracleParameter is incorrect.

--

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------
---------------

Jonaze said:
I have a function with the following code, I have written this function
with the example provided in the documentation provided with
OracleClient.

The called stored procedure functions perfectly, I have tested it
manually with success, but when I try to use the function, I get the
error "Object must implement IConvertible".

I also tested with the code in comment, without success and the same
result. If anyone has an idea...

int fileLength = Convert.ToInt32(_BinaryFile.Length);
byte[] fileBinary = new byte[fileLength];

int status = _BinaryFile.Read(fileBinary, 0, fileLength);

_BinaryFile.Close();

OracleConnection oc = new
OracleConnection(ConfigurationSettings.AppSettings["ConnectionString"]);

oc.Open();
OracleTransaction tx = oc.BeginTransaction();
OracleCommand ocm = oc.CreateCommand ();
ocm.Transaction = tx;
/*

ocm.CommandText = "declare xx blob; begin dbms_lob.createtemporary(xx,
false, 0); :tempblob := xx; end;";
ocm.Parameters.Add(new OracleParameter("tempblob",
OracleType.Blob)).Direction = ParameterDirection.Output;
ocm.ExecuteNonQuery();

OracleLob ol = (OracleLob)ocm.Parameters[0].Value;

ol.BeginBatch(OracleLobOpenMode.ReadWrite);
ol.Write(fileBinary, 0, fileLength);
ol.EndBatch();
*/
ocm.Parameters.Clear();

ocm.CommandText = PUT_FILE; //Constant
ocm.CommandType = CommandType.StoredProcedure;

ocm.Parameters.Add( new OracleParameter("PROCESS_ID",
OracleType.VarChar, 36)).Value = _ProcessID;
ocm.Parameters.Add( new OracleParameter("NSB", OracleType.VarChar,
15)).Value = _NSB;
ocm.Parameters.Add( new OracleParameter("GENERATION_DATE",
OracleType.DateTime)).Value = _Date;
if (_AllPublication)
ocm.Parameters.Add( new OracleParameter("ALLPUBLICATION",
OracleType.Number)).Value = 1;
else
ocm.Parameters.Add( new OracleParameter("ALLPUBLICATION",
OracleType.Number)).Value = 0;
ocm.Parameters.Add( new OracleParameter("FILTERED",
OracleType.Number)).Value = _Filtered;
ocm.Parameters.Add( new OracleParameter("DATE_FROM",
OracleType.DateTime)).Value = _DateFrom;
ocm.Parameters.Add( new OracleParameter("DATE_TO",
OracleType.DateTime)).Value = _DateTo;
ocm.Parameters.Add( new OracleParameter("FILE_FORMAT",
OracleType.VarChar, 3)).Value = _Format;
ocm.Parameters.Add( new OracleParameter("PATH", OracleType.VarChar,
500)).Value = _Path;
ocm.Parameters.Add( new OracleParameter("FILE_NAME",
OracleType.VarChar, 500)).Value = _FileName;
ocm.Parameters.Add( new OracleParameter("FILE_BINARY",
OracleType.Blob)).Value = fileBinary;
//ocm.Parameters.Add( new OracleParameter("FILE_BINARY",
OracleType.Blob)).Value = ol;
ocm.Parameters.Add( new OracleParameter("p_err",
OracleType.Number)).Direction = ParameterDirection.Output;

ocm.ExecuteNonQuery();

tx.Commit();

oc.Close();
Click to expand...
 
Sorry if I am stupid...

And which datatype I have to use ?
Into the Oracle procedure the column type is Blob and if you see the
example provided into MSDN and the Oracle documentation, they also use
blob.
Could you be more precise in your answer?

Thank you
Joël


Sahil Malik [MVP] a écrit :
The data type specified on the OracleParameter is incorrect.

--

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------
---------------

Jonaze said:
I have a function with the following code, I have written this function
with the example provided in the documentation provided with
OracleClient.

The called stored procedure functions perfectly, I have tested it
manually with success, but when I try to use the function, I get the
error "Object must implement IConvertible".

I also tested with the code in comment, without success and the same
result. If anyone has an idea...

int fileLength = Convert.ToInt32(_BinaryFile.Length);
byte[] fileBinary = new byte[fileLength];

int status = _BinaryFile.Read(fileBinary, 0, fileLength);

_BinaryFile.Close();

OracleConnection oc = new
OracleConnection(ConfigurationSettings.AppSettings["ConnectionString"]);

oc.Open();
OracleTransaction tx = oc.BeginTransaction();
OracleCommand ocm = oc.CreateCommand ();
ocm.Transaction = tx;
/*

ocm.CommandText = "declare xx blob; begin dbms_lob.createtemporary(xx,
false, 0); :tempblob := xx; end;";
ocm.Parameters.Add(new OracleParameter("tempblob",
OracleType.Blob)).Direction = ParameterDirection.Output;
ocm.ExecuteNonQuery();

OracleLob ol = (OracleLob)ocm.Parameters[0].Value;

ol.BeginBatch(OracleLobOpenMode.ReadWrite);
ol.Write(fileBinary, 0, fileLength);
ol.EndBatch();
*/
ocm.Parameters.Clear();

ocm.CommandText = PUT_FILE; //Constant
ocm.CommandType = CommandType.StoredProcedure;

ocm.Parameters.Add( new OracleParameter("PROCESS_ID",
OracleType.VarChar, 36)).Value = _ProcessID;
ocm.Parameters.Add( new OracleParameter("NSB", OracleType.VarChar,
15)).Value = _NSB;
ocm.Parameters.Add( new OracleParameter("GENERATION_DATE",
OracleType.DateTime)).Value = _Date;
if (_AllPublication)
ocm.Parameters.Add( new OracleParameter("ALLPUBLICATION",
OracleType.Number)).Value = 1;
else
ocm.Parameters.Add( new OracleParameter("ALLPUBLICATION",
OracleType.Number)).Value = 0;
ocm.Parameters.Add( new OracleParameter("FILTERED",
OracleType.Number)).Value = _Filtered;
ocm.Parameters.Add( new OracleParameter("DATE_FROM",
OracleType.DateTime)).Value = _DateFrom;
ocm.Parameters.Add( new OracleParameter("DATE_TO",
OracleType.DateTime)).Value = _DateTo;
ocm.Parameters.Add( new OracleParameter("FILE_FORMAT",
OracleType.VarChar, 3)).Value = _Format;
ocm.Parameters.Add( new OracleParameter("PATH", OracleType.VarChar,
500)).Value = _Path;
ocm.Parameters.Add( new OracleParameter("FILE_NAME",
OracleType.VarChar, 500)).Value = _FileName;
ocm.Parameters.Add( new OracleParameter("FILE_BINARY",
OracleType.Blob)).Value = fileBinary;
//ocm.Parameters.Add( new OracleParameter("FILE_BINARY",
OracleType.Blob)).Value = ol;
ocm.Parameters.Add( new OracleParameter("p_err",
OracleType.Number)).Direction = ParameterDirection.Output;

ocm.ExecuteNonQuery();

tx.Commit();

oc.Close();
Click to expand...
Click to expand...
 
Well, it's one of the parameters whose datatype is incorrect. You will have
to match up the database column data type to the OracleParameter.DbType
specified one by one .. sorry can't really tell much without actually
looking at your tables and have a working app in front of me.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------
---------------



Sorry if I am stupid...

And which datatype I have to use ?
Into the Oracle procedure the column type is Blob and if you see the
example provided into MSDN and the Oracle documentation, they also use
blob.
Could you be more precise in your answer?

Thank you
Joël


Sahil Malik [MVP] a écrit :
The data type specified on the OracleParameter is incorrect.

--

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
-------------------------------------------------------------------------- --
---------------

Jonaze said:
I have a function with the following code, I have written this function
with the example provided in the documentation provided with
OracleClient.

The called stored procedure functions perfectly, I have tested it
manually with success, but when I try to use the function, I get the
error "Object must implement IConvertible".

I also tested with the code in comment, without success and the same
result. If anyone has an idea...

int fileLength = Convert.ToInt32(_BinaryFile.Length);
byte[] fileBinary = new byte[fileLength];

int status = _BinaryFile.Read(fileBinary, 0, fileLength);

_BinaryFile.Close();

OracleConnection oc = new
OracleConnection(ConfigurationSettings.AppSettings["ConnectionString"]);

oc.Open();
OracleTransaction tx = oc.BeginTransaction();
OracleCommand ocm = oc.CreateCommand ();
ocm.Transaction = tx;
/*

ocm.CommandText = "declare xx blob; begin dbms_lob.createtemporary(xx,
false, 0); :tempblob := xx; end;";
ocm.Parameters.Add(new OracleParameter("tempblob",
OracleType.Blob)).Direction = ParameterDirection.Output;
ocm.ExecuteNonQuery();

OracleLob ol = (OracleLob)ocm.Parameters[0].Value;

ol.BeginBatch(OracleLobOpenMode.ReadWrite);
ol.Write(fileBinary, 0, fileLength);
ol.EndBatch();
*/
ocm.Parameters.Clear();

ocm.CommandText = PUT_FILE; //Constant
ocm.CommandType = CommandType.StoredProcedure;

ocm.Parameters.Add( new OracleParameter("PROCESS_ID",
OracleType.VarChar, 36)).Value = _ProcessID;
ocm.Parameters.Add( new OracleParameter("NSB", OracleType.VarChar,
15)).Value = _NSB;
ocm.Parameters.Add( new OracleParameter("GENERATION_DATE",
OracleType.DateTime)).Value = _Date;
if (_AllPublication)
ocm.Parameters.Add( new OracleParameter("ALLPUBLICATION",
OracleType.Number)).Value = 1;
else
ocm.Parameters.Add( new OracleParameter("ALLPUBLICATION",
OracleType.Number)).Value = 0;
ocm.Parameters.Add( new OracleParameter("FILTERED",
OracleType.Number)).Value = _Filtered;
ocm.Parameters.Add( new OracleParameter("DATE_FROM",
OracleType.DateTime)).Value = _DateFrom;
ocm.Parameters.Add( new OracleParameter("DATE_TO",
OracleType.DateTime)).Value = _DateTo;
ocm.Parameters.Add( new OracleParameter("FILE_FORMAT",
OracleType.VarChar, 3)).Value = _Format;
ocm.Parameters.Add( new OracleParameter("PATH", OracleType.VarChar,
500)).Value = _Path;
ocm.Parameters.Add( new OracleParameter("FILE_NAME",
OracleType.VarChar, 500)).Value = _FileName;
ocm.Parameters.Add( new OracleParameter("FILE_BINARY",
OracleType.Blob)).Value = fileBinary;
//ocm.Parameters.Add( new OracleParameter("FILE_BINARY",
OracleType.Blob)).Value = ol;
ocm.Parameters.Add( new OracleParameter("p_err",
OracleType.Number)).Direction = ParameterDirection.Output;

ocm.ExecuteNonQuery();

tx.Commit();

oc.Close();
Click to expand...
Click to expand...
 
Hi Jonaze,

Add cmd.parameters.refresh before actually appending the parameters
This will get the names, types of all parameters to the store
procedures & the parameters collction will get pre-filled.

This way, you will be able to identify the parameters to the stored
proc, their parameter name, type, direction, size etc

This will help you know what is the parameter type for FILE_BINARY
parameter

Does this help

Kalpesh

PS - Do not use cmd.parameters.refresh in your actual code
 
Back
Top