in src/odbc/rsodbc/rscatalog.cpp [3992:4291]
static void buildUniversalSchemaColumnsQuery(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,"
" table_schema AS TABLE_SCHEM,"
" table_name,"
" COLUMN_NAME,"
" CAST(CASE regexp_replace(data_type, '^_.+', 'ARRAY')"
" WHEN 'text' THEN 12"
" WHEN 'bit' THEN -7"
" WHEN 'bool' THEN -7"
" WHEN 'boolean' THEN -7"
" WHEN 'varchar' THEN 12"
" WHEN 'character varying' THEN 12"
" WHEN 'char' THEN 1"
" WHEN 'character' THEN 1"
" WHEN 'nchar' THEN 1"
" WHEN 'bpchar' THEN 1"
" WHEN 'nvarchar' THEN 12"
" WHEN '\"char\"' THEN 1"
" WHEN 'date' THEN 91"
" WHEN 'time' THEN 92 "
" WHEN 'time without time zone' THEN 92 "
" WHEN 'timetz' THEN 92 "
" WHEN 'time with time zone' THEN 92 "
" WHEN 'timestamp' THEN 93"
" WHEN 'timestamp without time zone' THEN 93"
" WHEN 'timestamptz' THEN 93"
" WHEN 'timestamp with time zone' THEN 93"
" WHEN 'smallint' THEN 5"
" WHEN 'int2' THEN 5"
" WHEN 'integer' THEN 4"
" WHEN 'int' THEN 4"
" WHEN 'int4' THEN 4"
" WHEN 'bigint' THEN -5"
" WHEN 'int8' THEN -5"
" WHEN 'decimal' THEN 3"
" WHEN 'real' THEN 7"
" WHEN 'float4' THEN 7"
" WHEN 'double precision' THEN 8"
" WHEN 'float8' THEN 8"
" WHEN 'float' THEN 6"
" WHEN 'numeric' THEN 2"
" WHEN 'bytea' THEN -2"
" WHEN 'oid' THEN -5"
" WHEN 'name' THEN 12"
" WHEN 'ARRAY' THEN 2003"
" WHEN 'geometry' THEN -4 "
" WHEN 'super' THEN -1 "
" WHEN 'varbyte' THEN -4 "
" WHEN 'geography' THEN -4 "
" WHEN 'intervaly2m' THEN 107 "
" WHEN 'intervald2s' THEN 110 "
" ELSE 0 END AS SMALLINT) AS DATA_TYPE,"
" COALESCE("
" domain_name,"
" CASE data_type"
" WHEN 'boolean' THEN 'bool'"
" WHEN 'character varying' THEN 'varchar'"
" WHEN '\"char\"' THEN 'char'"
" WHEN 'smallint' THEN 'int2'"
" WHEN 'integer' THEN 'int4'"
" WHEN 'bigint' THEN 'int8'"
" WHEN 'real' THEN 'float4'"
" WHEN 'double precision' THEN 'float8'"
" WHEN 'time without time zone' THEN 'time'"
" WHEN 'time with time zone' THEN 'timetz'"
" WHEN 'timestamp without time zone' THEN 'timestamp'"
" WHEN 'timestamp with time zone' THEN 'timestamptz'"
" ELSE data_type"
" END) AS TYPE_NAME,"
" CASE data_type"
" WHEN 'int4' THEN 10"
" WHEN 'bit' THEN 1"
" WHEN 'bool' THEN 1"
" WHEN 'boolean' THEN 1"
" WHEN 'varchar' THEN character_maximum_length"
" WHEN 'character varying' THEN character_maximum_length"
" WHEN 'char' THEN character_maximum_length"
" WHEN 'character' THEN character_maximum_length"
" WHEN 'nchar' THEN character_maximum_length"
" WHEN 'bpchar' THEN character_maximum_length"
" WHEN 'nvarchar' THEN character_maximum_length"
" WHEN 'date' THEN 13"
" WHEN 'time' THEN 15 "
" WHEN 'time without time zone' THEN 15 "
" WHEN 'timetz' THEN 21 "
" WHEN 'time with time zone' THEN 21 "
" WHEN 'timestamp' THEN 29"
" WHEN 'timestamp without time zone' THEN 29"
" WHEN 'timestamptz' THEN 35"
" WHEN 'timestamp with time zone' THEN 35"
" WHEN 'smallint' THEN 5"
" WHEN 'int2' THEN 5"
" WHEN 'integer' THEN 10"
" WHEN 'int' THEN 10"
" WHEN 'int4' THEN 10"
" WHEN 'bigint' THEN 19"
" WHEN 'int8' THEN 19"
" WHEN 'decimal' THEN numeric_precision"
" WHEN 'real' THEN 8"
" WHEN 'float4' THEN 8"
" WHEN 'double precision' THEN 17"
" WHEN 'float8' THEN 17"
" WHEN 'float' THEN 17"
" WHEN 'numeric' THEN numeric_precision"
" WHEN '_float4' THEN 8"
" WHEN 'oid' THEN 10"
" WHEN '_int4' THEN 10"
" WHEN '_int2' THEN 5"
" WHEN 'geometry' THEN NULL"
" WHEN 'super' THEN NULL"
" WHEN 'varbyte' THEN NULL"
" WHEN 'geography' THEN NULL"
" WHEN 'intervaly2m' THEN 32"
" WHEN 'intervald2s' THEN 64"
" ELSE 2147483647"
" END AS COLUMN_SIZE,"
" CAST(NULL as INTEGER) AS BUFFER_LENGTH,"
" CAST(CASE data_type"
" WHEN 'real' THEN 8"
" WHEN 'float4' THEN 8"
" WHEN 'double precision' THEN 17"
" WHEN 'float8' THEN 17"
" WHEN 'numeric' THEN numeric_scale"
" WHEN 'time' THEN 6"
" WHEN 'time without time zone' THEN 6"
" WHEN 'timetz' THEN 6"
" WHEN 'time with time zone' THEN 6"
" WHEN 'timestamp' THEN 6"
" WHEN 'timestamp without time zone' THEN 6"
" WHEN 'timestamptz' THEN 6"
" WHEN 'timestamp with time zone' THEN 6"
" WHEN 'geometry' THEN NULL"
" WHEN 'super' THEN NULL"
" WHEN 'varbyte' THEN NULL"
" WHEN 'geography' THEN NULL"
" WHEN 'intervaly2m' THEN 0"
" WHEN 'intervald2s' THEN 6"
" ELSE 0"
" END AS SMALLINT) AS DECIMAL_DIGITS,"
" CAST(CASE data_type"
" WHEN 'varbyte' THEN 2"
" WHEN 'geography' THEN 2"
" when 'varchar' THEN 0 "
" when 'character varying' THEN 0 "
" when 'char' THEN 0 "
" when 'character' THEN 0 "
" when 'nchar' THEN 0 "
" when 'bpchar' THEN 0 "
" when 'nvarchar' THEN 0 "
" ELSE 10"
" END AS SMALLINT) AS NUM_PREC_RADIX,"
" CAST(CASE is_nullable WHEN 'YES' THEN 1"
" WHEN 'NO' THEN 0"
" ELSE 2 end AS SMALLINT) AS NULLABLE,"
" REMARKS,"
" column_default AS COLUMN_DEF,"
" CAST(CASE regexp_replace(data_type, '^_.+', 'ARRAY')"
" WHEN 'text' THEN 12"
" WHEN 'bit' THEN -7"
" WHEN 'bool' THEN -7"
" WHEN 'boolean' THEN -7"
" WHEN 'varchar' THEN 12"
" WHEN 'character varying' THEN 12"
" WHEN 'char' THEN 1"
" WHEN 'character' THEN 1"
" WHEN 'nchar' THEN 1"
" WHEN 'bpchar' THEN 1"
" WHEN 'nvarchar' THEN 12"
" WHEN '\"char\"' THEN 1"
" WHEN 'date' THEN 9"
" WHEN 'time' THEN 9 "
" WHEN 'time without time zone' THEN 9 "
" WHEN 'timetz' THEN 9 "
" WHEN 'time with time zone' THEN 9 "
" WHEN 'timestamp' THEN 9"
" WHEN 'timestamp without time zone' THEN 9"
" WHEN 'timestamptz' THEN 9"
" WHEN 'timestamp with time zone' THEN 9"
" WHEN 'smallint' THEN 5"
" WHEN 'int2' THEN 5"
" WHEN 'integer' THEN 4"
" WHEN 'int' THEN 4"
" WHEN 'int4' THEN 4"
" WHEN 'bigint' THEN -5"
" WHEN 'int8' THEN -5"
" WHEN 'decimal' THEN 3"
" WHEN 'real' THEN 7"
" WHEN 'float4' THEN 7"
" WHEN 'double precision' THEN 8"
" WHEN 'float8' THEN 8"
" WHEN 'float' THEN 6"
" WHEN 'numeric' THEN 2"
" WHEN 'bytea' THEN -2"
" WHEN 'oid' THEN -5"
" WHEN 'name' THEN 12"
" WHEN 'ARRAY' THEN 2003"
" WHEN 'geometry' THEN -4"
" WHEN 'super' THEN -1"
" WHEN 'varbyte' THEN -4"
" WHEN 'geography' THEN -4"
" WHEN 'intervaly2m' THEN 107 "
" WHEN 'intervald2s' THEN 110 "
" ELSE 0 END AS SMALLINT) AS SQL_DATA_TYPE,"
" CAST(CASE data_type"
" WHEN 'date' THEN 1 "
" WHEN 'time' THEN 2 "
" WHEN 'time without time zone' THEN 2 "
" WHEN 'timetz' THEN 2 "
" WHEN 'time with time zone' THEN 2 "
" WHEN 'timestamp with time zone' THEN 3 "
" WHEN 'timestamptz' THEN 3 "
" WHEN 'timestamp' THEN 3 "
" WHEN 'timestamp without time zone' THEN 3 "
" ELSE NULL END AS SMALLINT) as SQL_DATETIME_SUB, "
" CASE data_type"
" WHEN 'int4' THEN 10"
" WHEN 'bit' THEN 1"
" WHEN 'bool' THEN 1"
" WHEN 'boolean' THEN 1"
" WHEN 'varchar' THEN character_maximum_length"
" WHEN 'character varying' THEN character_maximum_length"
" WHEN 'char' THEN character_maximum_length"
" WHEN 'character' THEN character_maximum_length"
" WHEN 'nchar' THEN character_maximum_length"
" WHEN 'bpchar' THEN character_maximum_length"
" WHEN 'nvarchar' THEN character_maximum_length"
" WHEN 'date' THEN 13"
" WHEN 'time' THEN 15"
" WHEN 'time without time zone' THEN 15"
" WHEN 'timetz' THEN 21"
" WHEN 'time with time zone' THEN 21"
" WHEN 'timestamp' THEN 29"
" WHEN 'timestamp without time zone' THEN 29"
" WHEN 'timestamptz' THEN 35"
" WHEN 'timestamp with time zone' THEN 35"
" WHEN 'smallint' THEN 5"
" WHEN 'int2' THEN 5"
" WHEN 'integer' THEN 10"
" WHEN 'int' THEN 10"
" WHEN 'int4' THEN 10"
" WHEN 'bigint' THEN 19"
" WHEN 'int8' THEN 19"
" WHEN 'decimal' THEN numeric_precision"
" WHEN 'real' THEN 8"
" WHEN 'float4' THEN 8"
" WHEN 'double precision' THEN 17"
" WHEN 'float8' THEN 17"
" WHEN 'float' THEN 17"
" WHEN 'numeric' THEN numeric_precision"
" WHEN '_float4' THEN 8"
" WHEN 'oid' THEN 10"
" WHEN '_int4' THEN 10"
" WHEN '_int2' THEN 5"
" WHEN 'geometry' THEN NULL"
" WHEN 'super' THEN NULL"
" WHEN 'varbyte' THEN NULL"
" WHEN 'geography' THEN NULL"
" WHEN 'intervaly2m' THEN 32"
" WHEN 'intervald2s' THEN 64"
" ELSE 2147483647 "
" END AS CHAR_OCTET_LENGTH,"
" ordinal_position AS ORDINAL_POSITION,"
" is_nullable AS IS_NULLABLE "
" FROM svv_columns");
result.append(" WHERE true ");
result.append(catalogFilter);
filterClause[0] = '\0';
addLikeOrEqualFilterCondition(pStmt, filterClause, "table_schema", (char *)pSchemaName, cbSchemaName);
addLikeOrEqualFilterCondition(pStmt, filterClause, "table_name", (char *)pTableName, cbTableName);
addLikeOrEqualFilterCondition(pStmt, filterClause, "COLUMN_NAME", (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);
}