This is an automated email from the ASF dual-hosted git repository. kxiao pushed a commit to branch branch-2.0 in repository https://gitbox.apache.org/repos/asf/doris.git
commit 669f4b3e8b33fcbd3797835fee7ab43f54d74de3 Author: lsy3993 <[email protected]> AuthorDate: Thu Jul 27 15:49:15 2023 +0800 [feature](catalog) support clob type in oracle jdbc catalog (#21532) --- .../docker-compose/oracle/init/03-create-table.sql | 5 +++ .../docker-compose/oracle/init/04-insert.sql | 2 + .../java/org/apache/doris/jdbc/JdbcExecutor.java | 50 ++++++++++++++++++++++ .../doris/external/jdbc/JdbcOracleClient.java | 2 +- .../jdbc_catalog_p0/test_oracle_jdbc_catalog.out | 4 ++ .../test_oracle_jdbc_catalog.groovy | 13 ++++++ 6 files changed, 75 insertions(+), 1 deletion(-) diff --git a/docker/thirdparties/docker-compose/oracle/init/03-create-table.sql b/docker/thirdparties/docker-compose/oracle/init/03-create-table.sql index 7d2b292125..c5f6b69121 100644 --- a/docker/thirdparties/docker-compose/oracle/init/03-create-table.sql +++ b/docker/thirdparties/docker-compose/oracle/init/03-create-table.sql @@ -107,3 +107,8 @@ create table doris_test.test_number4 ( id number(11) not null primary key, num1 NUMBER(5,-7) ); + +create table doris_test.test_clob ( + id number(11) not null primary key, + num1 clob +); diff --git a/docker/thirdparties/docker-compose/oracle/init/04-insert.sql b/docker/thirdparties/docker-compose/oracle/init/04-insert.sql index a5eb3bd5d1..ff4a38afce 100644 --- a/docker/thirdparties/docker-compose/oracle/init/04-insert.sql +++ b/docker/thirdparties/docker-compose/oracle/init/04-insert.sql @@ -75,4 +75,6 @@ insert into doris_test.test_number3 values (4, 0.99999); insert into doris_test.test_number4 values (1, 12345678); insert into doris_test.test_number4 values (2, 123456789012); +insert into doris_test.test_clob values (10086, 'yidong'); +insert into doris_test.test_clob values (10010, 'liantong'); commit; diff --git a/fe/be-java-extensions/jdbc-scanner/src/main/java/org/apache/doris/jdbc/JdbcExecutor.java b/fe/be-java-extensions/jdbc-scanner/src/main/java/org/apache/doris/jdbc/JdbcExecutor.java index 42ade80066..e2d720d27d 100644 --- a/fe/be-java-extensions/jdbc-scanner/src/main/java/org/apache/doris/jdbc/JdbcExecutor.java +++ b/fe/be-java-extensions/jdbc-scanner/src/main/java/org/apache/doris/jdbc/JdbcExecutor.java @@ -1520,6 +1520,53 @@ public class JdbcExecutor { UdfUtils.copyMemory(bytes, UdfUtils.BYTE_ARRAY_OFFSET, null, bytesAddr, offsets[numRows - 1]); } + private void oracleClobToString(Object[] column, boolean isNullable, int numRows, long nullMapAddr, + long offsetsAddr, long charsAddr) { + int[] offsets = new int[numRows]; + byte[][] byteRes = new byte[numRows][]; + int offset = 0; + if (isNullable) { + for (int i = 0; i < numRows; i++) { + if (column[i] == null) { + byteRes[i] = emptyBytes; + UdfUtils.UNSAFE.putByte(nullMapAddr + i, (byte) 1); + } else { + try { + oracle.sql.CLOB clob = (oracle.sql.CLOB) column[i]; + String result = clob.getSubString(1, (int) clob.length()); + byteRes[i] = result.getBytes(StandardCharsets.UTF_8); + } catch (Exception e) { + LOG.info("clobToString have error when convert " + e.getMessage()); + } + } + offset += byteRes[i].length; + offsets[i] = offset; + } + } else { + for (int i = 0; i < numRows; i++) { + try { + oracle.sql.CLOB clob = (oracle.sql.CLOB) column[i]; + String result = clob.getSubString(1, (int) clob.length()); + byteRes[i] = result.getBytes(StandardCharsets.UTF_8); + } catch (Exception e) { + LOG.info("clobToString have error when convert " + e.getMessage()); + } + offset += byteRes[i].length; + offsets[i] = offset; + } + } + byte[] bytes = new byte[offsets[numRows - 1]]; + long bytesAddr = JNINativeMethod.resizeStringColumn(charsAddr, offsets[numRows - 1]); + int dst = 0; + for (int i = 0; i < numRows; i++) { + for (int j = 0; j < byteRes[i].length; j++) { + bytes[dst++] = byteRes[i][j]; + } + } + UdfUtils.copyMemory(offsets, UdfUtils.INT_ARRAY_OFFSET, null, offsetsAddr, numRows * 4L); + UdfUtils.copyMemory(bytes, UdfUtils.BYTE_ARRAY_OFFSET, null, bytesAddr, offsets[numRows - 1]); + } + private void objectPutToString(Object[] column, boolean isNullable, int numRows, long nullMapAddr, long offsetsAddr, long charsAddr) { int[] offsets = new int[numRows]; @@ -1728,6 +1775,9 @@ public class JdbcExecutor { || tableType == TOdbcTableType.OCEANBASE)) { // for mysql bytea type byteaPutToMySQLString(column, isNullable, numRows, nullMapAddr, offsetsAddr, charsAddr); + } else if (column[firstNotNullIndex] instanceof oracle.sql.CLOB && tableType == TOdbcTableType.ORACLE) { + // for oracle clob type + oracleClobToString(column, isNullable, numRows, nullMapAddr, offsetsAddr, charsAddr); } else { // object like in pg type point, polygon, jsonb..... get object is // org.postgresql.util.PGobject..... diff --git a/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcOracleClient.java b/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcOracleClient.java index 52700dd3f3..2dd11fe2a7 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcOracleClient.java +++ b/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcOracleClient.java @@ -114,9 +114,9 @@ public class JdbcOracleClient extends JdbcClient { case "RAW": case "LONG RAW": case "INTERVAL": + case "CLOB": return ScalarType.createStringType(); case "BLOB": - case "CLOB": case "NCLOB": case "BFILE": case "BINARY_FLOAT": diff --git a/regression-test/data/jdbc_catalog_p0/test_oracle_jdbc_catalog.out b/regression-test/data/jdbc_catalog_p0/test_oracle_jdbc_catalog.out index 9fe36e5634..a6733d6a3f 100644 --- a/regression-test/data/jdbc_catalog_p0/test_oracle_jdbc_catalog.out +++ b/regression-test/data/jdbc_catalog_p0/test_oracle_jdbc_catalog.out @@ -110,3 +110,7 @@ DORIS_TEST 2 -99 -9999 -999999999 -999999999999999999 -999 -99999 -9999999999 -9999999999999999999 3 10 100 999999999 999999999999999999 999 99999 9999999999 9999999999999999999 +-- !query_clob -- +10010 liantong +10086 yidong + diff --git a/regression-test/suites/jdbc_catalog_p0/test_oracle_jdbc_catalog.groovy b/regression-test/suites/jdbc_catalog_p0/test_oracle_jdbc_catalog.groovy index a1fc12761b..ce85b618c0 100644 --- a/regression-test/suites/jdbc_catalog_p0/test_oracle_jdbc_catalog.groovy +++ b/regression-test/suites/jdbc_catalog_p0/test_oracle_jdbc_catalog.groovy @@ -137,5 +137,18 @@ suite("test_oracle_jdbc_catalog", "p0") { sql """drop catalog if exists ${catalog_name} """ + // test for clob type + sql """create catalog if not exists ${catalog_name} properties( + "type"="jdbc", + "user"="doris_test", + "password"="123456", + "jdbc_url" = "jdbc:oracle:thin:@127.0.0.1:${oracle_port}:${SID}", + "driver_url" = "https://doris-community-test-1308700295.cos.ap-hongkong.myqcloud.com/jdbc_driver/ojdbc8.jar";, + "driver_class" = "oracle.jdbc.driver.OracleDriver", + "lower_case_table_names" = "true" + );""" + sql """ switch ${catalog_name} """ + qt_query_clob """ select * from doris_test.test_clob order by id; """ + } } --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
