How to get ADONET VerticaTypes for table columns

joergschaber

Hi,
is there an easy way to get table column VerticaType using the ADONET driver? Currently, I get the column types from a SELECT statement and convert those to ADONET VerticaTypes.
Naively I tried

VerticaType type= (VerticaType)Enum.Parse(typeof(VerticaType), typeName, true);

However, e.g., from v_catalog.columns or v_catalog.types I get "int" or "Integer", but VerticaType is BigInt and, therefore, the above does not work.

best, Jörg

Hibiki

@joergschaber Can you try to use the following codes?

DataTable table = connection.GetSchema("Columns", new string[] { "<Database Name>", "<Schema Name>", "<Table Name>", "<Column Name>" });
foreach (DataRow row in table.Rows)
{
    foreach (DataColumn column in table.Columns)
    {
        Console.WriteLine(row["COLUMN_NAME"] + " : " + (VerticaType)Enum.Parse(typeof(VerticaType), row["DATA_TYPE"].ToString()));
    }
}

joergschaber

Yes, that works!
However, when I speciy all 4 restrictions, I only get one row with meta-info for the specified column, such that

(VerticaType)Enum.Parse(typeof(VerticaType), Rows[0]["DATA_TYPE"].ToString()));

is sufficient.

When I wnat to get the meta-info for all columns of a table with one GetSchema-command, then

DataTable table = connection.GetSchema("Columns", new string[] { "<Database Name>", "<Schema Name>", "<Table Name>", null});
foreach (DataRow row in table.Rows)
{
        Console.WriteLine(row["COLUMN_NAME"] + " : " + (VerticaType)Enum.Parse(typeof(VerticaType), row["DATA_TYPE"].ToString()));
}

Hibiki

@joergschaber Yes, you are right. If the column name is null, it gets all columns info. If the table name is also null, it gets all columns of all tables.

Just information. You can use the following fields.

• TABLE_CAT
• TABLE_SCHEM
• TABLE_NAME
• COLUMN_NAME
• DATA_TYPE
• TYPE_NAME
• COLUMN_SIZE
• BUFFER_LENGTH
• DECIMAL_DIGITS
• NUM_PREC_RADIX
• NULLABLE
• REMARKS
• COLUMN_DEF
• SQL_DATA_TYPE
• SQL_DATETIME_SUB
• CHAR_OCTET_LENGTH
• ORDINAL_POSITION
• IS_NULLABLE

It seems the data type of VARCHAR and LONG VARCHAR returned by GetSchema is not matched with VerticaType. If you see it returns 12, please recognize it as VARCHAR. In the case of -1, it is LONG VARCHAR. Please let me confirm with the engineering team about this behavior.

joergschaber

Hi Hibiki,

yes, I noticed that the VARCHAR and LONG VARCHAR is not matched with VerticaType, however, for Insert and Update operations it still seems to work.

Hibiki

Just information. The issue that the wrong VerticaType is returned for VARCHAR and LONG VARCHAR will be fixed in Feb 2022 release.

joergschaber

Hi Hibiki,

I noticed a similar problem with TIMESTAMPTZ. When I have a table with a column as TIMESTAMPTZ and try to get the type using
(VerticaType)Enum.Parse(typeof(VerticaType), row["DATA_TYPE"].ToString())

the ADONET.Driver version 11.1 returns Timestamp, i.e. 93, even through row["TYPE_NAME"] == TimestampTz.
Thus, when I get the tyble and column info suing 'GetSchema'
the column TYPE_NAME = TimestampTz, but DATA_TYPE == 93. It should be 1093. Seems like another bug to me.

joergschaber

By the way, indedd with driver version 11.1 the wrong VerticaType is returned for VARCHAR and LONG VARCHAR is fixed!
However, now there (still) is the issued with timestampTz returning the wrong Vertica type.

Hibiki

@joergschaber
Any data type with time zone is not listed in 'Vertica and ODBC Data Type Translation' section.
https://www.vertica.com/docs/11.1.x/HTML/Content/Authoring/ConnectingToVertica/ClientODBC/VerticaAndODBCDataTypeTranslation.htm

This is because Vertica ODBC driver supports ODBC 3.5 which doesn't support the time zones. It seems it supports the time zones in ODBC 3.8.
https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/c-data-types-in-odbc?view=sql-server-ver16

So, if you need to handle the time zone data, please cast it to VARCHAR on Vertica side and receive it on the client, and convert it to the timestamp with time zone.

joergschaber

Well, there is a VerticaType.TimestampTz. It is just not recognized correctly, when I parse the row["DATA_TYPE"] that I get from the getSchema command. I have to handle it myself:

DataTable cols = _dbConnection.GetSchema(
                        "Columns",
                        new[] { "<DataBase>", schemaTable[0], schemaTable[1], null });
                    foreach (DataRow row in cols.Rows)
                    {
                        VerticaType dataType =
                            (VerticaType)Enum.Parse(typeof(VerticaType), row["DATA_TYPE"].ToString());
                        if (row["TYPE_NAME"].ToString() == "TimestampTz") // can be removed when bug is fixed.
                        {
                            dataType = VerticaType.TimestampTz;
                        }

Hibiki

Yes, please use your workaround to avoid this limitation.
I hope we will implement new driver version to recognize the timestamp with time zone when we support the high version's ODBC.