How to get ADONET VerticaTypes for table columns

joergschaberjoergschaber Vertica Customer
edited November 2021 in General Discussion

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

Best Answer

  • HibikiHibiki Vertica Employee Employee
    Answer ✓

    @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()));
        }
    }
    

Answers

  • joergschaberjoergschaber Vertica Customer
    edited November 2021

    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()));
    }
    
  • HibikiHibiki Vertica Employee Employee

    @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.

  • joergschaberjoergschaber Vertica Customer
    edited November 2021

    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.

  • HibikiHibiki Vertica Employee Employee

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

  • joergschaberjoergschaber Vertica Customer
    edited June 2022

    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.

  • joergschaberjoergschaber Vertica Customer

    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.

  • HibikiHibiki Vertica Employee Employee

    @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.

  • joergschaberjoergschaber Vertica Customer

    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;
                            }
    
  • HibikiHibiki Vertica Employee Employee

    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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file