ODBC Function SQLColumns returns different data types than expected.

Ivan_Lopes_Jr

Env: Vertica 6.1.2 with Vertica ODBC on Linux64. Issue: ODBC SQLColumns returns different data types than expected. I ran a little test program that compares Vertica with 2 other databases to show the difference. It should be easy to reproduce, I'm just using the standard SQLColumns ODBC call. Table schema: CREATE TABLE TEST1( ID INTEGER NOT NULL,FNAME CHAR(15), LNAME CHAR(20),AGE SMALLINT,GENDER CHAR(1),SCORE FLOAT, TOTAL DOUBLE PRECISION) Program results: Vertica: ./test SQLColumns retcode: 0 TEST1.ID type -5 TEST1.FNAME type -8 TEST1.LNAME type -8 TEST1.AGE type -5 TEST1.GENDER type -8 TEST1.SCORE type 8 TEST1.TOTAL type 8 DBMS A: ./test SQLColumns retcode: 0 test1.id type 4 test1.fname type 1 test1.lname type 1 test1.age type 5 test1.gender type 1 test1.score type 7 test1.total type 8 DBMS B: ./test SQLColumns retcode: 0 test1.id type 4 test1.fname type 1 test1.lname type 1 test1.age type 5 test1.gender type 1 test1.score type 8 test1.total type 8 Has anyone seen that? Any ideas? Thanks, Ivan

Daniel_Leybovic

Hi! 1. Are you sure it was same ODBC API for all apps? The data types returned for ODBC 3.x and ODBC 2.x applications may be different. 2. Looks like everything is ok: Header sql.h

  /* SQL data type codes */  #define SQL_UNKNOWN_TYPE        0  #define SQL_CHAR            1  #define SQL_NUMERIC         2  #define SQL_DECIMAL         3  #define SQL_INTEGER         4  #define SQL_SMALLINT        5  #define SQL_FLOAT           6  #define SQL_REAL            7  #define SQL_DOUBLE          8  #if (ODBCVER >= 0x0300)  #define SQL_DATETIME        9  #endif  #define SQL_VARCHAR        12    /* One-parameter shortcuts for date/time data types */  #if (ODBCVER >= 0x0300)  #define SQL_TYPE_DATE      91  #define SQL_TYPE_TIME      92  #define SQL_TYPE_TIMESTAMP 93  ...  

Vertica

  daniel=> select odbc_type, odbc_subtype, type_name from types;   odbc_type | odbc_subtype |         type_name           -----------+--------------+---------------------------          -7 |            0 | Boolean          -5 |            0 | Integer           8 |            0 | Float           1 |            0 | Char          12 |            0 | Varchar          -3 |            0 | Varbinary          -2 |            0 | Binary           2 |            0 | Numeric          10 |          101 | Interval Year          10 |          107 | Interval Year to Month          10 |          102 | Interval Month          10 |          103 | Interval Day          10 |          108 | Interval Day to Hour          10 |          109 | Interval Day to Minute          10 |          110 | Interval Day to Second          10 |          104 | Interval Hour          10 |          111 | Interval Hour to Minute          10 |          112 | Interval Hour to Second          10 |          105 | Interval Minute          10 |          113 | Interval Minute to Second          10 |          106 | Interval Second           9 |           91 | Date           9 |           92 | Time           9 |           92 | TimeTz           9 |           93 | Timestamp           9 |           93 | TimestampTz  (26 rows)  

Compare values for NUMERIC or DATE/TIME or VARCHAR - they are equals. --- So why results so differs? Because you compared different data types: * sizeof(Vertica INT) = 8 bytes, sizeof(SQL standard INT) = 4 bytes * INT of Vertica equals to BIGINT in Postgres, MSSQL, MySQL * FLOAT of Vertica equals to DECIMAL in Postgres, MSSQL, MySQL and so on... PS Take a look on results of DBMS A and DBMS B. They are differs too and dont you have a problem with it?

Ivan_Lopes_Jr

Hi Daniel, Thanks for your post. My problem was specific to the char columns with Vertica, I posted the other databases as a reference. I found a workaround for my issue, here's my educated guess of what's happening: In my case, Vertica returned the char columns as -8, which is SQL_WCHAR. (while I was expecting 1, SQL_CHAR, like my other databases and as you also posted from sql.h and from the types table) sqlucode.h:#define SQL_WCHAR (-8) I then found this Vertica odbc.ini parameter called ColumnsAsChars, which makes Vertica odbc reports the char columns as regular CHAR type. "ColumnsAsChar By default, when driver is in Unicode mode, character column type is reported as WCHAR. If ColumnsAsChar is set to 1 then driver in unicode mode will return CHA R type for character columns" By setting this in my odbc.ini, I got the expected return for char columns: :~/src/vertica > ./test SQLColumns retcode: 0 TEST1.ID type -5 TEST1.FNAME type 1 TEST1.LNAME type 1 TEST1.AGE type -5 TEST1.GENDER type 1 TEST1.SCORE type 8 TEST1.TOTAL type 8 Thanks, Ivan

Daniel_Leybovic

Hi! What you did? I suggest you to revert your changes, because you did a wrong thing (can cause to problems with national languages in ODBC apps) SQL standard has CHAR(n) for fixed-width n-character string and NCHAR(n) for string supporting an international character set (witch is SQL_WCHAR data type). (SQL Data Types: http://en.wikipedia.org/wiki/SQL#Data_types) Vertica supports national characters with CHAR(n) as variable-length UTF8-encoded UNICODE character string (i.e. Vertica(CHAR) = SQL(NCHAR)). UTF-8 represents ASCII in 1 byte, most European characters in 2 bytes, and most oriental and Middle Eastern characters in 3 bytes. So Vertica's CHAR/VARCHAR actually is NCHAR/NVARCHAR data types (like INT of Vertica equals to BIGINT of SQL), it's mean that SQL_WCHAR should be reported and not SQL_CHAR!!! SQL_WCHAR = -8 PS Vertica ODBC driver reports right data types!