Question Re: Start a thread from CLR when called from SQL Anywhere in Technology Q&A

Start a thread from CLR when called from SQL Anywhere

Former Member — Wed, 07 Mar 2012 09:04:30 GMT

I am trying to trigger a url from a trigger in SQL Anywhere and not wait for the response.

In SQL Anywhere i have made the following function:

CREATE FUNCTION "DBA"."sendCallback"( in @serverip text,in @url text ) 
returns char(255)
not deterministic
external name 'Callback.dll::Namspace.Callback.OpenUrlAsync(string, string) string' language CLR

And in C# i have made the folowing program:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using System.Net;
using System.ComponentModel;

namespace Namspace
{
    public static class Callback
    {

        public static string OpenUrlAsync(string server, string url)
        {
            try
            {
                Thread t1 = new System.Threading.Thread
                  (() =>
                  {
                      using (WebClient wc = new WebClient())
                      {
                          try
                          {
                              string result = wc.DownloadString(server + url);
                          }
                          catch (Exception ee)
                          {
                              Console.Error.WriteLine(ee.Message);
                          }
                      }
                  });
                t1.Start();

            }
            catch (Exception e)
            {
                Console.Error.WriteLine(e.Message);
            }

            return "done";
        }

    }
}

When i call this from ISQL it works perfectly, but when it is called from the trigger it loks like the tread gets aborted before it can make the call.

If i add t1.Join() after it is started i get the desired effect, but then it uses to much time to be called from a trigger.

Is it correct that the CLR gets "teared down" when the connection calling it is finished? How can i make sure the thread finishes?

Re: Start a thread from CLR when called from SQL Anywhere

VolkerBarth — Wed, 07 Mar 2012 09:20:36 GMT

I don't have the answer, but like to hint to a different approach:

You may trigger an event (i.e. call TRIGGER EVENT myEvent) from the trigger, and let that event (as it will run in a separate connection) call any asynchronous CLR call or whatever you like.

At least that should de-couple the underlying DML statement from the CLR call, which might otherwise easily lead to errors and might rollback the current transaction....

Re: Start a thread from CLR when called from SQL Anywhere

Former Member — Wed, 07 Mar 2012 09:21:49 GMT

Of cource, my other approach was to have an event trigger every 1 sec and call from that one. I completely forgot taht i can trigger a event from code and make stuff "async" that way.

Re: Start a thread from CLR when called from SQL Anywhere

Former Member — Thu, 08 Mar 2012 04:09:16 GMT

I tried using TRIGGER EVENT and that works perfectly until i get a lot of inserts. Then i get the folowing error in serverlog:

Handler for event 'MeldFraOmNyeTider' caused SQLSTATE '40W06' All threads are blocked

Re: Start a thread from CLR when called from SQL Anywhere

VolkerBarth — Thu, 08 Mar 2012 04:59:08 GMT

OK, each event will run own its own connection and will try to occupy a worker thread from the pool - this will be queueed, but obviously there's too much "activity"/"demand" in the end.

Are you doing lots of single INSERTs (each inserting one row), or are these a few INSERT...SELECT statements but inserting many rows each?

In the latter case: Are you using row-level or statement-level triggers - a statement-level trigger help to reduce the number of events that are triggered.

As I don't know the actual task you are trying to achieve: A further approach would be to balance between a "push" and "poll" model, e.g. instead of triggering the event directly in the DML trigger, insert into a temp table, and then use some logic to trigger the event from that temp table once a certain limit/counter is hit...