Re: Custom sql function based on C module -- C400-L

I missed a parenthesis here:

VALUES (GETENV('HOME')) INTO TEMP;

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com

-----Original Message-----
From: Elvis Budimlic [mailto:ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx]
Sent: Tuesday, October 14, 2008 1:19 PM
To: 'C programming iSeries / AS400'
Subject: RE: [C400-L] Custom sql function based on C module

Steve,

I tested in on my V5R3 machine as well and still can't make it to fail as it
has for you.
Here's the WRKPTFGRP output on that machine if it helps any:

Opt PTF Group Level Status
SF99519 189 Not installed
SF99502 25 Not applicable

I'm hoping you're just missing a PTF and IBM can help you track it down.

First thing I'd try in this case is changing the line of code where you set
TEMP variable and do the following instead:

SET TEMP = GETENV('HOME');
or
VALUES (GETENV('HOME') INTO TEMP;

just in case you luck out and 'fix' the issue that way.

If that doesn't work, you can try debugging the test program by adding this
line just before the BEGIN statement in TESTENV:

SET OPTION DBGVIEW=*SOURCE

Then recreate TESTENV function and debug it.

There are two ways you can debug it.
In this case, I think the easier way would be to use iSeries Navigator
System Debugger, available as a drop-down menu option in the Run SQL Scripts
facility. Reason this is easier to use is that it allows you to debug SQL
statement code, rather than generated C code (ugly!). If you'd rather debug
the generated C code, you can do it with the same debugger (there is a 'C
source view' option).

Alternatively, if you'd rather debug the generated C code using a
traditional green-screen debugger (STRDBG), you can as well (although I
don't recommend it). After the recompile of the test function, your
PGMRPGMLIB will contain the TESTENV service program with debug view built
in. To debug it you need to have 2 green-screen sessions. Let's call them
Session A and Session B.
1) in Session A do STRSRVJOB JOB(<fully qualified session B job name>)
2) STRDBG SRVPGM(PGMRPGMLIB/TESTENV)
3) set the breakpoint at first executable line (just hit F6 anywhere in the
code)
4) F12
5) in Session B do STRSQL
6) SELECT TESTENV() FROM SYSIBM/SYSDUMMY1
7) Session A ought to break now on your breakpoint

All that said, I don't see anything wrong with your code and strongly
suspect an IBM bug of sorts (fixed by some existing PTF), so if you're on
support you can try opening a PMR and see if they can find the PTF you need.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com

-----Original Message-----
Subject: Re: [C400-L] Custom sql function based on C module

Here is the outer UDF:

DROP FUNCTION PGMRPGMLIB/TESTENV;
CREATE FUNCTION PGMRPGMLIB/TESTENV()
RETURNS VARCHAR(50)
LANGUAGE SQL
BEGIN

DECLARE TEMP VARCHAR(70);
DECLARE MYCOUNT INTEGER;

SET MYCOUNT = 5;

WHILE MYCOUNT > 0 DO

SET TEMP = '-';
SELECT GETENV('HOME') INTO TEMP FROM SYSIBM/SYSDUMMY1;
--SELECT DAYS('2005-02-22') INTO TEMP FROM SYSIBM/SYSDUMMY1;
SET MYCOUNT = MYCOUNT - 1;

END WHILE;
RETURN TEMP;

END

I am trying to get this working on V5R3.

-Thanks
Steve

On Tue, Oct 14, 2008 at 1:37 PM, Elvis Budimlic
<ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Everything looks fine to me so far (I've tested it on my machine).

Can you also show the entire CREATE FUNCTION source code for the outer UDF
(the one calling GETENV)?

BTW, I'm testing it on V5R4 version of the OS and WRKPTFGRP shows the
following (in case it's some sort of OS issue):

Opt PTF Group Level Status
SF99540 8057 Installed
SF99539 67 Installed
SF99504 15 Installed
SF99291 13 Installed
SF99114 12 Installed

Couple of miscellaneous points (not the culprit here) on your code so far:

* Perhaps GETENV should be NOT DETERMINISTIC, since you can get different
outputs for the same input if environment changes between two calls?
Probably unlikely, but figured I'd point it out just in case.
* In your outer UDF, I think using SET TEMP = GETENV('HOME') is clearer
syntactically than running a query against SYSDUMMY1 to set the TEMP
variable

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com

-----Original Message-----
Subject: Re: [C400-L] Custom sql function based on C module

CREATE FUNCTION sqlutil/getenv( VARCHAR(50) )
RETURNS VARCHAR(50)
EXTERNAL NAME 'SQLUTIL/GETENV(GETENV)'
LANGUAGE C
NO SQL
PARAMETER STYLE SQL
DETERMINISTIC;

SELECT GETENV('HOME') || GETENV('HOME') FROM SYSIBM/SYSDUMMY1

results in:

/noplace/noplace

-Steve

On Tue, Oct 14, 2008 at 12:55 PM, Elvis Budimlic
<ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Please show your CREATE FUNCTION code for GETENV registration.
Also, what's the output when you run:

SELECT GETENV('HOME') || GETENV('HOME') FROM SYSIBM/SYSDUMMY1;

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com

-----Original Message-----
Subject: [C400-L] Custom sql function based on C module

I have a very simple c module:

#include <stdio.h>
#include <errno.h>
#include <stdlib.h>

void GETENV( char* name,
char* returnMessage,
short* inind,
short* outind,
char* sqlstate,
char* funcname,
char* specname,
char* msgtext )
{
char *val1 = NULL;

val1 = getenv( name );
if( val1 == NULL )
{
strcpy( returnMessage, "\0" );
}
else
{
strcpy( returnMessage, val1 );
}

*outind = 0;
}

Provided that I execute:
ADDENVVAR ENVVAR(HOME) VALUE('/noplace') REPLACE(*YES)

SELECT GETENV('HOME') FROM SYSIBM/SYSDUMMY1;
produces '/noplace' as expected. ( So far so good ).

When I wrap this sql function inside of another sql function like:
SET MYCOUNT = 5;
WHILE MYCOUNT > 0 DO
SET TEMP = '-';
SELECT GETENV('HOME') INTO TEMP FROM SYSIBM/SYSDUMMY1;
SET MYCOUNT = MYCOUNT - 1;
END WHILE;
RETURN TEMP;

I get:
/noplace/noplace/noplace/noplace/noplace/noplace/n

How can I just get /noplace ??

All SQL seems fine because in the loop I can substitute
SELECT DAYS('2005-02-22') INTO TEMP FROM SYSIBM/SYSDUMMY1;
and I simply get:
731999

-Thanks
Steve More