This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 0bdd078b41 [fix](jdbc catalog) fixed the sqlserver jdbc url parm
concatenation error (#23841)
0bdd078b41 is described below
commit 0bdd078b417369af16792a086c15c0777463c238
Author: zy-kkk <zhongy...@gmail.com>
AuthorDate: Fri Sep 8 09:58:20 2023 +0800
[fix](jdbc catalog) fixed the sqlserver jdbc url parm concatenation error
(#23841)
---
.../org/apache/doris/catalog/JdbcResource.java | 51 ++++++++++--------
.../org/apache/doris/catalog/JdbcResourceTest.java | 63 ++++++++++++++++++++++
.../jdbc/test_sqlserver_jdbc_catalog.out | 15 ++++++
.../jdbc/test_sqlserver_jdbc_catalog.groovy | 13 +++++
4 files changed, 120 insertions(+), 22 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/catalog/JdbcResource.java
b/fe/fe-core/src/main/java/org/apache/doris/catalog/JdbcResource.java
index e9fc898b2e..999b5c4d34 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/catalog/JdbcResource.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/JdbcResource.java
@@ -297,26 +297,26 @@ public class JdbcResource extends Resource {
if (dbType.equals(MYSQL) || dbType.equals(OCEANBASE)) {
// `yearIsDateType` is a parameter of JDBC, and the default is
true.
// We force the use of `yearIsDateType=false`
- newJdbcUrl = checkAndSetJdbcBoolParam(newJdbcUrl,
"yearIsDateType", "true", "false");
+ newJdbcUrl = checkAndSetJdbcBoolParam(dbType, newJdbcUrl,
"yearIsDateType", "true", "false");
// MySQL Types and Return Values for GetColumnTypeName and
GetColumnClassName
// are presented in
https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-type-conversions.html
// When mysql's tinyint stores non-0 or 1, we need to read the
data correctly,
// so we need tinyInt1isBit=false
- newJdbcUrl = checkAndSetJdbcBoolParam(newJdbcUrl, "tinyInt1isBit",
"true", "false");
+ newJdbcUrl = checkAndSetJdbcBoolParam(dbType, newJdbcUrl,
"tinyInt1isBit", "true", "false");
// set useUnicode and characterEncoding to false and utf-8
- newJdbcUrl = checkAndSetJdbcBoolParam(newJdbcUrl, "useUnicode",
"false", "true");
- newJdbcUrl = checkAndSetJdbcBoolParam(newJdbcUrl,
"rewriteBatchedStatements", "false", "true");
- newJdbcUrl = checkAndSetJdbcParam(newJdbcUrl, "characterEncoding",
"utf-8");
+ newJdbcUrl = checkAndSetJdbcBoolParam(dbType, newJdbcUrl,
"useUnicode", "false", "true");
+ newJdbcUrl = checkAndSetJdbcBoolParam(dbType, newJdbcUrl,
"rewriteBatchedStatements", "false", "true");
+ newJdbcUrl = checkAndSetJdbcParam(dbType, newJdbcUrl,
"characterEncoding", "utf-8");
if (dbType.equals(OCEANBASE)) {
// set useCursorFetch to true
- newJdbcUrl = checkAndSetJdbcBoolParam(newJdbcUrl,
"useCursorFetch", "false", "true");
+ newJdbcUrl = checkAndSetJdbcBoolParam(dbType, newJdbcUrl,
"useCursorFetch", "false", "true");
}
}
if (dbType.equals(POSTGRESQL)) {
- newJdbcUrl = checkAndSetJdbcBoolParam(newJdbcUrl,
"reWriteBatchedInserts", "false", "true");
+ newJdbcUrl = checkAndSetJdbcBoolParam(dbType, newJdbcUrl,
"reWriteBatchedInserts", "false", "true");
}
if (dbType.equals(SQLSERVER)) {
- newJdbcUrl = checkAndSetJdbcBoolParam(newJdbcUrl,
"useBulkCopyForBatchInsert", "false", "true");
+ newJdbcUrl = checkAndSetJdbcBoolParam(dbType, newJdbcUrl,
"useBulkCopyForBatchInsert", "false", "true");
}
return newJdbcUrl;
}
@@ -332,21 +332,19 @@ public class JdbcResource extends Resource {
* @param expectedVal
* @return
*/
- private static String checkAndSetJdbcBoolParam(String jdbcUrl, String
params, String unexpectedVal,
+ private static String checkAndSetJdbcBoolParam(String dbType, String
jdbcUrl, String params, String unexpectedVal,
String expectedVal) {
+ String delimiter = getDelimiter(jdbcUrl, dbType);
String unexpectedParams = params + "=" + unexpectedVal;
String expectedParams = params + "=" + expectedVal;
+
if (jdbcUrl.contains(expectedParams)) {
return jdbcUrl;
} else if (jdbcUrl.contains(unexpectedParams)) {
jdbcUrl = jdbcUrl.replaceAll(unexpectedParams, expectedParams);
} else {
- if (jdbcUrl.contains("?")) {
- if (jdbcUrl.charAt(jdbcUrl.length() - 1) != '?') {
- jdbcUrl += "&";
- }
- } else {
- jdbcUrl += "?";
+ if (!jdbcUrl.endsWith(delimiter)) {
+ jdbcUrl += delimiter;
}
jdbcUrl += expectedParams;
}
@@ -361,20 +359,29 @@ public class JdbcResource extends Resource {
* @param params
* @return
*/
- private static String checkAndSetJdbcParam(String jdbcUrl, String params,
String expectedVal) {
+ private static String checkAndSetJdbcParam(String dbType, String jdbcUrl,
String params, String expectedVal) {
+ String delimiter = getDelimiter(jdbcUrl, dbType);
String expectedParams = params + "=" + expectedVal;
+
if (jdbcUrl.contains(expectedParams)) {
return jdbcUrl;
} else {
- if (jdbcUrl.contains("?")) {
- if (jdbcUrl.charAt(jdbcUrl.length() - 1) != '?') {
- jdbcUrl += "&";
- }
- } else {
- jdbcUrl += "?";
+ if (!jdbcUrl.endsWith(delimiter)) {
+ jdbcUrl += delimiter;
}
jdbcUrl += expectedParams;
}
return jdbcUrl;
}
+
+ private static String getDelimiter(String jdbcUrl, String dbType) {
+ if (dbType.equals(SQLSERVER)) {
+ return ";";
+ } else if (jdbcUrl.contains("?")) {
+ return "&";
+ } else {
+ return "?";
+ }
+ }
+
}
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/catalog/JdbcResourceTest.java
b/fe/fe-core/src/test/java/org/apache/doris/catalog/JdbcResourceTest.java
new file mode 100644
index 0000000000..e91898fb82
--- /dev/null
+++ b/fe/fe-core/src/test/java/org/apache/doris/catalog/JdbcResourceTest.java
@@ -0,0 +1,63 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package org.apache.doris.catalog;
+
+import org.apache.doris.common.DdlException;
+
+import org.junit.Assert;
+import org.junit.Test;
+
+public class JdbcResourceTest {
+
+ @Test
+ public void testHandleJdbcUrlForMySql() throws DdlException {
+ String inputUrl = "jdbc:mysql://127.0.0.1:3306/test";
+ String resultUrl = JdbcResource.handleJdbcUrl(inputUrl);
+
+ // Check if the result URL contains the necessary delimiters for MySQL
+ Assert.assertTrue(resultUrl.contains("?"));
+ Assert.assertTrue(resultUrl.contains("&"));
+ }
+
+ @Test
+ public void testHandleJdbcUrlForSqlServerWithoutParams() throws
DdlException {
+ String inputUrl =
"jdbc:sqlserver://43.129.237.12:1433;databaseName=doris_test";
+ String resultUrl = JdbcResource.handleJdbcUrl(inputUrl);
+
+ // Ensure that the result URL for SQL Server doesn't have '?' or '&'
+ Assert.assertFalse(resultUrl.contains("?"));
+ Assert.assertFalse(resultUrl.contains("&"));
+
+ // Ensure the result URL still contains ';'
+ Assert.assertTrue(resultUrl.contains(";"));
+ }
+
+ @Test
+ public void testHandleJdbcUrlForSqlServerWithParams() throws DdlException {
+ String inputUrl =
"jdbc:sqlserver://43.129.237.12:1433;encrypt=false;databaseName=doris_test;trustServerCertificate=false";
+ String resultUrl = JdbcResource.handleJdbcUrl(inputUrl);
+
+ // Ensure that the result URL for SQL Server doesn't have '?' or '&'
+ Assert.assertFalse(resultUrl.contains("?"));
+ Assert.assertFalse(resultUrl.contains("&"));
+
+ // Ensure the result URL still contains ';'
+ Assert.assertTrue(resultUrl.contains(";"));
+ }
+}
+
diff --git
a/regression-test/data/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.out
b/regression-test/data/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.out
index c8053812c1..17ddfbfee8 100644
---
a/regression-test/data/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.out
+++
b/regression-test/data/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.out
@@ -56,3 +56,18 @@
-- !id --
2
+-- !sql --
+INFORMATION_SCHEMA
+db_accessadmin
+db_backupoperator
+db_datareader
+db_datawriter
+db_ddladmin
+db_denydatareader
+db_denydatawriter
+db_owner
+db_securityadmin
+dbo
+guest
+sys
+
diff --git
a/regression-test/suites/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.groovy
b/regression-test/suites/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.groovy
index 6ced9b7d53..65a85a8fd3 100644
---
a/regression-test/suites/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.groovy
+++
b/regression-test/suites/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.groovy
@@ -73,6 +73,19 @@ suite("test_sqlserver_jdbc_catalog",
"p0,external,sqlserver,external_docker,exte
order_qt_id """ select count(*) from (select * from t_id) as a; """
+ sql """ drop catalog if exists ${catalog_name} """
+
+ sql """ create catalog if not exists ${catalog_name} properties(
+ "type"="jdbc",
+ "user"="sa",
+ "password"="Doris123456",
+ "jdbc_url" =
"jdbc:sqlserver://${externalEnvIp}:${sqlserver_port};encrypt=false;databaseName=doris_test;trustServerCertificate=false",
+ "driver_url" = "${driver_url}",
+ "driver_class" =
"com.microsoft.sqlserver.jdbc.SQLServerDriver"
+ );"""
+
+ order_qt_sql """ show databases from ${catalog_name} """
+
sql """ drop catalog if exists ${catalog_name} """
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
