Need help with Service Stored Procedures (SSPs)

Author: tomas.johansson@abilita.fi (tomas.johansson)

Hi, I'm struggling with an external stored procedure I'm trying to call from Uniface. If I run the stored procedure directly within Uniface using the sql command like so: sql "exec STR_TEST '%%name.test',''","mss" I do get value back in $result. However if I try to access the same stored procedure using Service Stored Procedures instead, using the following code: activate "TUSTRUDOK2".STR_TEST(in, out) then I do not receive any value in the out variable. One important thing to note is that in the stored procedure I'm inserting data into a table. If I modify the stored procedure to only perform a select statement I do not have any problems getting value back in the out variable of my SSP. Also note that even though I do not receive a return value the insert statement is still performed correctly. I'm working against Microsoft SQL Server and I'm currenlty using Uniface MSS driver U4.0. Here is the procedure not working with SSP: [spoiler] CREATE PROCEDURE [STR_TEST]   @name as varchar(50) AS BEGIN     declare @returnid table (id uniqueidentifier)     INSERT INTO tjtest(         name     )     output inserted.id into @returnid     VALUES(         @name     )     select 'Hello ' + @name + '! ID: ' + CONVERT(VARCHAR(50), r.id) from @returnid r END GO [/spoiler] And here's a slightly simplified version which do work with SSP: [spoiler]CREATE PROCEDURE [STR_TEST]   @name as varchar(50) AS BEGIN  select 'Hello ' + @name + '! ID: ' + CONVERT(VARCHAR(50), NEWID()) END GO[/spoiler] For completeness here's my test table to where I'm inserting data: [spoiler] CREATE TABLE tjtest(      [id] [uniqueidentifier] NULL,      [name] [varchar](50) NULL ) GO ALTER TABLE [tjtest] ADD CONSTRAINT [DF_tjtest_id]  DEFAULT (newsequentialid()) FOR [id] GO [/spoiler] So how can I configure the SSP to also return back the resulting value when I'm inserting data inside the stored procedure? And then to my second question, when calling a stored procedure that performs inserts I have to explicitly call commit in Uniface in order for the transaction to complete, is there a way to circumvent this and perform insert operations inside the stored procedure without Uniface interfering? Tomas

