[freetds] Freetds API query

manoranjan_sahu at agilent.com manoranjan_sahu at agilent.com
Mon Jun 9 14:32:59 EDT 2003


Bill, James,
   I really appreciate your comments. I have been breaking my head on this problem for a while.

To answer your question Bill, 
I am trying let our customer define a trigger on the table( say T1). They can define the trigger for whatever data they want, something like 

create trigger billing.showfees
after insert or update of fees
on billing.accounts
for each row
BEGIN
Send_DATA('UPDATE: fee change ' || :new.fees);
end;

Send_DATA is some functionality on the SQL server, to write the data on some port. ORACLE has a neat package called DBMS_PIPE to do this. Like you suggested we can put this data in a trigger table too..

And from unix system, I was hoping to use some freetds api to kind of wait on the port to read the data whenever it's available. I just need to read the data in my application.

I will see if we can go with your suggestion. The only issue, I see is that I need to be able to see the data as soon as it appears, rather than poll every x secs.

Thanks,
Manoranjan


-----Original Message-----
From: Thompson, Bill D (London) [mailto:ThompBil at exchange.uk.ml.com]
Sent: Monday, June 09, 2003 10:39 AM
To: 'FreeTDS Development Group'
Subject: RE: [freetds] Freetds API query


Manoranjan,

I think you're going to have to design this functionality yourself.

First, you're going to need a mechanism to simplify the task of finding out
whether the table has been updated or not.
I would suggest a trigger on the table which inserts a row into another
table whenever any update, delete or insert happens. This second table could
be a single-column table and you could put a unique clustered index on  that
column WITH IGNORE_DUP_KEY . That would prevent many rows being inserted
into the table. You'd just get one or none... a flag if you will.

next you need to design a program on the UNIX side, which will periodically
poll the second table looking for entries appearing. If you're not too
bothered about latency in your application, then you could have a 'sleep' in
your polling loop that would prevent you flooding the server with queries.
When you find a row in the 'trigger' table, you do what you have to do, then
delete it. then go back to polling again.

As james says, not pretty, not terribly efficient, but it will do the job
for you.

There may be a neater way to do this, it all depends on what you want to do
when this table changes. 
What do you want to do ?

HTH,


Bill

> -----Original Message-----
> From:	manoranjan_sahu at agilent.com [SMTP:manoranjan_sahu at agilent.com]
> Sent:	Monday, June 09, 2003 5:14 PM
> To:	freetds at lists.ibiblio.org
> Subject:	[freetds] Freetds API query
> 
> 
> Hello,
>    I am using freetds with unixodbc to communicate with MS SQL server.
> Here is what I am trying to do.
> 
> >From my unix system, my application needs to be notified of any
> change(insert, update, delete) to a table (say T1). Does freetds has any
> api I can use to do this task?
> 
> Thanks,
> Manoranjan 
> 
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds

_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds



More information about the FreeTDS mailing list