Using C# to insert blob into Oracle

[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?

 

[Nicholas Paldino [.NET/C# MVP]]

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.

 

[Chris Fink]

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!

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?

 

[Nicholas Paldino [.NET/C# MVP]]

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!

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?

 

[Nicholas Paldino [.NET/C# MVP]]

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.

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 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?

 

[Chris Fink]

The string is just text.

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!

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?

 

[Chris Fink]

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.

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.

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

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?

 

[Nicholas Paldino [.NET/C# MVP]]

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)

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?

 

[Chris Fink]

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

Thanks for your help.

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)

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?

 

[Chris Fink]

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

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)

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]"

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?

 

[Chris Fink]

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.

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

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)

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]"

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]"

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?

 

[Michael Voss]

I should also note that my connection to Oracle is via a System DSN using MS
ODBC provider for Oracle.

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.