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,

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,

Martin

David's post made me think maybe you need a COMMIT after the update..

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

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

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.

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,