SQl server TVF CLR function returns row index?

Question

I have this simple C# code which is used by Sql Server to return TVF:

[SqlFunction(FillRowMethodName = "FillRow3")]
public static IEnumerable GetCsv(string csv)
{
    string[] arr = csv.Split(',');
    return arr;
}

public static void FillRow3(Object obj, out int val, out int index)
{
    val = int.Parse((string)obj) ;
    index =  ??? <----------?
}

Hoever - I want to return a table which has 2 columns : ( val,index)

How can I return for each row , its index ( 0-based) according to arr

(obj is the row from arr.)

p.s - I can create an array of MyItem which will contain [value,index] in the GetCsv method.
And then - obj row ( val+index) will be visible to FillRow3 method. But I don't want to do that.

see this post may be helpful to you http://wfjanjua.blogspot.com/2012/05/sql-clr-table-valued-function-returns.html — Waqar Janjua, Aug 04 '12 at 18:21
@WaqarJanjua thanks but it doesnt help much.He doesnt analyze item location — Royi Namir, Aug 04 '12 at 18:21

Royi Namir · Answer 1 · 2012-08-04T18:46:50.353

0

I solved it.

I wanted to produce a counter in :

public static void FillRow3(Object obj, out int val, out int index)
{

}

But it must be static .

So I tried creating a static field :

public static   int g = 0;
public static void FillRow2(Object obj, out int val, out int index)
{
    val = (int.Parse((string) obj)*2);
    int h = g;
   index = h++;
}

but then SQL shouted :

CREATE ASSEMBLY failed because type 'Class1' in safe assembly 'MyDll2' has a static field 'g'. Attributes of static fields in safe assemblies must be marked readonly in Visual C#, ReadOnly in Visual Basic, or initonly in Visual C++ and intermediate language.

But if I will have a readonly int - I won't be able to change it.

So I created a wrapper reference object to that int.

the reference won't chage , the Int will... :)

So my final solution was :

  public class MyCounter
    {
        public  int cnt;
    }  


    public static readonly MyCounter mc = new MyCounter();

    public static void FillRow2(Object obj, out int val, out int index)
    {
        val = (int.Parse((string) obj)*2);

        index = mc.cnt++;
    }

And..................

enter image description here

edited Aug 04 '12 at 18:46

answered Aug 04 '12 at 18:41

Royi Namir

144,742
138
468
792

By using static state you are introducing a race condition between concurrent threads using your function. You *will* get incorrect results if SQL Server executes your query in parallel or executes multiple queries. – usr Aug 04 '12 at 19:53
@usr I use lock inside `GetCsv`. this will allow only 1 thread do its job - one at a time. However , each ignition should start from 0 . which it doesnt(currently) it starts from the last integer....I should see how im fixing it. – Royi Namir Aug 04 '12 at 22:01
First, GetCsv is called. Then, FillRow is called repeatedly. You don't protect both using locking, you only protect GetCsv. Just get rid of the static state. Please don't put such code on the internet. I'm tempted to answer this question properly but you can find correct examples on the web: http://www.codeproject.com/Articles/38710/Some-Utility-Functions-using-SQL-Server-2005-CLR-H *Please* delete this post as people will paste this literally and shoot themselves in the foot. – usr Aug 04 '12 at 22:11
@usr How does your link suppose to help ? It has just a simple code , no Indesx situation... I dont understand why _you_ pasted it – Royi Namir Aug 05 '12 at 04:51
@usr The documentation on this topic is sucks. where does it say that first getcsv is called , and then FillRow is called repeatedly ? if thats the situation , So I **MUST** fill indexes at the GetCsv method. – Royi Namir Aug 05 '12 at 05:26
I did not downvote. The sample code can be found searching for "IEnumerable Split". The best docs I could find are here: http://msdn.microsoft.com/en-us/library/ms131103.aspx The examples look good to me. – usr Aug 05 '12 at 10:41

score 0 · Accepted Answer · edited May 29 '14 at 06:44

0

It should look like this:

[SqlFunction(FillRowMethodName = "FillRow3")]
public static IEnumerable GetCsv(string csv)
{
    string[] arr = csv.Split(',');
    return arr.Select((x, i) => Tuple.Create(x, i));
}

public static void FillRow3(Object obj, out string val, out int index)
{
    var input = (Tuple<string, int>)obj;
    val = input.Item1;
    index = input.Item2;
}

Notice, that no static variables are needed. If you don't think static state inside of SQL Server is a problem please research this issue.

edited May 29 '14 at 06:44

James L.

9,384
5
38
77

answered Aug 04 '12 at 22:15

usr

168,620
35
240
369

I kind of ignored that one because I cannot find any reason. Why do you pose this constraint? – usr Aug 05 '12 at 10:42
cause I wanted to see if I can handle the index in the seconds method., – Royi Namir Aug 05 '12 at 10:47

SQl server TVF CLR function returns row index?

2 Answers2