Description:
I started writing my first UDF and after much dwelling found that the arg_type variable doesn't seem to be right. The function I called SMALIST (Sort/Merge and Accumulate List) for now. The best way to explain it is by example:
SELECT K, SMALIST(3, //# of fields
“1,2”, /*Sort Fields 1 and 2*/
"1,2", /*Merge on Fields 1 and 2*/
“3”, /*Accumulate field 3*/
F1, /*Field 1*/
F2, /*Field 2*/
F3 /*Field 3*/) AS Details
CREATE TABLE test_smalist
(
K CHAR(5),
F1 CHAR(5),
F2 INT(5),
F3 DOUBLE(5,2)
);
INSERT INTO test_smalist (k, F1, F2, F3) VALUES
(11111,11111,11111,5.00),
(11111,22222,11111,5.00),
(11111,22222,22222,5.00),
(11111,22222,22222,5.00);
The UDF is not finished. In it's current state is just lists them with out sorting, merging or accuming. 11111:11111:5.00;22222:11111:5.000;etc for example. But when I added the UDF in it's current state with:
CREATE AGGREGATE FUNCTION _smalist RETURNS STRING SONAME test.dll
I got results like this:
11111:◄:♣; because the arg_type was incorrect so the int and double colums did not get casted correctly.
The code for the UDF is in the How to repeat section. Oddly the first field must be a INT_RESULT and that check worked there, but for args->arg_type[4-?] the type always = 0/*STRING_RESULT*/ when it really wasn't, because when it got casted it was obviosuly a float or int. See the code below.
How to repeat:
The following was compiled in Borland C++ v6.0
//---------------------------------------------------------------------------
#include <windows.h>
#pragma argsused
int WINAPI DllEntryPoint(HINSTANCE hinst, unsigned long reason, void* lpReserved)
{
return 1;
}
//---------------------------------------------------------------------------
#include <stdio.h>
#include <vector>
#include <list>
#include <string>
#include <sstream>
#include <fstream>
#include <iomanip>
#include "c:\sorc\base\mysql\mysql.h"
#include "shareptr.h"
extern "C" __declspec( dllexport ) my_bool smalist_init( UDF_INIT* initid, UDF_ARGS* args, char* message );
extern "C" __declspec( dllexport ) void smalist_deinit( UDF_INIT* initid );
extern "C" __declspec( dllexport ) void smalist_reset( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error );
extern "C" __declspec( dllexport ) void smalist_add( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error );
extern "C" __declspec(dllexport) char *smalist(UDF_INIT * initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char * );
//------------------------------------------------------------------------------
typedef long long INT_TYPE;
typedef double REAL_TYPE;
class SmaField
{
public:
class WrongType;
void *Data;
Item_result Type;
SmaField(UDF_ARGS* args, size_t field);
~SmaField();
char const* AsString() const;
INT_TYPE AsInt() const;
REAL_TYPE AsReal() const;
};
class SmaField::WrongType: public std::exception
{
public:
virtual char const* what() const throw();
};
char const* SmaField::WrongType::what() const throw()
{
return "SmaField::WrongType";
}
SmaField::SmaField(UDF_ARGS* args, size_t field)
{
Type = args->arg_type[field];
switch(Type)
{
case STRING_RESULT:
this->Data = new char[args->lengths[field] + 1];
std::memcpy(this->Data, args->args[field], args->lengths[field]);
static_cast<char*>(this->Data)[args->lengths[field]] = '\0';
break;
case INT_RESULT:
this->Data = new INT_TYPE(*reinterpret_cast<INT_TYPE*>(args->args[field]));
break;
case REAL_RESULT:
this->Data = new REAL_TYPE(*reinterpret_cast<REAL_TYPE*>(args->args[field]));
break;
default:
throw WrongType();
}
}
SmaField::~SmaField()
{
switch(Type)
{
case STRING_RESULT:
delete[] static_cast<char*>(this->Data);
break;
case INT_RESULT:
delete static_cast<INT_TYPE*>(this->Data);
break;
case REAL_RESULT:
delete static_cast<REAL_TYPE*>(this->Data);
break;
}
}
char const* SmaField::AsString() const
{
if(this->Type != STRING_RESULT)
{
throw WrongType();
}
return static_cast<char const*>(this->Data);
}
INT_TYPE SmaField::AsInt() const
{
if(this->Type != INT_RESULT)
{
throw WrongType();
}
return *static_cast<INT_TYPE*>(this->Data);
}
REAL_TYPE SmaField::AsReal() const
{
if(this->Type != REAL_RESULT)
{
throw WrongType();
}
return *static_cast<REAL_TYPE*>(this->Data);
}
typedef std::vector<tr1::shared_ptr<SmaField> > SmaRecord;
typedef std::list<SmaRecord> SmaRecordList;
struct SmaStruct
{
//Compare Function
//Merge Function
int FieldCount;
SmaRecordList RecordList;
std::string Result;
};
//------------------------------------------------------------------------------
extern "C" __declspec( dllexport ) my_bool smalist_init( UDF_INIT* initid, UDF_ARGS* args, char* message )
{
if ( args->arg_type[0] != INT_RESULT ) //# of Records
{
strcpy(message, "Wrong argument type: _SMALIST() requires a STRING as parameter 1");
return 1;
}
if ( args->arg_type[1] != STRING_RESULT ) //Sort String
{
strcpy(message, "Wrong argument type: _SMALIST() requires a STRING as parameter 2");
return 1;
}
if ( args->arg_type[2] != STRING_RESULT ) //Merge String
{
strcpy(message, "Wrong argument type: _SMALIST() requires a STRING as parameter 3");
return 1;
}
if ( args->arg_type[3] != STRING_RESULT ) //Accum String
{
strcpy(message, "Wrong argument type: _SMALIST() requires a STRING as parameter 4");
return 1;
}
if (args->arg_count != *((int*)args->args[0]) + 4)
{
strcpy(message,
"_SMALIST() requires at least 4 arguments "
"(#ofFields, SortString, MergeString, AccumString, Field1,...Fieldn)");
return 1;
}
SmaStruct *smaStruct = new SmaStruct;
smaStruct->FieldCount = *((int*)args->args[0]);
initid->maybe_null = 0; // The result may be null
initid->max_length = 65535; // 65535 characters maximum (Maximum size of a MySQL "TEXT" field)
initid->ptr = (char*)smaStruct;
return 0;
}
//------------------------------------------------------------------------------
extern "C" __declspec( dllexport ) void smalist_deinit( UDF_INIT* initid )
{
SmaStruct* smaStruct = (SmaStruct*)initid->ptr;
delete smaStruct;
}
//------------------------------------------------------------------------------
extern "C" __declspec( dllexport ) void smalist_add( UDF_INIT* initid, UDF_ARGS* args, char* /*is_null*/, char* /*message*/ )
{
SmaStruct* smaStruct = (SmaStruct*)initid->ptr;
SmaRecord smaRecord;
for (int i(0); i < smaStruct->FieldCount; ++i)
{
smaRecord.push_back(tr1::make_shared_ptr(new SmaField(args, i + 4)));
}
smaStruct->RecordList.push_back(smaRecord);
}
//------------------------------------------------------------------------------
extern "C" __declspec( dllexport ) void smalist_reset( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* message )
{
SmaStruct* smaStruct = (SmaStruct*)initid->ptr;
smaStruct->RecordList.clear();
smaStruct->Result.resize(0);
smalist_add( initid, args, is_null, message );
}
//------------------------------------------------------------------------------
extern "C" __declspec( dllexport ) char *smalist(UDF_INIT * initid, UDF_ARGS * /*args*/, char * /*result*/, unsigned long *length, char *is_null, char * /*error*/ )
{
SmaStruct* smaStruct = (SmaStruct*)initid->ptr;
std::stringstream stream;
//smaStruct->RecordList.sort(/*function*/);
//smaStruct->RecordList.merge(/*function*/);
for (SmaRecordList::iterator listIter = smaStruct->RecordList.begin();
listIter != smaStruct->RecordList.end(); ++listIter)
{
for (SmaRecord::iterator recordIter = listIter->begin();
recordIter != listIter->end(); ++recordIter)
{
if (recordIter != listIter->begin())
{
smaStruct->Result += ':';
}
SmaField const& field(**recordIter);
switch(field.Type)
{
case STRING_RESULT:
smaStruct->Result += field.AsString();
break;
case INT_RESULT:
stream.clear();
stream.str("");
stream << field.AsInt();
smaStruct->Result += stream.str();
break;
case REAL_RESULT:
stream.clear();
stream.str("");
stream << std::setprecision(5) << field.AsReal();
smaStruct->Result += stream.str();
break;
}
}
smaStruct->Result += ';';
}
return const_cast<char*>(smaStruct->Result.c_str());
}
//------------------------------------------------------------------------------
Suggested fix:
???