Using C# to insert blob into Oracle

  • Thread starter Thread starter Chris Fink
  • Start date Start date
C

Chris Fink

I have setup an Oracle table which contains a blob field. How do I insert
data into this field using C# and ADO.net?
 
Chris,

The provider for Oracle should represent the field as a byte array. You
should be able to assign a new byte array to this field in a data set, and
then update the field through an ad-hoc query or stored procedure, just like
you would any other value in the database.

Hope this helps.
 
How do I convert a C# string into a byte array? Which class do I use, the
StreamReader, StringReader, etc? Any sample code is appreciated!

Nicholas Paldino said:
Chris,

The provider for Oracle should represent the field as a byte array. You
should be able to assign a new byte array to this field in a data set, and
then update the field through an ad-hoc query or stored procedure, just like
you would any other value in the database.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Chris Fink said:
I have setup an Oracle table which contains a blob field. How do I insert
data into this field using C# and ADO.net?
Click to expand...
Click to expand...
 
Chris,

It depends on the format of the string. Is it in hex, or is it base64
encoded, or something else?


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Chris Fink said:
How do I convert a C# string into a byte array? Which class do I use, the
StreamReader, StringReader, etc? Any sample code is appreciated!

in
message news:[email protected]...
Chris,

The provider for Oracle should represent the field as a byte array. You
should be able to assign a new byte array to this field in a data set,
and
then update the field through an ad-hoc query or stored procedure, just like
you would any other value in the database.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Chris Fink said:
I have setup an Oracle table which contains a blob field. How do I insert
data into this field using C# and ADO.net?
Click to expand...
Click to expand...
Click to expand...
 
Chris,

Yes, it is just text, but you want a byte array from it? There are many
ways to convert text to a byte array. Is the text representative of a
binary value, or is it actual text, and you want to store it in a binary
form in the table?


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Chris Fink said:
The string is just text.

in
message news:%[email protected]...
Chris,

It depends on the format of the string. Is it in hex, or is it
base64
encoded, or something else?


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Chris Fink said:
How do I convert a C# string into a byte array? Which class do I use, the
StreamReader, StringReader, etc? Any sample code is appreciated!

"Nicholas Paldino [.NET/C# MVP]" <[email protected]>
wrote
in
message Chris,

The provider for Oracle should represent the field as a byte
array.
You
should be able to assign a new byte array to this field in a data set,
and
then update the field through an ad-hoc query or stored procedure,
just
like
you would any other value in the database.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I have setup an Oracle table which contains a blob field. How do I
insert
data into this field using C# and ADO.net?
Click to expand...
Click to expand...
Click to expand...
 
The string is just text.

Nicholas Paldino said:
Chris,

It depends on the format of the string. Is it in hex, or is it base64
encoded, or something else?


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Chris Fink said:
How do I convert a C# string into a byte array? Which class do I use, the
StreamReader, StringReader, etc? Any sample code is appreciated!

in
message news:[email protected]...
Chris,

The provider for Oracle should represent the field as a byte array. You
should be able to assign a new byte array to this field in a data set,
and
then update the field through an ad-hoc query or stored procedure, just like
you would any other value in the database.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I have setup an Oracle table which contains a blob field. How do I insert
data into this field using C# and ADO.net?
Click to expand...
Click to expand...
Click to expand...
 
Sorry for not explaining in more detail. I want to take a string, ex: "This
is a test" and insert it into a blob field. So I will need to first convert
the string into a byte[] and then perform the insert. In actualitym this
string could be text as presented, or more common xml documents.

Nicholas Paldino said:
Chris,

Yes, it is just text, but you want a byte array from it? There are many
ways to convert text to a byte array. Is the text representative of a
binary value, or is it actual text, and you want to store it in a binary
form in the table?


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Chris Fink said:
The string is just text.

in
message news:%[email protected]...
Chris,

It depends on the format of the string. Is it in hex, or is it
base64
encoded, or something else?


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

How do I convert a C# string into a byte array? Which class do I
Click to expand...
use,
the
StreamReader, StringReader, etc? Any sample code is appreciated!

"Nicholas Paldino [.NET/C# MVP]" <[email protected]>
wrote
in
message Chris,

The provider for Oracle should represent the field as a byte
array.
You
should be able to assign a new byte array to this field in a data set,
and
then update the field through an ad-hoc query or stored procedure,
just
like
you would any other value in the database.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I have setup an Oracle table which contains a blob field. How do I
insert
data into this field using C# and ADO.net?
Click to expand...
Click to expand...
Click to expand...
 
Chris,

In that case, you can use the GetBytes method on the ASCIIEncoder or the
UnicodeEncoder (depending on the type of text) and use that to get the bytes
that you store in the blob field.

