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()));
}
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())); }