Object must implement IConvertible, writing to BLOB

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();
 
S

Sahil Malik [MVP]

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...
 
J

Jonaze

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...
 
S

Sahil Malik [MVP]

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...
 
K

Kalpesh

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Stored Procedure Oracle + Clob + Vb.Net 0
Using C# to insert data into an Oracle BLOB Field 0
Insert 3 Gb binary file to oracle blob using Ado.net oracleclient 1
Oracle Lob (blob) Read / Write woes 3
OracleClient and pls-00306 error message 4
VB.NET passing CLOB input parameter to Oracle stored function 0
retrieving BLOB from Oracle 2
retrieving BLOB from Oracle 9

Top