Data Replication 2
Results 1 to 7 of 7

Thread: Read sql server float from openvms with cobol

  1. #1
    Rudi is offline Junior Member
    Join Date
    Apr 2010
    Location
    brussels
    Posts
    6
    Rep Power
    0

    Read sql server float from openvms with cobol

    Hello,

    I'm trying to read a sqlserver float on OpenVMS using cobol and odbc.

    I can't seem to display or get the correct value.

    Here is an excerpt the table definition with value and an excerpt of the cobol program.

    sql server table definition

    discount type float value 1.1

    cobol excerpt:

    working-storage section.
    01 sales-record.
    03 stor_id pic x(4).
    03 filler pic x.
    03 discount comp-2.

    77 sql-char pic s9 value 1.
    77 sql-double pic s9 value 8.

    procedure division.
    ...
    move "select stor_id, discount from sales" to sql-text.
    call "SQLPrepare" using by value hstmt by reference sql-text
    by value 45 giving rc end-call.

    move SQL-CHAR to arg.
    call "SQLBindCol" using
    by value hstmt
    by value 1
    by value arg
    by reference stor_id of sales-record
    by value 5
    by reference work-len
    giving rc end-call.
    move SQL-DOUBLE to arg.
    call "SQLBindCol" using
    by value hstmt
    by value 2
    by value arg
    by reference discount of sales-record
    by value 8
    by reference work-len
    giving rc end-call.
    call "SQLExecute" using by value hstmt giving rc end-call.
    call "SQLFetch" using by value hstmt giving rc end-call.
    if rc = 0 then
    display stor_id of sales-record " "
    discount of sales-record with conversion

    At runtime i have the following value:
    $ run test
    6380 5.687500000000000E-01

    Any suggestions what i'm doing wrong.

    Thanks and regards,
    Rudy

  2. #2
    Join Date
    Sep 2006
    Posts
    233
    Rep Power
    10
    Normally such problem are related to the extra choice of floating point formats on OpenVMS. Which double float format is used in your COBOL program? G-Float, D-Float, S-Float?

    Note that answers are expected to be different depending on your actual platform (VAX, ALPHA and ITANIUM)

    Try to use NAV_UTIL EXECUTE <datasource> to run the query you are interested in to see that the value is properly returned (to exclude issues with client/server and the SQL Server driver).

    Try also to use explicit conversion to a numeric like in:

    PHP Code:
    select n_namenav_convert(n_nationkey,numeric(10,3)) from nation limit to 4 rows
    Here NAV_CONVERT is used rather than CONVERT so that the conversion would not be passed to SQL Server but instead be processed on the result.

    In COBOL, I think that would be MYNUM PIC S9(7)V9(3)
    By Dror Harari

    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  3. #3
    Rudi is offline Junior Member
    Join Date
    Apr 2010
    Location
    brussels
    Posts
    6
    Rep Power
    0

    still no results

    Hi,

    To answer your questions:

    - I'm on alpha and the default for float is d_float
    - nav_util execute pubs
    > select discount from sales works fine

    But from cobol i'm still having problems.

    To what datatype is sqlserver float mapped in odbc.

    I've used sql-numeric (with decimal point), sql-double but no change at all.

    If i had to use nav_convert and using pic s9(7)v999 comp, to which odbc datatype does it map ?

    Thanks and regards,
    Rudy

  4. #4
    Join Date
    Sep 2006
    Posts
    233
    Rep Power
    10
    Hi Rudy,

    > I'm on alpha and the default for float is d_float

    The Attunity ODBC provider on OpenVMS currently maps SQLDouble to the G-Float data type. Hence your problem is indeed explained by floating point type mismatch. You can verify this by compiling the COBOL program with /FLOAT=GFLOAT, although it is clearly not a solution since you probably have other data in DFLOAT.

    > To what datatype is sqlserver float mapped in odbc.

    That is not the right question to ask - the relevant question is what OpenVMS data type can you bind to via ODBC. The DFLOAT type is not currently bind-able but SQL-NUMERIC (value is 2) is bind-able and its underlying data type is a numeric string with right-overpunch (NRO) which, if I remember correctly, is mapped in COBOL as "PIC S9(m)V9(n)" without any COMP and with 'm' representing the number of digits to the left of the decimal point and 'n' representing the number of digits to the right of the decimal point.

    > I've used sql-numeric (with decimal point), sql-double but no change at all.

    If you use SQL-NUMERIC you _will_ get a different result, that is for sure. I think it will be the right one too.

    > If i had to use nav_convert and using pic s9(7)v999 comp...

    Forget about nav_convert -- the problem is in the binding and the data type in SQL Server can be any type with the same issue. Just bind using SQL-NUMERIC and use the right COBOL datatype as mentioned above.
    By Dror Harari

    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  5. #5
    Rudi is offline Junior Member
    Join Date
    Apr 2010
    Location
    brussels
    Posts
    6
    Rep Power
    0

    partly working...

    Hello DrorHarari,

    Compiling with /float=gfloat works ok, i have to check with the customer if that is fine. As a bank they shoudn't use floatings :) I will try to convince the customer to change its sql server datatype to something, though this database is not directly under his control.

    I've played with sql-numeric and using pic s9(5)v999 on the cobol side but the sqlbindcol return an -1.

    I'm attaching my test program where you might spot my error.

    Thanks,
    Rudy

  6. #6
    Join Date
    Sep 2006
    Posts
    233
    Rep Power
    10
    > Compiling with /float=gfloat works ok,

    Yes, as I mentioned, SQL-DOUBLE on OpenVMS is programmatic binding to G-Float. Note that this is NOT DEPENDENT on the data type in the database. It does not matter if a column is double, single, int, numeric or decimal. If you bind as SQL-DOUBLE you will get G-Float. If you use SQL-Numerc instead you will have to work with Cobol numeric.

    > i have to check with the customer if that is fine. As a bank they
    > shouldn't use floatings

    Someone that uses D-Float is unlikely to change because any file that the COBOL program writes (and later reads) will likely contain D-Float encoded data so switching is a much more involved process than just changing the compilation option.

    As for the bank using floating points, I have seen many cases where floating points are used with various statistics where perfect precision does not exist by definition...

    > I will try to convince the customer to change its sql server
    > datatype to something, though this database is not directly
    > under his control.

    There is no need - you should be able to work without any change to the customer's database.

    > I've played with sql-numeric and using pic s9(5)v999 on the
    > cobol side but the sqlbindcol return an -1.
    > I'm attaching my test program where you might spot my error.

    The program seems fine - upon further inspection it appears that SQL-Numeric is not supported on OpenVMS so I suggest you bind it to a text field (SQL-CHAR) and then move it to whatever type you need it in.

    /d
    By Dror Harari

    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  7. #7
    Rudi is offline Junior Member
    Join Date
    Apr 2010
    Location
    brussels
    Posts
    6
    Rep Power
    0

    all is fine

    Hi,

    All is fine now,

    there are 2 options:

    - compiling with /float=gfloat
    - using sql-char and using function numval to convert to a numeric

    Thanks for all your help.

    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •