# Using eXtremeSQL User Defined Functions in C++

> eXtremeSQL supports User Defined Functions (UDF) that can optimize the performance of database operations. UDFs are written in C++ and compiled into dynamic load libraries which can then be loaded at runtime by C++ applications.

> For example, the following code snippet defines then registers a UDF `mod()`:

>         static Value* mod(Value* a, Value* b)
>         {
>             if (a->isNull() || b->isNull())
>             {
>                 return NULL;
>             }
>             return new IntValue(a->intValue() % b->intValue());
>         }

>         // Below, f1 is an instance of the SqlFunctionDeclaration class.  The
>         // constructor links this declaration of a UDF named “mod” to the list
>         // of all UDFs maintained internally by eXtremeSQL:

>         static SqlFunctionDeclaration f1(
>             tpInt,      // tpInt is the return value of the UDF
>             "mod",      // the name of the function as we’ll use it in a query
>             (void*)mod, // the function pointer
>             2          // the number of arguments to the UDF
>         );

> Once registered, the UDF can then be called from within this application in a normal SQL `select` statement. For example, the following statement selects records where the value of the column named code is divisible by 3:

>         QueryResult result(engine.executeQuery("select * from T where mod(code,3) = 0;"));

> ## Loadable UDFs

> UDFs can also be compiled into a dynamic load library. Then these UDFs can be loaded by the SQL engine from any embedded eXtremeSQL application. To illustrate, consider the following code snippet extracted from SDK sample source file `samples/xsql/data_loaders/CME/modcme/modcme.cpp`, which creates UDF `load_cme_trades`:

>         extern "C" {
>             CME_EXPORTS McoSql::Value *load_cme_trades(McoSql::Runtime *runtime, McoSql::Vector<McoSql::Value> *args)
>             {
>                 return load_security_rec<securityTrades, int, Trade> (runtime->engine, runtime->trans, args, 11);
>             }
>         }

> This UDF calls function `load_security_rec()` that is performs a relatively complex set of database operations:

>         template <class secAcc, class sKey, class secRec>
>         McoSql::Value *load_security_rec(McoSql::SqlEngine *engine,
>             McoSql::Transaction *currTrans,  McoSql::Vector<McoSql::Value> *args, int ncolumns)
>         {
>             McoSql::Value *input = args->at(0);
>             McoSql::Value *book = args->at(1);
>             std::map<sKey, secAcc *> securities;

>             if (input->type() != McoSql::tpString)
>             {
>                 throw McoSql::InvalidArgument("input - string sxpected");
>             }

>             char *fname = input->stringValue(currTrans->allocator)->cstr();

>             if (book->type() != McoSql::tpString)
>             {
>                 throw McoSql::InvalidArgument("book - string sxpected");
>             }
>             char *Book = book->stringValue(currTrans->allocator)->cstr();

>             CsvReader r(currTrans->allocator, fname, ',', 2, ncolumns);
>             secRec s;

>             while (r.next(&s))
>             {
>                 secAcc *st = NULL;
>                 sKey key = s.key();
>                 typename std::map<sKey, secAcc*>::iterator f = securities.find(key);
>                 if (f == securities.end())
>                 {
>                     st = new secAcc(engine, currTrans, Book, key, BATCH_SIZE);
>                     st->findRecord();
>                     securities[key] = st;
>                 }
>                 else
>                 {
>                     st = f->second;
>                 }

>                 st->apply(&s);
>             }

>             typename std::map<sKey, secAcc *>::iterator it;
>             for (it = securities.begin(); it != securities.end(); it++)
>             {
>                 it->second->store();
>                 delete it->second;
>             }

>             return McoSql::IntValue::create(currTrans->allocator, securities.size());
>         }

> In order to build this file into a dynamic load library, a number of linker parameters must be specified like the following:

>         $(QUIET_PREFIX)$(LDCXX) $(SOLDFLAGS) $(LDOUTPUTF) $(MCO_BIN_SO)/libmcocme$(MCO_BUILDRT_SFX)$(MCO_CFG_SUFFIX_SO) $(OBJ_L) $(QUIET_SUFFIX)

> Note that this makefile snippet uses several macros from file `include/header.mak` in the eXtremeDB SDK build system. (For further details please see the full makefile `samples/xsql/data_loaders/CME/modcme/makefile`.)

> ## Loading and Executing UDFs

> To load UDFs from external dynamic load libraries, an embedded eXtremeSQL application calls the SQL DDL command ``create function`` specifying the file and UDF name. For example:

>         create function load_trades(fname string, book string) returns integer as 'mcocme', 'load_cme_trades';

> The create function command can be included in the `sql_statements` section of the xSQL configuration file to cause xSQL to create the UDF and load its implementation from the dynamic load library. Or UDFs can be loaded when the SQL engine is opened in a C++ application by setting the SqlOptimizerParamter class parameter `preloadUDF` to true to enable the preload functions feature. Then the optimizer parameters are added to the McoSqlOpenParameters passed the McoSqlEngine method `open()`. For example:

>         McoSqlEngine engine;
>         McoSqlOpenParameters params;
>         SqlOptimizerParameters oParams;
>         oParams.preloadUDF = true;
>         ...
>         params.optimizerParams = oParams;
>         engine.open(params);
>         ...

> Once loaded by the SQL engine, any eXtremeSQL application can invoke the UDF through an SQL `select` statement. For example, the following Python snippet executes the `load_trades` UDF defined above:

>         is_shm = False
>         is_debug = True
>         is_disk = True
>         tmgr='mursiw'

>         exdb.init_runtime(is_disk, tmgr, is_shm, is_debug)
>         conn = exdb.connect("localhost:5001")

>         cursor = conn.cursor()

>         sql =  '''SELECT load_trades('%s', '%s')''' % (fname, book)
>         cursor.execute(sql)
>         cursor.fetchall()
>         ...