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


  1.  Home
  2. MIDRANGE-L
  3. September 2008

RE: AW: Creating an SQL function


Dan - you are correct
Charles - my apologies, and thanks
MUCH appreciated.
Credit where credits due
In fact, my sincere thanks to everyone who came up with their suggestions



Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxxxxx
P:(631) 244-2000 ext. 5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill

midrange-l-bounces@xxxxxxxxxxxx wrote on 09/30/2008 02:29:40 PM:


Charles suggested it first.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
Sent: Tuesday, September 30, 2008 1:09 PM
To: Midrange Systems Technical Discussion
Subject: RE: AW: Creating an SQL function



Dan
Again - MUCH appreciated.
That was it.
I finally got some time to make the changes to the service program
and change the prototype from VALUE to CONST



Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxxxxx
P:(631) 244-2000 ext. 5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill

__________________

Morning Dan
Thanks for your reply
I will certainly give it a go
MUCH appreciated



Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxxxxx
P:(631) 244-2000 ext. 5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill

midrange-l-bounces@xxxxxxxxxxxx wrote on 09/29/2008 05:36:27 PM:

Yes. SQL is putting pointers on the stack and your RPG subprocedure is
trying to read the pointers as values. By changing to CONST or
REFERENCE(the default) RPG will pick the pointers up off the stack and
dereference the pointers to get to the value, all as if by magic.

Kimmel

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
Sent: Monday, September 29, 2008 3:44 PM
To: Midrange Systems Technical Discussion
Subject: Re: AW: Creating an SQL function


Hi Charles
Thanks for your reply
Are you then saying that I should shange subprocedure to accept the
parameters For example The prototype is presently
D Subproc pr 9 0
D ItemSuffix 4 value
D Sku# 6 value

should in fact be
D Subproc pr 9 0
D ItemSuffix 4 CONST
D Sku# 6 CONST

Thanks in advance


Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxxxxx
P:(631) 244-2000 ext. 5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill




"Charles Wilt"

<charles.wilt@gma

il.com>
To
Sent by: "Midrange Systems Technical

midrange-l-bounce Discussion"

s@xxxxxxxxxxxx <midrange-l@xxxxxxxxxxxx>


cc


09/29/2008 04:37
Subject
PM Re: AW: Creating an SQL function





Please respond to

Midrange Systems

Technical

Discussion

<midrange-l@midra

nge.com>









Alan,

Have you changed the procedure paramters?

You can't use RPG's (or C/C++ for that matter) ability to pass
parameters by VALUE.

The parameters for your external function must be defined as by
reference or by CONST reference.

Charles

On Mon, Sep 29, 2008 at 3:50 PM, Alan Shore <AlanShore@xxxxxxxx> wrote:

AH-HA
I'm making some headway
using
CREATE FUNCTION AVAILQTY (VARCHAR(4), VARCHAR(6)) RETURNS
DECIMAL(9,0) LANGUAGE RPGLE PARAMETER STYLE GENERAL EXTERNAL NAME
'PRODLIB/SERVPGM(SUBPROC)'
DETERMINISTIC
NO SQL
NOT FENCED
RETURNS NULL ON NULL INPUT

I do get some results
for example
ITEM SKUNO AVAIL
NO.
SUFFIX
0011 000070 999,999,999-

where the answer 999,999,999- is a valid answer when the item is
NOT
found on the item master
Even though I can see its there.
Trying to use debug does not get me into the service program

BUT - I'm making headway

Thanks to everyone who came up with suggestions



Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxxxxx
P:(631) 244-2000 ext. 5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill

midrange-l-bounces@xxxxxxxxxxxx wrote on 09/29/2008 03:04:55 PM:

Maybe create your function using VARCHAR instead of CHAR - or
explicitly CAST the values in your SELECT to be CHAR values.

-------------- Original message ----------------------
From: Alan Shore <AlanShore@xxxxxxxx>

Thanks for your reply Birgitta
Unfortunately I still got the same response

ITEM SKUNO AVAIL
NO.
SUFFIX
0011 000070 ++++++++++++



Alan Shore
Programmer/Analyst, Direct Response E:AShore@xxxxxxxxxxx
P:(631) 244-2000 ext. 5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill

midrange-l-bounces@xxxxxxxxxxxx wrote on 09/29/2008 01:39:32 PM:

Hi,

I assume it's the parameter style.
Parameter style expects some additional (optional) parameters
that
must
be
defined in the procedure interface or *ENTRY PLIST.
Try to recreate your function with parameter style general.
With
parameter
style general only the defined parameters are passed to the RPG
function.

Overloading cannot be the problem, because the function gets
called.
If a function is overloaded and the parameters are not passed
with
the
same
data types (BTW length must not be an exact match, because it's
not checked), the function gets not called at all. Be careful
CHAR and
VARCHAR
are different data types. SMALLINT, INT and BIGINT are also 3
different
data
types.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the
stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek
Bok) "What is worse than training your staff and loosing them?
Not
training
them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Alan
Shore
Gesendet: Monday, 29. September 2008 17:41
An: Midrange Systems Technical Discussion
Betreff: Creating an SQL function

Good morning all
I am trying to create an SQL function that uses a sub procedure
within a
service program
Here is what I attempted
Within STRSQL

create function MYLIB/AVAILQTY (CHAR, CHAR) returns
DECIMAL(9,0) External Name 'PRODLIB/SERVPGM(SUBPROC)'
Language RPGLE
Disallow Parallel
No SQL
Parameter Style DB2SQL
Deterministic

When I attempt to use this, for example SELECT SUFFIX, SKUNO,
AVAILQTY(SUFFIX, SKUNO) FROM ITEMMSTR WHERE SUFFIX = '0011' AND
SKUNO = '000070'

What I get is
ITEM SKUNO AVAILQTY
NO.
SUFFIX
0011 000070 ++++++++++++
The input to the sub procedure is 2 fields 4 characters, 6
characters
The output from the sub procedure is decimal(9,0) as noted
above I'll be honest I don't know where to start on debugging
this If anyone can help, it would be much appreciated)


Thanks in advance



Alan Shore
Programmer/Analyst, Direct Response E:AShore@xxxxxxxxxxx
P:(631) 244-2000 ext. 5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill
--
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.