Re: Custom sql function based on C module

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