Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Problem using CLR External Environment in SQL Anywhere 11

Posted on 2010-08-12
15
1,312 Views
Last Modified: 2013-12-14
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.GetValue() 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".
0
Comment
Question by:telcor57
  • 8
  • 5
15 Comments
 
LVL 6

Expert Comment

by:r3nder
ID: 33439288
Telcor57
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.GetValue() int'
EXEC stc_get_value
**NOTE
<location> is where you have it located for testing purposes
Hope this helps

0
 

Author Comment

by:telcor57
ID: 33447026
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.
0
 
LVL 6

Expert Comment

by:r3nder
ID: 33447549
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
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:telcor57
ID: 33447806
'sp_configure' is unknown to SQL Anywhere 11, I believe that is Microsoft SQL Server procedure.
0
 
LVL 6

Expert Comment

by:r3nder
ID: 33447943
sorry "Sybase" :)
have you looked into this - very close to what you are doing
http://www.sybase.com/detail?id=1056825&contentOnly=true
0
 

Author Comment

by:telcor57
ID: 33447971
Yes, I have reviewed and tried that sample also.
0
 
LVL 6

Expert Comment

by:r3nder
ID: 33448033
0
 
LVL 6

Expert Comment

by:r3nder
ID: 33448084
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.

0
 
LVL 6

Expert Comment

by:r3nder
ID: 33448105
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


0
 

Author Comment

by:telcor57
ID: 33448793
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.

0
 
LVL 6

Accepted Solution

by:
r3nder earned 500 total points
ID: 33449555
**Note - I compiled and ran all of my code from C:\
also my precompiled dll is different from yours. You were setting the namespace to clrtest and the class to StaticTest
**Notice that on compilation it is named there (out:clrtest.dll - not in the dll)
you also dont need a namespace just a pulic class

to compile from C# to a dll run this
csc /target:library /out:clrtest.dll StaticTest.cs

from Interactive SQL run this
CREATE FUNCTION stc_get_value() 
RETURNS INT 
EXTERNAL NAME 'clrtest.dll::StaticTest.GetValue() int' 
LANGUAGE CLR;

to test run this
SELECT stc_get_value();

Open in new window

StaticTest.cs
0
 

Author Closing Comment

by:telcor57
ID: 33449776
Thank you r3nder!
0
 
LVL 6

Expert Comment

by:r3nder
ID: 33449890
Sorry it took so long to spot it ;)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question