Output value from a stored procedure returning None when called from automation script

Question

I am trying to call a stored procedure from an automation script in Maximo.

The stored procedure is being called correctly and is running from the script fine, the problem I am running into is when it returns the output value it is setting it as None when it should be a string.

I have tested the stored procedure in SQL Server and I am getting the correct string I want when I call the procedure.

My automation script call looks like this:

storedProcedureWithParameter = '{call dbo.insertdelbkp3(?,?,?)}';
callableStatement = dbConnection.prepareCall(storedProcedureWithParameter);  
callableStatement.setString(1, pMon); 
callableStatement.setString(2, ArchData);
callableStatement.registerOutParameter(3, Types.VARCHAR);
callableStatement.execute(); 
output = str(callableStatement.getString(3));
print(output)

Does anyone have a thought on what is going wrong? Thanks.

EDIT****** For more information my stored procedure is archiving old records and should return a string when done. When I call my procedure in this script the records are getting archived but it is not returning my string. If I call the stored procedure inside SQL it archives records and returns the string as it should.

EDIT2****** The automation script is in Maximo being triggered by a cron task if that provides any more insight

EDIT3****** This is the stored procedure I am calling from my script. The stored procedure it calls(dbo.insertintobkp3) is fully functional and working fine.

USE [maxsb76]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER Procedure [dbo].[insertdelbkp3]

@mOld varchar(10), @TabList varchar(500), @logMessage VARCHAR(MAX) OUTPUT

as 

DECLARE @sp varchar(500),@tp varchar(20)

BEGIN

DECLARE @oldRecToArch VARCHAR(MAX), --inserts into woarchrecords that are ready for arch
        @rowcount int, --count for how many values are inserted into WOARCHRECORDS
        @inDelMessage VARCHAR(MAX)

SET @sp=@TabList

SET @logMessage = 'List of backup tables are : '+@sp+'\n'
print 'List of backup tables are : '+@sp

if(((Select CAST(@mOld AS INT)) < 60) or (@mOld=NULL))

   SET @mOld='60'

--inserts records to be archived into woarchrecords
SET @oldRecToArch = 'insert into dbo.woarchrecords(wonum, siteid) select wonum, siteid from dbo.workorder where status in (''CAN'', ''CLOSE'') and statusdate < dateadd(year,-'+@mOld+'/12,getdate())'


execute(@oldRecToArch)
SELECT @rowcount = @@ROWCOUNT
SET @logMessage = @logMessage + Convert(Varchar(12),@rowCount) +' new archivable records inserted into WOARCHRECORDS \n'
print Convert(Varchar(12),@rowCount) +' new archivable records inserted into WOARCHRECORDS'

DECLARE spcur cursor for select value from string_split(@sp,',')

OPEN spcur

FETCH next from spcur into @tp

WHILE 1=1

    BEGIN
        --checks that we were given tables to alter then seperates them and passes each table to be updated
        IF @@FETCH_STATUS=0

            BEGIN
            SET @logMessage = @logMessage + 'Currently Archiving Table '+SUBSTRING(@tp,1,LEN(@tp)-3)+'\n'
            print 'Currently Archiving Table '+SUBSTRING(@tp,1,LEN(@tp)-3)

            if((SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='dbo' AND TABLE_NAME=@tp) is not null)
                exec dbo.insertintobkp3 @mOld=@mOld,@TabList=@tp,@insertDeleteMessage=@inDelMessage output
                SET @logMessage = @logMessage + @inDelMessage
            FETCH next from spcur into @tp

            END

        IF @@FETCH_STATUS=-1

            BREAK

    END

print @logMessage

CLOSE spcur

DEALLOCATE spcur

END