12 Comments

  1. Maybe I do not understand you, but I do not see any output parameter in your code. When you look at the example in the documentation topic Service Stored Procedures on Microsoft SQL Server, it uses input and output parameters. CREATE procedure [WORKTODO].[STR_TEST]  @in char(40), @out char(8000) OUTPUT as begin set @out = @in end  


    Author: Theo Neeskens (tneeskens@itblockz.nl)
  2. Have you tried wrapping the insert statement in  set nocount on   set nocount off It may be returning a bad internal status.... 


    Author: Iain Sharp (i.sharp@pcisystems.co.uk)
  3. Theo Neeskens said Maybe I do not understand you, but I do not see any output parameter in your code. When you look at the example in the documentation topic Service Stored Procedures on Microsoft SQL Server, it uses input and output parameters. CREATE procedure [WORKTODO].[STR_TEST]  @in char(40), @out char(8000) OUTPUT as begin set @out = @in end    

    Sorry, this was my bad, when creating a simplified test to show you guys here on the forum my problem I accidentally removed too much. So to clarify I do have an OUTPUT parameter.

    Iain Sharp said Have you tried wrapping the insert statement in  set nocount on   set nocount off It may be returning a bad internal status....   

    Thank you very much Ian! This solved my Issue with not getting data back when using SSP.   Now on to my second problem, is there a way to not have to call commit inside Uniface proc when calling stored procedures that perform insert statements? If I execute the SSP using the code below and the underlying stored procedure performs an insert, the table remains locked by Uniface until I call commit. activate "TUSTRUDOK2".STR_TEST(in, out) What I would like todo is do the error handling and handle the commit and rollback from within the stored procedure instead of having todo it in Uniface proc, is this scenario even possible?   Tomas


    Author: tomas.johansson (tomas.johansson@abilita.fi)
  4. Tomas, Have you tried to do a "newinstance.... "transaction=TRUE" before your activate statement? I believe that should open a separate path / connection to the database - which you can then perform a separate commit/rollback on. Just remember to delete the instance afterwards in order to close / free up that extra connection . Regards, Knut


    Author: Knut (knut.dybendahl@gmail.com)
  5. Alternatively, try the 

    begin transaction jim ... ... commit transaction jim

    within the SSP. 


    Author: Iain Sharp (i.sharp@pcisystems.co.uk)
  6. @Knut Creating new instances of SSP's is not possible due to the requirement that SSP's are declared as Stateless components. Even if it was possible it defeats what I'm trying to accomplish, that is not having to call commit in Uniface but allow SQL Server to handle the transaction. @Iain Sharp Sorry but I've already tried that as well, I must admit I find it a bit strange that even if I wrap my procedure inside a transaction and call commit at the end I still have to call commit in Uniface proc.   Any other ideas?


    Author: tomas.johansson (tomas.johansson@abilita.fi)
  7. Tomas, Remember, when you go directly to the db (either via raw sql or via a SP) - the Uniface kernel / driver does not know what you're doing. Since you could be inserting / updating / deleting other records outside of the SP - Uniface would either be holding a lock or prepare to insert one or more rows. If the SP is running on the active path from Uniface, but is also called from other parts of your system outside of Uniface, maybe a parameter to the SP would be "I'm from a Uniface app - don't commit" whereas the other part(s) of your system that calls the SP either omit that parameter, or it sets "I'm not from a Uniface app - please commit". Apart from this - I'd say your fight is with the kernel / driver - and in this case, I believe the the kernel / driver is correct...Wink Knut


    Author: Knut (knut.dybendahl@gmail.com)
  8. If the kernel/driver does not know what I'm doing inside my SP then why doesn't the table remain locked if I only do a simple select inside my SP? I suppose I could live with the fact that I have to do commit/rollback in Uniface if I could get a reliable status back from my SP that something went wrong. So I modified my SP like below in hope that I would receive a statuscode back to Uniface telling me something went wrong and I could do a rollback instead of commit. But no, when running the SP below as a SSP from Uniface it will happily return status = 1 and the id output parameter will hold the value of the newly created guid instead of the (for me) expected result of status -1 and id = 00000000-0000-0000-0000-000000000000. Why is the Uniface driver ignoring the fact that I have a try catch block in my SP? [spoiler] CREATE  procedure [STR_TEST] @name varchar(50), @id varchar(40) OUTPUT, @status int OUTPUT as begin SET NOCOUNT ON; begin try --begin transaction jim declare @returnid table (id uniqueidentifier) declare @dummy int     INSERT INTO tjtest(         name     )     output inserted.id into @returnid     VALUES(         @name     )  select @id = CONVERT(VARCHAR(50), r.id) from @returnid r     set @dummy = 4/0 --this code is used to simulate an error inside the SP.      set @status = 1 --commit transaction jim end try begin catch   --rollback transaction jim   set @id = '00000000-0000-0000-0000-000000000000'   set @status = -1 end catch SET NOCOUNT OFF; end [/spoiler] Running the SP from within MS SQL Management Studio will give me the "correct" result, but as I've commented out the transaction/rollback code it will of course still insert the row. [spoiler]declare @id char(40) declare @status int exec [STR_TEST] 'sql', @id output, @status output select @id, @status[/spoiler]


    Author: tomas.johansson (tomas.johansson@abilita.fi)
  9. tomas.johansson said If the kernel/driver does not know what I'm doing inside my SP then why doesn't the table remain locked if I only do a simple select inside my SP? ... 

    I think this behaviour has nothing to do with Uniface: it is related to the standard MSS locking mode. If you need to move away from "read are locking write" default MSS behaviour I suggest you to have a look to connection modes available in the MSS driver USYS$MSS_PARAMS = lock isolation : level where level should be defined to support snapshot. Snapshots modes are available since SQL2005 but they got more mature and stable with 2008 and 2012. Snapshot support has some collateral effects on TempDB size because snapshot is maintained there. Hope this helps. Regards, Gianni


    Author: gianni (gianni.sandigliano@unifacesolutions.com)
  10. I think we have to put the locking behavior aside and concentrate on the second part, that is why is the catch part igonerd when calling the stored procedure from Uniface? Is there a setting somewhere that I need to change or what? I've run the query both from within Management Studio as well as created a simple .NET application using a ODBC command and in both cases the execution falls into the catch block inside the Stored Procedure. So what is Uniface (or my settings) doing differently when calling the SP from within Uniface? I had a look with SQL Profiler to see what is actually called when activating an SSP and this is what I found: [spoiler] declare @p1 int set @p1=NULL declare @p4 char(40) set @p4=NULL declare @p5 numeric(38,0) set @p5=1 exec sp_prepexecrpc @p1 output,N'dbo.STR_TEST','fdsf                                              ',@p4 output,@p5 output select @p1, @p4, @p5 exec sp_unprepare 8 [/spoiler] But calling that exakt same code directly in Management Studio works as expected for me, please help!


    Author: tomas.johansson (tomas.johansson@abilita.fi)
  11. Potentially has

    set ANSI_WARNINGS off
    set ARITHABORT off

    you  can test with 

    select SESSIONPROPERTY('ARITHABORT')

    You might find it better to use RAISERROR to generate your error to force the try/catch.  Iain


    Author: Iain Sharp (i.sharp@pcisystems.co.uk)
  12. Okay, setting either ANSI_WARNINGS or ARITHABORT to ON at the beginning of my procedure will cause Uniface to return $status = -150 but it still doesn't go into the catch block inside the procedure. So should I just be checking $status after activating my SSP and hope that if $status is equal to 0 everything *should* be okay? I must admit I do not feel very confident about relying on this statuscode rather than my own error logic inside the stored procedure.


    Author: tomas.johansson (tomas.johansson@abilita.fi)