Re: Custom sql function based on C module

["Stephen More" <stephen.more@xxxxxxxxx>]

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-----
> From: c400-l-bounces+ebudimlic=centerfieldtechnology.com@xxxxxxxxxxxx
> [mailto:c400-l-bounces+ebudimlic=centerfieldtechnology.com@xxxxxxxxxxxx] On
> Behalf Of Stephen More
> Sent: Tuesday, October 14, 2008 11:37 AM
> To: c400-l@xxxxxxxxxxxx
> 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
> --
> This is the C programming iSeries / AS400 (C400-L) mailing list
> To post a message email: C400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/c400-l
> or email: C400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/c400-l.
>
>
> --
> This is the C programming iSeries / AS400 (C400-L) mailing list
> To post a message email: C400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/c400-l
> or email: C400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/c400-l.