`

Inserting CLOB Values with setCharacterStream() Method

阅读更多
If you want to insert the entire content of a text file into a CLOB column, you should create a Reader object from this file, and use the PreparedStatement.setCharacterStream() method to pass the text file content to the CLOB column through the Reader object. There are 3 versions of the setCharacterStream() method, two of them were added as part of JDBC 4.0 (Java 1.6). Your JDBC driver may not support them:

setCharacterStream(int parameterIndex, Reader reader) - The data will be read from the Reader stream as needed until end-of-file is reached. This was added in JDBC 4.0 (Java 1.6).
setCharacterStream(int parameterIndex, Reader reader, int length) - The data will be read from the Reader stream as needed for "length" characters.
setCharacterStream(int parameterIndex, Reader reader, long length) - The data will be read from the Reader stream as needed for "length" characters. This was added in JDBC 4.0 (Java 1.6).
To test those setCharacterStream() methods, wrote the following program:

/**
 * SqlServerClobSetCharacterStream.java
 * Copyright (c) 2007 by Dr. Herong Yang. All rights reserved.
 */
import java.io.*;
import java.sql.*;
public class SqlServerClobSetCharacterStream {
  public static void main(String [] args) {
    Connection con = null;
    try {
      com.microsoft.sqlserver.jdbc.SQLServerDataSource ds 
        = new com.microsoft.sqlserver.jdbc.SQLServerDataSource();
      ds.setServerName("localhost");
      ds.setPortNumber(1269);
      ds.setDatabaseName("AdventureWorksLT");
      ds.setUser("Herong");
      ds.setPassword("TopSecret");
      con = ds.getConnection();

// Deleting the record for re-testing
      String subject = "Test of setCharacterStream() methods";
      Statement sta = con.createStatement(); 
      sta.executeUpdate("DELETE FROM Article WHERE Subject = '"
        +subject+"'");

// Inserting CLOB value with a PreparedStatement
      PreparedStatement ps = con.prepareStatement(
        "INSERT INTO Article (Subject, Body) VALUES (?,?)");
      ps.setString(1, subject);
      Reader bodyIn = 
        new FileReader("SqlServerClobSetCharacterStream.java");

// Test 1 - This will not work with JDBC 3.0 drivers
//    ps.setCharacterStream(2, bodyIn);

// Test 2 - This will not work with JDBC 3.0 drivers
//    File fileIn = new File("SqlServerClobSetCharacterStream.java");
//    ps.setCharacterStream(2, bodyIn, fileIn.length());

// Test 3 - This works with JDBC 3.0 drivers
      File fileIn = new File("SqlServerClobSetCharacterStream.java");
      ps.setCharacterStream(2, bodyIn, (int) fileIn.length());

      int count = ps.executeUpdate();
      bodyIn.close();
      ps.close();

// Retrieving CLOB value with getString()
      sta = con.createStatement(); 
      ResultSet res = sta.executeQuery("SELECT * FROM Article" 
        +" WHERE Subject = '"+subject+"'");
      res.next();
      System.out.println("The inserted record: "); 
      System.out.println("   Subject = "+res.getString("Subject"));
      System.out.println("   Body = "
        +res.getString("Body").substring(0,256)); 
      res.close();

      sta.close();
      con.close();
    } catch (Exception e) {
      System.err.println("Exception: "+e.getMessage());
      e.printStackTrace();
    }
  }
}
As I mentioned above setCharacterStream(int parameterIndex, Reader reader) will not work with JDBC 3.0 drivers. Here is what I got with the "Test 1" section open in my program. Remember that SQL Server JDBC driver 1.0 is a JDBC 3.0 driver.

C:\>java -cp .;\local\lib\sqljdbc.jar SqlServerClobSetCharacterStream

Exception in thread "main" java.lang.AbstractMethodError: com
  .microsoft.sqlserver.jdbc.SQLServerPreparedStatement
  .setCharacterStream(ILjava/io/Reader;)V
  at SqlServerClobSetCharacterStream.main(SqlServerClobSet...java:34)

"Test 2" also failed for the same reason - fileIn.length() returns "long" and that setCharacterStream(int, Reader, long) is JDBC 4.0 method:

C:\>java -cp .;\local\lib\sqljdbc.jar SqlServerClobSetCharacterStream

Exception in thread "main" java.lang.AbstractMethodError: com
  .microsoft.sqlserver.jdbc.SQLServerPreparedStatement
  .setCharacterStream(ILjava/io/Reader;J)V
  at SqlServerClobSetCharacterStream.main(SqlServerClobSet...java:38)



分享到:
评论

相关推荐

    Drag and Drop Inserting Text to Input Textbox with jQuery.zip

    在这个"Drag and Drop Inserting Text to Input Textbox with jQuery"的项目中,我们关注的是如何利用jQuery库来实现这一功能,特别是在输入文本框中插入拖放文本的场景。jQuery是一个流行的JavaScript库,它简化了...

    FlexGraphics_V_1.79_D4-XE10.2_Downloadly.ir

    - ADD: Add TFlexPanel.InvalidateControl virtual method which calls from TFlexControl.Invalidate and can be overriden (it is possible now to catch all object invalidation calls). - FIX: The TFlexPanel....

    Build iOS Database Apps with Swift and SQLite

    Readers will learn about SQL operations like selecting, inserting, updating and deleting data using various data types like text, numerical types, images and even audio/video data. After working ...

    UNIDAC 6.4.16 XE10~10.1

    Bug with inserting identity fields through TUniLoader in the Direct mode is fixed Bug with updating a field containing a space in an alias in the Direct mode is fixed Bug with setting FilterSQL to SQL...

    UNDAC 6.4.16 XE10.1

    Bug with inserting identity fields through TUniLoader in the Direct mode is fixed Bug with updating a field containing a space in an alias in the Direct mode is fixed Bug with setting FilterSQL to SQL...

    UNIDAC 6.4.16 XE8

    Bug with inserting identity fields through TUniLoader in the Direct mode is fixed Bug with updating a field containing a space in an alias in the Direct mode is fixed Bug with setting FilterSQL to SQL...

    UNIDAC 6.4.16 XE10

    Bug with inserting identity fields through TUniLoader in the Direct mode is fixed Bug with updating a field containing a space in an alias in the Direct mode is fixed Bug with setting FilterSQL to SQL...

    5822578_System_for_inserting_instruction.pdf

    CPU: _System_for_inserting_instruction

    互联网 社区结构算法

    This method is based on sequentially inserting the constituent links to the network and simultaneously keeping track of the emerging community structure. Unlike existing algorithms, the SCP method ...

    Modern Data Access with Entity Framework Core

    Modern Data Access with Entity Framework Core: Database Programming Techniques for .NET, .NET Core, UWP, and Xamarin with C# By 作者: Holger Schwichtenberg ISBN-10 书号: 1484235517 ISBN-13 书号: ...

    TMS Pack for FireMonkey2.3.0.1

    Fixed : Issue with default values for ArrowHeight and ArrowWidth in TTMSFMXPopup Fixed : Issue with focusing calendar in TTMSFMXCalendar Fixed : Issue with lookuplist in TTMSFMXEdit in XE3 Fixed :...

    Inserting Record Into Mongodb.zip

    print(f"Records inserted with IDs: {results.inserted_ids}") ``` 在上述代码中,我们一次性插入了两个用户记录,并打印出所有新插入文档的ID。 除了基本的插入操作,Python的`pymongo`库还提供了丰富的功能,如...

    ApiHooks for Win32

    ApiHooks allows inserting module (with hooks) into the specified process. ApiHooks exports EstablishApiHooks functions for usage in your programs. What is it good for? -------------------- File ...

    DWARF(WORD版)及官方网站的DWARF教程.rar

    may be the preferred method. There are lowlevel debuggers that allow you to step through the executable program, instruction by instruction, displaying registers and memory contents in binary.

    iPhone Coding Tutorial – Inserting A UITextField In A UIAlertView

    这篇教程“iPhone Coding Tutorial – Inserting A UITextField In A UIAlertViewController”将会教你如何在现代iOS环境中,在警告视图控制器中添加一个文本字段。 首先,我们需要了解`UIAlertController`的基本...

    ATM.rar_cvv_magnetic_smartcard

    On most modern ATMs, the customer is identified by inserting a plastic ATM card with a magnetic stripe or a plastic smartcard with a chip, that contains a unique card number and some security ...

Global site tag (gtag.js) - Google Analytics