SOLVED - SQL FUNCTION to retrieve host name from an IP address in SELECT statement (maybe better wrapping)

[Patrick L Archibald <Patrick.Archibald@xxxxxxxxxxxxx>]

Hi All

I finally got my getHost SQL function working.  Here are the details:



C Source code:

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

/* Get host name for passed IP   */

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

#include </netdb.h>

#include <sys/param.h>

#include <netinet/in.h>

#include <stdlib.h>

#include <stdio.h>

#include <sqludf.h>

#include <arpa/inet.h>

#include <sys/socket.h>

#define HEX00 '\x00'

void SQL_API_FN hostname

             (char *input,

              char *output,

              SQLUDF_TRAIL_ARGS_ALL) {

  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, input);

  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) {

   strcpy(output,input);

 }

 else {

   (void) memcpy((void *) host_name,

                 (void *) hst_ent.h_name,

                 MAXHOSTNAMELEN);

   strcpy(output,host_name);

 }




Here is the compile command:

CRTCMOD MODULE(QGPL/GPLCM04)

        SRCFILE(QGPL/QCSRC)

        OUTPUT(*print)

        DBGVIEW(*LIST)






Here is the create service program command:

CRTSRVPGM SRVPGM(QGPL/GPLCM04)  EXPORT(*ALL)





Here is the CREATE FUNCTION SQL statement:


CREATE FUNCTION qgpl/gethost (

ip    varchar(15) )

RETURNS         varchar(132)

LANGUAGE c

external name 'QGPL/GPLCM04(hostname)'

DETERMINISTIC

PARAMETER STYLE DB2SQL

     RETURNS NULL ON NULL INPUT

     NO EXTERNAL ACTION

   fenced


Here is the statement I use it in STRSQL:

select gethost(ip) from gplpfa10



It works nicely.


Thanx to all who helped, PLA