× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Great. Thank you very much for your help. Will report back once the dev
puts in the stored procedure!!!

On Mon, Mar 9, 2015 at 2:36 PM, <rob@xxxxxxxxx> wrote:

It should directly call the COBOL program.


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: "Guo, Fuwang" <fg33@xxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 03/09/2015 01:52 PM
Subject: Re: Call Parameterize iSeries Store Procedure from .NET C#
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



I queried QSYS2.SYSPROCS and wasn't able to find the stored procedure.
Once
they created the stored procedure is there anything else that needs to be
done like the routine definition? Or once I call the stored procedure
it'll
directly call the cobol program?

On Mon, Mar 9, 2015 at 11:52 AM, <rob@xxxxxxxxx> wrote:

That should work. Unless they store the procedure into a different
library (or schema). You can trying querying QSYS2.SYSPROCS to see if
you
can find it.


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: "Guo, Fuwang" <fg33@xxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 03/09/2015 11:49 AM
Subject: Re: Call Parameterize iSeries Store Procedure from .NET
C#
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



I get the following error message when I try to go on the AS400 through
the
URL.

"The server encountered an internal error or misconfiguration and was
unable to complete your request.

Please contact the server administrator, [no address given] and inform
them
of the time the error occurred, and anything you might have done that
may
have caused the error.

More information about this error may be available in the server error
log."


I tried the same thing using the System i Navigator but the Procedures
tab
was empty. Does this mean that the stored procedure wasn't created? If
not
should I have him run the following if the syntax is correct?
CREATE PROCEDURE SLICHK (IN INPUT CHAR(8), OUT RET CHAR(3))
LANGUAGE COBOL
PARAMETER STYLE GENERAL
EXTERNAL NAME DKSLICCHK1

Thanks

On Mon, Mar 9, 2015 at 11:32 AM, <rob@xxxxxxxxx> wrote:

True, they do not write stored procedures.
However, someone, somewhere ran something like this:
CREATE PROCEDURE GIMMIEDATE (IN DATE_IN CHAR (6),
OUT DATE_OUT DATE)
LANGUAGE COBOL
PARAMETER STYLE GENERAL
EXTERNAL NAME GETDATE6C

Since they're pretty determined to disavow any knowledge of having
done
that try the following:
open up a browser to http://youribmi:2001 replacing youribmi with
whatever
the name of your partition of IBM i is.
Expand IBM i Management
Expand Database
Expand Databases
Expand the name of your IBM i (well, that is the default database
name...)
Expand Schemas (aka libraries).
Expand the schema (library) containing the procedure.
Select Procedures.
Select the procedure.
Actions, Generate SQL. This 'decompiles' the stored procedure into
the
source used to create it.
Copy and paste the generated SQL.

Show us a sample you called with it hard coded parameters.

Show us as you want to call it from ODBC, also with the definition of
the
variables used.


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: "Guo, Fuwang" <fg33@xxxxxxxx>
To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxx>
Date: 03/09/2015 10:48 AM
Subject: Re: Call Parameterize iSeries Store Procedure from
.NET
C#
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



I asked for the data snippet and this is what he gave me below. I just
found out that they do not write stored procedures. Now I'm completely
stump. It seems I was somewhat able to call the program with ODBC
using
hard coded parameters. Is it even possible to get a return value using
.NET
from the AS400 program?

LINKAGE SECTION.
01 CALL-PARMS.
03 CODE PIC X(2).
03 NUMBER PIC X(5).
03 INP-PARMS.
05 REQUEST-TYPE PIC X(01).
88 CHK VALUE '1'.
01 CALL-PARMS1.
03 LS-RTN-CD PIC X(03).
88 GOOD-RTN-CD VALUE '000'.
88 BAD-RTN-CD VALUE '001'.
03 RTN-MSG-1 PIC X(40).
88 FOUND
VALUE 'Reuested SLIC Present'.
88 NOT-FOUND
VALUE 'Reuested SLIC NOT Present'.

On Mon, Mar 9, 2015 at 10:03 AM, <rob@xxxxxxxxx> wrote:

In case you need to explain this to the COBOL guy, overloading would
work
like this.
GIMMIEDATE with a 6 character date would call his GETDATE6C program.
GIMMIEDATE with a 6 digit date would call his GETDATE6D program.
GIMMIEDATE with a 8 character date would call his GETDATE8C program.
GIMMIEDATE with a 8 digit date would call his GETDATE8D program.

CREATE PROCEDURE GIMMIEDATE (IN DATE_IN CHAR (6),
OUT DATE_OUT DATE)
LANGUAGE COBOL
PARAMETER STYLE GENERAL
EXTERNAL NAME GETDATE6C

