數(shù)據(jù)庫中取Clob類型字段出現(xiàn)亂碼
2024-07-21 02:23:25
供稿：網(wǎng)友
datareader 的默認行為是在整個數(shù)據(jù)行可用時立即以行的形式加載傳入數(shù)據(jù)。但是，對于二進制大對象 (blob) 則需要進行不同的處理，因為它們可能包含數(shù)十億字節(jié)的數(shù)據(jù)，而單個行中無法包含如此多的數(shù)據(jù)。command.executereader 方法具有一個重載，它將采用 commandbehavior 參數(shù)來修改 datareader 的默認行為。您可以將 commandbehavior.sequentialaccess 傳遞到 executereader 方法來修改 datareader 的默認行為，以便讓 datareader 按照順序在接收到數(shù)據(jù)時立即將其加載，而不是加載數(shù)據(jù)行。這是加載 blob 或其他大數(shù)據(jù)結(jié)構(gòu)的理想方案。請注意，該行為可能會因數(shù)據(jù)源的不同而不同。例如，從 microsoft access 中返回 blob 將導(dǎo)致整個 blob 加載到內(nèi)存中，而不是按接收數(shù)據(jù)的順序加載數(shù)據(jù)。
在將 datareader 設(shè)置為使用 sequentialaccess 時，務(wù)必要注意訪問所返回字段的順序。datareader 的默認行為是在整個行可用時立即加載該行，這使您能夠在讀取下一行之前按任何順序訪問所返回的字段。但是，當使用 sequentialaccess 時，必須按順序訪問由 datareader 返回的不同字段。例如，如果查詢返回三個列，其中第三列是 blob，則必須在訪問第三個字段中的 blob 數(shù)據(jù)之前返回第一個和第二個字段的值。如果在訪問第一個或第二個字段之前訪問第三個字段，則第一個和第二個字段值將不再可用。這是因為 sequentialaccess 已修改 datareader，使其按順序返回數(shù)據(jù)，當 datareader 已經(jīng)讀取超過特定數(shù)據(jù)時，該數(shù)據(jù)將不可用。
在訪問 blob 字段中的數(shù)據(jù)時，請使用 datareader 的 getbytes 或 getchars 類型化訪問器，它們將用數(shù)據(jù)來填充數(shù)組。還可以對字符數(shù)據(jù)使用 getstring，但是為了節(jié)省系統(tǒng)資源，您可能不希望將整個 blob 值加載到單個字符串變量中。您可以指定要返回的特定數(shù)據(jù)緩沖區(qū)大小，以及從返回的數(shù)據(jù)中讀取的第一個字節(jié)或字符的起始位置。getbytes 和 getchars 將返回一個 long 值，它表示返回的字節(jié)或字符數(shù)。如果將一個空數(shù)組傳遞給 getbytes 或 getchars，則返回的長值將是 blob 中字符或字符的總數(shù)。您可以選擇將數(shù)組中的某個索引指定為所讀取數(shù)據(jù)的起始位置。
以下示例從 microsoft sql server 中的 pubs 示例數(shù)據(jù)庫中返回發(fā)行者 id 和徽標。發(fā)行者 id (pub_id) 是字符字段，而徽標則是圖形，即 blob。由于 logo 字段是位圖，因此該示例使用 getbytes 返回二進制數(shù)據(jù)。請注意，由于必須按順序訪問字段，所以將在訪問徽標之前訪問當前數(shù)據(jù)行的發(fā)行者 id。
[visual basic]
dim pubsconn as sqlconnection = new sqlconnection("data source=localhost;integrated security=sspi;initial catalog=pubs;")
dim logocmd as sqlcommand = new sqlcommand("select pub_id, logo from pub_info", pubsconn)
dim fs as filestream ' writes the blob to a file (*.bmp).
dim bw as binarywriter ' streams the binary data to the filestream object.
dim buffersize as integer = 100 ' the size of the blob buffer.
dim outbyte(buffersize - 1) as byte ' the blob byte() buffer to be filled by getbytes.
dim retval as long ' the bytes returned from getbytes.
dim startindex as long = 0 ' the starting position in the blob output.
dim pub_id as string = "" ' the publisher id to use in the file name.
' open the connection and read data into the datareader.
pubsconn.open()
dim myreader as sqldatareader = logocmd.executereader(commandbehavior.sequentialaccess)
do while myreader.read()
' get the publisher id, which must occur before getting the logo.
pub_id = myreader.getstring(0)
' create a file to hold the output.
fs = new filestream("logo" & pub_id & ".bmp", filemode.openorcreate, fileaccess.write)
bw = new binarywriter(fs)
' reset the starting byte for a new blob.
startindex = 0
' read bytes into outbyte() and retain the number of bytes returned.
retval = myreader.getbytes(1, startindex, outbyte, 0, buffersize)
' continue reading and writing while there are bytes beyond the size of the buffer.
do while retval = buffersize
bw.write(outbyte)
bw.flush()
' reposition the start index to the end of the last buffer and fill the buffer.
startindex += buffersize
retval = myreader.getbytes(1, startindex, outbyte, 0, buffersize)
loop
' write the remaining buffer.
bw.write(outbyte, 0 , retval - 1)
bw.flush()
' close the output file.
bw.close()
fs.close()
loop
' close the reader and the connection.
myreader.close()
pubsconn.close()
[c#]
sqlconnection pubsconn = new sqlconnection("data source=localhost;integrated security=sspi;initial catalog=pubs;");
sqlcommand logocmd = new sqlcommand("select pub_id, logo from pub_info", pubsconn);
filestream fs; // writes the blob to a file (*.bmp).
binarywriter bw; // streams the blob to the filestream object.
int buffersize = 100; // size of the blob buffer.
byte[] outbyte = new byte[buffersize]; // the blob byte[] buffer to be filled by getbytes.
long retval; // the bytes returned from getbytes.
long startindex = 0; // the starting position in the blob output.
string pub_id = ""; // the publisher id to use in the file name.
// open the connection and read data into the datareader.
pubsconn.open();
sqldatareader myreader = logocmd.executereader(commandbehavior.sequentialaccess);
while (myreader.read())
{
// get the publisher id, which must occur before getting the logo.
pub_id = myreader.getstring(0);
// create a file to hold the output.
fs = new filestream("logo" + pub_id + ".bmp", filemode.openorcreate, fileaccess.write);
bw = new binarywriter(fs);
// reset the starting byte for the new blob.
startindex = 0;
// read the bytes into outbyte[] and retain the number of bytes returned.
retval = myreader.getbytes(1, startindex, outbyte, 0, buffersize);
// continue reading and writing while there are bytes beyond the size of the buffer.
while (retval == buffersize)
{
bw.write(outbyte);
bw.flush();
// reposition the start index to the end of the last buffer and fill the buffer.
startindex += buffersize;
retval = myreader.getbytes(1, startindex, outbyte, 0, buffersize);
}
// write the remaining buffer.
bw.write(outbyte, 0, (int)retval - 1);
bw.flush();
// close the output file.
bw.close();
fs.close();
}
// close the reader and the connection.
myreader.close();
pubsconn.close();
國內(nèi)最大的酷站演示中心!
