Relativity and Linked Servers

My Cobol server is running in Linux and I am using Relativity to populate some data to a website. I have a linked server setup using the 64bit driver. First off looking to see if anyone else has experience using Relativity in this manner as currently the only way I can see data is to use OpenQuery with my linked server. In this manner my alphanumeric data is showing properly, and regular integers are okay, but decimal numbers are being stripped off such that 1.987654 shows up a 1.0 and is causing countless issues. In my case the field has a picture clause of 9(06)v9(06) and was pulled as an import from my file definition and using a tool like ODBC test shows proper data. So my problem appears to lie with OpenQuery and possibly the way Relativity is defining my data for sharing with Open Query. Any help would be greatly appreciated.

  • There is a previous post about rounding errors in linked servers that I don't seem to be able to comment on anymore.

    community.microfocus.com/.../13179.aspx

    The suggested solution seems to be to cast it to a decimal in SQL -- which does not seem to be working.  Using the query...

    SELECT TOP 1 double_price, decimal_2_1_price, CONVERT(DECIMAL(2,1), decimal_2_1_price) decimal_2_1_price_post, decimal_3_2_price, CONVERT(DECIMAL(3,2), decimal_3_2_price) decimal_3_2_price_post, decimal_4_3_price, CONVERT(DECIMAL(4,3), decimal_4_3_price) decimal_4_3_price_post

    FROM OPENQUERY(data, '

    SELECT CONVERT(1.555, SQL_DOUBLE) double_price, CONVERT(1.5, SQL_DECIMAL) decimal_2_1_price, CONVERT(1.55, SQL_DECIMAL) decimal_3_2_price, CONVERT(1.555, SQL_DECIMAL) decimal_4_3_price

    FROM <<some table here>>>

    ');

    The results show...

    double_price: 1.555 -- correct, but an imprecise datatype

    decimal_2_1_price: 1.0

    decimal_2_1_price_post: 1.0

    decimal_3_2_price: 1.00

    decimal_3_2_price_post: 1.00

    decimal_4_3_price: 1.000

    decimal_4_3_price_post: 1.000

    Interestingly here, it is correctly interpreting the datatype, as shown by giving the correct number of zeros back for each result, just not giving the decimal values back in the results.  I have not been able to figure out the exact syntax for a CAST instead of convert.  But potentially this helps add some more information into this problem.

  • I'm not familiar with OpenQuery, but there is a conflict with Relativity's ODBC 3.0 support and ADO, and the symptoms are exactly the same. For reasons that are not clear, ADO never tells Relativity how many digits of precision it wishes, and thus the default is 0.  We have a solution to this in Relativity 2.1, which is almost through QA, so you'll have a fix soon.

  • Michael, Thank you for your comment. It appears that we were using the non ADO  driver in ODBC. When I switch to ADO the decimal numbers are now showing however now I am seeing all fields from the file instead of just the fields that are in the table (based on record type). There does not seem to be any documentation that I can find regarding the proper use of Relativity catalogs with unix once they have been created.

  • Verified Answer

    Cliff,

    Are you sure that you are using the correct table?  The only way an ODBC client can 'see' any 'fields' is as columns in a table.  If you have a table defined that is not based upon record type, as well as the one that is, that could be the issue.  This behavior is baked in at a level that is below the particular interface level - e.g. ODBC or JDBC.  I am not an expert on ADO, but my guess is that it is still constrained by the same rules.

    There is nothing unique about Relativity on Unix regarding catalogs.  The metadata in the catalog is identical between Windows and Unix.  Of course, on Unix, the table connections, which contain path name information, would have to conform to the requirements of the host Unix system.

    Tom Morrison
    Hill Country Software 

  • Thanks for the reply Tom.  I currently only have the one table defined from my FD to make it easy to begin with. In that file most of the fields belong to the record type that the table uses. A particular field, lets call it COST is not defined in the record type and shows up upon a query of the table. The problem appears to be that this field is a sublevel of something higher in the structure that is a part of the record key, so even though it reports the field as not belonging to the record type it is like it is inheriting properties from above. So if I delete the table and then remove the record type from the upper level and redo the table I am now good with that.  Do you use Relativity and if so in what environment?

  • I use Relativity in all environments, Windows and Unix/Linux, RM, Micro Focus and Visual COBOLs.  I am typically in an RM/COBOL environment, but there is no operational difference - only the underlying COBOL fie manager is different.  In fact, Relativity for Micro Focus COBOL predates Visual COBOL by more than a decade.

    I would have to see the FD and Table representations from the Designer to understand your particular issue.  I would also use something like ODBCTest (the Microsoft version here and download from here) to check the actual columns that are being reported on the table.

    Have you made this table INSERT-able or UPDATE-able?

  • Tom,

    Good to hear that others are having success with the product. I have been struggling with the lack of documentation and some suggestions on "best practices". I have been using ODBCTest but only the 32-bit version. Do you have any helpful guidelines for someone just starting out with Relativity?  Also can you give me the syntax you are using for inserts? Currently I get a "function sequence error" - with a statement like this that matches up to the way the data is display in ODBCTest

    INSERT PCNTRL_RECORD VALUES ("0003223000N674  ", "0003223", 0, "N674  ", "V", "0000000", 0, 0, 0, "965   0001490000000000000000", "965   ", 0, 1.49, 0, 0, 0, 0, 20140301, 20140224, 20140224, 0, "test            ")

  • Verified Answer

    The documentation, on both a process 'How to' level, and on a reference level (screen by screen), is in the Relativity Designer help file, RELDBDSN.CHM.

    SQL syntax supported is in another help file: RELDBM32.CHM.

    "Function sequence error" is an indication that you are not going through all the steps in ODBCTest to connect to an ODBC data source, prepare and execute your SQL statement.  This requires an understanding of the ODBC API.  Basic steps are: Full Connect, type the statement, Highlight it, SQLExecDirect, fetch results.

  • The first problem was that the SQL statement has a typo in it.  The SQL Parser was correctly flagging this but Cliff didn't realize what the << ??? >> in the error message meant.  The second problem was that following the failure of the SQLExecDirect, a Get Data All was performed, and it was the thing returning "Function sequence error".

  • Just to clarify the knowledge of the two help files that Tom mentioned is highly important. The issue with the Insert statement was that I was using a quote symbol instead of an apostrophe to encase alpha settings and the 2nd issue was that the error message being returned is not very descriptive. For example if you try to INSERT into a table that is not "writable" the message that comes back with the SQLExecDirect is just a SQL_ERROR=-1 and Relativity  pcbErrorMsg =186. If there is a error listing somewhere that would be helpful. Obviously after seeing the documentation I was missing the INTO statement before the table name but it would be nice if the error message stated that instead of a << ??? >> after the table name.