Re: SQL FUNCTION to retrieve host name from an IP address in SELECT statement

HTH
Vern

Hello

I'm not a C programmer or an SQL expert so go easy on me. I have an ILE C program that looks up an IP's host name. I would like to create a SQL function for this. I want to be able to run an interactive (STRSQL) SELECT statement like "SELECT getHost(ipAddress) from someFile". Can this be done?

I've tried creating the SQL function but I keep getting error "CPF503E", with error code "2 -- The external program failed before it completed".

I was torn as to which mailing list to send it to /C400-L/ or MIDRANGE-L so I sent it to MIDRANGE-L.

Anyone have any ideas on how to make this work?

Details below.

Thanx, PLA



Here is the SQL CREATE FUNCTION statement:

CREATE FUNCTION QGPL/GETHOST (VARCHAR (15))

RETURNS VARCHAR(132)

EXTERNAL NAME GETHOST

SPECIFIC GETHOST

LANGUAGE C

DETERMINISTIC
NO SQL

NOT FENCED

PARAMETER STYLE DB2SQL

RETURNS NULL ON NULL INPUT

NO EXTERNAL ACTION



Here is the C source:

/********************************************************/

/* Get host name for passed IP */

/********************************************************/

#include </netdb.h>

#include <sys/param.h>

#include <netinet/in.h>

#include <stdlib.h>

#include <stdio.h>

#include <arpa/inet.h>

#include <sys/socket.h>

#define HEX00 '\x00'

int main(int argc, char *argv[]) {

int rc;

struct in_addr internet_address;

struct hostent hst_ent;

struct hostent_data hst_ent_data;

char dotted_decimal_address [16];

char host_name [MAXHOSTNAMELEN];

strcpy(dotted_decimal_address,argv[1]);

memset(&hst_ent_data,HEX00,sizeof(struct hostent_data));

internet_address.s_addr=inet_addr(dotted_decimal_address);

if ((rc=gethostbyaddr_r((char *) &internet_address,

sizeof(struct in_addr), AF_INET,

&hst_ent, &hst_ent_data)) == -1) {

exit(-1);

}

else {

(void) memcpy((void *) host_name,

(void *) hst_ent.h_name,

MAXHOSTNAMELEN);

strcpy(argv[2],host_name);

}

exit(0);

}

Here is how I compiled and created it:

CRTCMOD MODULE(QGPL/GPLCM02)        SRCFILE(QGPL/QCSRC)
       OUTPUT(*print)
       DBGVIEW(*LIST)
       SYSIFCOPT(*IFS64IO)

CRTPGM PGM(QGPL/GPLCM02)

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.