in src/odbc/rsodbc/rscatalog.cpp [4593:4908]
static void buildExternalSchemaColumnsQuery(char *pszCatalogQuery,
RS_STMT_INFO *pStmt,
SQLCHAR *pCatalogName,
SQLSMALLINT cbCatalogName,
SQLCHAR *pSchemaName,
SQLSMALLINT cbSchemaName,
SQLCHAR *pTableName,
SQLSMALLINT cbTableName,
SQLCHAR *pColumnName,
SQLSMALLINT cbColumnName)
{
std::string result = "";
char catalogFilter[MAX_LARGE_TEMP_BUF_LEN];
char filterClause[MAX_CATALOG_QUERY_FILTER_LEN];
// std::string unknownColumnSize = "2147483647";
getCatalogFilterCondition(catalogFilter, MAX_LARGE_TEMP_BUF_LEN, pStmt, (char *)pCatalogName, cbCatalogName,
TRUE, NULL);
// NOTE: Explicit cast on current_database() prevents bug where data returned from server
// has incorrect length and displays random characters. [JDBC-529]
result.append("SELECT current_database()::varchar(128) AS TABLE_CAT,"
" schemaname AS TABLE_SCHEM,"
" tablename AS TABLE_NAME,"
" columnname AS COLUMN_NAME,"
" CAST(CASE WHEN external_type = 'text' THEN 12"
" WHEN external_type = 'bit' THEN -7"
" WHEN external_type = 'bool' THEN -7"
" WHEN external_type = 'boolean' THEN -7"
" WHEN left(external_type, 7) = 'varchar' THEN 12"
" WHEN left(external_type, 17) = 'character varying' THEN 12"
" WHEN left(external_type, 4) = 'char' THEN 1"
" WHEN left(external_type, 9) = 'character' THEN 1"
" WHEN left(external_type, 5) = 'nchar' THEN 1"
" WHEN left(external_type, 6) = 'bpchar' THEN 1"
" WHEN left(external_type, 8) = 'nvarchar' THEN 12"
" WHEN external_type = '\"char\"' THEN 1"
" WHEN external_type = 'date' THEN 91"
" WHEN external_type = 'time' THEN 92 "
" WHEN external_type = 'time without time zone' THEN 92 "
" WHEN external_type = 'timetz' THEN 92 "
" WHEN external_type = 'time with time zone' THEN 92 "
" WHEN external_type = 'timestamp' THEN 93"
" WHEN external_type = 'timestamp without time zone' THEN 93"
" WHEN external_type = 'timestamptz' THEN 93"
" WHEN external_type = 'timestamp with time zone' THEN 93"
" WHEN external_type = 'smallint' THEN 5"
" WHEN external_type = 'int2' THEN 5"
" WHEN external_type = '_int2' THEN 5"
" WHEN external_type = 'integer' THEN 4"
" WHEN external_type = 'int' THEN 4"
" WHEN external_type = 'int4' THEN 4"
" WHEN external_type = '_int4' THEN 4"
" WHEN external_type = 'bigint' THEN -5"
" WHEN external_type = 'int8' THEN -5"
" WHEN left(external_type, 7) = 'decimal' THEN 2"
" WHEN external_type = 'real' THEN 7"
" WHEN external_type = 'float4' THEN 7"
" WHEN external_type = '_float4' THEN 7"
" WHEN external_type = 'double' THEN 8"
" WHEN external_type = 'double precision' THEN 8"
" WHEN external_type = 'float8' THEN 8"
" WHEN external_type = '_float8' THEN 8"
" WHEN external_type = 'float' THEN 6"
" WHEN left(external_type, 7) = 'numeric' THEN 2"
" WHEN external_type = 'bytea' THEN -2"
" WHEN external_type = 'oid' THEN -5"
" WHEN external_type = 'name' THEN 12"
" WHEN external_type = 'ARRAY' THEN 2003"
" WHEN external_type = 'geometry' THEN -4"
" WHEN external_type = 'super' THEN -1"
" WHEN external_type = 'varbyte' THEN -4"
" WHEN external_type = 'geography' THEN -4"
" WHEN external_type = 'intervaly2m' THEN 107"
" WHEN external_type = 'intervald2s' THEN 110"
" ELSE 0 END AS SMALLINT) AS DATA_TYPE,"
" CASE WHEN left(external_type, 17) = 'character varying' THEN 'varchar'"
" WHEN left(external_type, 7) = 'varchar' THEN 'varchar'"
" WHEN left(external_type, 4) = 'char' THEN 'char'"
" WHEN left(external_type, 7) = 'decimal' THEN 'numeric'"
" WHEN left(external_type, 7) = 'numeric' THEN 'numeric'"
" WHEN external_type = 'double' THEN 'double precision'"
" WHEN external_type = 'time without time zone' THEN 'time'"
" WHEN external_type = 'time with time zone' THEN 'timetz'"
" WHEN external_type = 'timestamp without time zone' THEN 'timestamp'"
" WHEN external_type = 'timestamp with time zone' THEN 'timestamptz'"
" ELSE external_type END AS TYPE_NAME,"
" CASE WHEN external_type = 'int4' THEN 10"
" WHEN external_type = 'bit' THEN 1"
" WHEN external_type = 'bool' THEN 1"
" WHEN external_type = 'boolean' THEN 1"
" WHEN left(external_type, 7) = 'varchar' "
" THEN CASE "
" WHEN regexp_instr(external_type, '\\\\(', 7) = 0 THEN '0' "
" ELSE regexp_substr(external_type, '[0-9]+', 7) "
" END::integer "
" WHEN left(external_type, 17) = 'character varying' "
" THEN CASE "
" WHEN regexp_instr(external_type, '\\\\(', 17) = 0 THEN '0' "
" ELSE regexp_substr(external_type, '[0-9]+', 17) "
" END::integer "
" WHEN left(external_type, 4) = 'char' "
" THEN CASE "
" WHEN regexp_instr(external_type, '\\\\(', 4) = 0 THEN '0' "
" ELSE regexp_substr(external_type, '[0-9]+', 4) "
" END::integer "
" WHEN left(external_type, 9) = 'character' "
" THEN CASE "
" WHEN regexp_instr(external_type, '\\\\(', 9) = 0 THEN '0' "
" ELSE regexp_substr(external_type, '[0-9]+', 9) "
" END::integer "
" WHEN left(external_type, 5) = 'nchar' "
" THEN CASE "
" WHEN regexp_instr(external_type, '\\\\(', 5) = 0 THEN '0' "
" ELSE regexp_substr(external_type, '[0-9]+', 5) "
" END::integer "
" WHEN left(external_type, 6) = 'bpchar' "
" THEN CASE "
" WHEN regexp_instr(external_type, '\\\\(', 6) = 0 THEN '0' "
" ELSE regexp_substr(external_type, '[0-9]+', 6) "
" END::integer "
" WHEN left(external_type, 8) = 'nvarchar' "
" THEN CASE "
" WHEN regexp_instr(external_type, '\\\\(', 8) = 0 THEN '0' "
" ELSE regexp_substr(external_type, '[0-9]+', 8) "
" END::integer "
" WHEN external_type = 'date' THEN 13 "
" WHEN external_type = 'time' THEN 15 "
" WHEN external_type = 'time without time zone' THEN 15 "
" WHEN external_type = 'timetz' THEN 21 "
" WHEN external_type = 'time with time zone' THEN 21 "
" WHEN external_type = 'timestamp' THEN 29 "
" WHEN external_type = 'timestamp without time zone' THEN 29"
" WHEN external_type = 'timestamptz' THEN 35"
" WHEN external_type = 'timestamp with time zone' THEN 35"
" WHEN external_type = 'smallint' THEN 5"
" WHEN external_type = 'int2' THEN 5"
" WHEN external_type = 'integer' THEN 10"
" WHEN external_type = 'int' THEN 10"
" WHEN external_type = 'int4' THEN 10"
" WHEN external_type = 'bigint' THEN 19"
" WHEN external_type = 'int8' THEN 19"
" WHEN left(external_type, 7) = 'decimal' THEN isnull(nullif(regexp_substr(external_type, '[0-9]+', 7),''),'0')::integer"
" WHEN external_type = 'real' THEN 8"
" WHEN external_type = 'float4' THEN 8"
" WHEN external_type = '_float4' THEN 8"
" WHEN external_type = 'double' THEN 17"
" WHEN external_type = 'double precision' THEN 17"
" WHEN external_type = 'float8' THEN 17"
" WHEN external_type = '_float8' THEN 17"
" WHEN external_type = 'float' THEN 17"
" WHEN left(external_type, 7) = 'numeric' THEN isnull(nullif(regexp_substr(external_type, '[0-9]+', 7),''),'0')::integer"
" WHEN external_type = '_float4' THEN 8"
" WHEN external_type = 'oid' THEN 10"
" WHEN external_type = '_int4' THEN 10"
" WHEN external_type = '_int2' THEN 5"
" WHEN external_type = 'geometry' THEN NULL"
" WHEN external_type = 'super' THEN NULL"
" WHEN external_type = 'varbyte' THEN NULL"
" WHEN external_type = 'geography' THEN NULL"
" WHEN external_type = 'intervaly2m' THEN 32"
" WHEN external_type = 'intervald2s' THEN 64"
" ELSE 2147483647 END AS COLUMN_SIZE,"
" CAST(NULL as INTEGER) AS BUFFER_LENGTH,"
" CAST(CASE WHEN external_type = 'real'THEN 8"
" WHEN external_type = 'float4' THEN 8"
" WHEN external_type = 'double' THEN 17"
" WHEN external_type = 'double precision' THEN 17"
" WHEN external_type = 'float8' THEN 17"
" WHEN left(external_type, 7) = 'numeric' THEN isnull(nullif(regexp_substr(external_type, '[0-9]+', 10),''),'0')::integer"
" WHEN left(external_type, 7) = 'decimal' THEN isnull(nullif(regexp_substr(external_type, '[0-9]+', 10),''),'0')::integer"
" WHEN external_type = 'time' THEN 6 "
" WHEN external_type = 'time without time zone' THEN 6 "
" WHEN external_type = 'timetz' THEN 6 "
" WHEN external_type = 'time with time zone' THEN 6 "
" WHEN external_type = 'timestamp' THEN 6"
" WHEN external_type = 'timestamp without time zone' THEN 6"
" WHEN external_type = 'timestamptz' THEN 6"
" WHEN external_type = 'timestamp with time zone' THEN 6"
" WHEN external_type = 'geometry' THEN NULL"
" WHEN external_type = 'super' THEN NULL"
" WHEN external_type = 'varbyte' THEN NULL"
" WHEN external_type = 'geography' THEN NULL"
" WHEN external_type = 'intervaly2m' THEN 0"
" WHEN external_type = 'intervald2s' THEN 6"
" ELSE 0 END AS SMALLINT) AS DECIMAL_DIGITS,"
" CAST(CASE WHEN external_type = 'varbyte' THEN 2"
" WHEN external_type = 'geography' THEN 2"
" when external_type = 'varchar' THEN 0 "
" when external_type = 'character varying' THEN 0 "
" when external_type = 'char' THEN 0 "
" when external_type = 'character' THEN 0 "
" when external_type = 'nchar' THEN 0 "
" when external_type = 'bpchar' THEN 0 "
" when external_type = 'nvarchar' THEN 0 "
" ELSE 10"
" END AS SMALLINT) AS NUM_PREC_RADIX,"
" CAST(CASE is_nullable WHEN 'true' THEN 1 WHEN 'false' THEN 0 ELSE NULL END AS SMALLINT) AS NULLABLE,"
" NULL AS REMARKS,"
" NULL AS COLUMN_DEF,"
" CAST(CASE WHEN external_type = 'text' THEN 12"
" WHEN external_type = 'bit' THEN -7"
" WHEN external_type = 'bool' THEN -7"
" WHEN external_type = 'boolean' THEN -7"
" WHEN left(external_type, 7) = 'varchar' THEN 12"
" WHEN left(external_type, 17) = 'character varying' THEN 12"
" WHEN left(external_type, 4) = 'char' THEN 1"
" WHEN left(external_type, 9) = 'character' THEN 1"
" WHEN left(external_type, 5) = 'nchar' THEN 1"
" WHEN left(external_type, 6) = 'bpchar' THEN 1"
" WHEN left(external_type, 8) = 'nvarchar' THEN 12"
" WHEN external_type = '\"char\"' THEN 1"
" WHEN external_type = 'date' THEN 9"
" WHEN external_type = 'time' THEN 9 "
" WHEN external_type = 'time without time zone' THEN 9 "
" WHEN external_type = 'timetz' THEN 9 "
" WHEN external_type = 'time with time zone' THEN 9 "
" WHEN external_type = 'timestamp' THEN 9"
" WHEN external_type = 'timestamp without time zone' THEN 9"
" WHEN external_type = 'timestamptz' THEN 9"
" WHEN external_type = 'timestamp with time zone' THEN 9"
" WHEN external_type = 'smallint' THEN 5"
" WHEN external_type = 'int2' THEN 5"
" WHEN external_type = '_int2' THEN 5"
" WHEN external_type = 'integer' THEN 4"
" WHEN external_type = 'int' THEN 4"
" WHEN external_type = 'int4' THEN 4"
" WHEN external_type = '_int4' THEN 4"
" WHEN external_type = 'bigint' THEN -5"
" WHEN external_type = 'int8' THEN -5"
" WHEN left(external_type, 7) = 'decimal' THEN 3"
" WHEN external_type = 'real' THEN 7"
" WHEN external_type = 'float4' THEN 7"
" WHEN external_type = '_float4' THEN 7"
" WHEN external_type = 'double' THEN 8"
" WHEN external_type = 'double precision' THEN 8"
" WHEN external_type = 'float8' THEN 8"
" WHEN external_type = '_float8' THEN 8"
" WHEN external_type = 'float' THEN 6"
" WHEN left(external_type, 7) = 'numeric' THEN 2"
" WHEN external_type = 'bytea' THEN -2"
" WHEN external_type = 'oid' THEN -5"
" WHEN external_type = 'name' THEN 12"
" WHEN external_type = 'ARRAY' THEN 2003"
" WHEN external_type = 'geometry' THEN -4"
" WHEN external_type = 'super' THEN -1"
" WHEN external_type = 'varbyte' THEN -4"
" WHEN external_type = 'geography' THEN -4"
" WHEN external_type = 'intervaly2m' THEN 107"
" WHEN external_type = 'intervald2s' THEN 110"
" ELSE 0 END AS SMALLINT) AS SQL_DATA_TYPE,"
" CAST(CASE WHEN external_type = 'date' THEN 1 "
" WHEN external_type = 'time' THEN 2 "
" WHEN external_type = 'time without time zone' THEN 2 "
" WHEN external_type = 'timetz' THEN 2 "
" WHEN external_type = 'time with time zone' THEN 2 "
" WHEN external_type = 'timestamp with time zone' THEN 3 "
" WHEN external_type = 'timestamptz' THEN 3 "
" WHEN external_type = 'timestamp' THEN 3 "
" WHEN external_type = 'timestamp without time zone' THEN 3 "
" ELSE NULL END AS SMALLINT) AS SQL_DATETIME_SUB, "
" CASE WHEN left(external_type, 7) = 'varchar' "
" THEN CASE "
" WHEN regexp_instr(external_type, '\\\\(', 7) = 0 THEN '0' "
" ELSE regexp_substr(external_type, '[0-9]+', 7) "
" END::integer "
" WHEN left(external_type, 17) = 'character varying' "
" THEN CASE "
" WHEN regexp_instr(external_type, '\\\\(', 17) = 0 THEN '0' "
" ELSE regexp_substr(external_type, '[0-9]+', 17) "
" END::integer "
" WHEN left(external_type, 4) = 'char' "
" THEN CASE "
" WHEN regexp_instr(external_type, '\\\\(', 4) = 0 THEN '0' "
" ELSE regexp_substr(external_type, '[0-9]+', 4) "
" END::integer "
" WHEN left(external_type, 9) = 'character' "
" THEN CASE "
" WHEN regexp_instr(external_type, '\\\\(', 9) = 0 THEN '0' "
" ELSE regexp_substr(external_type, '[0-9]+', 9) "
" END::integer "
" WHEN left(external_type, 5) = 'nchar' "
" THEN CASE "
" WHEN regexp_instr(external_type, '\\\\(', 5) = 0 THEN '0' "
" ELSE regexp_substr(external_type, '[0-9]+', 5) "
" END::integer "
" WHEN left(external_type, 6) = 'bpchar' "
" THEN CASE "
" WHEN regexp_instr(external_type, '\\\\(', 6) = 0 THEN '0' "
" ELSE regexp_substr(external_type, '[0-9]+', 6) "
" END::integer "
" WHEN left(external_type, 8) = 'nvarchar' "
" THEN CASE "
" WHEN regexp_instr(external_type, '\\\\(', 8) = 0 THEN '0' "
" ELSE regexp_substr(external_type, '[0-9]+', 8) "
" END::integer "
" WHEN external_type = 'string' THEN 16383"
" ELSE NULL END AS CHAR_OCTET_LENGTH,"
" columnnum AS ORDINAL_POSITION,"
" CASE IS_NULLABLE when 'true' THEN 'YES' when 'false' then 'NO' ELSE NULL END AS IS_NULLABLE"
" FROM svv_external_columns");
result.append(" WHERE true ");
result.append(catalogFilter);
filterClause[0] = '\0';
addLikeOrEqualFilterCondition(pStmt, filterClause, "schemaname", (char *)pSchemaName, cbSchemaName);
addLikeOrEqualFilterCondition(pStmt, filterClause, "tablename", (char *)pTableName, cbTableName);
addLikeOrEqualFilterCondition(pStmt, filterClause, "columnname", (char *)pColumnName, cbColumnName);
result.append(filterClause);
result.append(" ORDER BY table_schem,table_name,ORDINAL_POSITION ");
rs_strncpy(pszCatalogQuery, result.c_str(), MAX_CATALOG_QUERY_LEN);
}