BorlandTalk.com

Bill · Guest

I have scoured the newsgroups and read several articles and tried
several things but all to no avail.

No matter what I do, it seems that an image is stored in the database
as a BMP and thus causes problems when I try to retrieve and display
the image.

D7, ADO, SQL Server 2000
All data is passed to and from the database via Stored Procedures.

As a test, I wrote the following two methods to see what exactly would
occur. I save the scanned image to an actual JPG file on disk. Then
I load this image directly into the parameter via a loadfromfile.
When I retrieve the data, if I read from the save disk file(s) all is
well. If I read the data from the database and save it to a file
directly, the file format is a BMP, not a JPG and therefore causes an
error when I try to load it into the TImage. I have tried to load it
directly into the TImage and that does not work either, thus the disk
file test to see exactly what is going on.

When I examine the file created by the SaveImageData method, the file
has the appropriate JPG file signature ($FF$D Cool

as the first two bytes
of the file. When I examine the file created in the
BrokenLoadImageData method, the file has a BMP file signature (BM).
Stored procedures are at the end of the message.

We will be saving thousands upon thousands of rows of data and we
really need to use as little space as possible, thus the need to save
the images as JPEG. Since the image is actually being saved as BMP we
are using 20x the storage space. We could save the images directly to
disk but that is not really a desired option.

Any assistance would be greatly appreciated.

Bill

// Save the scanned image to a JPG file and then use that file to
write to the data table
// ???Full.JPG = 93K in size and appears to be a valid JPG file
procedure TdlgPlayer.SaveImageData;
var
FilePath: string;
begin
FilePath := AddBackSlashL(g_TempFolder);
ADOStoredproc1.Parameters.ParamByName('@PlayID').Value :=
sPlayer.PlayID;

if assigned(imgFullIdImage.Picture.Graphic) then
begin

TJPEGImage(imgFullIdImage.Picture.Graphic).SaveToFile(format('%s%dfull.jpg',[filepath,
splayer.PlayID]));

ADOStoredProc1.Parameters.ParamByName('@playidFullImage').LoadFromFile(format('%s%dfull.jpg',[filepath,
splayer.PlayID]), ftBlob);
end;

if assigned(imgFaceIdImage.Picture.Graphic) then
begin

TJPEGImage(imgFaceIdImage.Picture.Graphic).SaveToFile(format('%s%dface.jpg',[filepath,
splayer.PlayID]));

ADOStoredProc1.Parameters.ParamByName('@playidFaceImage').LoadFromFile(format('%s%dface.jpg',[filepath,
splayer.PlayID]), ftBlob);
end;

ADOStoredProc1.ExecProc;
end;

procedure TdlgPlayer.WorkingLoadImageData;
var
Filepath : string;
begin
FilePath := AddBackSlashL(g_TempFolder);

if FileExists(format('%s%dfull.jpg',[filepath, splayer.PlayID]))
then

imgFullIdImage.Picture.LoadFromFile(format('%s%dfull.jpg',[filepath,
splayer.PlayID]))
else
imgFullIdImage.Picture.Assign(nil);

if FileExists(format('%s%dface.jpg',[filepath, splayer.PlayID]))
then

imgFaceIdImage.Picture.LoadFromFile(format('%s%dface.jpg',[filepath,
splayer.PlayID]))
else
imgFullIdImage.Picture.Assign(nil);
end;

// ???Full_a.JPG = 2,045K in size and appears to be a BMP file
procedure TdlgPlayer.BrokenLoadImageData;
var
Filepath : string;
begin
FilePath := AddBackSlashL(g_TempFolder);

ADOStoredproc2.Parameters.ParamByName('@PlayID').Value :=
sPlayer.PlayID;
ADOStoredProc2.Open;
if ADOStoredProc2.FieldByName('playidFullImage').value <> null then
begin

TBlobField(ADOStoredProc2.FieldByName('playidFullImage')).SaveToFile(format('%s%dfull_a.jpg',[filepath,
splayer.PlayID]));

imgFullIdImage.Picture.LoadFromFile(format('%s%dfull_a.jpg',[filepath,
splayer.PlayID]));
end
else
imgFullIdImage.Picture.Assign(nil);

if FileExists(format('%s%dface.jpg',[filepath, splayer.PlayID]))
then

imgFaceIdImage.Picture.LoadFromFile(format('%s%dface.jpg',[filepath,
splayer.PlayID]))
else
imgFullIdImage.Picture.Assign(nil);
end;

CREATE procedure sp_PutPlayerIDData
@PlayID int,
@playidFullImage image,
@playidFaceImage image
as

-- Determine if we need to insert or update the data....
if exists(Select * from PlayerID where playid = @playID)
begin
-- Update the players data
Update playerid
set playID = @playID,
playIDFullImage = @playidFullImage,
playIDFaceImage = @playidFaceImage
end
else
begin
-- Insert a new row of data
insert into
PlayerID (playID,
playIDFullImage,
playIDFaceImage)
values (@playID,
@playidFullImage,
@playidFaceImage);

end

Return @@Error;
GO

CREATE PROCEDURE sp_rds_GetPlayerID
@PlayID integer
AS
select * from
PlayerID
where
playid = @playid;

Return @@error;
GO

Del M · Guest

What datatype does the field have in the database. I use type "image" and it
holds any kind of file and spits it back out unchanged. ie.. gif in, gif
out

Mike B. · Guest

Bill,

Your stored procedures are correct for the most part. The stored
procedure that gets the data should use "READTEXT" as below:

======================================

SET NOCOUNT ON
DECLARE @ptr varbinary(16), @l int
SET TEXTSIZE 2147483647

SELECT
@ptr = TEXTPTR(Pic),
@l = DATALENGTH(Pic)
FROM
tblImage (NOLOCK)
WHERE
(RecID = @RecID)

READTEXT tblImage.Pic @ptr 0 @l

======================================

In Delphi use something like this:

======================================
Save image to SQL Server 2000 Database...

var sp : TADOCommand;
a : TMemoryStream;
b : TJPEGImage;
begin
sp:=TADOCommand.Create(nil);
...
Set up the command
...
a:=TMemoryStream.Create;
try
b:=TJPEGImage.Create;
try
b.Assign(imgMain.Picture.Graphic);
b.SaveToStream(a);
finally
FreeAndNil(b);
end;
a.Position:=0;

sp.Parameters.CreateParameter('@Pic',ftBlob,pdInput,a.Size,null).LoadFromStr
eam(a,ftBlob);
finally
FreeAndNil(a);
end;
...
sp.Execute;
...
end;

======================================
Load image from SQL Server 2000 Database...

var sp : TADOCommand;
rs : TADODataSet;
a : TMemoryStream;
b : TJPEGImage;
begin
rs:=TADODataSet.Create(nil);
sp:=TADOCommand.Create(nil);
...
Set up the command
...
rs.Recordset:=sp.Execute;
...
b:=TJPEGImage.Create;
try
a:=TMemoryStream.Create;
try
TBlobField(rs.FieldByName('Pic')).SaveToStream(a);
a.Position:=0;
b.LoadFromStream(a);
finally
FreeAndNil(a);
end;
imgMain.Picture.Assign(b);
finally
FreeAndNil(b);
end;
...
end;

HTH (Hope this helps),
Michael P. Bobowski
Milwaukee, WI, USA
[email]mbobo (AT) wi (DOT) rr.com[/email]