Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-02-20 : 09:14:14
|
| Matt Shooks writes "I am trying to write a blob to the Database's file system using the ADO Stream object as there apears to be no other way without making a custom object. Below is the code I am currently using and I am wondering if it is even possible to use the ADO stream object in a stored procedure because whenever I try to pass the Write method my blob, which OLE Automation should convert to an array of bytes for the ADO Stream object, bombs out with the following error:sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BOTH params>Now the Write Method does not return anything so I don't need a return value and the only property is Buffer which I am sure I have the right syntax. Here is the stored procedure with @BinaryFile being the BLOB in question:Alter PROCEDURE spAddFile ( @FileName VARCHAR(270), @ZipName VARCHAR(270), @FolderID INT, @TypeID TINYINT, @Description TEXT, @Zipped BIT, @PasswordProtected BIT, @Password VARCHAR(10), @Expires SMALLDATETIME, @CommID VARCHAR(8), @IP VARCHAR(15), @BinaryFile IMAGE ) AS SET NOCOUNT ON DECLARE @intTempBlob INT DECLARE @intTempFileID INT DECLARE @strFileName VARCHAR(270) DECLARE @intObject INT DECLARE @intResult INT DECLARE @intRetVal INT DECLARE @intFileSize INT DECLARE @strFTPRoot VARCHAR(2000) DECLARE @strFileLocation VARCHAR(2270) DECLARE @strSource VARCHAR(255) DECLARE @strDescription VARCHAR(255) --Set the Physical file location on the DB server for the FTP directory DECLARE @intFolderID AS INT SET @intFolderID = @FolderID WHILE @intFolderID >= 1 BEGIN SELECT @strFTPRoot = SystemFolder + '\' + @strFTPRoot, @intFolderID = ParentFolderID FROM Folders WHERE ID = @intFolderID END SET @strFTPRoot = 'c:\inetpub\ftproot\' + @strFTPRoot --Create MWRFTP File control EXEC @intResult = sp_OACreate 'ADODB.Stream', @intObject OUT IF @intResult <> 0 BEGIN PRINT 'Failed to create object' EXEC @intResult = sp_OAGetErrorInfo @intObject, @strSource OUT, @strDescription OUT PRINT @strSource + ' - ' + @StrDescription RETURN END --Open the stream EXEC @intResult = sp_OAMethod @intObject, 'Open' IF @intResult <> 0 BEGIN PRINT 'Failed to open stream' EXEC @intResult = sp_OAGetErrorInfo @intObject, @strSource OUT, @strDescription OUT PRINT @strSource + ' - ' + @StrDescription RETURN @intResult END --Write to the stream EXEC @intResult = sp_OAMethod @intObject, 'Write', @Buffer = @BinaryFile IF @intResult <> 0 BEGIN PRINT 'Failed to write to stream' EXEC @intResult = sp_OAGetErrorInfo @intObject, @strSource OUT, @strDescription OUT PRINT @strSource + ' - ' + @StrDescription RETURN @intResult END --Retrieve the current file size EXEC @intResult = sp_OAGetProperty @intObject, 'Size', @intFileSize OUT IF @intResult <> 0 BEGIN PRINT 'Failed to get Size' EXEC @intResult = sp_OAGetErrorInfo @intObject, @strSource OUT, @strDescription OUT PRINT @strSource + ' - ' + @StrDescription RETURN END --Write The File SET @strFileLocation = @strFTPRoot + @FileName EXEC @intResult = sp_OAMethod @intObject, 'SaveToFile', @FileName = @strFileLocation, @SaveOptions = 2 IF @intResult <> 0 BEGIN PRINT 'Failed to write stream' EXEC @intResult = sp_OAGetErrorInfo @intObject, @strSource OUT, @strDescription OUT PRINT @strSource + ' - ' + @StrDescription RETURN @intResult END SET @strFileName = @FileName --Close The File EXEC @intResult = sp_OAMethod @intObject, 'Close' IF @intResult <> 0 BEGIN PRINT 'Failed to close stream' EXEC |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-20 : 09:51:54
|
| Part of your code got chopped off, but in any case there are better ways to output a text column to a file.I *think* you can do it using the bcp command line utility. Something like the following:bcp "SELECT textCol FROM myTable WHERE rowID=1" queryout C:\myBlob.txt -Sserver -Uusername -Ppassword -N -r -tThis would output the text column to C:\myBlob.txt...you can change the filename and extension to suit the content you're storing. The -N switch uses native format and will preserve binary data, but it will convert text to Unicode. The -r and -t parameters override the default row and column terminators. All of these are documented in Books Online under "bcp".If this DOES NOT work, there is an undocumented utility called textcopy.exe that is installed in your SQL Server program folders (look in the Binn subfolder for it). Run it on the command line like this:textcopy/?It will list its arguments and a brief description of how they work.You can use both bcp and textcopy from within SQL Server stored procedures by calling them with the xp_cmdshell procedure. This is also documented in Books Online. |
 |
|
|
shooksm
Starting Member
2 Posts |
Posted - 2002-02-21 : 08:10:19
|
| Yeah, the last part of the message did get cut off. I saw the message limit after I sent it. Sorry about that. The only thing left in the stored procedure was the command to destroy the object and then insert the location for the file into a files table. I also discussed really quickly why I was trying to pass a blob through a stored procedure as of reading the "How do I store an image in a database" threads leads me to believe it is bad to store a blob on the database. I have a NT4 Server SP6 web server on a different domain than the Database/FTP server with is also a NT4 Server SP6 machine running SQL 7 SP3. I wrote a fileupload ASP page that I want to pass the file to the stored procedure on the database server than have the database server write it out to its local ftp folder.I will try the bcp and textcopy commands by inserting the blob in a table first and let you know what I found out. Thanks. |
|
|
|
|
|
|
|
|
Writing a blob from a stored procedure to a file using ADO Stream