June 5, 2003 at 9:14 am
Is this a good way to do an insert of a record that has a blob field representing a picture?
This is what I am trying to do:
Select [picturefield] from [tablename] and store the blob stream output in a text (if possible), then do an insert of another record with the previous stream.
can this be done by using ISQLW?
Any ideas?
June 5, 2003 at 11:47 pm
Hi Pedro Estrada,
quote:
Is this a good way to do an insert of a record that has a blob field representing a picture?
sure there is!
quote:
This is what I am trying to do:Select [picturefield] from [tablename] and store the blob stream output in a text (if possible), then do an insert of another record with the previous stream.
can this be done by using ISQLW?
I'm not familiar with ISQLW. Most people would go for the ADO Stream Object. I think BCP can also handle binary data when setting the correct switches and params.
Do I understand you right you want to store binary data in a text(field)?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 6, 2003 at 2:26 am
Hi Pedro,
check on textcopy.exe
It's a tool located on the mssql\bin directory. You can use it to insert any file content into text/image/binary field.
The record you want to add the data to has to be created in advance. after that you can add the picture to it.
Here is an example of using it in a stored procedure (input and output)
CREATE PROCEDURE oprsdbo.sp_textcopy (
@srvname varchar (30),
@login varchar (30),
@password varchar (30) = null,
@dbname varchar (30),
@tbname varchar (30),
@colname varchar (30),
@filename varchar (500),
@whereclause varchar (40),
@direction char(1))
AS
begin
DECLARE @exec_str varchar (255)
SELECT @exec_str =
'textcopy /S ' + @srvname +
' /U ' + @login +
' /D ' + @dbname +
' /T ' + @tbname +
' /C ' + @colname +
' /W "' + @whereclause +
'" /F ' + @filename +
' /' + @direction +
case
when @password is not null then
' /P ' + @password
else
' /P '
end +
' /Z'
print @exec_str
EXEC master..xp_cmdshell @exec_str
end
GO
Regards,
Jurgen
June 6, 2003 at 9:02 pm
Thanks all for your replies.
Sorry if I did not explain myself correctly. This what I meant:
When you execute a statement in query analyzer to retrieve a blob/picture field, like select [picturefield] from [aTable] you get a result that is a stream of characters that represent the picture, something like this: 0x424D420400000000000042....etc (very large depending on the picture size). If I place a small picture (smaller than 8192 that is the max size that Query Analyzer can deliver) and I execute a select of the picture field, getting the result and executing an update statement on that same record like update [atable] set [picturefield] = 0x424D420400000000000042....etc, the picture is correctly written to the field. My question is how do I obtain the stream on a large blob. I have noticed that the field values start with 0x424D.
Any ideas? Thanks.
June 6, 2003 at 9:43 pm
Ok after some testing I think I have some understanding of the inner working under Query Analyzer:
By executing a query as I described, sql returns a large stream of numbers and letters. each pair of these represent a byte in the picture in hex.
To be able to write the picture back to the field by executing the statement indicated in my previous post I must make a change.
The first 2 chars are, at least in all my tests, 2 zeroes. By substituting the first 2 zeroes by a 0x, mssql takes the unquoted large string of hex consecutive numbers and writes the blob with no problem, for this I had to do by a small program since the max in QA is 64K.
If anyone is interested, I can post the small program source to demonstrate the conversion.
Thanks again.
June 8, 2003 at 9:33 pm
I would appreciate if you publish the small conversion.
Sounds interesting
Thank you
ps. we just have an issue with an image database and I would like to check this out
June 9, 2003 at 8:50 am
Here is the conversion, it is written in Pascal:
Please note that I have not been able to place a plus sign that can be seen in the preview, for some reason the plus sign dissapears; I have placed (Plus sign) instead.
function ConvertStringStreamToHex(aString: String): String;
var
x: Integer;
begin
Result := EmptyStr;
for x := 0 to length(aString) do
begin
Result := Result (Plus sign) IntToHex(Ord(aString[x]), 2);
end;
//Format the hext for sql
//Must change the 2 first chars by a "0x"
//for this to work
Result := '0x' (Plus sign) copy(Result, 3, length(Result));
end;
I have tryed this method and it works with a blob of aprox 83000 chars. May be someone has ideas of the correctness of this?
BTW ISQW cannot apply it since the blob hex representation is larger than 64K. If someone needs a tool for adding such a huge statement please let me know.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply