IDS-UD External C Function detailed working example

["Shulzhenko Vasyl" <vasilis@softline.kiev.ua>]

>amgad@starnet.com.eg wrote:
>: Hello Everyone,
>: I'm looking for a straight working example that starts from "A" and finished
>: at the "Z" of creating, compiling, and using an external function written in
>: "C" language on IDS-UD.
>
>   Hope this kills two birds with one stone. This is a small useful
>  example: a Soundex function. (Annoted) requested from another thread.
>
>   But before I get too far into it, a couple of suggestions:
>
>   1. Use DBDK ( DataBlade Developer's Kit ). This generates all of
>      the source stubs, header files and Makefiles for you.
> 
>   2. Look in the $INFORMIXDIR/demo directory. You should find a
>      couple of more comprehensive examples there.
>
>   3. Check out http://www.informix.com/idn, which contains a lot
>      of working example code, along with tricks, tips and
>      techniques.
>
>   4. Check out Sanchez, Angela. _Universal_Server_:_Best_Practices_
>      Prentice Hall. 1998. It contains a lot of examples that
>      are good 'habits of thought'.
>
>   OK. The procedure:
>
>   1: Write your 'C' code. The file here is called 'Soundex.c'. It's
>      all you need for a phonetic match. Note that this is a pretty
>      conventional algorithm. More exotic ones are probably more useful.
>
>      Create $INFORMIXDUR/extend/Soundex/{src,bin,install}
>
>      The Soundex.c and Makefile go in the src directory. The
>      bin is where the Makefile puts the shared library. The
>      install is a good place for the register.sql.
>
>/*
> *
> *    File: soundex.c
> *
> *      By:  Paul Brown
> *
> *   About:  This implements the 'soundex' functionality.
> *
> *           This is based on a version of the system I grabbed from a
> *           bunch of 'C' functions on the 'net.
> *
> *           There are lots of SAPI (Server API) notes scattered 
> *           through this piece.
>*/
>/*
>**  When you write your extension, you can generally make use of
>**  facilities from the standard 'C' libraries. There are a
>**  few exceptions, however.
>**
>**  Also, ABSOLUTELY NO SYSTEM CALLS (malloc, free, open, write, etc).
>**  These cause confusion between the server process and the operating 
>**  system.
>**/
>#include <ctype.h>
>#include <stdio.h>
>#include <string.h>
>#include <stddef.h>
>/*
>**  All of the mi_* functions are stubbed in this header file, as are
>**  all of the SAPI types. These are universally available on the
>**  platforms IUS supports.
>**
> **/
>#include <mi.h>
> 
>/*
>**  mi_lvarchar ('mi_' says 'this is a server data type, 'lvarchar' says
>**  that this is a variable length char array) is a general string
>**  literal type. That is, strings in queries are turned into this
>**  type. 
>**
>**  This function takes a single string as its only SQL argument,
>**  and returns a single string as its result. The MI_FPARAM *
>**  argument is an additional, server handle to conlvarchar information.
>**/
>mi_lvarchar *
>soundex(
>mi_lvarchar * inlvarchar,
>MI_FPARAM   * gen_fparam
>)
>{                 
>/*
>**  For each 'C' type and each SQL built-in type, there is a
>**  corresponding SAPI type. For example, SQL INTEGERS and
>**  'C' int are represented as mi_integer. SQL decimals are
>**  the dec_t type. 'C' uses NULL Terminated strings, whch are
>**  not really supported in SQL, but in SAPI are represented
>**  as mi_char (instead of char). 
>**/
>                     /* ABCDEFGHIJKLMNOPQRSTUVWXYZ */
>   mi_char    *table = "01230120022455012623010202";
>   mi_integer count = 0;
>
>   mi_lvarchar * outlvarchar;
>   mi_char * instr,
>           * outstro,
>           * outstr;
> 
>/*
>** SAPI provides facilities for turning it's string representation
>** into 'C' NULL terminated string.
>**/
>   instr = mi_lvarchar_to_string(inlvarchar);
>/*
>** Most of the UNIX/POSIX system calls have SAPI equivalents.
>**/
>   outstro = (char *)mi_alloc(6);      /* Always returns 6 characters */
>   outstr = outstro;
> 
>   while(!isalpha(instr[0]) && instr[0])
>           ++instr;
> 
>/*
>**  This illustrates how to return a NULL result from a UDR. Simply
>**  handing back a '0' (NULL) isn't enough. The function might
>**  be returning either a 0 INTEGER value or a NULL. So this
>**  SAPI Call instructs the ORDBMS that the function has
>**  returned a NULL value. In this case, an exception might be
>**  more appropriate, but what is the sound of an empty string?
>**  
>**/
>   if(!instr[0])     /* Hey!  Where'd the string go? */
>   {
>            mi_fp_setreturnisnull( pfParam, 0, MI_TRUE );
>            return (mi_lvarchar *)NULL;
>   }
>/*
>** Note how all of this code works with NULL Terminated strings, ah la
>** conventional 'C' programming.
>**/
>   if(toupper(instr[0]) == 'P' && toupper(instr[1]) == 'H')
>   {
>            instr[0] = 'F';
>            instr[1] = 'A';
>   }
>
>   *outstr++ = (char)toupper(*instr++);
> 
>   while(*instr && count < 5)
>   {
>           if(isalpha(*instr) && *instr != *(instr-1))
>           {
>                   *outstr = table[toupper(instr[0]) - 'A'];
>                   if(*outstr != '0')
>                   {
>                           ++outstr;
>                           ++count;
>                   }
>           }
>           ++instr;
>   }
>
>   *outstr = '\0';
>/*
>** And this turns the NULL terminated string into an mi_lvarchar for a
>** return result.
>**/
>
>   return(mi_string_to_lvarchar(outstro));
>}
>/*  END */
>
>  2. Compile your code. In general, I would really recommend the
>     Makefiles that DBDK generates. If you look at the WinNT.mak,
>     you can even figure out how to configure VC++ or Borland.
>
>     Put this in a file called 'Makefile' in the same directory 
>     as the Soundex.c. And make sure that you have a ../bin
>     directory created. When you use this makefile, you will
>     obviously have to change the compiler (I use gcc and
>     fully spec the path out of bad habit).
>
>     NOTE: The sharedlibrary you create (Soundex.bld) must be
>     owned by 'informix', and it must not be writeable by
>     other users. In other words, run this makefile as 'informix'
>     
>#-- BEGIN --
># This is the project title.
>PROJECT_TITLE= Soundex
> 
># TARGET must be set to the location/filename
># of the platform-specific make include file.
># include $(INFORMIXDIR)/incl/dbkd/$(TARGET).mak
>#
>MI_INCLUDE=$(INFORMIXDIR)/incl
>RM= rm
>RMFLAGS= -f
># CC= /usr/ucb/cc
># CC= /opt/SUNWspro/SC3.0/bin/cc -g
>CC= /usr/local/gnu/bin/gcc -g
>CSRVRFLAGS= -DMI_SERVBUILD #  not for gcc, but OK for other -KPIC
>CFLAGS= $(CSRVRFLAGS) -I$(MI_INCLUDE)/public -I$(MI_INCLUDE)/esql -I$(MI_INCLUDE
>) $(COPTS)
>LINK= /usr/ccs/bin/ld
>LINKFLAGS= -dy -G -Bsymbolic
>LIBS= -ldl -lnsl -lsocket -lm -lrpcsvc -lc
>DATABLADE_LIBS=
>SHLIBSUFF= bld
>BINDIR= ../bin
> 
> 
># Platform independent code goes here.
># The following code was generated by BladeSmith.
> 
>PROJECT_OBJS= $(BINDIR)/$(PROJECT_TITLE).o
> 
>all: server client
> 
># Construct the object file.
>$(BINDIR)/$(PROJECT_TITLE).o : $(PROJECT_TITLE).c
>    $(CC) $(CFLAGS) -o $(BINDIR)/$(PROJECT_TITLE).o -c $(PROJECT_TITLE).c
> 
> 
># Construct the shared library.
>$(BINDIR)/$(PROJECT_TITLE).$(SHLIBSUFF): $(BINDIR)/$(PROJECT_TITLE).o
>    $(LINK) $(LINKFLAGS) -o $(BINDIR)/$(PROJECT_TITLE).$(SHLIBSUFF) \
>    $(PROJECT_OBJS) $(LIBS) $(DATABLADE_LIBS)
> 
> 
>server: $(BINDIR)/$(PROJECT_TITLE).$(SHLIBSUFF)
> 
> 
>client:
>    echo "Client Makefile entry is empty - nothing to do."
> 
>clean:
>    $(RM) $(RMFLAGS)            \
>    $(BINDIR)/$(PROJECT_TITLE).$(SHLIBSUFF) \
>    $(BINDIR)/*.o               \
>    ../bin/$(PROJECT_TITLE).$(SHLIBSUFF)
> 
>$(BINDIR):
>    -mkdir $(BINDIR)
>#-- END --
>
>   3. Install the beast. At this point, you will have a shared library in
>      $INFORMIXDIR/extend/Soundex/bin. (NOTE: Case counts on UNIX, but not
>      on NT).
>
>      To register your UDR with the ORDBMS, use the following SQL script.
>
>-- BEGIN --
>--
>CREATE FUNCTION Soundex(lvarchar)
>RETURNS varchar(5)
>WITH ( NOT VARIANT, PARALLELIZABLE )
>EXTERNAL NAME '$INFORMIXDIR/extend/Examples/bin/Soundex.bld(soundex)'
>LANGUAGE C;
>--
>GRANT EXECUTE ON FUNCTION Soundex(lvarchar) TO PUBLIC;
>--
>EXECUTE FUNCTION Soundex('Brown');
>-- 
>-- END --
>   
>      If you get erorrs, check out the online.log for a more detailed
>    description of the problem. Probably can't find Soundex.bld, or 
>    the premissions on that file aren't set right.
>
>   4. Using it is pretty straightforward. Just treat this as if INFORMIX's
>      enginers wrote the code.
>
>
>  CREATE TABLE Employees ( 
> Id SERIAL NOT NULL PRIMARY KEY,
> .
>        Family_Name VARCHAR(64) NOT NULL
> .
>  );
>
>  Q1:
>
>   SELECT Id, Family_Name
>     FROM Employees
>    WHERE Soundex(Family_Name) = Soundex('Flintstone');
>
>  Q2:
>
>   -- You can also create an index on this as follows:
>
>   CREATE INDEX Emp_ndx1 ON Employees(Soundex(Family_Name));
>   UPDATE STATISTICS HIGH FOR TABLE Employees;
>
>   --
>   --  Now, the query above will use Emp_ndx1 instead of
>   --  a scan. ('Hi!' to all you lurkers from comp.oracle!)
>   --
>
>
>  5. Concluding Remarks:
>
>    I really, really, really recommend that you throw this away and
>   use DBDK. It does everything right. This example is an 
>   'under-the-hood' look at what's going on.
>
>  KR
>
>      Pb
>
>      
>