conversion of datatype "Image" to "Var Binary" in MS-sql

ashishkedia
ashishkedia used Ask the Experts™
on
Procedure to convert the data from DATA TYPE " IMAGE " to  VARBINARY  in MS-SQL server 2008 (version 10.0.4000.0) ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
You can use CAST or CONVERT function to convert between Image and varbinary datatypes.
Please be noted that you can't declare some local variables as Image datatype due to limitations..
Kindly post your entire code so that I can verify it once and suggest better.
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Are you talking about changing the data type of the column in table?
If yes, you can use:
ALTER TABLE table_name
ALTER COLUMN column_name VARBINARY(MAX);

Open in new window

Author

Commented:
I have a .jpeg file which is stored in My sql  data base in one of the table with column name say 'X' whose data type "IMAGE" .
The actual requirement is to retrieve the .jpeg file from the data base using procedure
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Yes, you can easily insert and export image data from Image datatype column using stored procedures..
Sample below..
https://www.mssqltips.com/sqlservertip/4963/simple-image-import-and-export-using-tsql-for-sql-server/

Author

Commented:
HI,
Thanks but The data is stored as  data type as "Image" not var binary . Hence we can not use the procedure mentioned in the link . We need to convert them in VARBINARY before use this procedure .
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
No need, just create the table with Image datatype and the above approach will work..
FYI, I've double checked this one as well..
CREATE TABLE Pictures (
   pictureName NVARCHAR(40) PRIMARY KEY NOT NULL
   , picFileName NVARCHAR (100)
   , PictureData image
   )
GO

Open in new window

Author

Commented:
What is version of the Sql server managment studio we are using ?
We are using sql 2008 .

SELECT @ImageData = (
         SELECT convert (VARBINARY (max), PictureData, 1)
         FROM Pictures
         WHERE pictureName = @PicName
         );
This convert is not working for Data Type of Image .

Also defining DECLARE @ImageData VARBINARY (max) as Image is giving error .

May we request you to share the sample code form your end this would be great help for us
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
I've tested in SQL Server 2008 R2 and the procedures mentioned in the above link provided was working fine for me for both INSERT and SELECT as well.
Currently, I don't have any SQL Server 2008 environment available, kindly let me know whether a quick session is possible or not..

Author

Commented:
We have also done in 2008 R2 and we will share the procedure sample for your feedback tomorrow .
Then based on that we can have a discussion

Author

Commented:
PictureData is a data type "IMAGE" in table Pictures_IMAGE.
Microsoft  sql server studio version is 10.0.4000.0

CREATE PROCEDURE dbo.usp_ExportImage (
   @PrimKey NVARCHAR (100) 
   ,@ImageFolderPath NVARCHAR(1000)
   ,@Filename NVARCHAR(1000)
   )
AS
BEGIN
   DECLARE @ImageData IMAGE;
   DECLARE @Path2OutFile NVARCHAR (2000);
   DECLARE @Obj INT
 
   SET NOCOUNT ON
 
   SELECT @ImageData = (
         SELECT PictureData
         FROM Pictures_IMAGE
         WHERE pictureName=@PrimKey 
         );
 
   SET @Path2OutFile = @ImageFolderPath + '\' + @Filename ;
    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
 
   SET NOCOUNT OFF
END
GO

Open in new window

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
This piece of code is missing from your script..
   SET @Path2OutFile = CONCAT (
         @ImageFolderPath
         ,'\'
         , @Filename
         );

Open in new window


INstead change the above code to the below one since CONCAT function is not available in SQL Server 2008.
   SET @Path2OutFile = @ImageFolderPath  + '\' + @Filename;

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial