Re: Stored procedure problem

See the last message on this thread:

http://www.ibm.com/developerworks/forums/message.jspa?messageID=13925192

http://www-912.ibm.com/n_dir/nas4apar.nsf/ALLAPARS/SE35276


Fix available in V6.1 provider with Service Pack SI34289 (Feb 2009) or later.


Craig Pelkie


----- Original Message ----- From: <BButterworth@xxxxxxxxxxxxxx>
To: <systemidotnet@xxxxxxxxxxxx>
Sent: Monday, August 09, 2010 3:10 PM
Subject: [SystemiDotNet] Stored procedure problem

Hello Everyone,

I'm trying to call an RPG subprocedure in a service program via SQL as a
stored procedure and am getting an error from the .NET data provider (ver.
6.1). I've tried a number of different things, but haven't had any success
fixing the problem. So I thought I'd see if anyone on the list has
encountered this error and would be kind enough to offer assistance.

The prototype for my RPG procedure is:

d getTechOnCall pr
d inPlaza 3a value
d outTechId 10a
d outRtnCod 10i 0
d outErrMsg 100a

The subprocedure is currently being called and working with other RPG
programs in production. The SQL stored procedure is defined as follows:

CREATE PROCEDURE BLAKE1.GETTECHONCALL (
IN PLAZA CHAR(3) ,
OUT TECHID CHAR(10) ,
OUT RTNCOD INTEGER ,
OUT ERRMSG CHAR(100) )
LANGUAGE RPGLE
SPECIFIC BLAKE1.GETTECHONCALL
NOT DETERMINISTIC
NO SQL
CALLED ON NULL INPUT
EXTERNAL NAME 'BLAKE1/TLS002(GETTECHONCALL)'
PARAMETER STYLE GENERAL ;

My C# code is as follows:

public static TollTech GetTechOnCall(short plaza, iDB2Connection conn)
{
iDB2Command cmd;
int returnCode;
string pagerId;
string error;
TollTech tech = null;

try
{
conn.Open();
cmd = new iDB2Command("call BLAKE1.GetTechOnCall(@plaza, @pagerId,
@rtnCod, @error)", conn);

cmd.CommandType = CommandType.Text;
cmd.DeriveParameters();

cmd.Parameters["@plaza"].Value = string.Format("{0:000}", plaza);
cmd.Parameters["@pagerId"].Value = " ";
cmd.Parameters["@rtnCod"].Value = 0;
cmd.Parameters["@error"].Value = " ";

try
{
cmd.ExecuteNonQuery();

returnCode = Convert.ToInt32(cmd.Parameters["@rtnCod"].Value);
pagerId = cmd.Parameters["@pagerId"].Value.ToString().Trim();
error = cmd.Parameters["@error"].Value.ToString().Trim();
}
catch (iDB2Exception ex)
{
throw ex;
}
etc...

When I run the code in debug (VS 2010 Express), I encounter an exception
at the cmd.ExecuteNonQuery() statement:

Message = "A conversion error occurred."
MessageCode = 6107
MessageDetails = "Row: 1, Column: 4"
SqlState = ""

I've tried a simplified version of the stored procedure with the input
plaza (3a) and a return message (20a), but get the same result (the error
is with "Row: 1, Column: 2", however). I've read some articles online and
referenced the ADO .NET Redbook, and it seems like I am coding things
correctly in terms of both the stored procedure definition and DB2 data
provider, but obviously something is wrong. Any guidance would be much
appreciated.

Blake
--
This is the .net use with the System i (SystemiDotNet) mailing list
To post a message email: SystemiDotNet@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/systemidotnet
or email: SystemiDotNet-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/systemidotnet.