Re: Question about SQLColumns results on VARCHAR types

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