These have to be written using C++/CLI. The following example tries to find the Year in a relatively free-format date string.
#include "stdafx.h"
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <ctype.h>
#include <stdarg.h>
#using <system.dll>
using namespace System;
using namespace System::Text::RegularExpressions;
#include <vcclr.h>
// StringConvertor from CodeProject (http://www.codeproject.com/KB/string/StringConvertor.aspx)
#include "StringConvertor.h"
#include "C:/Program Files/Sybase/SQL Anywhere 7/h/extfnapi.h"
#include "C:/Program Files/Sybase/SQL Anywhere 7/h/sqldef.h"
// This function is needed by SQL Anywhere
extern "C"
_declspec(dllexport)
UINT32 __stdcall
extfn_use_new_api( )
{
return EXTFN_API_VERSION;
}
extern "C"
_declspec(dllexport)
void __stdcall
GetYear(
an_extfn_api * api,
void * hArg)
{
an_extfn_value sInValue;
memset(&sInValue, 0, sizeof(an_extfn_value));
api->get_value(hArg, 1, &sInValue);
System::DateTime ^ birthyear = gcnew System::DateTime();
StringUtilities::StringConvertor sc1((const char *) sInValue.data);
an_extfn_value sOutValue;
try
{
System::DateTime ^ result = gcnew System::DateTime();
bool success = System::DateTime::TryParse(
sc1,
System::Globalization::CultureInfo::CreateSpecificCulture(gcnew System::String("en-US")),
System::Globalization::DateTimeStyles::None,
(System::DateTime%)result);
int x;
if (success)
x = result->Year;
else
{
System::Text::RegularExpressions::Regex ^ rx = gcnew Regex("\\d{4}");
Match ^ m = rx->Match(sc1);
if (m->Success)
{
// Debug and trace work, but output even in Release mode, so commented out
// System::Diagnostics::Debug::WriteLine(String::Format("This is what I found: '{0}'", m->Value));
x = System::Int32::Parse(m->Value);
}
else
{
Globalization::DateTimeFormatInfo ^ dtfi = gcnew System::Globalization::DateTimeFormatInfo();
dtfi->ShortDatePattern = "dd/MM/yy";
success = System::DateTime::TryParse(
(System::String ^)sc1,
dtfi,
System::Globalization::DateTimeStyles::None,
(System::DateTime%)result);
if (success)
x = result->Year;
else
x = 0;
}
}
sOutValue.data = &x;
sOutValue.piece_len = sOutValue.len.total_len = sizeof(int);
sOutValue.type = DT_INT;
}
catch (System::Exception ^ ex)
{
// return NULL
sOutValue.data = NULL;
sOutValue.piece_len = sOutValue.len.total_len = 0;
sOutValue.type = DT_INT;
}
api->set_value(hArg, 0, &sOutValue, false);
}
I also have a .DEF file, which looks like this:
LIBRARY SOMETHING
EXPORTS
extfn_use_new_api = _extfn_use_new_api@0 @1
GetYear = _GetYear@8 @2
To install, we place the DLL (plus any other DLLs such as MSVCR90.DLL etc) into either windows/system32 or probably where the engine is.
Then execute this SQL:
-- If you've changed it, you need to drop it first
-- drop function dbo.GetYear;
create function dbo.GetYear(IN datestring CHAR(255))
returns int
external name 'GetYear@something.dll';
grant execute on dbo.GetYear to public;
Print | posted on Friday, June 5, 2009 8:09 PM