如何在不缓冲所有数据的情况下检索LONG RAW字段 [英] How to retrieve a LONG RAW field without buffering all datas
问题描述
大家好，
我正在寻求你的帮助，试图解决我的问题。
我不知道怎么样检索一个LONG RAW字段，无需缓冲我的存储过程发送的光标中的所有数据。
我想逐个处理每个记录以避免任何爆炸风险RAM。
有没有办法将我的字段（dj.doc）的所有二进制内容加载到我的对象（dto）的内存中，而不使用InitialLONGFetchSize = - 1？
感谢您的建议。
我正在使用Oracle 10g数据库服务器，客户端Oracle v12（32位）和带有C＃语言的Visual Studio Pro 2017（15.4.5）
Hello everyone,
I'm asking for your help in trying to solve my problem.
I don't know how to retrieve a LONG RAW field whithout buffering all datas from the cursor sent by my stored procedure.
I want to treat each recording one by one to avoid any risk of explosion of the RAM.
Is there a way to load all binary content of my field (dj.doc) into memory in my object (dto) without using InitialLONGFetchSize = -1 ?
Thanks for your advices.
I'm working on Oracle 10g database server, with a Client Oracle v12 (32 bits) and Visual Studio Pro 2017 (15.4.5) with the C# language
public static void Extract2Files(string scodeUF)
{
IDbCommand command = GetStoredProcCommand("GetAllOnlyForUF");
command.Parameters.Add(CreateParameter("pin_id_unite_m", scodeUF, 12));
command.Parameters.Add(CreateOutputCursorParameter("t_cursor"));
command.InitialLONGFetchSize = -1;
command.Connection.Open();
OracleDataReader reader = (OracleDataReader)command.ExecuteReader();
// if (reader.HasRows) // <== this command is buffering all datas from the cursor sent by th stored procédure. I don't want this behavior !
//{
DtoParser parser = DtoParserFactory.GetParser(typeof(T));
parser.PopulateOrdinals(reader);
while (reader.Read()) // <== this command is buffering all datas from the cursor sent by th stored procédure. I don't want this behavior !
{
T dto = default(T); // null;
dto = (T)parser.PopulateDto(reader);
...
}
reader.Close();
//}
在PopulateDto函数中，我正在调用名为GetBinaryFromReaderInOnePass的函数。
以下代码仅在command.InitialLONGFetchSize = -1
时有效。如果command.InitialLONGFetchSize = 0，则retval值始终为0.
In PopulateDto function, i'm calling the function called GetBinaryFromReaderInOnePass.
The code below only works when command.InitialLONGFetchSize = -1
If command.InitialLONGFetchSize = 0 the retval value is always 0.
static public byte[] GetBinaryFromReaderInOnePass(IDataReader rdr, int ordinalBinaire)
// Size of the BLOB buffer.
int totalBinaryBufferSize = (int)rdr.GetBytes(ordinalBinaire, startIndex, null, 0, 0);
// The BLOB byte[] buffer to be filled by GetBytes
byte[] outByte = new byte[totalBinaryBufferSize];
// Read bytes into outByte[] and retain the number of bytes returned.
retval = rdr.GetBytes(ordinalBinaire, startIndex, outByte, 0, totalBinaryBufferSize);
这是存储的procédure，其中dj.doc是一个LONG RAW类型字段：
Here is the stored procédure where dj.doc is a LONG RAW type field :
PROCEDURE GetAllOnlyForUF(pin_id_unite_m VARCHAR2, cur_docsJoints out t_cursor) IS
BEGIN
OPEN cur_docsJoints FOR SELECT dj.IDT, dj.name, dj.doc
FROM TB_PAT_DOC_JOINT dj, TB_MVT m
WHERE dj.id_mvt = m.id_mvt
AND m.id_unite_m = pin_id_unite_m;
END GetAllOnlyForUF;
我尝试过：
当我使用command.InitialLONGFetchSize = -1时，RAM填充了我光标的所有二进制文件。这对于成千上万的记录是不可行的，其中每个二进制重量为2Mb
当我使用command.InitialLONGFetchSize = 0时，我无法确定LONG RAW字段大小（DOC）方法GetBytes
当我使用command.InitialLONGFetchSize = 4096时，我的所有二进制文件的大小都是4Kb。
没有数据库往返是为了恢复整个二进制文件。
我找不到出路...
What I have tried:
When i use command.InitialLONGFetchSize = -1, the RAM fills with all the binaries of my cursor. This is not viable with thousands of records where each binary weighs 2Mb
When i use command.InitialLONGFetchSize = 0, i cannot determine the LONG RAW field size (DOC) with the method GetBytes
When i use command.InitialLONGFetchSize = 4096, all my binaries have a size of 4Kb.
no database round-trip was incurred to recover the entire binary.
I can not find a way out ...
推荐答案
使用InitialLONGFetchSize = 0
您没有得到返回值的原因可能是读者不知道如何识别正确的返回值。
您需要在结果集中包含主键或RowID。 从OracleDataReader获取数据 [ ^ ]
如果你想调整阅读器缓冲的数据量，你可以设置reader.FetchSize = reader.RowSize * 100;如果你想要缓冲100行。
Use InitialLONGFetchSize = 0
The reason you don't get a returnvalue is probably that the reader don't know how to identify the correct returnvalue.
You need to include either the Primary Key or the RowID in the resultset. Obtaining Data From an OracleDataReader[^]
If you want to adjust the amount of data the reader buffers you can for example set thereader.FetchSize = reader.RowSize * 100;if you want to buffer 100 rows.
总结所有读者我有两个问题：
1）首先，数量当InitialLONGFetchSize = -1时默认检索到的记录。
第一点可以按照Jörgen的建议设置reader.FetchSize = reader.RowSize * N，
，N = 1，例如一个接一个地处理录音。
2）第二个问题是当InitialLONGFetchSize<>时，GetBytes函数返回的Long原始字段的大小值。 -1。
当我从Visual Studio中的C＃代码调用我的存储过程时，这是sytematic。
当我使用在中定义的查询替换对过程的调用时我的C＃代码是一个字符串，
如果我的查询有2个表之间的连接，我仍然有问题。
To summarize for all readers I had two problems:
1) first, the number of records retrieved by default when InitialLONGFetchSize = -1.
This first point can be set as proposed by Jörgen with reader.FetchSize = reader.RowSize * N,
with N = 1 for example to process a recording one after the other.
2) the second problem was the size value of the Long raw fields returned by the GetBytes function when InitialLONGFetchSize <> -1.
This is sytematic when I call my stored procedure from my C # code in Visual Studio.
When I replace the call to the procedure with a query defined in my C # code as a string,
I still have the problem with my query if it has a join between 2 tables.
string requeteDocJointUf = "SELECT dj.IDT, dj.NOM_DOC, dj.doc " +
" FROM TB_PAT_DOC_JOINT dj, TB_MVT m " +
" WHERE dj.id_mvt = m.id_mvt " +
" AND m.id_unite_m = '" + scodeUF + "'";
确实，看看reader.GetSchemaTable （），我看到我的主键（dj.IDT）未被识别为键
Indeed, looking at reader.GetSchemaTable (), I see that my primary key (dj.IDT) is not recognized as a key
Property: ColumnName Value: IDT
Property: IsUnique Value: False
Property: IsKey Value: False
Property: IsRowID Value: False
另一方面，如果我删除了连接，那么我的密钥被识别，GetBytes函数返回二进制字段的大小。
On the other hand, if I remove the join then my key is recognized and the GetBytes function returns the size of my binary field.
string requeteDocJointUf = "SELECT dj.IDT, dj.NOM_DOC, dj.doc " +
" FROM TB_PAT_DOC_JOINT dj " +
" WHERE dj.IDT= 274934";
Property: ColumnName Value: IDT
Property: IsUnique Value: True
Property: IsKey Value: True <==
Property: IsRowID Value: False
InitialLONGFetchSize = 0;
// Size of the BLOB buffer.
int totalBinaryBufferSize = (int)rdr.GetBytes(ordinalBinaire, startIndex, null, 0, 0);
// <== totalBinaryBufferSize recovers well 136671 while InitialLONGFetchSize = 0! Yes!
// Property: IsKey Value = True for IDT
// The BLOB byte[] buffer to be filled by GetBytes
byte[] outByte = new byte[totalBinaryBufferSize];
// Read bytes into outByte[] and retain the number of bytes returned.
retval = rdr.GetBytes(ordinalBinaire, startIndex, outByte, 0, totalBinaryBufferSize);
// <== the binary file is correctly recovered !!
我认为系统操作的问题与程序可能来自光标声明。
我将继续我的研究，如果我找到解决方案并调用存储过程，我会通知你。
继续...
当我删除程序光标中的连接时，我的密钥仍然无法识别：
I think that the problem of systematic operation with the procedure may come from the cursor declaration.
I will continue my research, and I will keep you informed if I find the solution with the call to the stored procedure.
To be continued ...
When i remove the join in the cursor of the procedure, my key is still not recognized:
TYPE t_cursor is ref cursor RETURN TB_PAT_DOC_JOINT%ROWTYPE;
PROCEDURE GetAllOnlyForUF(pin_id_unite_m VARCHAR2, cur_docsJoints out t_cursor) IS
BEGIN
OPEN cur_docsJoints FOR SELECT * -- dj.IDT, dj.NOM_DOC, dj.doc
FROM TB_PAT_DOC_JOINT dj
WHERE dj.IDT = 274934;
END GetAllOnlyForUF;
这篇关于如何在不缓冲所有数据的情况下检索LONG RAW字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
