Help. How do I do this. I have spent hours trying to do this. I have the image as a byte stream... FileStream fs = new System.IO.FileStream(fileName, FileMode.Open, FileAccess.Read); byte[] b = new byte[fs.Length - 1]; fs.Read(b, 0, b.Length); fs.Close(); THIS
DOESN'T WORK string query = "Insert into test1 (age, name, pict) values (54, 'kim', " + b + ")"; SO I STARTED THIS. string query = "Insert into test1 (age, name, pict) values (54, 'kim', empty_blob())"; DO I NEED TO NEXT DO A SELECT TO retrieve a pointer to
the blob and then fill it or something along those lines? CODE would be very valuable!!
I should add that this line works fine with Sql Server string query = "Insert into test1 (age, name, pict) values (54, 'kim', " + b + ")";+ but NOT Oracle!!
IIRC, you have to use Oracle's Managed Provider for Oracle to insert into a blob field. Last time I checked, Microsoft's Managed Provider for Oracle didn't (but that was awhile ago). Here's some sample code using some of the concepts you had above:
FileStream fs = new System.IO.FileStream(fileName, FileMode.Open, FileAccess.Read);
byte[] b = new byte[fs.Length - 1];
fs.Read(b, 0, b.Length);
fs.Close();
string sqlCode = "Insert into test1 (age, name, pict) values (:Age, :Name, :Pict)";
string connectionString = "YourConnectionString";
// Create the data connection
Oracle.DataAccess.Client.OracleConnection dataConnection = new Oracle.DataAccess.Client.OracleConnection(connectionString);
dataConnection.Open();
Oracle.DataAccess.Client.OracleCommand myCommand = new Oracle.DataAccess.Client.OracleCommand(sqlCode,dataConnection);
myCommand.CommandType = CommandType.Text;
// age
Oracle.DataAccess.Client.OracleParameter oracleParameter = new Oracle.DataAccess.Client.OracleParameter();
oracleParameter.ParameterName = "Age";
oracleParameter.OracleDbType = Oracle.DataAccess.Client.OracleDbType.Decimal;
oracleParameter.Direction = ParameterDirection.Input;
oracleParameter.Value = 'kim';
myCommand.Add(oracleParameter);
// Name
Oracle.DataAccess.Client.OracleParameter oracleParameter = new Oracle.DataAccess.Client.OracleParameter();
oracleParameter.ParameterName = "Name";
oracleParameter.OracleDbType = Oracle.DataAccess.Client.OracleDbType.Varchar2;
oracleParameter.Direction = ParameterDirection.Input;
oracleParameter.Value = 'kim';
myCommand.Add(oracleParameter);
// pict
oracleParameter = new Oracle.DataAccess.Client.OracleParameter();
oracleParameter.ParameterName = "Pict";
oracleParameter.OracleDbType = Oracle.DataAccess.Client.OracleDbType.Blob;
oracleParameter.Direction = ParameterDirection.Input;
oracleParameter.Size = b.Length;
oracleParameter.Value = b.Body;
myCommand.Add(oracleParameter);
myCommand.ExecuteNonQuery();
myCommand.Dispose();
myCommand = null;
if(dataConnection!=null)
{
dataConnection.Close();
dataConnection.Dispose();
dataConnection = null;
}
You most certainly can read and write blobs using Microsoft's System.Data.OracleClient. Since you're creating a new BLOB, the thing you have to remember is that you need to get a temporary blob from oracle first and write the data to that temporary blob. Something
like:
using (OracleConnection dbConn = MyConnectionManager.GetConnection())
{
dbConn.Open();
OracleTransaction tx = dbConn.BeginTransaction(); // transaction are required when working with BLOBs.
try
{
OracleCommand cmdGetTempBlob = dbConn.CreateCommand();
cmdGetTempBlob.CommandType = CommandType.StoredProcedure;
cmdGetTempBlob.Transaction = tx;
cmdGetTempBlob.CommandText = "DBMS_LOB.CREATETEMPORARY";
OracleParameter pBlob = cmdGetTempBlob.Parameters.Add("lob_loc", OracleType.Blob);
pBlob.Direction = ParameterDirection.InputOutput;
cmdGetTempBlob.Parameters.Add("cache", false);
cmdGetTempBlob.Parameters.Add("dur", 0);
cmdGetTempBlob.ExecuteNonQuery();
OracleLob blob = pBlob.Value as OracleLob;
if (null == blob) throw new Exception("Failed to retrieve temporary BLOB from Oracle");
// At this point, you have an OracleLob object. OracleLob is a descendant of Stream.
// so now you need to copy from your FileStream to the OracleLob like you would any other stream.
// don't forget to lock your FileStream first and Seek both of them to the beginning before you start reading and writing.
// I'll leave the details up to you.
// once you've written the data to the OracleLob object, you can do an INSERT statement as normal
// BUT YOU MUST USE PARAMETERS!
// you shouldn't be doing ad-hoc SQL in any case in .NET. Parameters are so much better.
OracleCommand cmd = dbConn.CreateCommand();
cmd.CommandText = "INSERT INTO test1 (age, name, pict) VALUES (:age, :name, :blob);";
cmd.Parameters.Add("age", 54);
cmd.Parameters.Add("name", "Kim");
cmd.Parameters.Add("blob", OracleType.Blob).Value = blob;
cmd.ExecuteNonQuery();
tx.Commit(); // don't forget this!
}
catch
{
tx.Rollback();
throw;
}
}
Thanks for the reply. So you are using a StoredProcedure to create your temp LOB? Can you reply with a copy of what the stored procedure looks like? Forgive me, I'm not an Oracle DB guru. Thanks again.
I whipped up some example code using Oracle's managed provider (ODP.Net). Im not at work so i cant test it. You may need to Open() the connection before creating the OracleBlob.
// assumes ODP.Net
// using Oracle.DataAccess.Client;
// using Oracle.DataAccess.Types;
FileStream fs = new System.IO.FileStream(fileName, FileMode.Open,FileAccess.Read);
byte[] b = new byte[fs.Length - 1];
fs.Read(b, 0, b.Length);
fs.Close();
OracleConnection conn = new OracleConnection(YourConnectionString);
OracleCommand cmd = new OracleCommand("Insert into test1 (age, name, pict) values (:0,:1,:2)",conn);
cmd.Parameters.Add("Age",OracleDbType.Int32);
cmd.Parameters.Add("Name",OracleDbType.Varchar2,255);
cmd.Parameters.Add("Picture",OracelDbType.Blob);
OracleBlob blob = null;
conn.Open();
try{
blob = new OracleBlob(conn); // you must pass the connection to the Blob.
blob.Erase();
blob.Write(b,0,b.Length);
cmd.Parameters.["Age"].Value = 54;
cmd.Parameters.["Name"].Value = "kim";
cmd.Parameters.["Picture"].Value = blob;
cmd.ExecuteNonQuery();
}
finally{
// blobs and connections MUST be manually Closed and Disposed!
if(null != blob){
blob.Close();
blob.Dispose();
}
conn.Close();
conn.Dispose();
}
If the answer I provided is useful or informative please check the "answer" button.
Warning: Code is often uncompiled and possibly started life written on the back of a napkin. Beware typos.
unicorn21
Member
409 Points
86 Posts
Inserting image into oracle blob field C#
May 21, 2004 02:53 AM|LINK
unicorn21
Member
409 Points
86 Posts
Re: Inserting image into oracle blob field C#
May 21, 2004 03:41 AM|LINK
Brian Bilbro
Member
115 Points
23 Posts
ASPInsiders
Re: Inserting image into oracle blob field C#
May 24, 2004 03:06 PM|LINK
FileStream fs = new System.IO.FileStream(fileName, FileMode.Open, FileAccess.Read); byte[] b = new byte[fs.Length - 1]; fs.Read(b, 0, b.Length); fs.Close(); string sqlCode = "Insert into test1 (age, name, pict) values (:Age, :Name, :Pict)"; string connectionString = "YourConnectionString"; // Create the data connection Oracle.DataAccess.Client.OracleConnection dataConnection = new Oracle.DataAccess.Client.OracleConnection(connectionString); dataConnection.Open(); Oracle.DataAccess.Client.OracleCommand myCommand = new Oracle.DataAccess.Client.OracleCommand(sqlCode,dataConnection); myCommand.CommandType = CommandType.Text; // age Oracle.DataAccess.Client.OracleParameter oracleParameter = new Oracle.DataAccess.Client.OracleParameter(); oracleParameter.ParameterName = "Age"; oracleParameter.OracleDbType = Oracle.DataAccess.Client.OracleDbType.Decimal; oracleParameter.Direction = ParameterDirection.Input; oracleParameter.Value = 'kim'; myCommand.Add(oracleParameter); // Name Oracle.DataAccess.Client.OracleParameter oracleParameter = new Oracle.DataAccess.Client.OracleParameter(); oracleParameter.ParameterName = "Name"; oracleParameter.OracleDbType = Oracle.DataAccess.Client.OracleDbType.Varchar2; oracleParameter.Direction = ParameterDirection.Input; oracleParameter.Value = 'kim'; myCommand.Add(oracleParameter); // pict oracleParameter = new Oracle.DataAccess.Client.OracleParameter(); oracleParameter.ParameterName = "Pict"; oracleParameter.OracleDbType = Oracle.DataAccess.Client.OracleDbType.Blob; oracleParameter.Direction = ParameterDirection.Input; oracleParameter.Size = b.Length; oracleParameter.Value = b.Body; myCommand.Add(oracleParameter); myCommand.ExecuteNonQuery(); myCommand.Dispose(); myCommand = null; if(dataConnection!=null) { dataConnection.Close(); dataConnection.Dispose(); dataConnection = null; }HTHs, Brianjmiller4
Participant
782 Points
157 Posts
Re: Inserting image into oracle blob field C#
Jan 27, 2005 01:25 PM|LINK
rponton
Participant
1460 Points
292 Posts
Re: Inserting image into oracle blob field C#
Jan 27, 2005 10:44 PM|LINK
using (OracleConnection dbConn = MyConnectionManager.GetConnection()) { dbConn.Open(); OracleTransaction tx = dbConn.BeginTransaction(); // transaction are required when working with BLOBs. try { OracleCommand cmdGetTempBlob = dbConn.CreateCommand(); cmdGetTempBlob.CommandType = CommandType.StoredProcedure; cmdGetTempBlob.Transaction = tx; cmdGetTempBlob.CommandText = "DBMS_LOB.CREATETEMPORARY"; OracleParameter pBlob = cmdGetTempBlob.Parameters.Add("lob_loc", OracleType.Blob); pBlob.Direction = ParameterDirection.InputOutput; cmdGetTempBlob.Parameters.Add("cache", false); cmdGetTempBlob.Parameters.Add("dur", 0); cmdGetTempBlob.ExecuteNonQuery(); OracleLob blob = pBlob.Value as OracleLob; if (null == blob) throw new Exception("Failed to retrieve temporary BLOB from Oracle"); // At this point, you have an OracleLob object. OracleLob is a descendant of Stream. // so now you need to copy from your FileStream to the OracleLob like you would any other stream. // don't forget to lock your FileStream first and Seek both of them to the beginning before you start reading and writing. // I'll leave the details up to you. // once you've written the data to the OracleLob object, you can do an INSERT statement as normal // BUT YOU MUST USE PARAMETERS! // you shouldn't be doing ad-hoc SQL in any case in .NET. Parameters are so much better. OracleCommand cmd = dbConn.CreateCommand(); cmd.CommandText = "INSERT INTO test1 (age, name, pict) VALUES (:age, :name, :blob);"; cmd.Parameters.Add("age", 54); cmd.Parameters.Add("name", "Kim"); cmd.Parameters.Add("blob", OracleType.Blob).Value = blob; cmd.ExecuteNonQuery(); tx.Commit(); // don't forget this! } catch { tx.Rollback(); throw; } }Brian Bilbro
Member
115 Points
23 Posts
ASPInsiders
Re: Inserting image into oracle blob field C#
Jan 28, 2005 12:20 AM|LINK
rponton
Participant
1460 Points
292 Posts
Re: Inserting image into oracle blob field C#
Jan 28, 2005 07:52 PM|LINK
jmiller4
Participant
782 Points
157 Posts
Re: Inserting image into oracle blob field C#
Jan 31, 2005 03:06 PM|LINK
JeffreyABeck...
All-Star
16423 Points
3329 Posts
Re: Inserting image into oracle blob field C#
Jan 31, 2005 08:08 PM|LINK
// assumes ODP.Net // using Oracle.DataAccess.Client; // using Oracle.DataAccess.Types; FileStream fs = new System.IO.FileStream(fileName, FileMode.Open,FileAccess.Read); byte[] b = new byte[fs.Length - 1]; fs.Read(b, 0, b.Length); fs.Close(); OracleConnection conn = new OracleConnection(YourConnectionString); OracleCommand cmd = new OracleCommand("Insert into test1 (age, name, pict) values (:0,:1,:2)",conn); cmd.Parameters.Add("Age",OracleDbType.Int32); cmd.Parameters.Add("Name",OracleDbType.Varchar2,255); cmd.Parameters.Add("Picture",OracelDbType.Blob); OracleBlob blob = null; conn.Open(); try{ blob = new OracleBlob(conn); // you must pass the connection to the Blob. blob.Erase(); blob.Write(b,0,b.Length); cmd.Parameters.["Age"].Value = 54; cmd.Parameters.["Name"].Value = "kim"; cmd.Parameters.["Picture"].Value = blob; cmd.ExecuteNonQuery(); } finally{ // blobs and connections MUST be manually Closed and Disposed! if(null != blob){ blob.Close(); blob.Dispose(); } conn.Close(); conn.Dispose(); }Warning: Code is often uncompiled and possibly started life written on the back of a napkin. Beware typos.
jmiller4
Participant
782 Points
157 Posts
Re: Inserting image into oracle blob field C#
Jan 31, 2005 09:00 PM|LINK
conUS.Open(); OracleTransaction tx = conUS.BeginTransaction(); strSQL="INSERT into tbl.TBLPKSCRIPTS (ScriptID,ScriptNumber,ScriptText,ScriptType,Photo,ContentType) VALUES "+ "("+this.rblScriptList.SelectedValue.ToString()+",:prmScriptNumber,:prmScriptText,:prmScriptType,:prmPhoto,:prmContentType)"; cmdSQL=new OracleCommand(); cmdSQL.CommandText=strSQL; cmdSQL.CommandType=CommandType.Text; cmdSQL.Connection=conUS; Array arrScriptNumber = hidScriptNumber.Value.Split(Convert.ToChar(",")); Array arrScriptText = hidScriptText.Value.Split(Convert.ToChar("~")); Array arrScriptType = hidScriptType.Value.Split(Convert.ToChar(",")); Array arrPhoto = hidScriptPhoto.Value.Split(Convert.ToChar(",")); string sSavePath; sSavePath = "./UploadedPhotos/"; string strFilename = arrPhoto.GetValue(0).ToString(); FileStream fs = new System.IO.FileStream(Server.MapPath(sSavePath + strFilename), FileMode.Open, FileAccess.Read); byte[] b = new byte[fs.Length]; fs.Read(b, 0, Convert.ToInt32(fs.Length)); fs.Close(); OracleBlob blob = new OracleBlob(conUS); // you must pass the connection to the Blob. blob.Erase(); blob.Write(b,0,b.Length); Array arrContentType = hidContentType.Value.Split(Convert.ToChar(",")); cmdSQL.ArrayBindCount = (Convert.ToInt16(this.hidarrcnt.Value.ToString())); OracleParameter prmScriptNumber = new OracleParameter("prmScriptNumber", OracleDbType.Varchar2); prmScriptNumber.Direction = ParameterDirection.Input;prmScriptNumber.Value = arrScriptNumber;cmdSQL.Parameters.Add(prmScriptNumber); OracleParameter prmScriptText = new OracleParameter("prmScriptText", OracleDbType.Varchar2); prmScriptText.Direction = ParameterDirection.Input;prmScriptText.Value = arrScriptText;cmdSQL.Parameters.Add(prmScriptText); OracleParameter prmScriptType = new OracleParameter("prmScriptType", OracleDbType.Varchar2); prmScriptType.Direction = ParameterDirection.Input;prmScriptType.Value = arrScriptType;cmdSQL.Parameters.Add(prmScriptType); OracleParameter prmPhoto = cmdSQL.Parameters.Add("prmPhoto",OracleDbType.Blob); prmPhoto.Direction = ParameterDirection.Input; prmPhoto.Value=blob; OracleParameter prmContentType = new OracleParameter("prmContentType", OracleDbType.Varchar2); prmContentType.Direction = ParameterDirection.Input;prmContentType.Value = arrContentType;cmdSQL.Parameters.Add(prmContentType); cmdSQL.ExecuteNonQuery();conUS.Close();tx.Commit();