James Barash
James at fcidms.com
Wed May 18 09:18:05 CDT 2005
I agree, it's not a great solution -- I didn't like it any more than you do -- but the only one I found when I tried to do something similar. If you can wait for SQL 2005, you'll be able to write stored procedures in the .Net language of your choice and then the solution becomes easy, although no longer T-SQL. 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 9:28 PM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] Insert a picture into a SQL column 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 _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com