However, I have to ask, why not just use a text field?

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Chris Fink said:
Sorry for not explaining in more detail. I want to take a string, ex:
"This
is a test" and insert it into a blob field. So I will need to first
convert
the string into a byte[] and then perform the insert. In actualitym this
string could be text as presented, or more common xml documents.

in
message news:[email protected]...
Chris,

Yes, it is just text, but you want a byte array from it? There are many
ways to convert text to a byte array. Is the text representative of a
binary value, or is it actual text, and you want to store it in a binary
form in the table?


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Chris Fink said:
The string is just text.

"Nicholas Paldino [.NET/C# MVP]" <[email protected]>
wrote
in
message Chris,

It depends on the format of the string. Is it in hex, or is it
base64
encoded, or something else?


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

How do I convert a C# string into a byte array? Which class do I use,
the
StreamReader, StringReader, etc? Any sample code is appreciated!

"Nicholas Paldino [.NET/C# MVP]" <[email protected]>
wrote
in
message Chris,

The provider for Oracle should represent the field as a byte
array.
You
should be able to assign a new byte array to this field in a data set,
and
then update the field through an ad-hoc query or stored procedure,
just
like
you would any other value in the database.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I have setup an Oracle table which contains a blob field. How do
I
insert
data into this field using C# and ADO.net?
Click to expand...
Click to expand...
Click to expand...
 
Oracle VARCHAR2 field is limited in size, BLOB is not. Many of the XML
transactions may exceed the VARCHAR2 limit.

Thanks for your help.

Nicholas Paldino said:
Chris,

In that case, you can use the GetBytes method on the ASCIIEncoder or the
UnicodeEncoder (depending on the type of text) and use that to get the bytes
that you store in the blob field.

However, I have to ask, why not just use a text field?

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Chris Fink said:
Sorry for not explaining in more detail. I want to take a string, ex:
"This
is a test" and insert it into a blob field. So I will need to first
convert
the string into a byte[] and then perform the insert. In actualitym this
string could be text as presented, or more common xml documents.

in
message news:[email protected]...
Chris,

Yes, it is just text, but you want a byte array from it? There are many
ways to convert text to a byte array. Is the text representative of a
binary value, or is it actual text, and you want to store it in a binary
form in the table?


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

The string is just text.

"Nicholas Paldino [.NET/C# MVP]" <[email protected]>
wrote
in
message Chris,

It depends on the format of the string. Is it in hex, or is it
base64
encoded, or something else?


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

How do I convert a C# string into a byte array? Which class do I use,
the
StreamReader, StringReader, etc? Any sample code is appreciated!

"Nicholas Paldino [.NET/C# MVP]" <[email protected]>
wrote
in
message Chris,

The provider for Oracle should represent the field as a byte
array.
You
should be able to assign a new byte array to this field in a data set,
and
then update the field through an ad-hoc query or stored procedure,
just
like
you would any other value in the database.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I have setup an Oracle table which contains a blob field. How do
I
insert
data into this field using C# and ADO.net?
Click to expand...
Click to expand...
Click to expand...
 
Now that I am on the correct path, the string to byte array was not
difficult. However, the insert of the byte array is throwing an Oracle
Exception, {"ORA-01465: invalid hex number" }. It is obvious as to why when
looking at the SQL (System.Byte[]) , but I am not sure what I need to do to
pass this byte array into the Blob field properly in my SQL statement.

Following is the code, notice that the runtime SQL string is as follows:
INSERT INTO INBOUNDWEBSERVICES (STRUCTUREID, STATUS, LASTUPDATEDBY, MSGIN)
VALUES ('structureID10','Test status1','111','System.Byte[]')

string asciiString = "Test String";
Encoding ascii = Encoding.ASCII;

// Convert the string into a byte[].
byte[] baMsgIn = ascii.GetBytes(asciiString);

string sql = String.Format(("INSERT INTO INBOUNDWEBSERVICES (STRUCTUREID,
STATUS, LASTUPDATEDBY, MSGIN) VALUES ('{0}','{1}','{2}','{3}')"),
structureID, status, lastUpdatedBy, baMsgIn);


try
{
OracleConnection cn = new OracleConnection(DATASOURCE8I);
cn.Open();
OracleCommand cmd = new OracleCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
cmd.Connection = cn;
cmd.ExecuteNonQuery();
cn.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}


Chris Fink said:
Oracle VARCHAR2 field is limited in size, BLOB is not. Many of the XML
transactions may exceed the VARCHAR2 limit.

Thanks for your help.

message news:[email protected]...
Chris,

In that case, you can use the GetBytes method on the ASCIIEncoder or the
UnicodeEncoder (depending on the type of text) and use that to get the bytes
that you store in the blob field.

However, I have to ask, why not just use a text field?

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Chris Fink said:
Sorry for not explaining in more detail. I want to take a string, ex:
"This
is a test" and insert it into a blob field. So I will need to first
convert
the string into a byte[] and then perform the insert. In actualitym this
string could be text as presented, or more common xml documents.

