Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] FOC1400 on creating synonym for stored procedure
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] FOC1400 on creating synonym for stored procedure
 Login/Join
 
Platinum Member
posted
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, Martin

This 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
 
Posts: 161 | Registered: March 29, 2013Reply With QuoteReport This Post
Master
posted Hide Post
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
 
Posts: 807 | Registered: April 23, 2003Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1944 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
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 
 
Posts: 5686 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 161 | Registered: March 29, 2013Reply With QuoteReport This Post
Master
posted Hide Post
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    


WebFOCUS Master created by Synonym Builder:
FILENAME=UPD_DEPT, SUFFIX=SQLMSS  , $
  SEGMENT=INPUT, SEGTYPE=S0, $
    FIELDNAME=@DEPARTMENTIDIN, ALIAS=P0001, USAGE=I6, ACTUAL=I4,
      MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
    FIELDNAME=@NAMEIN, ALIAS=P0002, USAGE=A50V, ACTUAL=A50V,
      MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
    FIELDNAME=@GROUPNAMEIN, ALIAS=P0003, USAGE=A50V, ACTUAL=A50V,
      MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
  SEGMENT=OUTPUT, SEGTYPE=S0, PARENT=INPUT, $
    FIELDNAME=@RETURN_VALUE, ALIAS=P0000, USAGE=I11, ACTUAL=I4, $  


Update Focexec:
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
 
Posts: 807 | Registered: April 23, 2003Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 161 | Registered: March 29, 2013Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] FOC1400 on creating synonym for stored procedure

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.