List of SQL UDF "Do's and Don'ts"

[gwilburn]

Is there a list or short article anywhere on the Do's and Don'ts when creating SQL Fuctions over ILE RPG procedures?

I don't do these very often, but when I do I seem to always run into things.

1. CHAR versus VARCHAR parmeters in my RPG procedure (SQL UDF does not like CHAR parameters in RPG procedure)
2. Optional parameters in my RPG procedure (%parms in the RPG procedure does not function when called from an SQL UDF)

Ran into both of these issues today. Sad truth is that I ran into the CHAR issue 2 years ago as well.

There were some posts from a few years ago referencing links that no longer exist.

[tomliotta]

Only thing I can think of for "optional" parms would be that RPG declares either *OMIT or *NOPASS with *NOPASS probably most appropriate. They aren't declared as "optional" in a function definition; only by declaring multiple definitions, some declaring some parms and others leaving parms out entirely to result in different signatures. Is that the situation? The same RPG might handle all of them by checking %PARMS(). I haven't needed to do that yet, so I'd need to do some testing. But an example would be useful if you can prepare one.

But AFAIK, CHAR vs. VARCHAR is usually a problem if the UDF parm is declared as a CHAR and the UDF is called with a VARCHAR value (or perhaps vice versa, but I haven't run into that). I've haven't seen it be a problem otherwise.

In my experience, the problem usually arises when a parm is declared perhaps as CHAR(6) and the function is invoked as myFunc( 'ABCDEF' ). Since 'ABCDEF' is a VARCHAR value (not CHAR), a function with matching signature isn't found. A quoted string used as an undeclared value is a VARCHAR. It needs at least to be CAST() as a CHAR before using it as a CHAR. There are other ways of mixing the two types up in code. That's just the one that I've always seen. Do you have a different example?

[B.Hauser]

1. CHAR ans VARCHAR are 2 different data types for SQL. Because a UDF can be overloaded, the UDF may not be found if a VarChar parameter is passed but a Char parameter expected. BTW the inverse, i.e. a VARCHAR parameter is expected and a CHAR parameter is passed will be handled nicely.
2. %PARMS works only with RPG, i.e. with prototyping. UDFs are neither defined nor embedded with prototyping, in this way the number of paramteres cannot be determined and %Parms returns -1.
For UDFs with optional parameters we used to write RPG functions with the appropriate number of parameters with then call the original function. These RPG UDFs get registered with the same UDF name (UDFs can be overloaded). In this way we can call the UDF with any number of parameters.
In Release 7.3 and with the appropriate TR in 7.2 and 7.1 it is also possible to register an UDF with all parameters and specifiy default values for the optinal parameters.
In this way the function gets always called with all parameter, but instead of the not passed parameters the predefined default value is passed.

Birgitta

[gwilburn]

My two issues were as follows:

1. I defined both the RPG procedure and SQL UDF as having CHAR parameters. Unfortunately, I can't recall the exact message I received. It was something about "definition not found for <function name>". I encountered this before - not sure what I'm doing wrong, but changing he RPG procedure interface and associated SQL UDF to use VARCHAR instead of CHAR, fixed the problem.
- since this RPG procedure wasn't used many places, this was an easier route for me.

2. The RPG procedure was defined with the 4th parameter OPTIONS(*NOPASS). When I defined the SQL UDF, I defined 4 parameters. My problem was that when the RPG procedure was invoked from the UDF, the %PARMS (in the RPG code) did not indicate 4 parms were passed.

Birgitta - are you saying that %PARMS within the RPG procedure only works properly when called by another HLL? I would think it should work no matter what language the calling program is written in. That is the point of ILE, right?

[Scott Klement]

Birgitta - are you saying that %PARMS within the RPG procedure only works properly when called by another HLL? I would think it should work no matter what language the calling program is written in. That is the point of ILE, right?

In ILE, there's something known as an "operational descriptor" that provides information about the parameters being passed. Operational descriptors ("opdesc" for short) are optional, and not required to be passed. The language support works like this:

1) RPG always passes the part of the opdesc that contains the parameter count. It will only pass the rest of the opdesc if you code the OPDESC keyword.

2) CL always passes the full opdesc.

3) Cobol will pass the opdesc if you code "using all described"

4) C/C++ lets you control exactly which parts of the opdesc are passed if you use "#pragma descriptor".

5) SQL never passes an operational descriptor.

Since the parameter count is part of the opdesc, you cannot rely on it to be there. It will never be there with SQL.

If you make it a program call, %PARMS will work since then you're not using the ILE procedure interface. The program interface works differently, and always includes the parameter count.

If you want to have optional parameters, the right way to handle them in SQL is to use SQL's overloading mechanism. I wrote an article that demonstrates this, here: http://iprodeveloper.com/print/rpg-p...d-sql-udfs-rpg

[gwilburn]

Thanks for the explanation and the link to your article!