Question about SQLColumns results on VARCHAR types

From: Jacobo Sánchez <jsanchez(at)denodo(dot)com>
To: pgsql-odbc(at)postgresql(dot)org
Subject: Question about SQLColumns results on VARCHAR types
Date: 2017-01-12 09:12:56
Message-ID: 0aa10fc0-9de9-2af0-445d-35ae5d109bc0@denodo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-odbc

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

Responses

Browse pgsql-odbc by date

  From Date Subject
Next Message howchange 2017-01-13 10:19:45 Row cannot be located for updating
Previous Message martin.baumgaertner 2017-01-11 22:03:24 have you seen that already?