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,

--
Jerry

[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.

--
Jerry

[Paul Clement]

On 28 Apr 2006 08:59:03 -0700, "Jerry" <je*******@gmai l.com> wrote:

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

[Göran 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 wrote:

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,

[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.TotalBy tes <>0 Then
BlobSize = Request.TotalBy tes
BlobData = Request.BinaryR ead( 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("Pictur e")= strPicture
'this is the original record set
set rsBLOB = Server.CreateOb ject("ADODB.Rec ordset")
rsBLOB.ActiveCo nnection = CON_STRING
rsBLOB.Source = "SELECT * FROM tblblob"
rsBLOB.CursorTy pe = 2
rsBLOB.CursorLo cation = 2
rsBLOB.LockType = 3
rsBLOB.Open()
rsBLOB_numRows = 0
'add a new record
rsBLOB.Addnew
rsBLOB("Blob_ph oto").AppendChu nk strPicture
rsBLOB("Blob_ph otoYesNo") = "88" 'the 1 turns the camera image on
rsBLOB("Blob_bs ize") = BlobSize 'put the size into the db
rsBLOB.update
rsBLOB.close
End If
%>

--
Jerry

[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_oasetpropert y @fso, 'Type', 1--adTypeBinary=1
EXEC @hr = sp_OAMethod @fso, 'Open'
set @sql = N'SELECT @c =(select DATALENGTH('+@c olumn+')/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(varbina ry(1000),conver t(nvarchar(500) ,t))
from #t)
EXEC @hr = sp_oasetpropert y @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(varbina ry(1000),conver t(nvarchar(500) ,t))
from #t)
EXEC @hr = sp_oasetpropert y @fso, 'Write', @buffer
delete #t
END
drop table #t
EXEC @hr = sp_oasetpropert y @fso, 'SaveToFile', @filename
EXEC @hr = sp_OAMethod @fso, 'Close'
GO

[Brian]

Can I get the code for "getREADTEX T "

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('+@colu mn+') from '+@table+' '+@where+
' READTEXT '+@table+'.'+@c olumn+' @txtPtr '+str(@position )+'
'+str(@length)+ ''
exec(@sql)
GO

[Brian]

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