SOLVED - SQL FUNCTION to retrieve host name from an IP address in SELECT statement

[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