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