SQLTeam.com | Weblogs | Forums

Export Jpeg images

#1

HI
{edited Query to Use a Cursor but it runs without error but does not output}
I want to export Images associated to Products from a sales Order , I have borrowed and eddited a stored procedure to do this but i have come unstuck on 2 points

1- I cant get @Filename to link to the relevant Product
2 - It will only output one Image file and only if i apply TOP 1 to the Syntax

Any assistance as always appreciated

use Training

DECLARE @ImageData VARBINARY (max);
DECLARE @Path2OutFile NVARCHAR (2000);
DECLARE @Obj INT
DECLARE @ImageFolderPath NVARCHAR(1000)
DECLARE @ImageFilename NVARCHAR(1000)
SET @ImageFolderPath = '\ART-WM01\Import Services\TEST DUMP'
-- select @ImageFilename = (Select Productid from [#TempT3])
-- select @ImageFilename = (Select Prodcutid from [#TempT2])

CREATE TABLE [dbo].[#TempT3](
Productid NVARCHAR(15),
ImageData VARBINARY (max)
)
INSERT INTO #TempT3
select top 1 ProductId as ImageFilename ,
(SELECT top 1
(DocumentData) AS Expr1
FROM Documents
WHERE (TableName LIKE 'WebImages') and (DocumentTitle LIKE Products.ProductId) and(DocumentArchived = 0) AND (Identifier = Products.Product)
GROUP BY DocumentData) as ImageData

FROM SalesOrders INNER JOIN
SalesOrderItems ON SalesOrders.SalesOrder = SalesOrderItems.SalesOrder INNER JOIN

                     Products ON SalesOrderItems.Product = Products.Product AND SalesOrderItems.Product = Products.Product 

WHERE Salesorders.SalesOrderId = 'UKS0074438'

--select *
--from [dbo].[#TempT1]

SET NOCOUNT ON

DECLARE db_cursor CURSOR FOR 

SELECT @ImageData

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @Path2OutFile

WHILE @@FETCH_STATUS = 0
BEGIN

SET @Path2OutFile = CONCAT (
   @ImageFolderPath
     ,'\'
     , @ImageFilename
	 +'.Jpeg'
     );


BEGIN TRY
 EXEC sp_OACreate 'ADODB.Stream' ,@Obj OUTPUT;
 EXEC sp_OASetProperty @Obj ,'Type',1;
 EXEC sp_OAMethod @Obj,'Open';
 EXEC sp_OAMethod @Obj,'Write', NULL, @ImageData;
 EXEC sp_OAMethod @Obj,'SaveToFile', NULL, @Path2OutFile, 2;
 EXEC sp_OAMethod @Obj,'Close';
 EXEC sp_OADestroy @Obj;
END TRY

BEGIN CATCH
EXEC sp_OADestroy @Obj;
END CATCH
FETCH NEXT FROM db_cursor INTO @Path2OutFile
END

CLOSE db_cursor
DEALLOCATE db_cursor

Drop Table #TempT3

#2

I have never come across a situation where images need to be exported by a SP.

It is probably best to use an external program/script. Here is an example with Powershell:

#3

HI , I wanted to run it from an SP so it would only export the specific images that relate to Products on a SalesOrder . Our ERP system allows us to add buttons on the Salesorder screen that will execute the SP
Hope that makes sense