tos版本4.0.2
最近同事在弄数据交换,用的是talend open studio(tos),在一个数据库的情况下,使用toracleoutput控件可以实现blob的导入导出。但是在两个数据库的情况下执行涉及好的过程就报错了,报没有表或者视图。很怪异的报错。
我经过查看数据交换工具生成的运行期代码,发现,blob的处理方式是用jdbc 的setObject(blob)方法
原代码如下
pstmt_tOracleOutput_3.setObject(6, rs.getObject(6));
这个方法在两个库之间是不行的。
改用如下代码,可以通过
java.sql.Blob oblob = (java.sql.Blob) (rs.getObject(6));
pstmt_tOracleOutput_3.setObject(6, oblob.getBytes(1, (int) oblob.length()));
通过修改tos下面
TOS-Win32-r43696-V4.0.2\plugins\org.talend.designer.components.localprovider_4.0.2.r43696\components\templates下的db_output_bulk.skeleton文件
generateSetStmt方法,添加else分支
public String generateSetStmt(String typeToGenerate, String dbType, Column column, int index, String incomingConnName, String cid, int actionType) {
boolean isObject = false;
String prefix = null;
if(actionType == NORMAL_TYPE) {
prefix = "pstmt_";
} else if(actionType == INSERT_TYPE) {
prefix = "pstmtInsert_";
} else if(actionType == UPDATE_TYPE) {
prefix = "pstmtUpdate_";
}
StringBuilder setStmt = new StringBuilder();
if(typeToGenerate.equals("Character")) {
isObject = true;
setStmt.append("if(" + incomingConnName + "." + column.getName() + " == null) {\r\n");
setStmt.append(prefix + cid + ".setNull(" + index + ", java.sql.Types.CHAR);\r\n");
} else if(typeToGenerate.equals("Date")) {
isObject = true;
setStmt.append("if(" + incomingConnName + "." + column.getName() + " == null) {\r\n");
setStmt.append(prefix + cid + ".setNull(" + index + ", java.sql.Types.DATE);\r\n");
} else if(typeToGenerate.equals("byte[]")) {
isObject = true;
setStmt.append("if(" + incomingConnName + "." + column.getName() + " == null) {\r\n");
if(dbType != null && (dbType.equals("LONG RAW") || dbType.equals("RAW"))) {
setStmt.append(prefix + cid + ".setBytes(" + index + ", null);\r\n");
} else {
setStmt.append(prefix + cid + ".setNull(" + index + ", java.sql.Types.ARRAY);\r\n");
}
} else if(typeToGenerate.equals("Long") || typeToGenerate.equals("Byte") || typeToGenerate.equals("Integer") || typeToGenerate.equals("Short")) {
isObject = true;
setStmt.append("if(" + incomingConnName + "." + column.getName() + " == null) {\r\n");
setStmt.append(prefix + cid + ".setNull(" + index + ", java.sql.Types.INTEGER);\r\n");
} else if(typeToGenerate.equals("String")) {
isObject = true;
setStmt.append("if(" + incomingConnName + "." + column.getName() + " == null) {\r\n");
if(dbType != null && dbType.equals("CLOB")) {
setStmt.append(prefix + cid + ".setNull(" + index + ", java.sql.Types.CLOB);\r\n");
} else {
setStmt.append(prefix + cid + ".setNull(" + index + ", java.sql.Types.VARCHAR);\r\n");
}
} else if(typeToGenerate.equals("Object")) {
isObject = true;
setStmt.append("if(" + incomingConnName + "." + column.getName() + " == null) {\r\n");
if(dbType != null && dbType.equals("BLOB")) {
setStmt.append(prefix + cid + ".setNull(" + index + ", java.sql.Types.BLOB);\r\n");
} else if("CLOB".equals(dbType)){
setStmt.append(prefix + cid + ".setNull(" + index + ", java.sql.Types.CLOB);\r\n");
} else {
setStmt.append(prefix + cid + ".setNull(" + index + ", java.sql.Types.OTHER);\r\n");
}
} else if(typeToGenerate.equals("Boolean")) {
isObject = true;
setStmt.append("if(" + incomingConnName + "." + column.getName() + " == null) {\r\n");
setStmt.append(prefix + cid + ".setNull(" + index + ", java.sql.Types.BOOLEAN);\r\n");
} else if(typeToGenerate.equals("Double")) {
isObject = true;
setStmt.append("if(" + incomingConnName + "." + column.getName() + " == null) {\r\n");
setStmt.append(prefix + cid + ".setNull(" + index + ", java.sql.Types.DOUBLE);\r\n");
} else if(typeToGenerate.equals("Float")) {
isObject = true;
setStmt.append("if(" + incomingConnName + "." + column.getName() + " == null) {\r\n");
setStmt.append(prefix + cid + ".setNull(" + index + ", java.sql.Types.FLOAT);\r\n");
}
if(isObject) {
setStmt.append("} else {");
}
typeToGenerate = getGenerateType(typeToGenerate);
if(typeToGenerate.equals("Char") || typeToGenerate.equals("Character")) {
if(isObject) {
setStmt.append("if(" + incomingConnName + "." + column.getName() + " == null) {\r\n");
} else {
setStmt.append("if(String.valueOf(" + incomingConnName + "." + column.getName() + ").toLowerCase().equals(\"null\")) {\r\n");
}
setStmt.append(prefix + cid + ".setNull(" + index + ", java.sql.Types.CHAR);\r\n");
setStmt.append("} else if(" + incomingConnName + "." + column.getName() + " == '\0'){\r\n");
setStmt.append(prefix + cid + ".setString(" + index + ", \"\");\r\n");
setStmt.append("} else {\r\n");
setStmt.append(prefix + cid + ".setString(" + index + ", String.valueOf(" + incomingConnName + "." + column.getName() + "));\r\n");
setStmt.append("}");
} else if(typeToGenerate.equals("Date")) {
setStmt.append("if(" + incomingConnName + "." + column.getName() + " != null) {\r\n");
setStmt.append(prefix + cid + ".setTimestamp(" + index + ", new java.sql.Timestamp(" + incomingConnName + "." + column.getName() + ".getTime()));\r\n");
setStmt.append("} else {\r\n");
setStmt.append(prefix + cid + ".setNull(" + index + ", java.sql.Types.DATE);\r\n");
setStmt.append("}\r\n");
} else if(typeToGenerate.equals("Bytes") && (dbType != null && (dbType.equals("LONG RAW") || dbType.equals("RAW")))) {
setStmt.append(prefix + cid + ".setBytes(" + index + ", " + incomingConnName + "." + column.getName() + ");\r\n");
} else if(typeToGenerate.equals("String") && (dbType != null && dbType.equals("CLOB"))) {
setStmt.append(prefix + cid + ".setCharacterStream(" + index + ", new java.io.StringReader(" + incomingConnName + "." + column.getName() + "), " + incomingConnName + "." + column.getName() + ".length());\r\n");
} else if (typeToGenerate.equals("Bytes") && (dbType != null && dbType.equals("BLOB"))) {
setStmt.append(prefix + cid + ".setBinaryStream(" + index + ", new java.io.ByteArrayInputStream((byte[])" + incomingConnName + "." + column.getName() + "), ((byte[])" + incomingConnName + "." + column.getName() + ").length);\r\n");
} else if (typeToGenerate.equals("Object") && (dbType != null && dbType.equals("BLOB"))) {//添加的else代码
setStmt.append("java.sql.Blob oblob = (java.sql.Blob)(" + incomingConnName + "." + column.getName()+");\r\n");
setStmt.append(prefix + cid + ".set" + typeToGenerate + "(" + index + ", oblob.getBytes(1, (int)oblob.length()));\r\n");
}
else {
setStmt.append(prefix + cid + ".set" + typeToGenerate + "(" + index + ", " + incomingConnName + "." + column.getName() + ");\r\n");
}
if(isObject) {
setStmt.append("}\r\n");
}
return setStmt.toString();
}