I need a clr function example

[g_g_99]

I got in c#

// namespace SqlAnywhereDotNetDll

public class Util { public static string toHexStr(Int64 id) { return id.ToString("X2"); } public static string intToHexStr(int id) { return id.ToString("X2"); }

public static int fromHexStr(string h) { return int.Parse(h, System.Globalization.NumberStyles.HexNumber); }

}

and I have tried to interface to toHexStr with the clr function declared as

ALTER FUNCTION "dba"."uidToHexStr"(in @ID bigInt) returns varchar(16) not deterministic external name 'SqlAnywhereDotNetDll.dll::SqlAnywhereDotNetDll.Util.toHexStr( numeric)' language CLR

But it's clearly wrong as I get from isql :

Procedure 'uidToHexStr' terminated with unhandled exception 'Invalid type in parameter list: numeric' SQLCODE=-91, ODBC 3 State="HY000"

with

begin declare @bi bigint; set @bi=9223372036854775807; select dba.uidToHexStr(@bi); end

Note I have tried bigInt, Int64 instead of numeric in the stored function arg type

as a side note, we don't need uidToHexStr nor intToHexStr since SA has a built-in function intToHex that works on bigint and int. I used the clr dll just to try out how to integrate clr function before I proceed to something serious

[MCMartin]

The documentation lists long as the corresponding CLR type for bigint, have you tried that?

[g_g_99]

I did try and I got null output from SELECT "dba"."uidToHexStr"(16546878931561) also null for a lot smaller number

[g_g_99]

for those looking string clr function return:

ALTER FUNCTION "dba"."$formatListNormalize"( in list long varchar) 
returns long varchar
not deterministic
external name 'Z:\\\\SQLAnywhereExtEnv\\\\SqlAnywhereDotNetDll.DLL::Util.intToHexStr(string) string' language CLR 

.net code

/// <summary>
    /// remove space(s) around comma, start and end of list string
    /// e.g. " a b, item2 ,   item3 0%, item4 " becomes "a b,item2, item3,item4"
    /// </summary>
    /// <param name="list"></param>
    /// <returns></returns>
    public static string formatListNormalize(string list)
    {
        return Regex.Replace(list, @"(?<comma>\\s[,]\\s)", ",").Trim();
    }

[jeff_albion]

The list of types provided to the external function call in the external name definition are the CLR types:

http://dcx.sybase.com/index.html#sa160/en/dbprogramming/pg-extenv-clr.html

So in this case, you will want:

CREATE FUNCTION "dba"."uidToHexStr"(in id_field bigint)
returns varchar(16)
not deterministic
external name
'SqlAnywhereDotNetDll.dll::SqlAnywhereDotNetDll.Util.toHexStr( long )' language CLR;

[g_g_99]

thx, it works after adding string after the )

sorry for not commenting earlier

[g_g_99]

OT: despite only .net 2 is supported for Sql anywhere v 11, .net 3 can do the job as well so far