Problem with SQL Anywhere 16 CLR EXTERNAL procedure with resultset

[Former Member]

Hi, all

I have trouble with generating resultset from CLR -procedure.

1) I'm using sample from Generating resultset from CLR - SQLA Forum

2) SQL Anywhere 16 64-bit

3) .NET 4.0 (  ALTER EXTERNAL ENVIRONMENT CLR LOCATION ‘dbextclr16_v4.0' )

4) C# project - "build Platform Target - 64bit

When i calling select * from CLR_RESULT(), i get errors

   at iAnywhere.SAClrClassLoader.GetTypeFromString(String str)

  at iAnywhere.SAClrClassLoader.ParseArgTypes(String argString, String returnString, SADbType[]& argTypes, Boolean& hasReturn, Boolean& hasResults)

  at iAnywhere.SAClrClassLoader.Execute(String methodSig)

Invalid type in parameter list:

What am I doing wrong?

[former_member188493]

Please show us the exact code you are using.

[Former Member]

Hi, Breck

C# code:

using System;

using System.Data;

using System.Collections.Generic;

using System.Text;

    public class RSTest

    {

        public static void GetRows(IDataReader[] readers)

        {

            DataTable table = new DataTable("Table");

            table.Columns.Add("c1");

            table.Columns.Add("c2");

            //Add rows

            for (int i = 0; i <= 100; i++)

            {

                table.Rows.Add(i * 2, i * 3);

            }

            DataSet set = new DataSet("Result");

            set.Tables.Add(table);

            readers[0] = set.CreateDataReader();

        }

    }

Step: Create the procedure:

CREATE PROCEDURE CLR_RESULT() RESULT (c1 int, c2 int) DYNAMIC RESULT SETS 1

EXTERNAL NAME 'f:\\db\\CLR\\RStest.dll::RSTest.GetRows( IDataReader[] )'  LANGUAGE CLR;

[Former Member]

Alexander,

poor SQL Anywhere will most likely call DataTableReader.GetSchemaTable() to learn about the names and the data types of the columns returned by the reader. The schema table in return is build from the Columns of the underlying DataTable.

And I'm pretty sure that's the problem with your code: you do not specify the datatype of your columns. Please try:

   table.Columns.Add("c1", typeof(Int32));

   table.Columns.Add("c2", typeof(Int32));

And by the way, you don't need the DataSet.

  readers[0] = new DataTableReader(table);

is just fine.

Kind regards,

Michael

[Former Member]

Alexander,

please see http://sqlanywhere-forum.sap.com/questions/24905/do-i-really-need-hardcoded-assembly-pathes-for-the-... for a fully working example.

Michael

[Former Member]

Michael,

Thank you

Now my sample work fine.