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"


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

asked 17 Jul '13, 00:08

gg99's gravatar image

accept rate: 0%

edited 17 Jul '13, 16:19

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

(17 Jul '13, 04:27) Martin

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

(17 Jul '13, 15:46) gg99

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();

(06 Nov '14, 04:06) gg99

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


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;
permanent link

answered 23 Jul '13, 15:49

Jeff%20Albion's gravatar image

Jeff Albion
accept rate: 24%


thx, it works after adding string after the )

sorry for not commenting earlier

(06 Nov '14, 03:57) gg99

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

(06 Nov '14, 03:59) gg99
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 17 Jul '13, 00:08

question was seen: 1,343 times

last updated: 06 Nov '14, 05:24