The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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?

asked 07 Mar '12, 09:04

Quick's gravatar image

Quick
86227
accept rate: 0%

edited 15 Mar '13, 21:21

Mark%20Culp's gravatar image

Mark Culp
22.3k9129262


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....

permanent link

answered 07 Mar '12, 09:20

Volker%20Barth's gravatar image

Volker Barth
29.3k287438644
accept rate: 32%

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.

(07 Mar '12, 09:21) Quick

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

(08 Mar '12, 04:09) Quick
Replies hidden
1

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...

(08 Mar '12, 04:59) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×58
×15
×9

question asked: 07 Mar '12, 09:04

question was seen: 2,536 times

last updated: 15 Mar '13, 21:21