从 Oracle DB 检索图像 [英] Retrieve image from Oracle DB

问题描述

所以，我正在使用 web api 来检索图像！！但是，在数据库中，图像是 LongRaw.我在谷歌看到我需要使用 OracleDbType.Blob但是，当我尝试使用它时，

So, i am using a web api to retrieve a image!! But, at the DB the image is a LongRaw. Im seeing at the google that i need to use the OracleDbType.Blob But, when i try to use this,

public IEnumerable<FotoEnvolvido> GetFoto(string suspid)
        {

            DataSet lretorno = new DataSet();

            string connectionString = GetConnectionString();
            using (OracleConnection connection = new OracleConnection())
            {
                connection.ConnectionString = connectionString;

                OracleDataReader reader = null;
                OracleCommand cmd = new OracleCommand();
                cmd.InitialLONGFetchSize = 50000;
                cmd.Connection = connection;
                cmd = new OracleCommand("MOBILE.XAPIMANDADOMOBILE.BUSCAFOTO", connection);
                cmd.CommandType = CommandType.StoredProcedure;

                //variáveis entrada            
                cmd.Parameters.Add(new OracleParameter("SUSPID", suspid));
                //variáveis de saida          
                cmd.Parameters.Add(new OracleParameter("oretorno", OracleDbType.Blob)).Direction = ParameterDirection.Output;

                connection.Open();
                cmd.ExecuteNonQuery();
                OracleDataAdapter da = new OracleDataAdapter(cmd);

                da.Fill(lretorno); 

                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                //CRIO A LISTA
                lretorno.Load(reader, LoadOption.OverwriteChanges, "BUSCAFOTO");
                connection.Close();
                connection.Dispose();

                var teste = lretorno.Tables[0].AsEnumerable().Select(row => new FotoEnvolvido
                {
                    FOTO = (byte[])(row["FOTO"]),
                });

                return teste;
            }
        }

我在 cmd.ExecuteNonQuery() 上有错误:

i have a error on cmd.ExecuteNonQuery() :

"ORA-06550: linha 1, coluna 7:
PLS-00306: número errado ou tipos de argumentos na chamada para 'BUSCAFOTO'
ORA-06550: linha 1, coluna 7:
PL/SQL: Statement ignored"

Oracle.ManagedDataAccess.Client.OracleException was unhandled by user code
  DataSource=""
  ErrorCode=-2147467259
  HResult=-2147467259
  IsRecoverable=false
  Message=ORA-06550: linha 1, coluna 7:
PLS-00306: número errado ou tipos de argumentos na chamada para 'BUSCAFOTO'
ORA-06550: linha 1, coluna 7:
PL/SQL: Statement ignored
  Number=6550
  Procedure=""
  Source=Oracle Data Provider for .NET, Managed Driver
  StackTrace:
       em OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
       em OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean bFirstIterationDone)
       em OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, Boolean isFromEF)
       em Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()
       em WebApiApp.Controllers.FotoController.GetFoto(String suspid) na C:Users50216740DocumentsVisual Studio 2015ProjectsAppMobilePCRJAppMobilePCRJWebApiAppControllersFotoController.cs:linha 49
       em lambda_method(Closure , Object , Object[] )
       em System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass10.<GetExecutor>b__9(Object instance, Object[] methodParameters)
       em System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.Execute(Object instance, Object[] arguments)
       em System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken)
  InnerException: 

如果我像其他 web api 一样使用 OracleDbType.RefCursor，我得到字符串，不要给我这个错误...问题是参数来 [{"ISUSPID":0,"FOTO":""}]，即使在 DB 上给我带来了图像！

我做错了什么，就是 Json 上的 FOTO 是空的???

What i am doind wrong, thats the FOTO is empty on Json ???

推荐答案

我不确定 lretorno.Load(...) 正在做什么来读取数据，但是这个使用 select 语句的 sudo 代码示例可能会对您有所帮助...我总是不得不专门获取 blob 并将其读出以获取过去的字节.

I'm not sure what lretorno.Load(...) is doing to read the data, but this sudo code sample using a select statement might help you... I've always had to specifically get the blob and read it out to get the bytes in the past.

检索LONG RAW数据类型的示例

var imgCmd = new OracleCommand("SELECT photo FROM photos WHERE photo_id = 1", _con);
imgCmd.InitialLONGFetchSize = -1; // Retrieve the entire image during select instead of possible two round trips to DB
var reader = imgCmd.ExecuteReader();
if (reader.Read()) {
    // Fetch the LONG RAW
    OracleBinary imgBinary = reader.GetOracleBinary(0);
    // Get the bytes from the binary obj
    byte[] imgBytes = imgBinary.IsNull ? null : imgBinary.Value;
}
reader.Close();

检索BLOB数据类型的示例

var imgCmd = new OracleCommand("SELECT photo FROM photos WHERE photo_id = 1", _con);
var reader = imgCmd.ExecuteReader();
if (reader.Read()) {
    // Fetch the blob
    OracleBlob imgBlob = reader.GetOracleBlob(0);
    // Create byte array to read the blob into
    byte[] imgBytes = new byte[imgBlob.Length];
    // Read the blob into the byte array
    imgBlob.Read(imgBytes, 0, imgBlob.Length);
}
reader.Close();

这篇关于从 Oracle DB 检索图像的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！