Did you register your SPROC to DB2 ?
Doesn't look like it is being found in DKDEXE.
Regards,
Richard Schoen | Director of Document Management Technologies, HelpSystems
T: + 1 952-486-6802
RJS Software Systems | A Division of HelpSystems
richard.schoen@xxxxxxxxxxxxxxx
www.rjssoftware.com
------------------------------
message: 6
date: Fri, 6 Mar 2015 10:02:57 -0500
from: Steve Richter <stephenrichter@xxxxxxxxx>
subject: Re: Call Parameterize iSeries Store Procedure from .NET C#
did you use ODBC administration on the Windows PC? The user DSN name
setup there is then specified as the DSN= in the connection string.
static OdbcConnection OpenConnection(string Dsn, string UserName,
string Password)
{
var connString = "DSN=" + Dsn + "; UID=" + UserName +
"; PWD=" + Password + ";";
var conn = new OdbcConnection(connString);
conn.Open();
return conn;
}
-Steve
On Fri, Mar 6, 2015 at 12:52 AM, Guo, Fuwang <fg33@xxxxxxxx> wrote:
I have a stored procedure created on the AS400 that takes in 2 parameters
and returns either 000 if found and 001 if not found. I don't think I have
the correct syntax for the commandtext. The following passes ?0 and 00 to
the AS400. When I watch commandtext at the prepared statement it still
shows ? and ?. Not sure if it normally fills it in.
I can hardcode the parameters "CALL DKDEXE.DKSLICCHK1 ('AAAAAA1','*43
spaces*')" and the AS400 gets the correct data and returns a valid 001 but
I can not get that 001 back to my program. I'm out of ideas as to how to
call the store procedure and get the return code. What is wrong with my
code?
using (OdbcConnection cn = new OdbcConnection("Driver=Client Access
ODBC Driver (32-bit);System=X;Uid=U;Pwd=P"))
{
cn.Open();
using (OdbcCommand cm = cn.CreateCommand())
{
cm.CommandText = "CALL DKDEXE.DKSLI ('?','?')";
cm.CommandType = CommandType.StoredProcedure;
cm.Parameters.Add("P1", OdbcType.Char).Value =
"AAAAAAA1";
cm.Parameters["P1"].Size = 8;
cm.Parameters["P1"].Direction =
ParameterDirection.Input;
cm.Parameters.Add("P2", OdbcType.Char);
cm.Parameters["P2"].Size = 43;
cm.Parameters["P2"].Direction =
ParameterDirection.InputOutput;
cm.Prepare();
cm.ExecuteNonQuery();
string result =
cm.Parameters["P2"].Value.ToString();
}
I also tried {CALL DKDEXE.DKSLICCHK1 (?,?)} but I get an error ERROR
[42S02] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0204 - DKSLI in
DKDEXE type *N not found.
Thanks
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
------------------------------
message: 7
date: Fri, 6 Mar 2015 16:29:14 +0000
from: Ted Breedon <tbreedon@xxxxxxxxxxx>
subject: IBM i Upgrades and Virtualization
Hi guys,
Are there any caveats when upgrading IBM i that's virtualized - PowerVM, IVM or IBM i on i. Or is just the same standard procedure - run the pre-ver tool, read the memo to users, etc
Thanks
------------------------------
message: 8
date: Fri, 06 Mar 2015 10:38:33 -0600
from: CRPence <CRPbottle@xxxxxxxxx>
subject: Re: Call Parameterize iSeries Store Procedure from .NET C#
On 05-Mar-2015 23:52 -0600, Guo, Fuwang wrote:
I have a stored procedure created on the AS400 that takes in 2
parameters and returns either 000 if found and 001 if not found.
The SQL DDL for a CREATE PROCEDURE that exhibits the error would be
best for a reviewer of the scenario. If not LANGUAGE SQL, then the
parameter-list definitions of the invoked program would also best be
included.
I don't think I have the correct syntax for the commandtext.
Coded as "CALL DKDEXE.DKSLI ('?','?')", the request is not properly
coded using _parameter markers_; instead, that coded request is passing
two arguments, each as one-character strings, each string being the sole
question-mark character. Parameter markers are just the question-mark
character, without delimiters; the second attempt described as failing
coded correctly for parameter markers, but to match more closely,
apparently was intended to have been coded as?:
"CALL DKDEXE.DKSLI ( ? , ? )"
<<SNIP what presumably describes the garbage-in passing '?'s>>
I can hardcode the parameters "CALL DKDEXE.DKSLICCHK1
('AAAAAA1','*43 spaces*')" and the AS400 gets the correct data and
returns a valid 001 but I can not get that 001 back to my program.
Difficult [for me anyway] to understand what that implies. If the
second parameter is INOUT, then passing a literal is not legitimate, and
of course for the lack of a variable passed as an argument, a returned
value can not be made available to the invoker.
Also, that the routine name is not the same as coded in the
example(s), is at a minimum, confusing.
I'm out of ideas as to how to call the store procedure and get the
return code. What is wrong with my code?
using (OdbcConnection cn = new OdbcConnection(
"Driver=Client Access ODBC Driver (32-bit);System=X;Uid=U;Pwd=P"))
{
cn.Open();
using (OdbcCommand cm = cn.CreateCommand())
{
cm.CommandText = "CALL DKDEXE.DKSLI ('?','?')";
cm.CommandType = CommandType.StoredProcedure;
cm.Parameters.Add("P1", OdbcType.Char).Value = "AAAAAAA1";
cm.Parameters["P1"].Size = 8;
cm.Parameters["P1"].Direction = ParameterDirection.Input;
cm.Parameters.Add("P2", OdbcType.Char);
cm.Parameters["P2"].Size = 43;
cm.Parameters["P2"].Direction = ParameterDirection.InputOutput;
cm.Prepare();
cm.ExecuteNonQuery();
string result = cm.Parameters["P2"].Value.ToString();
}
Try in the above:
cm.CommandText = "CALL DKDEXE.DKSLI (?,?)";
I also tried {CALL DKDEXE.DKSLICCHK1 (?,?)} but I get an error
ERROR [42S02] [IBM][System i Access ODBC Driver][DB2 for i5/OS]
SQL0204 - DKSLI in DKDEXE type *N not found.
That alternate name apparently defines an external stored procedure
that refers to the EXTERNAL NAME 'DKDEXE/DKSLI' or similar? Sorry,
again, the CREATE PROCEDURE statement could help someone to diagnose
difficulties; as well, the clarification on the [changing] routine-name
specified on the CALLs could help someone to understand better.
FWiW: A difficulty with INOUT or OUT parameters in a PROCEDURE often
can be circumvented either by encapsulating the CALL to the PROCEDURE
inside a UDF and then instead obtaining the value from the UDF or by
coding the PROCEDURE to return the information as a result-set rather
than via a parameter. I recall I had to resort to returning a value in
a result set once, using an interface that had not [yet] provided any
support for anything other than input variables; I no longer recall what
the interface was, but REXX has always had that restriction.
--
Regards, Chuck
------------------------------
Subject: Digest Footer
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) digest list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/midrange-l.
------------------------------
End of MIDRANGE-L Digest, Vol 14, Issue 338
*******************************************
_______________
Confidentiality Notice: This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately.
________________
midrange.com
