Bug #1786 PreparedStatement.setClob() does not work.
Submitted: 9 Nov 2003 15:05 Modified: 9 Nov 2003 16:33
Reporter: Mahmoud Parsian Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:3.1.0-alpha OS:Microsoft Windows (windows 2000 professional)
Assigned to: CPU Architecture:Any

[9 Nov 2003 15:05] Mahmoud Parsian
Description:
PrepareStatement.setClob(pos, clob) did not work:

setClob() passes the first argument as 0 (zero)
    public void setClob(int i, Clob x) throws SQLException {
        setString(i, x.getSubString(0L, (int) x.length()));
    }

and then take a look at Clob.getSubString()
    public String getSubString(long startPos, int length)
        throws SQLException {
        if (startPos < 1) {
            throw new SQLException("CLOB start position can not be < 1", "S1009");
        }

it says if the startPos is < 1 (where 0<1) then throw an exception
This is the error. This means that this will throw an exception
regardless of the size of the clob object.

Thanks.
Regards,
Mahmoud Parsian

How to repeat:
Here is how to repeat:

mysql> create table clob_table (
    -> id VARCHAR(12) NOT NULL PRIMARY KEY,
    -> clob_column TEXT);
Query OK, 0 rows affected (0.10 sec)

mysql> desc clob_table
    -> ;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | varchar(12) |      | PRI |         |       |
| clob_column | text        | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)

mysql> insert into clob_table(id, clob_column)
    -> values('id-1', '123abc');
Query OK, 1 row affected (0.02 sec)

mysql> insert into clob_table(id, clob_column)
    -> values('id-2', '444zzzzz');
Query OK, 1 row affected (0.00 sec)

mysql> select * from clob_table;
+------+-------------+
| id   | clob_column |
+------+-------------+
| id-1 | 123abc      |
| id-2 | 444zzzzz    |
+------+-------------+
2 rows in set (0.00 sec)

import java.util.*;
import java.io.*;
import java.sql.*;

import jcb.db.*;
import jcb.meta.*;
/**
 * @author: Mahmoud Parsian
 */
public class Demo_PreparedStatement_SetClob_MySQL {

	public static Connection getConnection() throws Exception {
		String driver = "org.gjt.mm.mysql.Driver";
    	String url = "jdbc:mysql://localhost/tiger";
    	String username = "root";
    	String password = "root";
		Class.forName(driver);	// load MySQL driver
		Connection conn = DriverManager.getConnection(url, username, password);
		return conn;
	}

	public static void main(String[] args) {

		// set up input parameters:
		String id = args[0];
		String newID = args[1];

    	Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
      		System.out.println("------Demo_PreparedStatement_SetClob_MySQL begin---------");

      		// get a database connection object
      		conn = getConnection();
      		System.out.println("conn="+conn);
      		System.out.println("---------------");

			//
			// begin transaction
			//
			conn.setAutoCommit(false);

      		// prepare blob object from an existing binary column
      		String query1 = "select clob_column from clob_table where id = ?";
			pstmt = conn.prepareStatement(query1);
			pstmt.setString(1, id);
			rs = pstmt.executeQuery();
			rs.next();
			java.sql.Clob clob = rs.getClob(1);
      		System.out.println("clob.length()="+clob.length());

			// prepare SQL query for inserting a new row using setClob()
			String query = "insert into clob_table(id, clob_column) values(?, ?)";

			// create PrepareStatement object
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, newID);
			pstmt.setClob(2, clob);

			//
			// execute query and return number of rows created
			//
			int rowCount = pstmt.executeUpdate();
      		System.out.println("rowCount="+rowCount);

			//
			// end transaction
			//
			conn.commit();			// prepare query

      		System.out.println("------Demo_PreparedStatement_SetClob_MySQL end---------");
        }
		catch(Exception e){
			e.printStackTrace();
			System.exit(1);
		}
		finally {
			// release database resources
			//DatabaseUtil.close(rs);
			//DatabaseUtil.close(pstmt);
			//DatabaseUtil.close(conn);
		}
	}
}

then run

C:\zmp\book\src\client>java Demo_PreparedStatement_SetClob_MySQL   id-1 id-1000
------Demo_PreparedStatement_SetClob_MySQL begin---------
conn=com.mysql.jdbc.Connection@506411
---------------
clob.length()=6
java.sql.SQLException: CLOB start position can not be < 1
        at com.mysql.jdbc.Clob.getSubString(Clob.java:169)
        at com.mysql.jdbc.PreparedStatement.setClob(PreparedStatement.java:534)
        at Demo_PreparedStatement_SetClob_MySQL.main(Demo_PreparedStatement_SetClob_MySQL.java:57)

C:\zmp\book\src\client>

Suggested fix:
According to the java.sql.Clob interface, clob.getSubString(position, length),
the position must start from 1 (not zero). You can fix this by replacing this

    public void setClob(int i, Clob x) throws SQLException {
        setString(i, x.getSubString(0L, (int) x.length()));
    }

with

    public void setClob(int i, Clob x) throws SQLException {
        setString(i, x.getSubString(1L, (int) x.length()));
    }
[9 Nov 2003 16:33] Mark Matthews
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

This has been fixed for quite some time in the source tree. Please see a nightly snapshot of 3.1.x from http://downloads.mysql.com/snapshots.php