Arthur Fuller
artful at rogers.com
Tue May 17 20:28:01 CDT 2005
I had found that one myself, and hated the solution on two grounds: 1. way too many parameters, almost all of which would be assumed by the context... i.e. I could start with that code and rewrite it significantly. 2. the code reaches outside T-SQL to run textcopy, which subverts my original intent. I wanted to do the whole thing in T-SQL, and I'm concluding that it cannot be done. So my choices are to do it in the front-end using ADO or to pretend I'm doing it in the back end by running textcopy. Don't take this as a put-down of your proposed solution, or a slight upon your efforts to search for a solution for me. If there's a slam aimed anywhere, it's at Microsoft -- either for not providing the functionality I want or for providing it and not documenting it. And not only with image files! I would want this functionality with numerous file types, but let's take just a few simple cases that I might need -- extract the contents of a text file, extract the contents of a Word file, and so on. I might even want to inhale a .com or .exe file (can't think of a reason at the moment, but that's not the point) and store the contents in a file. I would want to declare a variable and manipulate it like so.... DECLARE @myVar AS mp3File SET @myVar = FileOpen( "MyFavouriteSong.mp3" UPDATE SongList SET Song = @myVar WHERE PK = 12345 But from everything I've read so far on this subject, it appears that I cannot do it in T-SQL. Why does this bug me so much? Because I believe that everything that can be done in the back end should be done in the back end. Then I wouldn't have to code the same stuff in several FE languages -- I could just have each FE language fire the sproc that does it all. Oh well. I live in a dream world. LOL. Arthur James Barash wrote: >Arthur: > >I think this article will give you what you are looking for. It's not very >straightforward but if you are determined to do this in T-SQL, it does work. > >http://www.databasejournal.com/features/mssql/article.php/1443521 > >Just remember that filenames must be from the Server's point-of-view. > >Hope this helps. > >James Barash > > >-----Original Message----- >From: dba-sqlserver-bounces at databaseadvisors.com >[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur >Fuller >Sent: Tuesday, May 17, 2005 1:50 PM >To: dba-sqlserver at databaseadvisors.com >Subject: Re: [dba-SQLServer] Insert a picture into a SQL column > > >That was interesting but not exactly where I want to go. If at all >possible, I want to do it all in the SQL database, using a sproc that >might resemble this: > >CREATE PROCEDURE ImageImport_ap >( > @pk int, > @imageFileName varchar(100) >) >AS > /* maybe I need a stream object or something here, I don't know > but I want ideally to handle everything in the sproc, with no FE >code but passing the parms > */ > UPDATE myTable > SET Picture = [data read from image file] > WHERE PK = @pk > >Anyone know how to achieve this? >Arthur > >Francisco Tapia wrote: > > > >>Look in BOL under blob, >>here is a MS example on how to do what you want >> >>http://support.microsoft.com/kb/q173308/ >> >>I'm looking at a similar situation only because I'm going to need to >>display >>the same picture out on the web as in an internal app. Not sure if I'll >> >> >use > > >>this approach due to the typical cautions. >> >>On 5/16/05, Arthur Fuller <artful at rogers.com> wrote: >> >> >> >> >>>Let's leave aside the wisdom of doing this and focus on the fact of >>>doing it. I have a sproc that doesn't work. I pass a filename and the >>>PK of the row into which I want to insert said image file. It doesn't >>>work as written. I think that maybe I need to create a stream object >>>and read chunks and then update the row using said accumulated chunk, >>>but I cannot yet figure out how to do this in pure T-SQL. >>> >>>Suggestions? >>> >>>TIA, >>>Arthur >>> >>> >>> >>> >>> >>>> >>>> >>>> >>>> >>>-- >>>No virus found in this outgoing message. >>>Checked by AVG Anti-Virus. >>>Version: 7.0.308 / Virus Database: 266.11.10 - Release Date: 5/13/2005 >>> >>>_______________________________________________ >>>dba-SQLServer mailing list >>>dba-SQLServer at databaseadvisors.com >>>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >>>http://www.databaseadvisors.com >>> >>> >>> >>> >>> >>> >> >> >> >> > > > > -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.10 - Release Date: 5/13/2005