Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

How to get ADONET VerticaTypes for table columns

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 Employee
    Accepted 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

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

  • 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 Employee

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

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.