数据库中取Clob类型字段出现乱码
2024-07-21 02:23:25
供稿：网友
datareader 的默认行为是在整个数据行可用时立即以行的形式加载传入数据。但是，对于二进制大对象 (blob) 则需要进行不同的处理，因为它们可能包含数十亿字节的数据，而单个行中无法包含如此多的数据。command.executereader 方法具有一个重载，它将采用 commandbehavior 参数来修改 datareader 的默认行为。您可以将 commandbehavior.sequentialaccess 传递到 executereader 方法来修改 datareader 的默认行为，以便让 datareader 按照顺序在接收到数据时立即将其加载，而不是加载数据行。这是加载 blob 或其他大数据结构的理想方案。请注意，该行为可能会因数据源的不同而不同。例如，从 microsoft access 中返回 blob 将导致整个 blob 加载到内存中，而不是按接收数据的顺序加载数据。
在将 datareader 设置为使用 sequentialaccess 时，务必要注意访问所返回字段的顺序。datareader 的默认行为是在整个行可用时立即加载该行，这使您能够在读取下一行之前按任何顺序访问所返回的字段。但是，当使用 sequentialaccess 时，必须按顺序访问由 datareader 返回的不同字段。例如，如果查询返回三个列，其中第三列是 blob，则必须在访问第三个字段中的 blob 数据之前返回第一个和第二个字段的值。如果在访问第一个或第二个字段之前访问第三个字段，则第一个和第二个字段值将不再可用。这是因为 sequentialaccess 已修改 datareader，使其按顺序返回数据，当 datareader 已经读取超过特定数据时，该数据将不可用。
在访问 blob 字段中的数据时，请使用 datareader 的 getbytes 或 getchars 类型化访问器，它们将用数据来填充数组。还可以对字符数据使用 getstring，但是为了节省系统资源，您可能不希望将整个 blob 值加载到单个字符串变量中。您可以指定要返回的特定数据缓冲区大小，以及从返回的数据中读取的第一个字节或字符的起始位置。getbytes 和 getchars 将返回一个 long 值，它表示返回的字节或字符数。如果将一个空数组传递给 getbytes 或 getchars，则返回的长值将是 blob 中字符或字符的总数。您可以选择将数组中的某个索引指定为所读取数据的起始位置。
以下示例从 microsoft sql server 中的 pubs 示例数据库中返回发行者 id 和徽标。发行者 id (pub_id) 是字符字段，而徽标则是图形，即 blob。由于 logo 字段是位图，因此该示例使用 getbytes 返回二进制数据。请注意，由于必须按顺序访问字段，所以将在访问徽标之前访问当前数据行的发行者 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();
国内最大的酷站演示中心!
