Writing SqlAnywhere 7 stored procedures in C#

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 05, 2009 8:09 PM

Feedback

No comments posted yet.
Title  
Name
Email (never displayed)
Url
Comments   
Please add 6 and 5 and type the answer here: