February 11, 2002 at 5:25 am
Hi there,
I'm fairly new to SQL so this may be an easy question for someone to answer.
I have a database table which contains a column of image data. That translates to a list of files.
The process that I have to achieve is this...
When an insert is performed on the table, I need to get the blob from the database into a file so I can perform some further processing on it.
I created a trigger to get the file out, but I can't find any way of getting MS SQL to create a file for me that I can stream the data straight into.
My next plan was to get the trigger to call a stored procedure (passing the row index)which in turn made use of an activeX dll, to create an ADO connection to the database and get the file that way. This seemed a plausible route except that when I tried it out, the table is locked by SQL until the trigger has finished so my ADO connection times out waiting on the table to be free, which won't happen until the trigger finishes!
I can't see any other way of doing it, does anyone out there have any advice/resolution to this problem?
Thanks in advance for any replies,
Simon.
February 11, 2002 at 5:33 am
Simon,
Even if you could stream the data directly to a file not sure you'd want to - strive to make your triggers as quick and as light as possible. You can make your solution work with just a minor tweak or two. Use the trigger to insert the pkeys from the inserted table into a "WriteToFileQueue" table. Then run a job that will process all the rows in the table and pass each pkey to your stored proc that does the actual work of writing the file to disk. This will result in some latency of course, but the nice part is that its hugely scaleable, an insert of 1000 rows wont bring the server to a crawl.
Andy
February 11, 2002 at 5:45 am
Andy,
Thanks for that, it seems like a sensible way forward, I'll have a go at that. Makes sense rather than getting the trigger to do lots of work as you say,
Cheers,
Simon.
quote:
Simon,Even if you could stream the data directly to a file not sure you'd want to - strive to make your triggers as quick and as light as possible. You can make your solution work with just a minor tweak or two. Use the trigger to insert the pkeys from the inserted table into a "WriteToFileQueue" table. Then run a job that will process all the rows in the table and pass each pkey to your stored proc that does the actual work of writing the file to disk. This will result in some latency of course, but the nice part is that its hugely scaleable, an insert of 1000 rows wont bring the server to a crawl.
Andy
February 11, 2002 at 6:14 am
Glad to help. May want to wait a bit, see if others come up with better/different solutions!
Andy
February 11, 2002 at 5:40 pm
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply