Export image datatype to disk - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Export image datatype to disk

Author

Topic

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-04-25 : 11:02:56

I have a table with an IMAGE datatype. Without using xp_cmdshell and bcp, is it possible to get this image to disk? We do have the OLE procedures enabled so I can use those to create in instance of the Scripting.FileSystemObject and write to disk but how do I convert the image datatype to something I can store on disk and it actually be a readable format? If it helps, we only store this particular field in JPEG format in the database.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 11:09:04

If the data is stored as IMAGE, it means it is stored binary.
Make a binary filestream with Scripting.FileSystemObject .

E 12°55'05.25"
N 56°04'39.16"

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-04-25 : 12:00:52

Here lies my issue... can you use the Scripting.FileSystemObject to write a binary file using T-SQL? I know in VB / C#, etc you can by using a byte array but how would I emulate that in T-SQL? Perhaps I can use an ADODB.Stream object instead...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-25 : 14:06:37

could you create a CLR sproc to do this? it is prefered way to do custom disk read/write activity in sql server 2005.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-04-25 : 14:27:38

quote:
Originally posted by spirit1

could you create a CLR sproc to do this? it is prefered way to do custom disk read/write activity in sql server 2005.

I might be able to but I won't :). In my situation, I do not want to open the avenue of developers coding CLR on my databases for obvious reasons. Here is a method I use for standard text files:

SET NOCOUNT ON

DECLARE @ErrorNumber INT
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
DECLARE @ErrorProcedure NVARCHAR(126)
DECLARE @ErrorLine INT
DECLARE @ErrorMessage NVARCHAR(2048)

--For OLE Automation.
DECLARE @ObjectReturn INT
DECLARE @ObjectToken INT
DECLARE @ErrorSource VARCHAR(255)
DECLARE @ErrorDesc VARCHAR(255)
DECLARE @FileHandle INT
DECLARE @ObjectTargetFileName VARCHAR(1000)
DECLARE @Buffer VARCHAR(4000)
DECLARE @BufferPos INT
DECLARE @BufferSize INT; SET @BufferSize = 4000

DECLARE @ScheduleData XML

/* Get the schedule data */
--custom code went here to return XML datatype into @ScheduleData - so assume @ScheduleData is valid XML.

BEGIN TRY
    /* Create a file system object. */
    EXEC @ObjectReturn = sp_OACreate 'Scripting.FileSystemObject', @ObjectToken OUTPUT
    IF (@ObjectReturn <> 0)
        BEGIN
            EXEC sp_OAGetErrorInfo @ObjectToken, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
            RAISERROR('Create Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
        END

    /* Open the file. */
    SET @ObjectTargetFileName = '\\SERVERNAME\SHARENAME\SYSMAINT.XML'
    EXEC @ObjectReturn = sp_OAMethod @ObjectToken, 'OpenTextFile', @FileHandle OUTPUT, @FileName=@ObjectTargetFileName, @IOMode=2, @Create=1
    IF (@ObjectReturn <> 0)
        BEGIN
            EXEC sp_OAGetErrorInfo @ObjectToken, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
            RAISERROR('OpenTextFile Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
        END

    /* Write the file */
    /* Since the writing of the file gets truncated at 4000 characters, buffer the writes. */
    SET @BufferPos = 0
    WHILE (@BufferPos < LEN(CAST(@ScheduleData AS NVARCHAR(MAX))))
        BEGIN
            SET @Buffer = SUBSTRING(CAST(@ScheduleData AS NVARCHAR(MAX)), @BufferPos + 1, @BufferSize)
            SET @BufferPos = @BufferPos + @BufferSize

            EXEC @ObjectReturn = sp_OAMethod @FileHandle, 'Write', NULL, @Text=@Buffer
            IF (@ObjectReturn <> 0)
                BEGIN
                    EXEC sp_OAGetErrorInfo @FileHandle, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
                    RAISERROR('Write Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
                END
        END

    /* Close the file. */
    EXEC @ObjectReturn = sp_OAMethod @FileHandle, 'Close'
    IF (@ObjectReturn <> 0)
        BEGIN
            EXEC sp_OAGetErrorInfo @FileHandle, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
            RAISERROR('Close Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
        END

    /* Destroy the text stream object. */
    EXEC @ObjectReturn = sp_OADestroy @FileHandle
    IF (@ObjectReturn <> 0)
        BEGIN
            EXEC sp_OAGetErrorInfo @FileHandle, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
            RAISERROR('TextStream Destroy Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
        END

    /* Destroy the file system object. */
    EXEC @ObjectReturn = sp_OADestroy @ObjectToken
    IF (@ObjectReturn <> 0)
        BEGIN
            EXEC sp_OAGetErrorInfo @ObjectToken, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
            RAISERROR('FileSystemObject Destroy Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
        END
END TRY
BEGIN CATCH
    SELECT
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorProcedure = ERROR_PROCEDURE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorMessage = ERROR_MESSAGE();
    RAISERROR('Procedure ''%s'' failed on line number ''%u'' with message ''%s'' - (error number: ''%u'', severity: ''%u'', state: ''%u'').', 15, 1, @ErrorProcedure, @ErrorLine, @ErrorMessage, @ErrorNumber, @ErrorSeverity, @ErrorState)
END CATCH

Now when I tailor this for an image datatype, the write always fails. The error message is very vague unfortunately but I have to assume it's due to the file being binary. Here is a similar script I have at this point for binary data (note the line in red that failes):

DECLARE @ErrorNumber INT
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
DECLARE @ErrorProcedure NVARCHAR(126)
DECLARE @ErrorLine INT
DECLARE @ErrorMessage NVARCHAR(2048)

--For OLE Automation.
DECLARE @ObjectReturn INT
DECLARE @ObjectToken INT
DECLARE @ErrorSource VARCHAR(255)
DECLARE @ErrorDesc VARCHAR(255)
DECLARE @FileHandle INT
DECLARE @ObjectTargetFileName VARCHAR(1000)
DECLARE @Buffer VARBINARY(4000)

DECLARE @BufferPos INT
DECLARE @BufferSize INT; SET @BufferSize = 4000

DECLARE @Image VARBINARY(MAX)

--custom code went here to retrieve an IMAGE column into variable @Image from an IMAGE datatype in a table.
--SELECT @Image = assume valid query is here.

BEGIN TRY
    /* Create a file system object. */
    EXEC @ObjectReturn = sp_OACreate 'Scripting.FileSystemObject', @ObjectToken OUTPUT
    IF (@ObjectReturn <> 0)
        BEGIN
            EXEC sp_OAGetErrorInfo @ObjectToken, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
            RAISERROR('Create Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
        END

    /* Open the file. */
    SET @ObjectTargetFileName = '\\SQLPRODVIRTUAL\SQLUPLOAD\TEMP\' + 'BEN01.JPG'
    EXEC @ObjectReturn = sp_OAMethod @ObjectToken, 'OpenTextFile', @FileHandle OUTPUT, @FileName=@ObjectTargetFileName, @IOMode=2, @Create=1
    IF (@ObjectReturn <> 0)
        BEGIN
            EXEC sp_OAGetErrorInfo @ObjectToken, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
            RAISERROR('OpenTextFile Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
        END

    SET @BufferPos = 0
    WHILE (@BufferPos < DATALENGTH(@Image))
        BEGIN
            SET @Buffer = SUBSTRING(@Image, @BufferPos + 1, @BufferSize)
            SET @BufferPos = @BufferPos + @BufferSize

            EXEC @ObjectReturn = sp_OAMethod @FileHandle, 'Write', NULL, @Text=@Buffer
            IF (@ObjectReturn <> 0)
                BEGIN
                    EXEC sp_OAGetErrorInfo @FileHandle, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
                    RAISERROR('Write Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
                END
        END

    /* Close the file. */
    EXEC @ObjectReturn = sp_OAMethod @FileHandle, 'Close'
    IF (@ObjectReturn <> 0)
        BEGIN
            EXEC sp_OAGetErrorInfo @FileHandle, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
            RAISERROR('Close Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
        END

    /* Destroy the text stream object. */
    EXEC @ObjectReturn = sp_OADestroy @FileHandle
    IF (@ObjectReturn <> 0)
        BEGIN
            EXEC sp_OAGetErrorInfo @FileHandle, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
            RAISERROR('TextStream Destroy Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
        END

    /* Destroy the file system object. */
    EXEC @ObjectReturn = sp_OADestroy @ObjectToken
    IF (@ObjectReturn <> 0)
        BEGIN
            EXEC sp_OAGetErrorInfo @ObjectToken, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
            RAISERROR('FileSystemObject Destroy Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
        END
END TRY
BEGIN CATCH
    SELECT
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorProcedure = ERROR_PROCEDURE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorMessage = ERROR_MESSAGE();
    RAISERROR('Procedure ''%s'' failed on line number ''%u'' with message ''%s'' - (error number: ''%u'', severity: ''%u'', state: ''%u'').', 15, 1, @ErrorProcedure, @ErrorLine, @ErrorMessage, @ErrorNumber, @ErrorSeverity, @ErrorState)
END CATCH

The error I get back is
Msg 50000, Level 15, State 1, Line 105
Procedure '(null)' failed on line number '69' with message 'Write Error (return: '2148139013', source: '(null)', description: '(null)')' - (error number: '50000', severity: '15', state: '1').

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-25 : 14:41:01

i can't believe you prefer sp_OA* to CLR...

Write can only write string. so you'll have to convert your binary to string.
usually this is done with ADODB.RecordSet object:


Function BinaryToString(Binary) 
 Dim RS, LBinary 
 Const adLongVarChar = 201 
 Set RS = Server.CreateObject("ADODB.Recordset") 
 LBinary = LenB(Binary) 


 If LBinary>0 Then 
  RS.Fields.Append "mBinary", adLongVarChar, LBinary 
  RS.Open 
  RS.AddNew 
  RS("mBinary").AppendChunk Binary 
  RS.Update 
  BinaryToString = RS("mBinary") 
 Else 
  BinaryToString = "" 
 End If 

 RS.Close 
 Set RS = Nothing 
End Function

so... i still can't convince you to use clr for this? it's just a few lines of code

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-04-25 : 14:48:29

As far as CLR is concerned, I'd love to honestly but I'm in a situation where if I did, some developer would start writing code on my database and cause it to fail... guaranteed :). Not maliciously or anything but we do from time to time struggle with quality control of the developers overseas (we outsource 1/2 our shop).

Anyway, I did change one line and the writes started working:
EXEC @ObjectReturn = sp_OAMethod @ObjectToken, 'OpenTextFile', @FileHandle OUTPUT, @FileName=@ObjectTargetFileName, @IOMode=2, @Create=1, @Format=-1

However the file still isn't a valid JPG... most likely due to not being converted properly. I was really hoping there was some casting I could do in T-SQL to accomplish this (like SUBSTRING since it was reworked to work with VARBINARY(MAX) datatype).

Anyway, I would like to actually not even use the sp_OA procs so if you have any other suggestions I definitely would welcome them. I need to run but I'm going to try a ASCII(CHAR(n)) on each character later... LOL.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-25 : 15:01:02

so don't let them have permissions to create assemblies.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-04-28 : 12:03:38

quote:
Originally posted by spirit1

so don't let them have permissions to create assemblies.

I just might go this route TBH. Especially since I'm pretty fluent in C# as well. I suspect I can make this work in our current security model as well (we have a daily job that strips and resets permissions across all of our databases). As long as I just keep the permissions for creating CLR to a few system IDs I should be good to go. Correct me if I'm wrong but this is already part of the sysadmin role so I really will not need to make any security changes with the exception of executing. Which I can do since we have standard roles that I can just assign this proc to (methinks).

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-04-28 : 16:36:42

Hey spirit (and anyone else that responded)... I ended up taking a shortcut and still using the sp_OA procs. In summary I just utilized the ADODB.Stream object something like this:

EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, 'Type', 1
EXEC sp_OAMethod @ObjectToken, 'Open'
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, <variable defined as VARBINARY(MAX)>
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, <location on disk>, 2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken

I would love to go the CLR route but right now I do not have the luxary to fit it into our environment and this works simply put.

I will however put a task on my list to upgrade our approach (we've been using this for literally YEARS).

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-28 : 16:43:25

nice!

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

pprajapati
Starting Member

2 Posts

Posted - 2009-01-12 : 01:49:16

i have tried with single image with following command , anf get ..sucess
DECLARE @ObjectToken INT
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, 'Type', 1
EXEC sp_OAMethod @ObjectToken, 'Open'
EXEC sp_OAMethod @ObjectToken, 'Write', NULL,0xFFD8FFE000104A46494600010101006000600000FFDB004300080606070605080707070909080A0C140D0C0B0B0C1912130F141D1A1F1E1D1A1C1C20242E2720222C231C1C2837292C30313434341F27393D38323C2E333432FFDB0043010909090C0B0C180D0D1832211C213232323232323232323232323232323232323232323232323232323232323232323232323232323232323232323232323232FFC0001108009300C003012200021101031101FFC4001F0000010501010101010100000000000000000102030405060708090A0BFFC400B5100002010303020403050504040000017D01020300041105122131410613516107227114328191A1082342B1C11552D1F02433627282090A161718191A25262728292A3435363738393A434445464748494A535455565758595A636465666768696A737475767778797A838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE1E2E3E4E5E6E7E8E9EAF1F2F3F4F5F6F7F8F9FAFFC4001F0100030101010101010101010000000000000102030405060708090A0BFFC400B51100020102040403040705040400010277000102031104052131061241510761711322328108144291A1B1C109233352F0156272D10A162434E125F11718191A262728292A35363738393A434445464748494A535455565758595A636465666768696A737475767778797A82838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE2E3E4E5E6E7E8E9EAF2F3F4F5F6F7F8F9FAFFDA000C03010002110311003F00F7FA28A2800A28A0D00646B5ACD9E816D1DDDFB98ED9E6589A5C709BB3827DB3DFB66B4A374954323065619041C823B1AE13E2DCCABE1358187FAD93A7D01FF1AE17E0CF8C35A6D67FE11B9209AF74C0AC566033F63239C13FDD3D31D8918EF408F7AA29BBBA678A5CFB503168A4CD31E458D0BB90AA3A92700500494564E95AED8EB573791D8C9E725A388E4957EE97C64807BE38FCEB5A800A28A2800A28A2800A28A2800A28A2800A28A2800A28A2800A28A2800A43CD2E6A379022E5B8A37137632F59D0F4CD6E144D56D5278A324AABB103F1C1E7E95512EF4DD12D3ECFA658C3042BD1228C469F9015A13092EF214F15C77885DECF721356A36DCC9CEFB0BAA78DEF6DF3E4B429EDB73FCEB9B6F8AFAC5ACB978ED6741D5594AFEA2B9AD4EEDD98F35CBDEC8C4934DA434D9EE3E1EF8B3A16AF325A5F16D32EDCE144E7F76C7D9FA7E75C97C5D93C53A7C46EA391E5D1E4FF0096D0E40873D9C761D81E873DB35E3B7ADB95BA7D2BD07E16FC48974CB98BC39AFC9F69D22E4F950BCDF37904F014E7AA1F43D2A4B3D47E0F581B3F87967330FDE5E3BDC9FA1385FD003F8D77F556D2DE0B4B68ADEDA258A08902468830AAA3A003D3D2AD549485A28A2800A28A2800A28A2800A28A2800A28A2800A28A280133C534B80093C629DD4579CF8CAC7C69668F71A15FCD7D6C397B5E1664FF00748C6F1EDD68427B1DADA6AF657F7D79676F2EF9ECCA89D7046D2C323F4ACFD53530246556E14915C2FC2596F4E8FE23D66FD2459A7B9C319410C5913073919E32055CB9BD2E4FCD5A4519CD9D1C1AD8451C8E2B8CF156ABF68B82734B3DD98D7EF5729AB5D9772734DB25233AF65DD9AC2B871C8357A79B20E4D645C12ED815172D2332E86F7E3A55431F0548E0F6AD378BD6AAC8983D2819F4EFC2AD7DFC45E06B4927937DD5A936D313D495E84FD548AEE6BC4BE005D36CD76D09F94345281EE43027F20BF957B6D4B2905145140C28A28A0028A28A0028A28A0028A28A0028A28A000D215F7A5A28032F5A8CFF00615F88C7CDE431000F419AF2017DBB043706BDC1D43A1561907820D7CF5AEC6FE1DF10DD69B704AA236E89B1C321FBA7F2E3EA0D54591245ABFBF1BB19ED5CDDFDD073D6A4BCBA471B964073DF358CF2177EB4D89682B317E299E4D4F1A8A9582AAE4D21999247B413D3EB504763757F23C5676B35C48AA5D96242C428E49E3A62ACCCFE649850E79030832C7271851DD8E781D4FD335EDFAEA68BF0E3E1A5CC7A74456EAF61F2236947EFA691C632DDFE5049C7418C7140EC73DF0060FDFEBD71DB6C099C7FBE7FA57B8579AFC14D19B4DF038BB740AD7F31997DD000AA7F1DA4FE35E9552C6828A28A0614514500145145001451450014514500145145001451450015E7DF13FC18FE26D205E58479D52D14F96A3832A7529F5CF23F1F5AF41A4233401F2058D86A178F2A5B59DC4EF0FF00AD58A32CC98E0E40E473C74A9C0685CA4A8C8E3AABAED3F91FEB5EE7E2DF87D35FEA5FF090F862ECE9BAE2F2C549549CE3BE38CFB9041F4EF5C16ABE3AF11E9979041E2BF0B69B757B6FFEA27BBB6DAC0FAAB0CA9FF80E29DC9B77391538EB55EE673B4F5C115A3AFF008B755F14DD4736A33A08E2C98ADE14D91A67A9F73EE49AC776CA1C914C96765E05F184BA5E9D3D85A68BA699E326E64BEBB94F247DD3B71924678C11593A70D6FE2BF8E618350B979510E66741B52DE1079D83B6781EA73C935C5497A239E48D243B5BEFED3D87AFB5743F0F7C4B1F857C73697B753BC562EAF1DCB2E486420E381D70429FCA868699F57DADBC5696D15B408B1C312048D17A2803007E9562B96F0A78EFC3FE30130D2AECBCB072F14A851C2E786C1EA3DEBA9073525A168A28A0028A28A0028A28A0028A28A0028A28A0028A28A0028A28A0028A28A0069E9CD792FC6FF10AD8F87ED74582545BABF93738C02CB1AFA64704B6067D8D7A9DD5C2DBC259B19CE003DCD7CCFA95C8F1C7C70B08FCE77864BA48F0790B1C7F31007A100FE74D225BE87211CB3AFDE556FA714F92569176F96467DEB6752D38D96AD796CE815A299D718EDB8E3F4A804031D2A89397B9B421CC983C765EA6BA6BC934CB4F09D9594304736A1227993CCC39048F9554F6007E64531E24504BAF1EA05567B588FCC49C7B7423B50328E9FAC5E7876FA2D434EB9782F9788D978E0F5CFB7B77E2BE9EF863E3393C67E16FB5DD2A26A16F2186E420C027008603B6411C7A835F2CBDB99AE1E46FB99C007DABE97F833E1E4D1BC109785834FA93FDA1CA9C80A3851F90CFE349A1A67A45145152505145140051451400514514005145140051451400514514005068A43D280384F88FAD1D27C3B7D70AFB596230C7FEF3704FE033F9D78D7C10B07D47E268BD6048B3B5966627D5B083FF004327F0AEB3E2F5D5C5DC62D51599036F603D7FFD55B1F02BC3A34DF0D5C6BB29066D4DF11E3B448481F9B6EFD2ADEC671DEE735E36B059EEA7BB840F39246DC07F10CD711E6F1D6BB5D6EF835C5C1CF576CFBF27FC6B84BD6549894FBADFA50C445792EE4F6FA66BD5FC1DE00D3FC57F09ADC48AB6FA84934CF0DD851B94EF6001C755E3A578F5C3E5083E95F4B7C24DADF0C747C6305643FF00911A93291E1DAA7C32F1868F3BA3E9135DC609C4D6789030F5C0E47E38AF78F869A3DE685E05B0B1D406CB9F9E46889E630EC582FE00D75BB01EBCFD40A7018A572921D451452185145140051451400514514005145140051451400514514005238CA914B41A00F25BF9EDF5696E609954CD16720F7A93E1CEAD67A6E85ACE9525E46B7105C4B3C303B80DE5B229F941FF006B77E75C8F896F5B43F1E48240444D7261909E06D63D7F506B93F144517F6DDADCA30F3E2B84CB0EB82403F85599EC4BACDE157605B93D6B99925691B24F15B7E2288ACA1B9C74A67863C31A9F8BE5BF874936E65B28848C923905F39C05E3AF14D891872E592BE83F815A9A5DF8164B12D992C6E9D08F456F9C7EA5BF2AF9E8316046D21870C31D0F715EA3F01354FB2F8A752D2DDB0B796DE6A027AB237FF12C7F2A96544FA1A8A4CE696A4B0A28A2800A28A2800A28A2800A28A2800A28A2800A28A2800A28A2800A46E94B41A00F1EF8D1E1D12D826B31C67CB51E55D3019DA07DC73FCBF2AF13BF9655F20A4F14C59D773AB77CD7D8F2451CD1B472A2BA30C15619047B8AC47F04785E47DEFE1FD30B6739FB2A673F9534C968F06F14D88F2D594727A56A7C067F27C67ABDB9E3CDB00E0671F76403FF0066A7F8A154168978DAC56B23E1EEA4BE1FF893A74927CB0DD96B263E9BF1B7FF001E0B54F6263A30D4BC2E9078E7C456FB3114576EE831DA4C38FD1AA9784A71E1EF88BA4DD676442E444E4F657F94FF003AF50F1ADB25B78B6EA72003756F13E7D71B97FA0AF2BD660DD33C89C1EA08EC68E83EA7D543AD158DE14D54EB9E15D37526FF00593C0A64FF007C70DFA835B55058514514005145140051451400514514005145140051451400514514005145140051451401E03E3050BE21D4540C05B87C0F4F9ABCF75A91E1B88DE362AC8C8EA47660E3068A2AFA19F53D9BE29FCBAA696EBC33DBBEE3EB8C579B4FCC0D45142067B2FC2291DFC051066242CF305F61BC9FEA6BBDED45152CB5B0B451452185145140051451400514514005145140051451401FFFD9
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, 'd:\20090112115849813.bmp', 2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken

and it is sucessfuly excuted and i mage was generated.

But i am getting one most problem...

when i use cursor loop for multiple images i am getting conversatrion error of varbinary to varchar..
like ..

DECLARE @SQLIMG VARCHAR(MAX)
DECLARE @IMG_PATH varbinary(MAX)
DECLARE @TIMESTAMP VARCHAR(MAX)
DECLARE IMGPATH CURSOR FAST_FORWARD FOR
select csl_CompanyLogo from mlm_CSCompanySettingsLocalizations
OPEN IMGPATH
FETCH NEXT FROM IMGPATH INTO @IMG_PATH
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TIMESTAMP = replace(replace(replace(replace(convert(varchar,getdate(),121),'-',''),':',''),'.',''),' ','')
SET @SQLIMG = '
DECLARE @ObjectToken INT
EXEC sp_OACreate ''ADODB.Stream'', @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, ''Type'', 1
EXEC sp_OAMethod @ObjectToken, ''Open''
EXEC sp_OAMethod @ObjectToken, ''Write'', NULL, '+@IMG_PATH+'
EXEC sp_OAMethod @ObjectToken, ''SaveToFile'', NULL, ''d:\'+@TIMESTAMP+'.bmp'', 2
EXEC sp_OAMethod @ObjectToken, ''Close''
EXEC sp_OADestroy @ObjectToken
'
PRINT (@SQLIMG)
EXEC(@SQLIMG)
FETCH NEXT FROM IMGPATH INTO @IMG_PATH
END
CLOSE IMGPATH
DEALLOCATE IMGPATH

Pls try with table with contain images..and pls help me to find the solution..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-12 : 03:21:47

Something like this?

DECLARE @SQLIMG VARCHAR(MAX),
	@IMG_PATH VARBINARY(MAX),
	@TIMESTAMP VARCHAR(MAX),
	@ObjectToken INT

DECLARE IMGPATH CURSOR FAST_FORWARD FOR 
		SELECT csl_CompanyLogo from mlm_CSCompanySettingsLocalizations
		
OPEN IMGPATH 

FETCH NEXT FROM IMGPATH INTO @IMG_PATH 

WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @TIMESTAMP = 'd:\' + replace(replace(replace(replace(convert(varchar,getdate(),121),'-',''),':',''),'.',''),' ','') + '.bmp'

		PRINT @TIMESTAMP
		PRINT @SQLIMG

		EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
		EXEC sp_OASetProperty @ObjectToken, 'Type', 1
		EXEC sp_OAMethod @ObjectToken, 'Open'
		EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH
		EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @TIMESTAMP, 2
		EXEC sp_OAMethod @ObjectToken, 'Close'
		EXEC sp_OADestroy @ObjectToken

		FETCH NEXT FROM IMGPATH INTO @IMG_PATH 
	END 

CLOSE IMGPATH
DEALLOCATE IMGPATH

E 12°55'05.63"
N 56°04'39.26"

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2009-02-10 : 16:50:40

I completely missed this but Peso is right. The root cause is you attempted to do some string manipulation inline with a procedure call. Can't do that in T-SQL. Peso's solution is correct in that he's formatting the variable before the call and uses that variable in the call.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-10 : 17:14:03

Thank you.

E 12°55'05.63"
N 56°04'39.26"

jcuttitta
Starting Member

1 Post

Posted - 2009-02-23 : 14:22:30

Peso, Thanks so much for the sample code.

Ailey
Starting Member

1 Post

Posted - 2010-11-26 : 17:52:24

How could I modify Peso's code so the file name (of the image to be save) could come from a column in that same table.

so in this case say I had something like this

SELECT csl_CompanyLogo, DesiredFileName from mlm_CSCompanySettingsLocalizations

thank you for your assistance.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-26 : 18:07:38

Wow..resurrected this one.

You can concatenate the two fields together such that the @IMGPATH in his sample would contain the desired file name as well.

To modify your code..

Select csl_CompanyLogo + '/' + DesiredFileName from mim_CSCCompanySettingsLocalizations

If the you need to reference one column for the file itself, and another which would have a different name, you would declare another variable and populate the desired file name in the code . Presuming the desired filename would ALSO have the path, this is the idea..


DECLARE @SQLIMG VARCHAR(MAX),
	@IMG_PATH VARBINARY(MAX),
	@TIMESTAMP VARCHAR(MAX),
	@ObjectToken INT,
	
	@FILENAME varbinary(MAX)---new variable here

DECLARE IMGPATH CURSOR FAST_FORWARD FOR 
		SELECT csl_CompanyLogo 
		      ,DesiredFileName ---column selected here
		from mlm_CSCompanySettingsLocalizations
		
OPEN IMGPATH 

FETCH NEXT FROM IMGPATH INTO @IMG_PATH ,@FILENAME ---new column in cursor with new variable

WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @TIMESTAMP = 'd:\' + replace(replace(replace(replace(convert(varchar,getdate(),121),'-',''),':',''),'.',''),' ','') + '.bmp'

		PRINT @TIMESTAMP
		PRINT @SQLIMG

		EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
		EXEC sp_OASetProperty @ObjectToken, 'Type', 1
		EXEC sp_OAMethod @ObjectToken, 'Open'
		EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @FILENAME --new variable here
		EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @TIMESTAMP, 2
		EXEC sp_OAMethod @ObjectToken, 'Close'
		EXEC sp_OADestroy @ObjectToken

		FETCH NEXT FROM IMGPATH INTO @IMG_PATH ,@FILENAME --new variable here also
	END 

CLOSE IMGPATH
DEALLOCATE IMGPATH

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources