This discussion is archived
10 Replies Latest reply: Jul 9, 2013 6:16 AM by kgronau RSS

Oracle <-> MS SQL server, problem with DATE

user609503 Newbie
Currently Being Moderated

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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    -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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    > 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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    > 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 Guru
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points