Return resultset from CLR

[Former Member]

Hi.

I've created a C# DLL which is called in a CLR Function in a SA12 database.

The DLL successfully returns a decimal value from this call:

SELECT sfw_GetKostverdi(param1, param2 ....paramN);

No problem, and everything checks out great.

But now I need to return a DataSet, containing one or more columns, and one or more rows.

I've tried to return a DataSet from the DLL, but I get the following error:

Table 'ExtEnvResultSet29' not found

SQLCODE=-141, ODBC 3 State="42S02"

I found an example using IDataReader, but it will not work.

Have anyone got at working example regarding this?

Or can shed any light over the issue?

Regards,

Bjarne Anker

Maritech Systems AS

Norway

[former_member188493 · Accepted solution]

Please show us the code that is not working (either your code, the sample you found, or both).

SQL Anywhere 12 comes with sample code in this folder...

C:\Users\Public\Documents\SQL Anywhere 12\Samples\SQLAnywhere\ExternalEnvironments\CLR

If that is the sample that is not working for you, please post the symptom.

[Former Member · Accepted solution]

I tried the example in the reply to this post:

http://sqlanywhere-forum.sap.com/questions/11524/generating-resultset-from-clr

In short, I have this function in my DLL:

public static Dataset GetKostVerdi(int p1, string p2)

{

     DataTable dt = new DataTable("Table");

     DataSet ds = new DataSet("Result");

     decimal value1;

     decimal value2;

     ....some code....

     dt.Rows.Add(value1, value2);

     ds.Tables.Add(dt);

     return ds;

}

The stored procedure in SA12 is defined like this:

CREATE PROCEDURE spw_GetKostverdi( in p1 integer,in p2 varchar(8) )

result( Res1 numeric(10,3),Res2 numeric(10,3) ) dynamic result sets 1

external name 'D:\\TEST\\CLRKalkyle.dll::CLRKalkyle.CLRKalkyle.GetKostVerdi2(int,string)' language clr

It's important that I cas pass parameters into the DLL and receive a resultset.

I wasn't aware of the folder with the provided examples, and I will of course test this.

[Former Member · Accepted solution]

I've tried the provided examples, and they all work good.

But as soon as I add an in-parameter to both the CLR Stored procedure and the function "GetResultSet1", the call fails with the same error as my code:

Could not execute statement.

Table 'ExtEnvResultSetNN' not found

SQLCODE=-141, ODBC 3 State="42S02"

The stored procedure looks like this:

CREATE PROCEDURE CLRGetResultSet1(in p1 integer) RESULT( c1 int, c2 char(128) )

    DYNAMIC RESULT SETS 1

    EXTERNAL NAME 'clrtest.dll::ResultSet.GetResultSet1( IDataReader[],int )'

    LANGUAGE CLR;

And the function looks like this:

public static void GetResultSet1(IDataReader[] readers,int p1)

Any ideas?

Is it not possible to pass parameters into a CLR stored procedure which resturns a IDataReader?

[former_member188493 · Accepted solution]

That string ExtEnvResultSetNN appears nowhere that I could find on either Google or in the SQL Anywhere 12 executables... is it in your code?

Please show us the ACTUAL code for your procedure (not just the SQL proxy CREATE)... thanks

[Former Member · Accepted solution]

The error appears in Interactive SQL when running either "call procedure()" or "select * from procedure()".

I've modified the example that you provided in this folder:

C:\Users\Public\Documents\SQL Anywhere 12\Samples\SQLAnywhere\ExternalEnvironments\CLR

STORED PROCEDURE:

CREATE PROCEDURE CLRGetResultSet1( in p1 integer )

result( c1 integer,c2 char(128) ) dynamic result sets 1

external name 'CLRKalkyle.dll::CLRKalkyle.CLRKalkyle.GetResultSet1( IDataReader[],int )' language CLR

DLL:

namespace CLRKalkyle

{

    public class CLRKalkyle

    {

        public static void GetResultSet1(IDataReader[] readers,int p1)

        {

            // Assume ResultSet_Tab already exists

            readers[0] = SAServerSideConnection.Connection.ExecuteReader("SELECT c1,c2 FROM ResultSet_Tab WHERE c1 = " + p1.ToString() + " ORDER BY c1");

        }

    }

}

Test:

call GetResultSet1(1);

It works fine without the int parameter.

The string 'ExtEnvResultSetNN' starts at 'ExtEnvResultSet01' and increments by 1 for every call of the store procedure.

[former_member188493 · Accepted solution]

It is possible that SQL Anywhere doesn't support parameters and result sets in the same CLR EXTERNAL call but we'll never know from the Help or Samples... the Help leaves all discussion of result sets to the samples (even in SQL Anywhere 16), and the sample code doesn't show parameters together with result sets.

My guess is this: you will get much better results if you ask the question that needs asking: "How do I code a SQL Anywhere 12 CLR EXTERNAL procedure that accepts a parameter and returns a result set?"

Or... try hacking around; e.g., try putting p1 ahead of readers in public static void GetResultSet1(IDataReader[] readers,int p1)

[Former Member · Accepted solution]

It looks like it works now.

I moved the "int p1" parameter to the start of the public static void function (pretty sure I've tried it before).

The stored procedure has one parameter in: "IN p1 INT"

The CLR definition has two parameters, to match the function in the DLL: (int p1, IDataReader readers[]).

When calling the procedure like this:

call GetResultSet1(2)

It returns all rows in the example-table where c1=2 (I've also modified the select-statement from the example).

I have no idea why it works, but now it does!

Regards,

Bjarne