Re: Custom sql function based on C module

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

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