| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
02-06-12, 14:46
|
|
Registered User
|
|
Join Date: Jan 2012
Posts: 37
|
|
|
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
|
|
02-06-12, 15:46
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
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
|
|
02-07-12, 08:41
|
|
Registered User
|
|
Join Date: Jan 2012
Posts: 37
|
|
|
|
Thanks, but it still returns null. All the scripts that return 0, 1, or rowcount are returning null.
|
|
02-07-12, 09:12
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
What specifically is "returning null"?
Andy
|
|
02-07-12, 10:01
|
|
Registered User
|
|
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.
|
|
02-07-12, 10:13
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
How does the return value of command.ExecuteScalar() depend on the stored procedure your provided?
Andy
|
|
02-08-12, 10:19
|
|
Registered User
|
|
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.
|
|
02-08-12, 10:30
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
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
|
|
02-08-12, 14:08
|
|
Registered User
|
|
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?
|
|
02-08-12, 14:15
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Return value from as400 script (c# asp.net)
