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 10 6884
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.
--
Jerry
On 28 Apr 2006 08:59:03 -0700, "Jerry" <je*******@gmail.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)
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,
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
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.
--
Jerry This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Bryan Russell |
last post by:
Hi,
My asp/sql server application gets an error, but only when you use the
Excel export option:
<% Response.ContentType="application/vnd.ms-excel" %> (very popular w/
users)
I think...
|
by: Bill |
last post by:
Hi all,
I have an asp page that writes to an ntext field in SQL Server 2000.
All was going well until I had to put in a section of text greater than 8000
bytes.
Then I got a timeout error and...
|
by: Matik |
last post by:
Hello to all,
Below the sample code:
declare @arg_szMsgText ntext
set @arg_szMsgText = isnull(@arg_szMsgText, N'unknown message')
Now the error message I get:
"The assignment operator...
|
by: Sileesh |
last post by:
HI
I know this is not the right forum to post this question, but i think some
one might have a suggestion.
I have a Table "Test" with columns
Id bigint (PK), Number Varchar(50), Notes ntext....
|
by: Igor |
last post by:
Is there a way to transfer ntext data from one table to another?
I tried this
UPDATE
SET = (SELECT FROM WHERE
=1)
WHERE = 1;
|
by: =?Utf-8?B?ZGF2aWQ=?= |
last post by:
I try to follow Steve's paper to build a database, and store a small text
file into SQL Server database and retrieve it later. Only difference between
my table and Steve's table is that I use NTEXT...
|
by: JHNielson |
last post by:
I have a somewhat simple question, but have been baffled by it for a while, and now I'm on a tight deadline - have to get it done within 24 hours.
I am trying to export a set of files to my hard...
|
by: verb13 |
last post by:
I am running this query to an sql server 2000 database from my asp
code:
"select * from MyTable where
MySqlServerRemoveStressFunction(MyNtextColumn) = '" &
MyAdoRemoveStressFunction(MyString) &...
|
by: felciano |
last post by:
Hi --
Is there a standard way to use the csv module to export data that
contains multi-line values to Excel? I can get it mostly working, but
Excel seems to have difficulty displaying the...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |