Home > mailing lists

Thread: [ODBC] Question about SQLColumns results on VARCHAR types

[ODBC] Question about SQLColumns results on VARCHAR types

From

Jacobo Sánchez

Date:

12 January 2017, 15:12:56

Hello

I have a question about metadata exposed using SQLColumns function. I have created the following table in PostgresSQL:

CREATE TABLE testvarchar (vc2000 varchar(2000), vc500 varchar(500))

Then i configure a DSN (tested versions 9.3.400 and 9.5.400) and execute SQLColumns for the previous table getting the following results:

"TABLE_QUALIFIER", "TABLE_OWNER", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "PRECISION", "LENGTH", "SCALE", "RADIX", "NULLABLE", "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE", "DISPLAY_SIZE", "FIELD_TYPE", "AUTO_INCREMENT", "PHYSICAL NUMBER", "TABLE OID", "BASE TYPEID", "TYPMOD"

ANSI with MaxVarchar 500:

"xxx", "xxx", "testvarchar", "vc2000", -1, "varchar", 2000, 6000, <Null>, <Null>, 1, "", <Null>, -1, <Null>, 6000, 1, <Null>, 2000, 1043, 0, 1, 46594, 0, 2000
"xxx", "xxx", "testvarchar", "vc500", 12, "varchar", 500, 500, <Null>, <Null>, 1, "", <Null>, 12, <Null>, 500, 2, <Null>, 500, 1043, 0, 2, 46594, 0, 500

ANSI with MaxVarchar 2000

"xxx", "xxx", "testvarchar", "vc2000", 12, "varchar", 2000, 2000, <Null>, <Null>, 1, "", <Null>, 12, <Null>, 2000, 1, <Null>, 2000, 1043, 0, 1, 46594, 0, 2000
"xxx", "xxx", "testvarchar", "vc500", 12, "varchar", 500, 1500, <Null>, <Null>, 1, "", <Null>, 12, <Null>, 1500, 2, <Null>, 500, 1043, 0, 2, 46594, 0, 500

UNICODE with MaxVarchar 500

"xxx", "xxx", "testvarchar", "vc2000", -10, "varchar", 2000, 4000, <Null>, <Null>, 1, "", <Null>, -10, <Null>, 4000, 1, <Null>, 2000, 1043, 0, 1, 46594, 0, 2000
"xxx", "xxx", "testvarchar", "vc500", -9, "varchar", 500, 1000, <Null>, <Null>, 1, "", <Null>, -9, <Null>, 1000, 2, <Null>, 500, 1043, 0, 2, 46594, 0, 500

UNICODE with MaxVarchar 2000

"xxx", "xxx", "testvarchar", "vc2000", -9, "varchar", 2000, 4000, <Null>, <Null>, 1, "", <Null>, -9, <Null>, 4000, 1, <Null>, 2000, 1043, 0, 1, 46594, 0, 2000
"xxx", "xxx", "testvarchar", "vc500", -9, "varchar", 500, 1000, <Null>, <Null>, 1, "", <Null>, -9, <Null>, 1000, 2, <Null>, 500, 1043, 0, 2, 46594, 0, 500

I do not understand very well the variation between precision and length but my real question comes on DATA_TYPES. What i see is that using a MaxVarchar configuration for less than the varchar declared size it changes from VARCHAR (12) to LONGVARCHAR(-1). However using the UNICODE it comes from NVARCHAR(-9) to a code -10 which i can not find what really means. I expected to see -16? for LONGNVARCHAR.

So what does -10 points to? and where can i find the mapping for it?

Regards, Jacobo

Re: [ODBC] Question about SQLColumns results on VARCHAR types

From

"Inoue, Hiroshi"

Date:

17 January 2017, 17:50:58

Hi Jacobo,

On 2017/01/12 18:12, Jacobo Sánchez wrote:

Hello
I have a question about metadata exposed using SQLColumns function. I have created the following table in PostgresSQL:
CREATE TABLE testvarchar (vc2000 varchar(2000), vc500 varchar(500))
Then i configure a DSN (tested versions 9.3.400 and 9.5.400) and execute SQLColumns for the previous table getting the following results:
"TABLE_QUALIFIER", "TABLE_OWNER", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "PRECISION", "LENGTH", "SCALE", "RADIX", "NULLABLE", "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE", "DISPLAY_SIZE", "FIELD_TYPE", "AUTO_INCREMENT", "PHYSICAL NUMBER", "TABLE OID", "BASE TYPEID", "TYPMOD"
ANSI with MaxVarchar 500:
"xxx", "xxx", "testvarchar", "vc2000", -1, "varchar", 2000, 6000, <Null>, <Null>, 1, "", <Null>, -1, <Null>, 6000, 1, <Null>, 2000, 1043, 0, 1, 46594, 0, 2000
"xxx", "xxx", "testvarchar", "vc500", 12, "varchar", 500, 500, <Null>, <Null>, 1, "", <Null>, 12, <Null>, 500, 2, <Null>, 500, 1043, 0, 2, 46594, 0, 500
ANSI with MaxVarchar 2000
"xxx", "xxx", "testvarchar", "vc2000", 12, "varchar", 2000, 2000, <Null>, <Null>, 1, "", <Null>, 12, <Null>, 2000, 1, <Null>, 2000, 1043, 0, 1, 46594, 0, 2000
"xxx", "xxx", "testvarchar", "vc500", 12, "varchar", 500, 1500, <Null>, <Null>, 1, "", <Null>, 12, <Null>, 1500, 2, <Null>, 500, 1043, 0, 2, 46594, 0, 500
UNICODE with MaxVarchar 500
"xxx", "xxx", "testvarchar", "vc2000", -10, "varchar", 2000, 4000, <Null>, <Null>, 1, "", <Null>, -10, <Null>, 4000, 1, <Null>, 2000, 1043, 0, 1, 46594, 0, 2000
"xxx", "xxx", "testvarchar", "vc500", -9, "varchar", 500, 1000, <Null>, <Null>, 1, "", <Null>, -9, <Null>, 1000, 2, <Null>, 500, 1043, 0, 2, 46594, 0, 500
UNICODE with MaxVarchar 2000
"xxx", "xxx", "testvarchar", "vc2000", -9, "varchar", 2000, 4000, <Null>, <Null>, 1, "", <Null>, -9, <Null>, 4000, 1, <Null>, 2000, 1043, 0, 1, 46594, 0, 2000
"xxx", "xxx", "testvarchar", "vc500", -9, "varchar", 500, 1000, <Null>, <Null>, 1, "", <Null>, -9, <Null>, 1000, 2, <Null>, 500, 1043, 0, 2, 46594, 0, 500

I do not understand very well the variation between precision and length but my real question comes on DATA_TYPES. What i see is that using a MaxVarchar configuration for less than the varchar declared size it changes from VARCHAR (12) to LONGVARCHAR(-1). However using the UNICODE it comes from NVARCHAR(-9) to a code -10 which i can not find what really means. I expected to see -16? for LONGNVARCHAR.
So what does -10 points to? and where can i find the mapping for it?

Please look at sqlucode.h.

regards,
Hiroshi Inoue