http://starrocks.io logo
Join Slack
Powered by
Hello! I have a SQL Server connected to StarRocks ...
# questions-and-troubleshooting
a
Hello! I have a SQL Server connected to StarRocks as an External Catalog: 
CREATE
 
EXTERNAL
 
*CATALOG* mib_sql_server
PROPERTIES (
*"type"*=*"jdbc"*,
*"user"*=*"xxxxx"*,
*"password"*=*"xxxxx"*,	*"jdbc_uri"*=*"jdbc:sqlserver://;serverName=xxxx;databaseName=xxx;encrypt=true;trustServerCertificate=true;"*,
*"driver_url"*=*"<https://github.com/microsoft/mssql-jdbc/releases/download/v13.2.1/mssql-jdbc-13.2.1.jre11.jar>"*,
*"driver_class"*=*"com.microsoft.sqlserver.jdbc.SQLServerDriver"*,
*"sessionVariables"*=*"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"*
);
The table GVP_CONTENTS_LABELS has a column called "ORDER", reserved. I'm supposed to escape ORDER by using backticks. `*SELECT* ID, *`ORDER`*` 
*FROM* mib_sql_server.dbo.GVP_CONTENTS_LABELS;
But this fails with the following error 
SQL Error [1064] [42000]: open JDBCScanner failed, error: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'ORDER'.
Surprisingly, this command works fine: `EXPLAIN SELECT ID, *`ORDER`*` 
FROM
 
mib_sql_server.dbo.GVP_CONTENTS_LABELS;
Explain String                                               |
-------------------------------------------------------------+
PLAN FRAGMENT 0                                              |
OUTPUT EXPRS:ID | ORDER                                     |
PARTITION: UNPARTITIONED                                   |
|
RESULT SINK                                                |
|
0:SCAN JDBC                                                |
TABLE: [dbo].[GVP_CONTENTS_LABELS]                      |
QUERY: SELECT ID, ORDER FROM [dbo].[GVP_CONTENTS_LABELS]|
Is this a bug, related to the mapping from StarRocks to SQL Server? Is there a way to make it work without "touching" the SQL Server?
I have managed to use the ORDER column, but using the CREATE EXTERNAL TABLE method, which is deprecated: CREATE EXTERNAL RESOURCE sqlserver_gvp_resource PROPERTIES ( "type" = "jdbc", "jdbc_uri" = *"jdbc:sqlserver://xxxx:1433;databaseName=xxx;encrypt=true;trustServerCertificate=true;"*, "driver_url"="https://github.com/microsoft/mssql-jdbc/releases/download/v13.2.1/mssql-jdbc-13.2.1.jre11.jar", "driver_class" = "com.microsoft.sqlserver.jdbc.SQLServerDriver", "user" = "xxxx", "password" = "xxxx" ); CREATE EXTERNAL TABLE GVP_CONTENTS_LABELS_SR ( ID INT, DATEINS DATETIME, OWNER INT NULL, NAME *VARCHAR*(255), TAG_ID INT NULL, CONTENT_ID INT NULL, CONTENT_ORDER INT NULL, SCORING FLOAT NULL ) ENGINE=jdbc PROPERTIES ( "resource" = "sqlserver_gvp_resource", "database" = "gvp", "table"= "(SELECT ID,DATEINS,OWNER,NAME,TAG_ID,CONTENT_ID, [ORDER] as CONTENT_ORDER, SCORING FROM dbo.GVP_CONTENTS_LABELS) as T" ); And then, the query to this external table: SELECT ID, DATEINS, NAME, TAG_ID AS TAGID, CONTENT_ID AS CONTENTID,CONTENT_ORDER, SCORING, OWNER FROM GVP_CONTENTS_LABELS_SR; As this method works OK, it seems there is a problem or bug 🐛with how the external catalog method propagates the query to SQL Server. Should I raise an issue about this?
Open in Slack
PreviousNext
Powered by