CREATE PROCEDURE GIMMIEDATE (IN DATE_IN DECIMAL(6,0),
OUT DATE_OUT DATE)
LANGUAGE COBOL
PARAMETER STYLE GENERAL
EXTERNAL NAME GETDATE6D

CREATE PROCEDURE GIMMIEDATE (IN DATE_IN CHAR (8),
OUT DATE_OUT DATE)
LANGUAGE COBOL
PARAMETER STYLE GENERAL
EXTERNAL NAME GETDATE8C

CREATE PROCEDURE GIMMIEDATE (IN DATE_IN DECIMAL(8,0),
OUT DATE_OUT DATE)
LANGUAGE COBOL
PARAMETER STYLE GENERAL
EXTERNAL NAME GETDATE8D

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: rob@xxxxxxxxx
To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxx>
Date: 03/09/2015 09:11 AM
Subject: Re: Call Parameterize iSeries Store Procedure from
.NET
C#
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



That is COBOL code. What we are looking for is the code he used for
CREATE PROCEDURE. There is a way to create a sql stored procedure
which
will execute a COBOL program. We really need to see how the
parameters
are defined there.
Another problem with his snippet of COBOL code is he left out the
(excuse
me, my COBOL is rusty) DATA section. From there we need to see the
'PIC'
of the parameters passed into this COBOL program.

One good reason why this may work from a command line but not
elsewhere
is

that SQL procedures support 'overloading'. What this means is that
you
can have multiple procedures with the exact same name,
capitalization
and
all. But based solely on the parameters it will execute different
ones.
For example, if I can create a procedure called GIMMIEDATE a couple
different ways. Once with a 8 character parameter, once with a 8
digit
parameter, once with a 6 character parameter, once with a 6 digit
parameter. And it will execute the right one based on the parameter
type
you send it. If you try to send it a 10 character parameter it will
tell
you that GIMMEDATE is not found. It won't say there's a parameter
mismatch. This is a common mistake when trying something from the
command

line versus elsewhere.


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: "Guo, Fuwang" <fg33@xxxxxxxx>
To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxx>
Date: 03/09/2015 08:58 AM
Subject: Re: Call Parameterize iSeries Store Procedure from
.NET
C#
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



I just got the code from the AS400 dev and I'm not sure if it is a
stored
procedure.

PROCEDURE DIVISION USING CALL-PARMS
CALL-PARMS1

1000-MAIN.

PERFORM 9999-PGM-INZ
PERFORM 9999-PGM-EXIT

GOBACK

CONTINUE.

9999-PGM-INZ.

SET GOOD-RTN-CD TO TRUE
MOVE SPACES TO RTN-MSG-1

CALL 'SETLIB' USING
OBJ-LIB
DB-LIB
END-CALL

DISPLAY CODE
DISPLAY NUMBER
SET PGM-STR-PRG-MSG TO TRUE
PERFORM 9999-SND-PRG-MSG
CONTINUE.

9999-SND-ON-OFF-MSG.
EVALUATE TRUE
WHEN CHK
SET CHK1 TO TRUE
END-EVALUATE
EVALUATE TRUE
WHEN GOOD-RTN-CD
SET GOOD-RTN-TXT TO TRUE
WHEN OTHER
SET BAD-RTN-TXT TO TRUE
END-EVALUATE
STRING
NUMBER OF CALL-PARMS
LS-RTN-CD
RTN-MSG-1
REQ-TYP-TXT
REQ-RESULT-TXT
DELIMITED BY SIZE
INTO SND-ERR-MSG-SUB-DTA
END-STRING
PERFORM 9999-SND-ERR-MSG
CONTINUE.
9999-SND-PRG-MSG.
SET LOG-MSG TO TRUE
SET LOG-IN-PRG-MSG-ID TO TRUE
STRING
THIS-PROGRAM-NAME
CUR-PRG-MSG
DELIMITED BY SIZE
INTO SND-ERR-MSG-SUB-DTA
END-STRING
PERFORM 9999-SND-ERR-MSG
CONTINUE.


9999-PGM-EXIT.
PERFORM 9999-SND-ON-OFF-MSG
SET PGM-END-PRG-MSG TO TRUE
PERFORM 9999-SND-PRG-MSG
CONTINUE.

On Fri, Mar 6, 2015 at 12:06 PM, Richard Schoen <
Richard.Schoen@xxxxxxxxxxxxxxx> wrote:

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.

________________
--
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.


--
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.



--
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.



--
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.


--
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.



--
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.


--
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.



--
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.


--
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.



--
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 thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.