RE: SQL external UDF or proc: Ability to specify binding directory to locate external functions and procedures

Mike, I don't know if the following is useful for your requirement, but here
goes...

I know that with an external SQL stored proc, in IBM i Navigator, I can
right-click, select "Generate SQL", and then save the generated stored proc
statements as a script to a source member or file.

I can then use RUNSQLSTM against the generated statements to create the
stored proc. An option on RUNSQLSTM is to write the generated source (in
this case, the ILE C program that is the actual implementation of the stored
proc) to a source member. Once I have the ILE C source, I can work with that
if I want to.

I don't know and can't quickly test the Generate SQL option for UDF, but if
it has the same option to generate to a source member and then run
RUNSQLSTM, you might be able to capture the complete ILE C program for the
UDT and work with that.

Craig Pelkie

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Mike
Jones
Sent: Thursday, November 23, 2017 12:07 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL external UDF or proc: Ability to specify binding directory
to locate external functions and procedures

Hi Mark,

I'm aware of all that.

The primary purpose for specifying a binding directory is so we don't have
to dig to find out where an IBM, or third party, service program procedure
or function is located.

For example, do you know where the IBM C language SLEEP function actually
resides (i.e. the name of the service program)?

In RPG, we can specify binding directory QC2LE, and create a prototype to
use the C language SLEEP function. At compile time, the RPG compiler will
figure out for us where that function resides, and bind in the compiler
located service program containing SLEEP.

I'd like the compilation of SQL external functions and procedures to do the
same thing. Granted, compiling an external SQL function or procedure simply
creates a row in SYSPROCS or SYSFUNCS, but that row created contains the
external name specified (column EXTERNAL_NAME). I want the compile time
lookup of that external name to be able to be done from a binding directory,
and not have to take the time to figure out where the C language SLEEP
function resides.

On one of our V7R1 systems, the QC2LE binding directory contains 34 service
programs. If I want to, for example, create an SQL wrapper around the SLEEP
function, I don't want to have to dig through 34 service programs to find
the one that exports SLEEP. I want to specify a binding directory and a
routine name, and let the SQL compiler figure out where the routine resides,
so it can store the compiler located external name location in
SYSPROCS.EXTERNAL_NAME or SYSFUNCS.EXTERNAL_NAME.

I'd like the SQL compiler to locate the routine for me, just like using a
binding directory in RPG, or a namespace in C#, or a package in Java.

Mike

date: Wed, 22 Nov 2017 23:54:16 -0500
from: Mark Murphy <jmarkmurphy@xxxxxxxxx>
subject: Re: SQL external UDF or proc: Ability to specify binding
directory to locate external functions and procedures

I'm not real sure what you are asking for here. When you define an
external UDF or procedure, this points at an already compiled object,
either a program or service program object. The specific object is
named in EXTERNAL NAME. No binding directory necessary here, or even
useable, the definition of the external UDF or procedure tells where
to find the program or procedure. You can use a binding directory when
creating the program or service program if necessary, but the SQL
definition is just a pointer, it doesn't cause anything to be compiled or

created.

Second point, the binding directory is only used when a program or
service program object is created. Since defining an external SQL UDF
or procedure does not create a program object, not real sure how you
think a binding directory might be helpful here either.

On Wed, Nov 22, 2017 at 4:01 PM, Mike Jones
<mike.jones.sysdev@xxxxxxxxx>
wrote:

Hi,

I don't believe we yet have a way to specify a binding directory for
locating external functions and procedures when creating SQL
external UDF's and procedures. If I'm wrong, please educate me.

For example, in RPG, we can use an H-spec or CTL-OPT spec with:
BNDDIR( 'QC2LE' ), and then specify a prototype for the C language
SLEEP

function.

When creating an SQL external UDF or procedure, I'd like to be able
to
specify:

PROGRAM TYPE SUB EXTERNAL NAME BNDDIR( 'QC2LE' ) EXTPROC( 'SLEEP' )

or

PROGRAM TYPE MAIN EXTERNAL NAME BNDDIR( 'MY_BNDDIR' ) EXTPGM(
'MY_PGM' )

...or something similar, like we can in RPG.

Unless I'm missing something, today, some of our alternatives include:
* Figure out where SLEEP actually resides and point the EXTERNAL
NAME at its location.
* Create a, for example, RPG wrapper specifying BNDDIR( 'QC2LE' )
plus a prototype with EXTPROC( 'SLEEP' ), and then point the SQL
external UDF or procedure EXTERNAL NAME to the RPG wrapper program or

procedure.

The ability to specify a binding directory and routine would make it
faster, and easier to create SQL external UDFs and procedure wrappers.

I'm wondering if there's significant interest in creating a RFE

requesting

said ability from IBM.

Mike

--
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: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD