10 Replies Latest reply: Jul 9, 2013 8:16 AM by Kgronau-Oracle RSS

    Oracle <-> MS SQL server, problem with DATE

    user609503

      Current HS options are:

       

      HS_FDS_TRACE_LEVEL = 255

      HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so

      HS_FDS_FETCH_ROWS=1

      HS_FDS_SQLLEN_INTERPRETATION=32

       

      The following picture contains 2 screenshots.

       

      http://maslovd.no-ip.org/public/doc/date_problem.png

       

      1. Selecting from table in MS SQL server.

      2. Selecting in Oracle via DB link.

       

      Any suggestions?

        • 1. Re: Oracle <-> MS SQL server, problem with DATE
          user609503

          Maybe FreeDTS and unixODBC configuration files may give additional information.

           

          ----------------------------

          /usr/local/etc/freetds.conf

           

          [SQL1]

              host = 192.168.8.142

              port = 1433

              tds version = 8.0

              client charset = cp1251

           

          --------------------------------

          /usr/local/etc/locales.conf

           

          [default]

              date format = %b %e %Y %I:%M:%S:%z%p

           

          [en_US]

              date format = %b %e %Y %I:%M:%S:%z%p

              language = us_english

              charset = iso_1

           

          [es_ES]

              date format = %b %d %Y %I:%M%p

              language = spanish

              charset = iso_1

           

          [pt_BR]

              date format = %d/%m/%Y %H:%M

              language = Portuguese

              charset = iso_1

           

          [it_IT]

              date format = %d/%m/%Y %H:%M

              language = Italiano

              charset = iso_1

           

          -----------------------------------

          I suppose the problem is in locale. Then the question is - how to set Russian locale?

          • 2. Re: Oracle <-> MS SQL server, problem with DATE
            user609503

            I've turned on SQL trace and see this.

             

               [ODBC][14305][SQLDescribeCol.c][474]

               Column Name = [DateFrom]          
               Data Type = -9           

             

            For comparison:

             

               Column Name = [NumberTo]          
              Data Type = -> 4          
            • 3. Re: Oracle <-> MS SQL server, problem with DATE
              user609503

              Looking in unixODBC sources (SQLDescribeCol.c:474) I can only say that data type here depends on ODBC protocol version.

               

              Data Type = %s\

              ...

              __sptr_as_string( s2, data_type )

               

              ...

               

              *data_type=__map_type(MAP_SQL_D2DM,statement->connection, *data_type);

               

              --------------------------------------------------

              __connection.c

               

              SQLSMALLINT __map_type(int map, DMHDBC connection, SQLSMALLINT type)

              {

                int driver_ver=connection->driver_act_ver;

                int wanted_ver=connection->environment->requested_version;

               

                if(driver_ver==SQL_OV_ODBC2 && wanted_ver==SQL_OV_ODBC3) {

                  switch(map) {

                  case MAP_SQL_DM2D:

                    type=sql_new_to_old(type);

                    break;

               

                  case MAP_SQL_D2DM:

                    type=sql_old_to_new(type);

                    break;

              ............

               

              static SQLSMALLINT sql_new_to_old(SQLSMALLINT type)

              {

                  switch(type) {

                  case SQL_TYPE_TIME:

                    type=SQL_TIME;

                    break;

               

                  case SQL_TYPE_DATE:

                    type=SQL_DATE;

                    break;

               

                  case SQL_TYPE_TIMESTAMP:

                    type=SQL_TIMESTAMP;

                    break;

                  }

                  return type;

              }

               

              static SQLSMALLINT c_old_to_new(SQLSMALLINT type)

              {

                  switch(type) {

                  case SQL_C_TIME:

                    type=SQL_C_TYPE_TIME;

                    break;

               

                  case SQL_C_DATE:

                    type=SQL_C_TYPE_DATE;

                    break;

               

                  case SQL_C_TIMESTAMP:

                    type=SQL_C_TYPE_TIMESTAMP;

                    break;

                  }

                  return type;

              }

               

              .....................

               

              --------------------------------------------------

              sql.h

               

              #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

              #endif

              ############ SQL_DATETIME = 9, but not -9 !  I couldn't find -9 at all.

               

              So the data type for this column determined wrong (did I know it before digging in sources? ).

               

              Before debugging in ODBC driver I'd like to solve the problem by changing settings.

              • 4. Re: Oracle <-> MS SQL server, problem with DATE
                Kgronau-Oracle

                What I'm wondering here is why does the DateForm column gets returned as data type -9 (SQL_WVARCHAR).


                Could you please post the SQL Server table definition?

                What's the FreeTDS version and on which platform did you configure DG4ODBC?


                - Klaus


                • 5. Re: Oracle <-> MS SQL server, problem with DATE
                  Kgronau-Oracle

                  -9 is SQL_WVARCHAR the Unicode data type of SQL_VARCHAR. Please post the requested info mentioned in my previous update.

                  • 6. Re: Oracle <-> MS SQL server, problem with DATE
                    user609503

                    Oh, I've missed sqlucode.h, where

                     

                    #define SQL_WVARCHAR        (-9)

                     

                    is defined.

                     

                    > Could you please post the SQL Server table definition?


                    This is the first step I should have been done, thanx.


                    At first look, it says "DATE".

                     

                    SELECT column_name AS [Name],

                           DATA_TYPE

                    FROM   INFORMATION_SCHEMA.Columns

                    WHERE  table_name = 'Pools'


                    Name    DATA_TYPE

                    ID    int

                    PoolOperator    int

                    PoolRegion    int

                    NumberFrom    int

                    NumberTo    int

                    DateFrom    date

                    DateTo    date

                     

                    But at second view, it says date(-9).

                     

                    exec sp_columns 'Pools'

                     

                    TABLE_QUALIFIER  TABLE_OWNER    TABLE_NAME    COLUMN_NAME    DATA_TYPE    TYPE_NAME

                    master    npool    Pools    ID    4    int

                    master    npool    Pools    PoolOperator    4    int

                    master    npool    Pools    PoolRegion    4    int

                    master    npool    Pools    NumberFrom    4    int

                    master    npool    Pools    NumberTo    4    int

                    master    npool    Pools    DateFrom    -9    date

                    master    npool    Pools    DateTo    -9    date

                     

                    The MS SQL server create statement was:

                     

                    create table npool."Pools" (PoolOperator int, PoolRegion int, NumberFrom int, NumberTo  int, DateFrom date, DateTo  date )

                     

                    I've read about SQL server data types and created another table using datetime datatype.

                     

                    create table npool."Pools2" (ID bigint, PoolOperator bigint, PoolRegion bigint, NumberFrom bigint, NumberTo  bigint, DateFrom datetime, DateTo  datetime )

                     

                    ("bigint" instead of "int", also)

                     

                    exec sp_columns 'Pools2' now says

                     

                    DateFrom    11    datetime

                    DateTo    11    datetime

                     

                    and selecting via DB link is OK.

                     

                    Thank you.

                    • 7. Re: Oracle <-> MS SQL server, problem with DATE
                      user609503

                      > What's the FreeTDS version and on which platform did you configure DG4ODBC?


                      This is not important now, I suppose.

                      But to fulfill the topic - I've dowloaded and compiled freetds-0.91.

                      The platform was Linux Cent OS 5 and Solaris 10.

                      • 8. Re: Oracle <-> MS SQL server, problem with DATE
                        Kgronau-Oracle

                        Just created a demo table in my SQL Server:

                        drop table Pools;

                        create table Pools( DateFrom date, DateTo  datetime );

                        insert into Pools values (getdate(),GETDATE());

                        select * from Pools;

                         

                        When I select it using the gateway the content is displayed correctly:

                        DateFrom                             DateTo

                        ---------------------------------------- ------------------

                        2013-07-09                           09-JUL-13

                         

                         

                         

                        Looking at the ODBC trace shows for the first column also the -9 data type:

                        [ODBC][24049][1373361638.137050][SQLDescribeColW.c][460]

                                        Exit:[SQL_SUCCESS]

                                                Column Name = [D]

                                                Data Type = 0x7fffc560defc -> -9

                                                Column Size = 0x7fffc560deb0 -> 10

                                                Decimal Digits = 0x7fffc560df04 -> 0

                                                Nullable = 0x7fffc560df08 -> 1

                         

                        The second one is mapped to

                        [ODBC][24049][1373361638.137380][SQLDescribeColW.c][460]

                                        Exit:[SQL_SUCCESS]

                                                Column Name = [D]

                                                Data Type = 0x7fffc560defc -> 93

                                                Column Size = 0x7fffc560deb0 -> 23

                                                Decimal Digits = 0x7fffc560df04 -> 3

                                                Nullable = 0x7fffc560df08 -> 1

                         

                        FreeTDS version I'm using is

                        libtdsodbc.so, DriverVer:0.91

                        ODBC Driver Manager is unixODBC release 2.3

                         

                         

                        What happens when you add to your gateway init file the parameter:

                        HS_NLS_NCHAR = UCS2

                        • 9. Re: Oracle <-> MS SQL server, problem with DATE
                          user609503

                          > What happens when you add to your gateway init file the parameter:

                          > HS_NLS_NCHAR = UCS2

                           

                          The behaviour changes.

                           

                          create table npool.Pools2( DateFrom date, DateTo  datetime );

                           

                          exec sp_columns Pools2

                           

                          DateFrom    -9    date

                          DateTo    11    datetime

                           

                          insert into npool.Pools2 values('01.01.2013', '01.01.2013')

                          insert into npool.Pools2 values('01.01.2013', '01.01.2013')

                          insert into npool.Pools2 values('02.01.2013 10:00:00', '02.01.2013 10:00:00')

                          insert into npool.Pools2 values('02.01.2013 11:00:00', '02.01.2013 11:00:00')

                           

                          -------------------------

                          Without "HS_NLS_NCHAR = UCS2":

                           

                          select * from npool."Pools2"@REGIONS

                           

                          DateFrom    DateTo

                          2    01.01.2013

                          2    01.02.2013

                          2    01.02.2013 10:00:00

                          2    01.02.2013 11:00:00

                           

                          [ODBC][25146][SQLDescribeCol.c][474]

                                          Exit:[SQL_SUCCESS]

                                                  Column Name = [DateFrom]

                                                  Data Type = 0x7fffad91ceb8 -> -9

                           

                          [ODBC][25146][SQLDescribeCol.c][474]

                                          Exit:[SQL_SUCCESS]

                                                  Column Name = [DateTo]

                                                  Data Type = 0x7fffad91ceb8 -> 93

                           

                           

                           

                          -------------------------

                           

                          With "HS_NLS_NCHAR = UCS2":

                           

                          select * from npool."Pools2"@REGIONS

                           

                          DateFrom    DateTo

                          2013-01-01    01.01.2013

                          2013-02-01    01.02.2013

                          2013-02-01    01.02.2013 10:00:00

                          2013-02-01    01.02.2013 11:00:00

                           

                          ODBC trace doesn't change.

                           

                          $ odbcinst -j

                          unixODBC 2.2.11

                          • 10. Re: Oracle <-> MS SQL server, problem with DATE
                            Kgronau-Oracle

                            Looks better now although the second record seems to be incorrect when the insert from above does not contain a typo.

                            At least HS_NLS_NCHAR = UCS2 should be used as as all SQL Server nvarchar columns are always stored using UCS2 character set.

                            Regarding the format discrepancy we won't be able to change this as the date column is retrieved from the SQl Server as a nvarchar whereas the smalldate column as a date which is then mapped to an oracle date. The data type mapping is done by the ODBC driver so when this needs to be changed you have to change the source code (but that's really risky) or you can get in touch with the ODBC vendor and ask if they could change the mapping.

                            BTW, using unixODBC 2.2.1 might also cause some toruble so i would also recommend here to move to 2.3 release as it for example supports the 64bit standard by default without the need to compile everything correctly.

                             

                            - Klaus