Highlighted
New Member.
2824 views

Relativity and Linked Servers

Jump to solution

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.

0 Likes
2 Solutions

Accepted Solutions
Highlighted
Knowledge Partner
Knowledge Partner

RE: Relativity and Linked Servers

Jump to solution

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 


Tom Morrison
Consultant

View solution in original post

0 Likes
Highlighted
Knowledge Partner
Knowledge Partner

RE: Relativity and Linked Servers

Jump to solution

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.


Tom Morrison
Consultant

View solution in original post

0 Likes
11 Replies
Highlighted
Absent Member.
Absent Member.

RE: Relativity and Linked Servers

Jump to solution

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.

0 Likes
Highlighted
Micro Focus Frequent Contributor
Micro Focus Frequent Contributor

RE: Relativity and Linked Servers

Jump to solution

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 Schultz
Software System Developer - Senior Principal

0 Likes
Highlighted
New Member.

RE: Relativity and Linked Servers

Jump to solution

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.

0 Likes
Highlighted
Knowledge Partner
Knowledge Partner

RE: Relativity and Linked Servers

Jump to solution

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 


Tom Morrison
Consultant

View solution in original post

0 Likes
Highlighted
New Member.

RE: Relativity and Linked Servers

Jump to solution

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?

0 Likes
Highlighted
Knowledge Partner
Knowledge Partner

RE: Relativity and Linked Servers

Jump to solution

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 Morrison
Consultant

0 Likes
Highlighted
New Member.

RE: Relativity and Linked Servers

Jump to solution

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            ")

0 Likes
Highlighted
Knowledge Partner
Knowledge Partner

RE: Relativity and Linked Servers

Jump to solution

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.


Tom Morrison
Consultant

View solution in original post

0 Likes
Highlighted
Micro Focus Frequent Contributor
Micro Focus Frequent Contributor

RE: Relativity and Linked Servers

Jump to solution

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".

Michael Schultz
Software System Developer - Senior Principal

0 Likes
Highlighted
New Member.

RE: Relativity and Linked Servers

Jump to solution

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.

0 Likes
Highlighted
Knowledge Partner
Knowledge Partner

RE: Relativity and Linked Servers

Jump to solution

A final comment on the << ??? >> indication of an error.  We would all love to have error messages that pinpoint errors, but I thought some background on the 'why' of this type of error message would be in order.

SQL is a computer language and, as such, is compiled into an 'executable' much the same way COBOL or C or VB .Net is compiled into something that is 'executable'.  In particular, the 'executable' delivered by the SQL compiler in Relativity is called a 'query plan', and Relativity has tools to display the query plan in a graphical fashion.

So, the error message is coming out of a computer language compiler.  Compilers have often scanned several symbols ahead of the actual symbol (or lack of symbol) causing the error, and may have imperfect knowledge of what went wrong, but are certain that 'something' went wrong.  That is what happened here, and the << ??? >> indicates where the source scanner was when the error was discovered (it was expecting some reserved word, not a user word).

SQL compilers are somewhat special in that they are often invoked as part of a production environment.  In previous times, SQL statements would be prepared (i.e. pre-compiled) and the execution/query plan saved away for later use.  That still happens in high performance situations (especially mainframes), but in ad hoc reporting the PREPARE is often simply not done in favor of direct execution.  This fact places a premium on the performance of the SQL compiler as measured in time and optimization of the query plan (which can dramatically affect runtime performance).  This performance may come as a trade off on error message clarity and exactness, but is understandable because - unlike other compilers - the SQL compiler spends most of its life compiling and optimizing absolutely correct statements as quickly as possible.


Tom Morrison
Consultant

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.