You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by bp...@apache.org on 2009/08/11 05:40:09 UTC
svn commit: r802985 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/loc/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: bpendleton
Date: Tue Aug 11 03:40:09 2009
New Revision: 802985
URL: http://svn.apache.org/viewvc?rev=802985&view=rev
Log:
DERBY-4256: Allow ALTER TABLE to increase the max size of BLOB or CLOB
This patch was contributed by Eranda Sooriyabandara (070468D at gmail dot com)
This patch enhances the Derby ALTER TABLE command so that it allows the
maximum length limit of a BLOB or CLOB column to be increased. The patch also
adds some new regression tests verifying the new functionality, and adjusts
the text of the ALTER TABLE error message to make it more clear that it is
legal to increase the length of a VARCHAR, BLOB, or CLOB column.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java
db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/modifyColumn.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java?rev=802985&r1=802984&r2=802985&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java Tue Aug 11 03:40:09 2009
@@ -116,7 +116,9 @@
// can only alter the length of varchar, bitvarying columns
String typeName = getType().getTypeName();
if (!(typeName.equals(TypeId.VARCHAR_NAME)) &&
- !(typeName.equals(TypeId.VARBIT_NAME)))
+ !(typeName.equals(TypeId.VARBIT_NAME))&&
+ !(typeName.equals(TypeId.BLOB_NAME))&&
+ !(typeName.equals(TypeId.CLOB_NAME)))
{
throw StandardException.newException(
SQLState.LANG_MODIFY_COLUMN_INVALID_TYPE);
Modified: db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml?rev=802985&r1=802984&r2=802985&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml Tue Aug 11 03:40:09 2009
@@ -2491,7 +2491,7 @@
<msg>
<name>42Z16</name>
- <text>Only columns of type VARCHAR may have their length altered. </text>
+ <text>Only columns of type VARCHAR, CLOB, and BLOB may have their length altered. </text>
</msg>
<msg>
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/modifyColumn.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/modifyColumn.out?rev=802985&r1=802984&r2=802985&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/modifyColumn.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/modifyColumn.out Tue Aug 11 03:40:09 2009
@@ -29,15 +29,15 @@
ij> -- lets start with negative tests first.
alter table alltypes alter c set data type char(20);
-ERROR 42Z16: Only columns of type VARCHAR may have their length altered.
+ERROR 42Z16: Only columns of type VARCHAR, CLOB, and BLOB may have their length altered.
ij> alter table alltypes alter b set data type char(4) for bit data;
-ERROR 42Z16: Only columns of type VARCHAR may have their length altered.
+ERROR 42Z16: Only columns of type VARCHAR, CLOB, and BLOB may have their length altered.
ij> alter table alltypes alter nc set data type char(20);
-ERROR 42Z16: Only columns of type VARCHAR may have their length altered.
+ERROR 42Z16: Only columns of type VARCHAR, CLOB, and BLOB may have their length altered.
ij> alter table alltypes alter dc set data type decimal (8,2);
-ERROR 42Z16: Only columns of type VARCHAR may have their length altered.
+ERROR 42Z16: Only columns of type VARCHAR, CLOB, and BLOB may have their length altered.
ij> alter table alltypes alter n set data type numeric (12,8);
-ERROR 42Z16: Only columns of type VARCHAR may have their length altered.
+ERROR 42Z16: Only columns of type VARCHAR, CLOB, and BLOB may have their length altered.
ij> alter table alltypes alter c set data type varchar(10);
ERROR 42Z15: Invalid type specified for column 'C'. The type of a column may not be changed.
ij> alter table alltypes alter b set data type varchar(2) for bit data;
@@ -45,7 +45,7 @@
ij> alter table alltypes alter dc set data type numeric(8,2);
ERROR 42Z15: Invalid type specified for column 'DC'. The type of a column may not be changed.
ij> alter table alltypes alter tn set data type int;
-ERROR 42Z16: Only columns of type VARCHAR may have their length altered.
+ERROR 42Z16: Only columns of type VARCHAR, CLOB, and BLOB may have their length altered.
ij> alter table alltypes alter v set data type varchar(1);
ERROR 42Z17: Invalid length specified for column 'V'. Length must be greater than the current column length.
ij> alter table alltypes alter v set data type varchar(49);
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java?rev=802985&r1=802984&r2=802985&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java Tue Aug 11 03:40:09 2009
@@ -19,9 +19,12 @@
*/
package org.apache.derbyTesting.functionTests.tests.lang;
+import java.io.InputStream;
+import java.sql.Blob;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.CallableStatement;
+import java.sql.Clob;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLWarning;
@@ -32,6 +35,7 @@
import java.sql.SQLException;
import junit.framework.Test;
import junit.framework.TestSuite;
+import org.apache.derbyTesting.functionTests.util.TestInputStream;
import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.TestConfiguration;
@@ -2653,9 +2657,77 @@
assertStatementError("42X14", st,
" alter table \"d3355_a\" alter column \"c1\" not null");
}
+
+ public void testJira4256() throws SQLException{
+
+ Statement st = createStatement();
+ createTestObjects(st);
+
+ //increase the maximum size of the clob
+
+ Clob clob = null;
+ Blob blob=null;
+ int val = 1;
+ int size = 15 * 1024;
+ InputStream stream;
+
+ st.executeUpdate("create table clob_tab(c1 int,clob_col clob(10K))");
+ conn.commit();
+
+ pSt=conn.prepareStatement("INSERT INTO clob_tab values (?,?)");
+ stream = new TestInputStream(size, val);
+
+ //this insert fails(size>10K)
+ pSt.setInt(1, val);
+ pSt.setAsciiStream(2, stream, size);
+ assertStatementError("XJ001", pSt);
+ pSt.close();
+
+ conn.rollback();
+
+ st.executeUpdate("ALTER TABLE clob_tab ALTER COLUMN "
+ +"clob_col SET DATA TYPE clob(20K)");
+
+ pSt=conn.prepareStatement("INSERT INTO clob_tab values (?,?)");
+ stream = new TestInputStream(size, val);
+
+ //this insert succeed (maximum blob size not increased to 20K)
+ pSt.setInt(1, val);
+ pSt.setAsciiStream(2, stream, size);
+ pSt.executeUpdate();
+ pSt.close();
+
+
+ //increase the maximum size of the blob
+
+ st.executeUpdate("CREATE TABLE blob_tab ( C1 INTEGER," +
+ "blob_col BLOB(10K) NOT NULL)");
+
+ conn.commit();
+
+ pSt=conn.prepareStatement("INSERT INTO blob_tab values (?,?)");
+ stream = new TestInputStream(size, val);
+
+ //this insert fails(size>10K)
+ pSt.setInt(1, val);
+ pSt.setBinaryStream(2, stream, size);
+ assertStatementError("22001", pSt);
+ pSt.close();
+
+ conn.rollback();
+
+ st.executeUpdate("ALTER TABLE blob_tab ALTER COLUMN "
+ +"blob_col SET DATA TYPE blob(20K)");
+
+ pSt=conn.prepareStatement("INSERT INTO blob_tab values (?,?)");
+ stream = new TestInputStream(size, val);
+
+ //this insert succeed (maximum blob size not increased to 20K)
+ pSt.setInt(1, val);
+ pSt.setBinaryStream(2, stream, size);
+ pSt.executeUpdate();
+ pSt.close();
+
+ conn.rollback();
+ }
}
-
-
-
-
-