[no solution]WEBFOCUS can't get "FOR XML" out put from SQL Server?
I am trying to get a hierarchy XML output from SQL server using 'FOR XML PATH' query. It works in SQL Server but in webfocus it returns an error message:
(FOC1400) SQLCODE IS 6819 (HEX: 00001AA3) XOPEN: 42000 : Microsoft SQL Native Client: [42000] The FOR XML clause is not allowed i : n a CURSOR statement.
The webfocus version is 7.6.x. Does higher webfocus version support this?This message has been edited. Last edited by: bug,
7.66 and 7.704 System: Windows / AIX / Linux Output: Mostly HTML, with some PDF, Excel and Lotus(!)
January 20, 2016, 04:09 PM
BabakNYC
I could test it for you in 8105 if you provide code that works with generic data in SQL server like Northwind.
You might also get the same results with ON TABLE PCHOLD FORMAT XMLThis message has been edited. Last edited by: BabakNYC,
WebFOCUS 8206, Unix, Windows
January 20, 2016, 04:24 PM
bug
quote:
Originally posted by BabakNYC: I could test it for you in 8105 if you provide code that works with generic data in SQL server like Northwind.
You might also get the same results with ON TABLE PCHOLD FORMAT XML
"ON TABLE PCHOLD FORMAT XML" outputs flat XML. I want a hierarchy XML that can only be done on SQL server.
Can you try any SQL select statement, just add
FOR XML AUTO
at the end of that statement?
Thanks.
7.66 and 7.704 System: Windows / AIX / Linux Output: Mostly HTML, with some PDF, Excel and Lotus(!)
January 21, 2016, 08:36 AM
BabakNYC
I tested in 8105. I don't get an error when I run this SQL:
ENGINE SQLMSS SET DEFAULT_CONNECTION TEST SQL SQLMSS PREPARE SQLOUT FOR select orderid, CustomerID from [dbo].[Orders] FOR XML AUTO END TABLE FILE SQLOUT PRINT * END
However, the output is a single BLOB column with content I can't decipher. The column TITLE is similar to what I see in SQL Server but printing it returns data I don't recognize. But PRINT of a BLOB column may be the reason why I get these
WebFOCUS 8206, Unix, Windows
January 21, 2016, 11:55 AM
Michael L Meagher
I ran this code in 8.0.09:
ENGINE SQLMSS SET DEFAULT_CONNECTION SNAKE
SQL SQLMSS PREPARE SQLOUT FOR
SELECT [BI_PROJECT_BY]
,[BI_AWARD_BY]
,[PROJECT_ID]
,[BI_BUDGET_YEAR]
,[DESCR]
FROM [FMSIBIDEV].[dbo].[BI_DM_DETAIL]
WHERE
PROJECT_ID IN ('210284','207453','217219')
ORDER BY BI_PROJECT_BY, BI_BUDGET_YEAR
FOR XML AUTO
END
-RUN
TABLE FILE SQLOUT
PRINT *
END
WebFOCUS 8.2.03 - Production WebFOCUS 8.2.04 - Sand Box Windows 2012 R2 Server HTML, PDF, Excel In FOCUS since 1980
January 28, 2016, 11:03 AM
bug
I guess that's not possible. Thanks for the test.
7.66 and 7.704 System: Windows / AIX / Linux Output: Mostly HTML, with some PDF, Excel and Lotus(!)
January 28, 2016, 11:05 AM
bug
quote:
Originally posted by BabakNYC: I tested in 8105. I don't get an error when I run this SQL:
ENGINE SQLMSS SET DEFAULT_CONNECTION TEST SQL SQLMSS PREPARE SQLOUT FOR select orderid, CustomerID from [dbo].[Orders] FOR XML AUTO END TABLE FILE SQLOUT PRINT * END
However, the output is a single BLOB column with content I can't decipher. The column TITLE is similar to what I see in SQL Server but printing it returns data I don't recognize. But PRINT of a BLOB column may be the reason why I get these
BabakNYC, Can you use "ON TABLE PCHOLD FORMAT XML" in the last output?
7.66 and 7.704 System: Windows / AIX / Linux Output: Mostly HTML, with some PDF, Excel and Lotus(!)