在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 driver
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");
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的版本和之前版本还是有很多行为不一致的问题,这次在升级的过程中还发现了很多,下次有机会再整理一下。