telcor57
asked on
Problem using CLR External Environment in SQL Anywhere 11
I am trying to execute a C# function in an assembly from a stored procedure in SQL Anywhere 11.
C# Code:
using System;
using System.Collections.Generic ;
using System.Text;
namespace clrTest
{
class StaticTest
{
private static int val = 0;
public static int GetValue()
{
val += 1;
return val;
}
}
SQL code:
CREATE FUNCTION stc_get_value()
RETURNS INT
EXTERNAL NAME 'clrtest.dll::statictest.G etValue() int'
LANGUAGE CLR;
SELECT stc_get_value();
The output from running the last statement should be 1, 2, 3, 4 by executing the select statement 4 times.
The problem, I get a (NULL) back all the time.
I know the assembly is being found and loaded by SQLAnywhere, because when I unload the assembly, i get an error as :
Procedure 'stc_get_value' terminated with unhandled exception 'Object
reference not set to an instance of an object.'
SQLCODE=-91, ODBC 3 State="HY000".
C# Code:
using System;
using System.Collections.Generic
using System.Text;
namespace clrTest
{
class StaticTest
{
private static int val = 0;
public static int GetValue()
{
val += 1;
return val;
}
}
SQL code:
CREATE FUNCTION stc_get_value()
RETURNS INT
EXTERNAL NAME 'clrtest.dll::statictest.G
LANGUAGE CLR;
SELECT stc_get_value();
The output from running the last statement should be 1, 2, 3, 4 by executing the select statement 4 times.
The problem, I get a (NULL) back all the time.
I know the assembly is being found and loaded by SQLAnywhere, because when I unload the assembly, i get an error as :
Procedure 'stc_get_value' terminated with unhandled exception 'Object
reference not set to an instance of an object.'
SQLCODE=-91, ODBC 3 State="HY000".
ASKER
I tried the code above from r3nder, but SQL Anywhere 11 does not recognize the CREATE ASSEMBLY syntax. Did a lookup on CREATE ASSEMBLY in the Sybase Books online, and found nothing. But thanks for the suggestion.
is CLR enabled in the assembly for the database: type this to find out
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure
GO
there will be a 1 if it is enabled and 0 if not and 0 if waiting for a restart :) let me know
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure
GO
there will be a 1 if it is enabled and 0 if not and 0 if waiting for a restart :) let me know
ASKER
'sp_configure' is unknown to SQL Anywhere 11, I believe that is Microsoft SQL Server procedure.
sorry "Sybase" :)
have you looked into this - very close to what you are doing
http://www.sybase.com/detail?id=1056825&contentOnly=true
have you looked into this - very close to what you are doing
http://www.sybase.com/detail?id=1056825&contentOnly=true
ASKER
Yes, I have reviewed and tried that sample also.
type this
START EXTERNAL ENVIRONMENT CLR;
If the database server fails to start CLR, then the database server is likely not able to locate the CLR executable. The CLR executable is dbextclr12.exe. Make sure that this file is present in the install-dir\Bin32 or install-dir\Bin64 folder, depending on which version of the database server you are using.
START EXTERNAL ENVIRONMENT CLR;
If the database server fails to start CLR, then the database server is likely not able to locate the CLR executable. The CLR executable is dbextclr12.exe. Make sure that this file is present in the install-dir\Bin32 or install-dir\Bin64 folder, depending on which version of the database server you are using.
this is the best reference I could find - After following these simple instructions I was able to get it to run locally
http://dcx.sybase.com/index.html#1200en/dbprogramming/pg-extenv-clr.html
http://dcx.sybase.com/index.html#1200en/dbprogramming/pg-extenv-clr.html
ASKER
I have executed the 'START EXTERNAL ENVIRONMENT CLR; And the dbextclr11.exe is then present in the Process view of task manager. The sample that is in the last reference, is the code that I am trying to get to work.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you r3nder!
Sorry it took so long to spot it ;)
Please change your code to
CREATE ASSEMBLY stc_get_value() FROM 'C:\<location>\clrtest.dll
CREATE PROCEDURE stc_get_value
AS EXTERNAL NAME 'clrtest.dll::statictest.G
EXEC stc_get_value
**NOTE
<location> is where you have it located for testing purposes
Hope this helps