Read Image stored in Oracle using Long DataType

Question

I want to read the image stored in Oracle Long datatype. Number of images are stored in a remote Oracle database in a column with datatype long. I just need to retrieve those images and show them on my aspx page. I could retrieve the image from database but when tried to caste it to byte array, it threw error that, string can not be converted to byte[]'. Anybody have any suggestions on how to retrieve these images stored in long column in database.

byte[] signatureBlobReceived = cls_TBL_BROKER_BL.GetInstance().GetSignatureBlobFromAccountNumber_BL(strCRNnumber);
 return File(signatureBlobReceived, "image/jpeg");


public byte[] GetSignatureBlobFromAccountNumber_BL()
{
object SignatureBlob = null;
Database db = DatabaseFactory.CreateDatabase("imageConnectionString");
DbCommand dbc = db.GetSqlStringCommand(ConfigurationSettings.AppSettings["signqry"].ToString());
dbc.CommandType = CommandType.Text;
SignatureBlob = db.ExecuteScalar(dbc);
byte[] array = Encoding.ASCII.GetBytes(Convert.ToString(SignatureBlob));
 string aa = string.Empty;
return array;
}

Query used is:
<add key="signqry" value="SELECT image FROM table1"/> `

have u tried converting from Long to Lob type (alter the table itself)? Long types are deprecated in Oracle, should be a blob for storing images. — tbone
– tbone, Commented Feb 14, 2012 at 13:00
Thanks for the response but I can not alter the table structure. It is a remote database & I don't have rights to do this. I just have to fetch the data from their database and display it. — ABC
– ABC, Commented Feb 14, 2012 at 15:36
Hi. Have you found any solution to this? I am stuck in the same case. I too am trying to fetch my data from a Finacle CBS. — Raxak
– Raxak, Commented Jan 9, 2018 at 9:17

tbone · Accepted Answer · 2012-02-15 12:58:37Z

1

Try this (odp.net)

            string connStr = "User Id=user;Password=pwd;Data Source=mySID;";
            OracleConnection _conn = new OracleConnection(connStr);
            _conn.Open();

            string sel = @"select long_raw_col from long_raw_test";
            OracleCommand cmd = new OracleCommand(sel, _conn);
            cmd.InitialLONGFetchSize = 5000;
            OracleDataReader reader = cmd.ExecuteReader();

            int rows = 0;
            // loop through rows from table
            while (reader.Read())
            {
                rows++;
                byte[] buf = new byte[5000];
                long bytesRead = reader.GetBytes(reader.GetOrdinal("long_raw_col"), 0, buf, 0, 5000);
                FileStream fs = new FileStream("C:\\test\\test_long" + rows + ".dat", FileMode.Create);
                fs.Write(buf, 0, (int)bytesRead);
                fs.Close();

                Console.WriteLine("Row " + rows + ": Read " + bytesRead + " bytes from table, see test_long" + rows + ".dat");
            }

This example just reads the long raw data from Oracle into a byte array, then outputs to a file. Note the InitalLONGFetchSize > 0.

edited Feb 15, 2012 at 12:58

answered Feb 14, 2012 at 16:49

tbone

15.5k3 gold badges37 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

ABC Over a year ago

Thanks for the help, but the column that I am fetching is of datatype LONG and not LONG RAW. I tried your solution but its of no use in my scenario.

tbone Over a year ago

@ABC you are storing images in a LONG column?

ABC Over a year ago

Not me, I am just fetching the data from a remote database, the data in long column is stored by someone else. That is the reason I can't modify the table structure and change the LONG column to BLOB or something else.

tbone Over a year ago

@ABC I believe only character data (not binary) should be stored in LONG columns. For new schemas, LONG maps to CLOB, and LONG RAW maps to BLOB. From Oracle: "Columns defined as LONG can store variable-length character data containing up to 2 gigabytes of information. LONG data is text data that is to be appropriately converted when moving among different systems". See docs.oracle.com/cd/B28359_01/server.111/b28318/…

tbone Over a year ago

@ABC is any other program or app using this data correctly? I almost always deal with BLOBs so I'm surprised images are in LONG and not LONG RAW

|

Anyname Donotcare · Accepted Answer · 2012-02-14 14:27:02Z

I use this class :my database is informix and the images are stored in Byte type .Hope this can help you.

 public class MyPhoto
    {
        public static Stream RetrievePhoto()
        {
            DBConnection DAL_Helper = new DBConnection(ConfigurationSettings.AppSettings["connection"].ToString());
            Byte[] myByteBuff;
            Stream myImgStream;
            string qry = "----------";
            DataTable dt = DAL_Helper.Return_DataTable(qry);
            try
            {
                if (dt.Rows.Count > 0)
                {
                    if (!string.IsNullOrEmpty(dt.Rows[0][0].ToString()))
                    {
                        myByteBuff = (Byte[])((object)(dt.Rows[0][0]));
                        myImgStream = new MemoryStream(myByteBuff);
                    }
                    else
                    {
                        myImgStream = RetrievePhotoNoProfile();
                    }
                }
                else
                {
                    myImgStream = RetrievePhotoNoProfile();
                }
            }
            catch (Exception ex)
            {
                myImgStream = RetrievePhotoNoProfile();
            }
            return myImgStream;
        }

        public static byte[] StreamToByteArray(Stream stream)
        {
            if (stream is MemoryStream)
            {
                return ((MemoryStream)stream).ToArray();
            }
            else
            {
                return ReadFully(stream);
            }
        }
        public static byte[] ReadFully(Stream input)
        {
            byte[] buffer = new byte[input.Length];
            using (MemoryStream ms = new MemoryStream())
            {
                int read;
                while ((read = input.Read(buffer, 0, buffer.Length)) > 0)
                {
                    ms.Write(buffer, 0, read);
                }
                return ms.ToArray();
            }
        }

        private static Stream RetrievePhotoNoProfile()
        {
            string noprofileimgPath = HttpContext.Current.Server.MapPath("~/images/noprofile.png");
            System.IO.FileStream fs = new System.IO.FileStream(noprofileimgPath, System.IO.FileMode.Open, FileAccess.Read);
            byte[] ba = new byte[fs.Length];
            fs.Read(ba, 0, (int)fs.Length);
            Stream myImgStream = new MemoryStream(ba);
            fs.Close();
            return myImgStream;
        }

        public static Image byteArrayToImage(byte[] byteArrayIn)
        {
            MemoryStream ms = new MemoryStream(byteArrayIn);
            Image returnImage = Image.FromStream(ms);
            return returnImage;
        }
    }

Thanks for the help, but when I try to cast it in byte[], it throws error- Unable to cast object of type 'System.String' to type 'System.Byte[]'. It works when the image is stored as Blob in database, but in my case the image is stored as LONG.

Collectives™ on Stack Overflow

Read Image stored in Oracle using Long DataType

2 Answers 2

10 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related