You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@seatunnel.apache.org by "LoverAndrew (via GitHub)" <gi...@apache.org> on 2024/01/02 03:12:52 UTC
Re: [I] [Bug] [connector-jdbc] OracleCatalog's SELECT_COLUMNS_SQL_TEMPLATE have some COLUMN_LENGTH issue when the column‘s type is NVARCHAR2 or NCHAR [seatunnel]
LoverAndrew commented on issue #6054:
URL: https://github.com/apache/seatunnel/issues/6054#issuecomment-1873588195
SqlServerCatalog's SELECT_COLUMNS_SQL_TEMPLATE has the same issue.
Before sql statement is

```
SELECT tbl.name AS table_name,
col.name AS column_name,
ext.value AS comment,
col.column_id AS column_id,
types.name AS type,
col.max_length AS max_length,
col.precision AS precision,
col.scale AS scale,
col.is_nullable AS is_nullable,
def.definition AS default_value
FROM sys.tables tbl
INNER JOIN sys.columns col ON tbl.object_id = col.object_id
LEFT JOIN sys.types types ON col.user_type_id = types.user_type_id
LEFT JOIN sys.extended_properties ext ON ext.major_id = col.object_id AND ext.minor_id = col.column_id
LEFT JOIN sys.default_constraints def ON col.default_object_id = def.object_id AND ext.minor_id = col.column_id AND ext.name = 'MS_Description'
WHERE schema_name(tbl.schema_id) = 'dbo' AND tbl.name = 'student'
ORDER BY tbl.name, col.column_id;
```
After sql statement is

```
SELECT col.TABLE_NAME AS table_name,
col.COLUMN_NAME AS column_name,
prop.VALUE AS comment,
col.ORDINAL_POSITION AS column_id,
col.DATA_TYPE AS type,
CASE WHEN col.DATA_TYPE in ('nchar','nvarchar','ntext') THEN col.CHARACTER_MAXIMUM_LENGTH
ELSE col.CHARACTER_OCTET_LENGTH end AS max_length,
CASE WHEN col.DATA_TYPE in ('datetime','datetime2','datetimeoffset','date','time','smalldatetime' ) THEN col.DATETIME_PRECISION
ELSE col.NUMERIC_PRECISION end AS precision,
col.NUMERIC_SCALE AS scale,
col.IS_NULLABLE AS is_nullable,
col.COLUMN_DEFAULT AS default_value
FROM INFORMATION_SCHEMA.COLUMNS col
LEFT JOIN sys.extended_properties prop
ON prop.major_id = OBJECT_ID(col.TABLE_SCHEMA + '.' + col.TABLE_NAME)
AND prop.minor_id = col.ORDINAL_POSITION
AND prop.name = 'MS_Description'
WHERE col.TABLE_SCHEMA='dbo' AND col.TABLE_NAME = 'student'
ORDER BY col.TABLE_NAME, col.ORDINAL_POSITION;
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org