I need a clr function example

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

function clr

asked 17 Jul '13, 00:08

gg99
227●29●30●38
accept rate: 10%

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

flat view

One Answer:

active answers oldest answers newest answers popular answers

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;

permanent link

answered 23 Jul '13, 15:49

Jeff Albion
10.8k●1●71●175
accept rate: 25%

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

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

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

By RSS:

Answers

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

learn more about Markdown

Question tags:

function ×24
clr ×19

question asked: 17 Jul '13, 00:08

question was seen: 3,431 times

last updated: 06 Nov '14, 05:24

SAP SQL Anywhere

I need a clr function example

Follow this question

Related questions