Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2012
    Posts
    37

    Unanswered: Return value from as400 script (c# asp.net)

    As per another thread, I was converting scripts from sql to as400. I have been able to get all the scripts created in system i navigator, and am now testing them on our application.

    The goal with this script is to check to see if a user has been created, otherwise create the user. It is called from IdbCommand.ExecuteScalar. However, the value that comes back is always null. I am thinking it is with how I am trying to return the value.

    First I tried it without the "out @result", Get Diagnotistics result = Row_Count, tried with "return @result" and even tried to "select" the variable (errored out).


    Code:
    CREATE OR REPLACE PROCEDURE STSIQRY_SECURITY_CREATEUSER(
    	@SICode char(6),
    	@CSUSER char(30),
    	@RLNme char(35),
    	@RFNme char(20),
    	@RMNme char(20),
    	@RSuffx char(3),
    	@RPhone char(20),
    	@RPhon2 char(20),
    	@RAdd1 char(40),
    	@RAdd2 char(40),
    	@RCity char(40),
    	@RState char(2),
    	@RZip char(9),
    	@RCntry varchar(50),
    	@RCommt varchar(60),
    	@REmail char(60),
    	@RStat char(1),
    	@RSDat date,
    	@RRsnSt varchar(35),
    	@RCntac char(1),
    	@RTitle varchar(50),
    	@ROrgan varchar(50),
    	@RQual1 varchar(20),
    	@RQual2 varchar(20),
    	@RQual3 varchar(20),
    	@PM46bA char(40),
    	@RolId char(6),
    	@Crtuser char(30),
    	@DPUPDUSR char(30),
    	@SecQues varchar(200),
    	@SecAns varchar(200),
    	OUT @result INT)
    	LANGUAGE SQL
    	MODIFIES SQL DATA
    	RESULT SETS 1
    BEGIN
    
    DECLARE @ACCNUM NUMERIC;
    DECLARE @Crtdate DATE;
    DECLARE @OBF003 TIME;
    DECLARE @Chgdate DATE;
    DECLARE @OBF006 TIME;
    
    set @result=0;
    IF not EXISTS(
    	SELECT *
    	FROM CMUSRRQP
    	WHERE CSUSER=@CSUSER and SICODE=@SICODE
    	) 
    then
    
    
    SET @ACCNUM=(SELECT IFNULL(max(ACCNUM),0) + 1
    	FROM CMUSRRQP
    	WHERE SICode=@SICode);
    	
    set @OBF003=current time;
    SET @OBF006=@OBF003;
    SET @Crtdate=current date;
    SET @Chgdate=@Crtdate;
    SET @RSDat = @Crtdate;
    
    	INSERT INTO CMUSRRQP
               (SICode, ACCNUM, CSUSER, RLNme, RFNme, RMNme, RSuffx, RPhone, RPhon2, RAdd1, RAdd2, RCity
    			,RState, RZip, RCntry, RCommt, REmail, RStat, RSDat, RRsnSt, RCntac, RTitle, ROrgan, RQual1, RQual2
    			,RQual3, PM46bA, RolId, Crtuser, Crtdate, OBF003, DPUPDUSR, Chgdate, OBF006)
         VALUES
               (@SICode, @ACCNUM, @CSUSER, @RLNme, @RFNme, @RMNme, @RSuffx, @RPhone, @RPhon2, @RAdd1,
    			@RAdd2, @RCity, @RState, @RZip, @RCntry, @RCommt, @REmail, @RStat, @RSDat, @RRsnSt, @RCntac,
    			@RTitle, @ROrgan, @RQual1, @RQual2, @RQual3, @PM46bA, @RolId, @Crtuser, @Crtdate, @OBF003, @DPUPDUSR, @Chgdate, @OBF006);
    
    	INSERT into CMUSRREP(SICode,ACCNUM,SecQues,SecAns)
    	values (@SICode,@ACCNUM,@SecQues,@SecAns);
                                                                                                                                                                                                   
    
    	set @result = 1;
    	end if;
    END

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I think your problem is with this statement:

    Code:
    (SELECT IFNULL(max(ACCNUM),0) + 1
    	FROM CMUSRRQP
    	WHERE SICode=@SICode)
    If no row matches @SICode, the the entire expression is NULL.

    You need to make it like this:
    Code:
    coalesce((SELECT IFNULL(max(ACCNUM),0) + 1
    	FROM CMUSRRQP
    	WHERE SICode=@SICode),1)
    Andy

  3. #3
    Join Date
    Jan 2012
    Posts
    37
    Thanks, but it still returns null. All the scripts that return 0, 1, or rowcount are returning null.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What specifically is "returning null"?

    Andy

  5. #5
    Join Date
    Jan 2012
    Posts
    37
    Code:
    var scalar = command.ExecuteScalar();
                if (scalar == null)
                {
                    scalar = 0;
                }
    Scalar should be 0 or 1, however, it is null.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    How does the return value of command.ExecuteScalar() depend on the stored procedure your provided?

    Andy

  7. #7
    Join Date
    Jan 2012
    Posts
    37
    ExecuteScalar is the function used to execute the stored procedure and grab the return value. If ExecuteScalar is null, which it shouldn't be, then in my opinion, something in the script needs to be changed. The sql equivalent, which isn't much difference, works just fine.

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    How would that function know how to tie the OUT parameter as the return value. Stored Procedures can have multiple OUT parameters.

    Your Stored Procedure is not returning anything, hence the NULL. You would need to add a RETURN statement to actually return something.

    Andy

  9. #9
    Join Date
    Jan 2012
    Posts
    37
    "First I tried it without the "out @result", Get Diagnotistics result = Row_Count, tried with "return @result" and even tried to "select" the variable (errored out)."

    If you don't mind, would you give me an example of how you would write the procedure?

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I think the problem is you are using the wrong routine to call a Stored Procedure.

    IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •