|
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 parametershave
and returns either 000 if found and 001 if not found. I don't think I
the correct syntax for the commandtext. The following passes ?0 and 00 tobut
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
I can not get that 001 back to my program. I'm out of ideas as to how toin
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
DKDEXE type *N not found.list
Thanks
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
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.
________________
--
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
