doris3.0.6创建外部目录,连接sqlserver数据库,创建语句无报错,目录也创建好了,但目录下无法查到数据库
说明:mes账号只有LeanMES数据库的查询权限
CREATE CATALOG sqlserver_catalog_mes PROPERTIES (
'type' = 'jdbc',
'user' = 'mes',
'password' = 'password',
'jdbc_url' = 'jdbc:sqlserver://192.168.0.134:1433;databaseName=LeanMES;encrypt=false',
'driver_url' = 'mssql-jdbc-13.2.0.jre8.jar',
'driver_class' = 'com.microsoft.sqlserver.jdbc.SQLServerDriver'
);
查到的数据库如下
但我们需要的数据库是 LeanMES,模式名是dbo
经过多中验证,连接Mysql数据库就可以正常使用,但连接sqlserver和Oracle都能连接可都查不到数据库和表,下面用Java脚本辅助验证,又能刷出需要的数据库,有哪位大神知道怎么回事吗?
// File: TestJDBCCatalogs.java
import java.sql.*;
public class TestJDBCCatalogs {
public static void main(String[] args) {
String url = "jdbc:sqlserver://192.168.0.134:1433;encrypt=false";
String user = "mes";
String password = "mes123!@#";
try {
// Load JDBC driver
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
System.out.println("? Driver loaded successfully.");
// Establish connection
Connection conn = DriverManager.getConnection(url, user, password);
System.out.println("? Connection to SQL Server established.");
// Get metadata
DatabaseMetaData meta = conn.getMetaData();
// Test 1: List all catalogs (databases) via JDBC
System.out.println("\n?? Testing meta.getCatalogs():");
ResultSet rs1 = meta.getCatalogs();
boolean foundLeanMES = false;
while (rs1.next()) {
String catalogName = rs1.getString("TABLE_CAT");
System.out.println(" Database: " + catalogName);
if ("LeanMES".equalsIgnoreCase(catalogName)) {
foundLeanMES = true;
}
}
if (foundLeanMES) {
System.out.println("? SUCCESS: 'LeanMES' is visible via meta.getCatalogs()");
} else {
System.err.println("? FAILED: 'LeanMES' is NOT visible via meta.getCatalogs()");
}
// Test 2: Manually query sys.databases
System.out.println("\n?? Testing manual query on sys.databases:");
Statement stmt = conn.createStatement();
ResultSet rs2 = stmt.executeQuery("SELECT name FROM sys.databases WHERE state = 0");
while (rs2.next()) {
String dbName = rs2.getString("name");
System.out.println(" Online DB: " + dbName);
}
conn.close();
System.out.println("\n? Connection closed.");
} catch (Exception e) {
e.printStackTrace();
}
}
}
上面这段代码执行java -cp ".:/data/doris/fe/jdbc_drivers/mssql-jdbc-13.2.1.jre11.jar" TestJDBCCatalogs 后,会输出以下内容
? Driver loaded successfully.
? Connection to SQL Server established.
?? Testing meta.getCatalogs():
Database: BackupLocDb_ccdc1751-aa09-4b3c-b9bb-335d81e43d9f
Database: DWConfiguration
Database: DWDiagnostics
Database: DWQueue
Database: LeanMES
Database: LeanMES2020
Database: LeanMES2021
Database: LeanMES-6-26
Database: LeanMESC2023
Database: master
Database: model
Database: msdb
Database: ReportServer
Database: ReportServerTempDB
Database: tempdb
Database: TestB
? SUCCESS: 'LeanMES' is visible via meta.getCatalogs()
?? Testing manual query on sys.databases:
Online DB: master
Online DB: tempdb
Online DB: model
Online DB: msdb
Online DB: ReportServer
Online DB: ReportServerTempDB
Online DB: DWDiagnostics
Online DB: DWConfiguration
Online DB: DWQueue
Online DB: LeanMES2021
Online DB: BackupLocDb_ccdc1751-aa09-4b3c-b9bb-335d81e43d9f
Online DB: LeanMES
Online DB: TestB
Online DB: LeanMES-6-26
Online DB: LeanMESC2023
Online DB: LeanMES2020