Hello!

I have a SQL Server database with gif images stored in a nText field as
binary. I've been asked to export these images to actual gif files
(about 250 of them). I found a stored procedure that was using some ADO
and it would write a small part of the image correctly and then the
rest was a mess. I was told to check out the ADO and VB groups to see
if anyone knows of a solution for this.

Does anyone have any resources or examples you could point me to? I've
never used ADO before and I've used little VB.

Thanks,

I would never store a gif in a database. Store the image in a folder
and record the location in the database.

I wouldn't store an image in a database either. The images are already
stored in the database and I'm the lucky one who inherited the issue.

¤ Hello!
¤
¤ I have a SQL Server database with gif images stored in a nText field as
¤ binary. I've been asked to export these images to actual gif files
¤ (about 250 of them). I found a stored procedure that was using some ADO
¤ and it would write a small part of the image correctly and then the
¤ rest was a mess. I was told to check out the ADO and VB groups to see
¤ if anyone knows of a solution for this.
¤
¤ Does anyone have any resources or examples you could point me to? I've
¤ never used ADO before and I've used little VB.
¤

See if the following helps. I'm assuming they are not stored as OLE Objects.

How To Read and Write BLOBs Using GetChunk and AppendChunk
http://support.microsoft.com/default.aspx/kb/194975


Paul
~~~~
Microsoft MVP (Visual Basic)

The ntext data type is used to store unicode text. If someone has stored
binary data in such a field you need to know exactly how it is stored in
order to retreive it correctly.

Jerry wrote:
Show quoteHide quote

I was able to see the upload code this morning.

<%
'this is the code block that breaks apart the image and turns it into
blob data
   If Request.TotalBytes <>0 Then
   BlobSize = Request.TotalBytes
   BlobData = Request.BinaryRead( BlobSize )
   bnCRLF = chrB( 13 ) & chrB( 10 )
   Divider = LEFTB( BlobData,  INSTRB( BlobData, bnCRLF ) - 1 )
   BlobDataStart = INSTRB( BlobData, bnCRLF & bnCRLF ) + 4
   BlobDataEnd = INSTRB( BlobDataStart + 1, BlobData, divider ) -
BlobDataStart
   strPicture= MIDB( BlobData, BlobDataStart, BlobDataEnd )
   Session("Picture")= strPicture
'this is the original record set
    set rsBLOB = Server.CreateObject("ADODB.Recordset")
    rsBLOB.ActiveConnection = CON_STRING
    rsBLOB.Source = "SELECT * FROM tblblob"
    rsBLOB.CursorType = 2
    rsBLOB.CursorLocation = 2
    rsBLOB.LockType = 3
    rsBLOB.Open()
    rsBLOB_numRows = 0
'add a new record
    rsBLOB.Addnew
    rsBLOB("Blob_photo").AppendChunk strPicture
       rsBLOB("Blob_photoYesNo") = "88"  'the 1 turns the camera image on
    rsBLOB("Blob_bsize") = BlobSize 'put the size into the db
    rsBLOB.update
    rsBLOB.close
    End If
%>

I found a solution.

CREATE PROCEDURE ntext2file @filename nvarchar(128), @table
nvarchar(128),@column nvarchar(128), @where nvarchar(4000)
--Saves text from an Ntext column to a file
AS
IF (@filename is NULL) OR (@table is NULL) OR (@column is NULL) OR
(@where is NULL)
    BEGIN
    PRINT 'saveNtext2file saves text from an Ntext column to a file'
    PRINT 'Usage:'
    PRINT 'EXEC saveNtext2file FileName, tableName, columnName,
WhereCondition'
    PRINT ''
    PRINT 'For example: EXEC ntext2file ''C:\test.txt'', ''customers'',
''memo'', ''where customerID=234'''
    RETURN
    END
DECLARE @hr int,@fso int,@i int, @j int, @blocks int, @c int,  @buffer
varbinary(1000), @sql nvarchar(4000)
EXEC @hr = sp_OACreate 'ADODB.Stream', @fso OUT
exec @hr = sp_oasetproperty @fso, 'Type', 1--adTypeBinary=1
EXEC @hr = sp_OAMethod @fso, 'Open'
set @sql = N'SELECT @c =(select DATALENGTH('+@column+')/2 from
'+@table+' '+@where+')'
exec sp_executesql @sql, N'@c int OUTPUT', @c OUTPUT
set @j=0
create table #t ( t ntext )
SET @i=@c
--read 1000 bytes at a time
WHILE @i > 500
    BEGIN
    insert into #t
    exec getREADTEXT @column,@table,@where,@j,500
    set @buffer=(select convert(varbinary(1000),convert(nvarchar(500),t))
from #t)
    EXEC @hr = sp_oasetproperty @fso, 'Write', @buffer
    delete #t
    SET @i=@i-500
    SET @j=@j+500
    END
--read remaining bytes
if @i > 0
    BEGIN
    insert into #t
    exec getREADTEXT @column,@table,@where,@j,@i
    set @buffer=(select convert(varbinary(1000),convert(nvarchar(500),t))
from #t)
    EXEC @hr = sp_oasetproperty @fso, 'Write', @buffer
    delete #t
    END
drop table #t
EXEC @hr = sp_oasetproperty @fso, 'SaveToFile', @filename
EXEC @hr = sp_OAMethod @fso, 'Close'
GO

Can I get the code for "getREADTEXT "

Thanks!

Sorry about that. Here it is!

CREATE PROCEDURE getREADTEXT @column nvarchar(128), @table
nvarchar(128),@where nvarchar(4000),@position int,@length int
--given a column, table and filter clause plus a position to start
reading the text and length to be read, the selected portion of the
text will be returned
AS
declare @sql nvarchar(4000)
set @sql='declare @txtPtr varbinary(16)
select @txtPtr = TEXTPTR('+@column+') from '+@table+' '+@where+
' READTEXT '+@table+'.'+@column+' @txtPtr '+str(@position)+'
'+str(@length)+''
exec(@sql)
GO

Thanks!
I have it looping through several thousand records and it stops spitting out
files after 256. Do you have any idea why?

Sorry, I have no idea. What I did was export the file ID and physically
code them into an array and then I looped through the array. I did this
because at the time the only thing I could run on my computer was .NET
and I didn't know how to connect to a database with .NET (ASP classic
wouldn't and still doens't run in my IIS for some reason). I exported
260-some images from the table using the array.

At a loss figuring out if an IP is on LAN or INET
DWORDS ? LONG? - I need to create constants for the following
Function call problem
How to Obsolete two related classes?
Calling PaintBox Paint event
Invalid Cast Errors
Can't open a project by iteself? Solution opens every time!
Invoking apps within VB
VS.NET 2005
Cast from string to System.DayOfWeek