Re: Call Parameterize iSeries Store Procedure from .NET C# -- MIDRANGE-L

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.