Vertica6.1.x对超长字符串的处理

在Vertica5中，对于超长的字符串类型的列在插入/更新的时候会自动将字符串的值揭短成数据库中定义长度，然后将截短后的字符串写入数据库，但是在升级了Vertica和Vertica JDBC驱动到6以后，再执行同样的操作，就会出异常，看下面代码

这里创建了一个表，其中字符串列的长度为32字节

CREATE TABLE t1 (id Integer NOT NULL,col1 Varchar(32),col2 Varchar(32),col3 Varchar(32),CONSTRAINT C_PRIMARY PRIMARY KEY (id));

然后看Java代码，这里使用的是Batch模式插入，使用Batch模式主要是为了有更好的写入性能

package com.googlecode.garbagecan.dbtest.vertica;import java.sql.BatchUpdateException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ParameterMetaData;import java.sql.PreparedStatement;import java.sql.SQLException;import java.util.Arrays;public class VerticaTest {// vertica5 driver// private static final String driver = "com.vertica.Driver";// vertica6 driverprivate static final String driver = "com.vertica.jdbc.Driver";private static final String url = "jdbc:vertica://padev3:5433/padb";private static final String username = "pauser";private static final String password = "papassword";public static void main(String[] args) throws Exception {test();}private static void test() throws Exception {Connection conn = getConnection();PreparedStatement ps = null;try {ps = conn.prepareStatement("insert into t1(id, col1, col2, col3)values(?,?,?,?)");ps.setInt(1, 1);ps.setObject(2, "value1");ps.setObject(3, "value2");ps.setObject(4, "value3");ps.addBatch();ps.setInt(1, 2);ps.setObject(2, "value1");ps.setObject(3, "value2");ps.setObject(4, "=========================================================");ps.addBatch();int[] results = ps.executeBatch();System.out.println("No exception and results: " + Arrays.toString(results));conn.commit();} catch(SQLException ex) {if (ex instanceof BatchUpdateException) {System.out.println("SQLException and results: " + Arrays.toString(((BatchUpdateException) ex).getUpdateCounts()));}conn.rollback();throw ex;} finally {conn.close();}}private static Connection getConnection() throws Exception {Class.forName(driver);Connection conn = DriverManager.getConnection(url, username, password);conn.setAutoCommit(false);return conn;}}

这里向数据库表中用Batch模式插入了两条记录，其中第一条记录的col3的值是小于schema中定义的长度的，而第二条记录的col3的值大于schema。

首先在Vertica5上用Vertica5的JDBC驱动测试，得出如下结果：

No exception and results: [1, 1]

返回结果是[1, 1]说明两条记录都写入成功，查看数据库，第二条记录的col3列的值被截断了。

然后我们在Vertica6上用Vertica6的JDBC驱动再测试一下，得出结果如下：

SQLException and results: [1, -3]Exception in thread "main" java.sql.BatchUpdateException: [Vertica][VJDBC](100172) One or more rows were rejected by the server.at com.vertica.jdbc.SStatement.processBatchResults(Unknown Source)at com.vertica.jdbc.SPreparedStatement.executeBatch(Unknown Source)at com.googlecode.garbagecan.dbtest.vertica.VerticaTest.test(VerticaTest.java:45)at com.googlecode.garbagecan.dbtest.vertica.VerticaTest.main(VerticaTest.java:22)

代码执行的时候出了异常，然后我们在捕获的异常代码块中用下面的代码来获取执行的结果

if (ex instanceof BatchUpdateException) {System.out.println("SQLException and results: " + Arrays.toString(((BatchUpdateException) ex).getUpdateCounts()));}

结果是[1, -3]，说明第一条记录执行成功，第二条执行失败。这里由于在出异常的时候执行了rollback操作，所以数据库里应该没有值。

查看Vertica6.1.x的文档https://my.vertica.com/docs/6.1.x/HTML/index.htm#16701.htm，在新版本中只能自己来trancate超过数据库定义的字符串，所以没办法只能自己干了。下面是更新后的代码：

package com.googlecode.garbagecan.dbtest.vertica;import java.sql.BatchUpdateException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ParameterMetaData;import java.sql.PreparedStatement;import java.sql.SQLException;import java.util.Arrays;public class VerticaTest {private static final String driver = "com.vertica.jdbc.Driver";private static final String url = "jdbc:vertica://padev3:5433/padb";private static final String username = "pauser";private static final String password = "papassword";public static void main(String[] args) throws Exception {test();}private static void test() throws Exception {Connection conn = getConnection();PreparedStatement ps = null;try {ps = conn.prepareStatement("insert into t1(id, col1, col2, col3)values(?,?,?,?)");ps.setInt(1, 1);ps.setObject(2, "value1");ps.setObject(3, "value2");ps.setObject(4, "value3");ps.addBatch();ps.setInt(1, 2);ps.setObject(2, "value1");ps.setObject(3, "value2");ParameterMetaData pmd = ps.getParameterMetaData();String col3 = "=========================================================";if (pmd.getParameterTypeName(4).equalsIgnoreCase("VARCHAR")) {int maxLength = pmd.getPrecision(4);if (col3.toString().length() > maxLength) {col3 = col3.substring(0, maxLength);}}ps.setObject(4, col3);ps.addBatch();int[] results = ps.executeBatch();System.out.println("No exception and results: " + Arrays.toString(results));conn.commit();} catch(SQLException ex) {if (ex instanceof BatchUpdateException) {System.out.println("SQLException and results: " + Arrays.toString(((BatchUpdateException) ex).getUpdateCounts()));}conn.rollback();throw ex;} finally {conn.close();}}private static Connection getConnection() throws Exception {Class.forName(driver);Connection conn = DriverManager.getConnection(url, username, password);conn.setAutoCommit(false);return conn;}}

其中的使用了ParameterMetaData类来获取数据库中定义的列的长度，然后根据定义的长度来truncate数据长度，然后再加入到Batch里执行。下面是修改后代码执行的结果：

No exception and results: [1, 1]

返回结果是[1, 1]说明两条记录都写入成功，查看数据库，第二条记录的col3列的值被截断了。

所以，Vertica的6.1.x的版本和之前版本还是有很多行为不一致的问题，这次在升级的过程中还发现了很多，下次有机会再整理一下。