Are the braces necessary? Is the outbound parameter being lost due to the scope inside the braces being lost? — Preacher, Nov 15 '19 at 04:16
Have you tried `exec` instead of `call`? On Microsoft SQL Server `call someprocedure` gives you the error message: `Msg 2812 Level 16 State 62 Line 1 Could not find stored procedure 'call'.` — AlwaysLearning, Nov 15 '19 at 14:05
The stored procedure is running and working when I call it from the automation script, it just isn't returning my output variable back to the script. Instead its returning None. — Quinn, Nov 15 '19 at 14:06
Just for laughs try `exec dbo.insertdelbkp3 ?, ?, ? output` and see what happens. — AlwaysLearning, Nov 15 '19 at 14:09
So I tried the exec and adding output and neither worked alone or together. — Quinn, Nov 15 '19 at 14:18
What is wrong with the syntax? The procedure call from my script is working and archiving my records just not returning string. I can post sections of my procedure but I have 3 procedures nested within each other. It would be alot to post. — Quinn, Nov 15 '19 at 14:22
Well how do you expect us to assist if we dont have a full picture of everything? Maybe you should review the process and verify if it is actually returning anything at all. — Doug Coats, Nov 15 '19 at 14:23
I have verified in SQL that the script is working perfectly fine. It archives the records and returns my string to the output value which I can print in SQL. Something about my call isn't returning the out. Is their any specific part of the procedure you think would be help to post? I just didn't want to post it all. — Quinn, Nov 15 '19 at 14:25
well multiple people have told you that youre using WRONG syntax for executing the Procedure. SQL SERVER does not recognize CALL. The correct syntax is EXEC. — Doug Coats, Nov 15 '19 at 14:27
I don't think you understand... And capitalizing WRONG does not help any with my question. This call is not from SQL it is from an automation script in Maximo which is calling the procedure. Here is a link on how to do such a thing for you https://learn.microsoft.com/en-us/sql/connect/jdbc/using-a-stored-procedure-with-output-parameters?view=sql-server-ver15 — Quinn, Nov 15 '19 at 14:29
Please show the stored procedure. If it's too much, hollow it out to a sproc that does more or less the same thing in a couple of lines (pretends to do work, puts a time string in the output) - minimal complete repro of the problem — Caius Jard, Nov 15 '19 at 14:46
As an aside, have you tried copying the code from the MSDN you linked and running that? Does it work ? — Caius Jard, Nov 15 '19 at 14:50
Try using SET NOCOUNT ON. And then RETURN the value you need instead of printing them. I have a suspicion that the unnecessary communication is whats causing your issue — Doug Coats, Nov 15 '19 at 18:21
Also youre using a table variable, not a temp table. Kind of a big difference. — Doug Coats, Nov 15 '19 at 18:22
I tried the SET NOCOUNT ON and nothing changed. I can't use RETURN because it can only use integers. Could you specify where you are talking about me using the table variable vs the temp table, thanks. — Quinn, Nov 15 '19 at 23:03
I was reading through your insertdelbkp3 code to see if there were circumstances where `@logMessage` could become null. What's the likelihood that the first table in your `@TabList` input parameter is *not* owned by the `dbo` schema? On the first cursor iteration `@inDelMessage` is null and if the first table is not owned by the `dbo` schema then `SET @logMessage = @logMessage + @inDelMessage` will evaluate to a null result. Table names shorter than three characters could also throw `Msg 536 Level 16 State 8 Line 1 Invalid length parameter passed to the substring function.` — AlwaysLearning, Nov 17 '19 at 10:02
All the tables are in the dbo schema. The weird thing is when I call my stored procedure from inside SQL the procedure works, archiving the records and returning the correct string. When I call it from the automation script it archives the records but does not return the string. — Quinn, Nov 18 '19 at 01:17

score 0 · Answer 1 · answered Nov 18 '19 at 16:42

0

Hi guys thanks for the comments and help. I figured out the SetString function for the ArchData was not passing my full list of tables. I instead fetched tables from the stored procedure and it is now working.

answered Nov 18 '19 at 16:42

Quinn

89
2
10

Output value from a stored procedure returning None when called from automation script

1 Answers1