in
message Chris,

Yes, it is just text, but you want a byte array from it? There are
many
ways to convert text to a byte array. Is the text representative of a
binary value, or is it actual text, and you want to store it in a binary
form in the table?


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

The string is just text.

"Nicholas Paldino [.NET/C# MVP]" <[email protected]>
wrote
in
message Chris,

It depends on the format of the string. Is it in hex, or is it
base64
encoded, or something else?


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

How do I convert a C# string into a byte array? Which class do I
use,
the
StreamReader, StringReader, etc? Any sample code is appreciated!

"Nicholas Paldino [.NET/C# MVP]"
Click to expand...
wrote
in
message Chris,

The provider for Oracle should represent the field as a byte
array.
You
should be able to assign a new byte array to this field in a data
set,
and
then update the field through an ad-hoc query or stored procedure,
just
like
you would any other value in the database.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I have setup an Oracle table which contains a blob field. How do
I
insert
data into this field using C# and ADO.net?
Click to expand...
Click to expand...
Click to expand...
 
I should also note that my connection to Oracle is via a System DSN using MS
ODBC provider for Oracle. In my C# class, I am making ADO.NET calls using
the OracleConnection, OracleCommand objects from the added reference
Oracle.DataAccess.

Not sure if any of that matters, but just wanted to point that out.

Chris Fink said:
Now that I am on the correct path, the string to byte array was not
difficult. However, the insert of the byte array is throwing an Oracle
Exception, {"ORA-01465: invalid hex number" }. It is obvious as to why when
looking at the SQL (System.Byte[]) , but I am not sure what I need to do to
pass this byte array into the Blob field properly in my SQL statement.

Following is the code, notice that the runtime SQL string is as follows:
INSERT INTO INBOUNDWEBSERVICES (STRUCTUREID, STATUS, LASTUPDATEDBY, MSGIN)
VALUES ('structureID10','Test status1','111','System.Byte[]')

string asciiString = "Test String";
Encoding ascii = Encoding.ASCII;

// Convert the string into a byte[].
byte[] baMsgIn = ascii.GetBytes(asciiString);

string sql = String.Format(("INSERT INTO INBOUNDWEBSERVICES (STRUCTUREID,
STATUS, LASTUPDATEDBY, MSGIN) VALUES ('{0}','{1}','{2}','{3}')"),
structureID, status, lastUpdatedBy, baMsgIn);


try
{
OracleConnection cn = new OracleConnection(DATASOURCE8I);
cn.Open();
OracleCommand cmd = new OracleCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
cmd.Connection = cn;
cmd.ExecuteNonQuery();
cn.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}


Chris Fink said:
Oracle VARCHAR2 field is limited in size, BLOB is not. Many of the XML
transactions may exceed the VARCHAR2 limit.

Thanks for your help.

message news:[email protected]...
Chris,

In that case, you can use the GetBytes method on the ASCIIEncoder
Click to expand...
or
the
UnicodeEncoder (depending on the type of text) and use that to get the bytes
that you store in the blob field.

However, I have to ask, why not just use a text field?

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Sorry for not explaining in more detail. I want to take a string, ex:
"This
is a test" and insert it into a blob field. So I will need to first
convert
the string into a byte[] and then perform the insert. In actualitym this
string could be text as presented, or more common xml documents.

in
message Chris,

Yes, it is just text, but you want a byte array from it? There are
many
ways to convert text to a byte array. Is the text representative
Click to expand...
Click to expand...
of
a
binary value, or is it actual text, and you want to store it in a binary
form in the table?


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

The string is just text.

"Nicholas Paldino [.NET/C# MVP]"
Click to expand...
wrote
in
message Chris,

It depends on the format of the string. Is it in hex, or is it
base64
encoded, or something else?


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

How do I convert a C# string into a byte array? Which class
Click to expand...
Click to expand...
do
I
use,
the
StreamReader, StringReader, etc? Any sample code is appreciated!

"Nicholas Paldino [.NET/C# MVP]"
Click to expand...
wrote
in
message Chris,

The provider for Oracle should represent the field as a byte
array.
You
should be able to assign a new byte array to this field in a data
set,
and
then update the field through an ad-hoc query or stored procedure,
just
like
you would any other value in the database.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I have setup an Oracle table which contains a blob field.
Click to expand...
How
do
I
insert
data into this field using C# and ADO.net?
Click to expand...
Click to expand...
Click to expand...
 
Chris said:
I should also note that my connection to Oracle is via a System DSN using MS
ODBC provider for Oracle.
Click to expand...

Have you considered using a CLOB field instead of a BLOB ? If you are
inserting text data, this might be a better choice, although there you
cannot insert strings longer than 4000 characters in one string literal
using ODBC without Oracle host variables.
 
Back
Top