Export nText as Files

[Jerry]

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,

 

[Tim]

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

 

[Jerry]

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.

 

[Paul Clement]

¤ 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)

 

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

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]

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
%>

 

[Jerry]

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

 

[Guest]

Can I get the code for "getREADTEXT "

Thanks!

 

[Jerry]

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

 

[Guest]

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

 

[Jerry]

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.