Reading, saving, and updating of blob and clob fields in various databases

Some people often talk about reading and writing BLOB fields.

I have been writing something recently. I have made some comparisons on the data types and functions of common databases, and I have also written a lot of tests.Code, The following are my test results:

Test Database: ms SQL Server2000,
db2v8.1,
Oracle9i,
20177.4,
MySQL v4.1,
Interbase v6.5.
1. Driver Program
A. Microsoft's JDBC driver does not support lock update, the driver declaration does not support batch update, but it can work at a high speed. However, the jtds SQL Server JDBC driver can be used to implement lock update.

B. when MySQL sets the character set to GBK, using the parameter or the inserted string field parameter will reference


Exception. Change the character set to gb2312. The manual declares that substring, Len, and other functions are multi-byte safe. In fact, all functions related to the length are operated by byte.
The corresponding functions of his database (except DB2, which is basically the same as MySQL) are different (the corresponding functions of other databases are operated by character ).

C. The best implementation of INTERBASE drivers is that few common JDBC functions are not implemented.

And batch update is not supported. An exception occurs when batch operation (addbatch () is used. You cannot specify the encoding in the URL like other databases.
The getconnection () function brings parameters. Although improvements have been made in version 7.1 (encoding can be saved in the System Object in advance ),
Getconnection () is included in the parameter, but it seems that the encoding cannot be specified in the URL.

2. Data Type

A. Only SQL Server2000 does not provide blob and clob, but the image and text types are provided, corresponding to blob and clob. It is used in the same way.

B. The Blob and clob types of DB2 must be specified.

B. You can use setbytes () of the preparedstatement object for BLOB databases.

C and clob types can be set using setstring () of the preparedstatement object.

Note:The preparedstatement object that represents parameters should be used for insertion and update.

The following code reads and updates the blob and clob Data Types of the Oracle database:

Java code

2. [B] Reading BLOB: [/B]
   
4. Blob blob = Rs. getblob (INDEX );;
6. If
   (Blob! =
   Null
   );{
   
8. Byte
   Buf [] =
   New
    
   Byte
   [(
   Int
   ); Blob. Length ();];
   
10. Inputstream in = blob. getbinarystream ();;
12. Try
    {
    
14. In. Read (BUF );;
16. Ur code
18. }Catch
    (Ioexception E );{
    
20. Throw
     
    New
    Sqlexception ();;
    
22. }
24. }
26. [B] clob Data Type Read: [/B]
28. Clob = Rs. getclob (INDEX );;
30. If
    (Clob! =
    Null
    );{
    
32. Char
    Buf [] =
    New
     
    Char
    [(
    Int
    ); Clob. Length ();];
    
34. Reader reader = Rs. getclob (INDEX);. getcharacterstream ();;
36. Try
    {
    
38. Reader. Read (BUF );;
40. // Your code
    
    
42. }Catch
    (Ioexception E );{
    
44. Throw
     
    New
    Sqlexception ();;
    
46. }
48. }

[B] Reading BLOB: [/B] blob = Rs. getblob (INDEX); If (blob! = NULL); {byte Buf [] = new byte [(INT); blob. length ();]; inputstream in = blob. getbinarystream (); try {In. read (BUF); // your code} catch (ioexception E); {Throw new sqlexception () ;;}} [B] clob Data Type Read: [/B] clob = Rs. getclob (INDEX); If (clob! = NULL); {char Buf [] = new char [(INT); clob. length ();]; reader = Rs. getclob (INDEX );. getcharacterstream (); try {reader. read (BUF); // your code} catch (ioexception E); {Throw new sqlexception ();;}}
 

The above arguments are related to the original author's experience and may be incorrect or one-sided. If you have any knowledge, please point out to the original author so as not to mislead others, or let the original author correct the mistakes on his own.

 

Original reference address: http://www.javaeye.com/topic/5378