[SOLVED] FOC1400 on creating synonym for stored procedure
I am trying to create synonym for a stored procedure I have on SQL Server (Azure SQL).
I get (FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 24000 Without any further explanation. I looked on techsupport and forums but did not find any further explanation. Can anyone assist where to look and what to try?
Some further information on the stored procedure. It is a simple update statement which requires no input parameters and gives a "return 1" to force a result to provide to WebFocus.
CREATE PROCEDURE [dirxml].[ImportProvider] AS BEGIN UPDATE .... SET ... FROM .. ; return 1; END
I have Execute permission for the user used in the adapter. I can execute stored procedure with same user from SQL Server Management Studio.
Any sugggestions? Thanks, MartinThis message has been edited. Last edited by: FP Mod Chuck,
WebFocus 8206M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
April 15, 2020, 05:08 PM
David Briars
I've created synonyms for stored procedures that provide an answer set and then used the WebFOCUS TABLE command to create reports.
I didn't even know of the possibility of creating a synonym for a stored procedure that does an update.
From your code it looks like the TABLE will be returning the return code the sp sets, so I guess in that sense you are going to get back an 'answer set'.
Interesting.
When I looked up a '24000' SQL error code up, the entries I saw said the issue is something like an 'invalid cursor state'.
Thanks for posting, hopefully someone can help.This message has been edited. Last edited by: David Briars,
Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster
April 15, 2020, 06:15 PM
FP Mod Chuck
Martin
David's post made me think maybe you need a COMMIT after the update..
Thank you for using Focal Point!
Chuck Wolff - Focal Point Moderator WebFOCUS 7x and 8x, Windows, Linux All output Formats
April 16, 2020, 03:15 AM
Tony A
quote:
David's post made me think maybe you need a COMMIT after the update.
Close, you need an END after every SQL (signified by the semicolon) to commit the SQL. so -
CREATE PROCEDURE [dirxml].[ImportProvider]
AS
BEGIN
UPDATE ....
SET ...
FROM .. ;
END
next SQL statement;
END
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
April 17, 2020, 02:51 PM
Martin vK
Thanks David, Chuck and Tony for your responses. It helped me figuring out the issue and find a solution.
The extra END in the stored procedure caused an error for the create procedure.
After trying much I found out that WebFocus refuses to work with the update statement in de stored procedure.
I finally have rewritten the sp to use dynamic sql, and that is what WebFocus did accept. So the original which did not work was:
CREATE PROCEDURE [dirxml].[ImportProvider]
AS
BEGIN
UPDATE ....
SET ...
FROM .. ;
return 1;
END
What finally worked was following:
CREATE PROCEDURE [dirxml].[ImportProvider]
AS
BEGIN
DECLARE @v_SQL nvarchar(4000) = ''
SELECT @v_SQL = 'UPDATE ....
SET ...
FROM .. ;
EXEC sp_executesql @v_SQL
return 1;
END
WebFocus 8206M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
April 18, 2020, 01:58 PM
David Briars
I created a Stored Procedure in MS SQL Server with an UPDATE command.
Then I was able to create the WebFOCUS metadata (.mas/acx files) AOK with the Synonym Builder in WebFOCUS Application Studio.
Stored Procedure:
USE [AdventureWorks2017Test]
GO
DROP PROCEDURE [HumanResources].[upd_Dept]
GO
CREATE PROCEDURE HumanResources.upd_Dept
(
@DepartmentIDIN int,
@NameIN varchar(50),
@GroupNameIN varchar(50)
)
AS
BEGIN TRY
BEGIN
UPDATE [HumanResources].[Department]
SET [Name] = @NameIN, [GroupName] = @GroupNameIN, [ModifiedDate] = CURRENT_TIMESTAMP
WHERE [DepartmentID] = @DepartmentIDIN;
RETURN 1
END
END TRY
BEGIN CATCH
RETURN 999
END CATCH
SET PAGE = OFF
-*
-* Department Name/Group to be updated.
-*
-SET &DEPTID = '206';
-SET &NAME = 'WebFOCUS Awesomeness';
-SET &GROUPNAME = 'Information Technology';
-*
-* State of row before update.
-*
TABLE FILE DEPARTMENT
"Before Update"
PRINT *
WHERE DEPARTMENTID EQ &DEPTID
ON TABLE HOLD FORMAT HTMTABLE AS HOLD1
ON TABLE SET STYLE *
INCLUDE = enblue-medium.sty, $
ENDSTYLE
END
-RUN
-*
-* Update row in DEPARTMENT SQL Server Table.
-*
TABLE FILE UPD_DEPT
PRINT @RETURN_VALUE
WHERE @DEPARTMENTIDIN EQ &DEPTID
WHERE @NAMEIN EQ '&NAME'
WHERE @GROUPNAMEIN EQ '&GROUPNAME'
ON TABLE SAVE AS MYSAVE
END
-RUN
-READ MYSAVE &RETURNVALUE.I11.
-TYPE RETURNVALUE IS &RETURNVALUE
-SET &RETURNDESC = IF &RETURNVALUE EQ 1 THEN 'Row updated.' ELSE
- 'Row not updated.';
-*
-* Display updated row.
-*
TABLE FILE DEPARTMENT
"After Update"
"Return Code/Description: &RETURNVALUE/&RETURNDESC"
PRINT *
WHERE DEPARTMENTID EQ &DEPTID
ON TABLE HOLD FORMAT HTMTABLE AS HOLD2
ON TABLE SET STYLE *
INCLUDE = enblackbluepurple.sty, $
ENDSTYLE
END
-RUN
-*
-* Display reports to the user.
-*
-HTMLFORM BEGIN
!IBI.FIL.HOLD1;
<BR>
!IBI.FIL.HOLD2;
-HTMLFORM END
I am using the Adventureworks2017 MS SQL Server sample database for this model.
Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster
April 19, 2020, 02:17 AM
Martin vK
Thanks David. I learnt something new with TRY and CATCH.
I wondered why your example worked and my did not, until I finally found out through your example the real problem.
My stored procedure was like:
CREATE PROCEDURE [dirxml].[ImportProvider]
AS
BEGIN
UPDATE ....
SET ...
FROM .. ;
return 1;
END
This worked fine in SQL Server Management Studio, but WebFocus stumbled over the the semicolon behind "return 1". Once I removed that semicolon WebFocus was willing to work with it.....
Martin.This message has been edited. Last edited by: Martin vK,
WebFocus 8206M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster