How to Extract/Retreive a Tiff image from a MSSQL server?

You can test to see if the field is a TBlobField, typecast as TBlobField, and use the method SaveToFile.

var
  Field : TField;
  BlobField : TBlobField;
 
begin
  Aq.Close;
  Aq.SQL.text:='select * from I_FIERROS where clave=''A500''';
  Aq.Open;
  if not Aq.IsEmpty then
  begin
    Field := Aq.FieldByName('IAMGEN');
    if notField.isnull and (Field is TBlobField) then
    begin
       BlobField := TBlobField(Field);
       BlobField.SaveToFile('c:\temp\xr.gif');
    end;
  end;
end;

Select allOpen in new window

i execute the code, without any error, but open the file: c:\temp\xr.gif and is only a red X (its empty)

remember tath the header of the file in the db is: 4D4D it is a Tif image.

Thanks

 You may need to do the opposite to make sure the data in the database is correct.  Load the data into the record first using TBlobField.LoadFromFile.  This will store any file from the first byte to the last.  I know the first few bytes of the file identify it as TIFF, but those bytes need to be there for graphics software to recognize it as TIFF too.  I looked up the specification for TIFF files

http://partners.adobe.com/public/developer/en/tiff/TIFF6.pdf

and found out that the first 4 bytes of the file can be a wide range of values.

  You can use a TBlobStream to see what the size of the data is in the record you are opening too.  This may be another indication that the data in the database records is not what it is supposed to be.

Let me know how that testing goes and we can go from there.

p.s. I am using this type of blob based load and store for excel files, word files, pdf files, as well as many types of image files all in one table and others in separate tables on SQL Server.

ok I understand if you don´t trust what I´m telling you, please follow me:

download a software named SQLLobEditor

here the info:

Server: 201.100.9.214
Databas: tb
Authentication: SQL Server Authentication
User Name: sa
Password: ganado

hit button connect
click on Query Button on the tool bar

type these query:

Select * from i_fierros where clave='A500'

press F8

return 1 row. go to end of the record look the field: IMAGEN

click on the button ...
and check please the Tab: Image and Hex.

you see the image?

with delphi how to extract? i use Adoconnection.

Thanks

Did you use this software(SqlLobEditor)  to put the image into the field?  I was able to extract the image and it was not recognizable as a GIF to GIMP (free image manipulation software).  It is possible that the program puts wrapper information around what it places in the field so that it can, later, recognize the type of file that the field contains.  I would suggest that you use your own programming to fill in the field and use your own programming to pull it back out.  This will avoid this type of issue.  Another suggestion I might make is to make the field IMAGE type instead of NTEXT because the software may change how it reads or writes the data based on the field type.

So, I need to know how the image was put into that record.  Then I can do some more testing.

hi thanks for the reply

i paste a Code for extract a GIF image but at the end of mi first post i tell you that is a TIF image not a GIf.
Next i cant change the data type because it is a comercial app.
i not use Sqllobeditor for save images, only for check that exist a image.

thanks.

I will check on it with TIF tomorrow.

i added 100 more points to question, for you, if help you, thanks

The data in the field is corrupted.  It does not follow the rules for a TIFF file.  From what I can see on a quick visual scan of the hex dump of the file every other byte in the file is a 0.  The file is supposed to start with a 4949 hex or a 4D4D hex.  This information starts with 4D004D00 hex.  As near as I have been able to tell all the information in this file is taking twice as much space as it should to write out (according to the TIFF specification I linked earlier).  I tried removing every other character since MOST of the added characters are zeros.  I did get the GIMP program to load the image with errors at that point, but it had several errors.

Image resolution is out of bounds
corrupted LZW table at scanline 0
currupted LZW table at scanline 1
Too many error messages

How did the image get into the database?  Can you post the image so I can put it in the database?  However it is being done now, standard graphics software is not able to read it.

strange Vfox can read/show the image here the vfox code:

SQLEXEC(cs, "SELECT * from I_fierros where clave=?xCvefierro", 'Vfierros')
SELECT vfierros
IF NOT EOF()
   SCATTER memvar
   ThisForm.Image2.Pictureval=Vfierros.imagen
ELSE
   N=MEssagebox("CLAVE DE FIERRO NO REGISTRADA",0+48,"AVISO")
   RETU .F.
ENDIF

and why SQLLobEditor read the file?

added the tif image, extracted from sqllobeditor(i havent access to image files that are on the Sql Server), have extension jpg rename it to tif (here not allowed tif files )

i pasted some code that try get the tif image, but only get 30 bytes, not know why...

Thanks

procedure TForm1.Button2Click(Sender: TObject);
var
  v:variant;
  i:integer;
  b:byte;
begin
  Query1.SQL.Text:='SELECT cast(cast(imagen as nvarchar) as varbinary) as IMAGEN FROM I_FIERROS where clave=''A500''';
  Query1.Open;
  try
    Query1.First;
    assert(not Query1.eof);
    v:=Query1.FieldByName('IMAGEN').AsVariant;
    with tfilestream.create('c:\temp\xcv.txt', fmcreate) do
    try
      for i:=VarArrayLowBound(v, 1) to VarArrayHighBound(v, 1) do
//      for i:=VarArrayLowBound(v, 1) to 1024 do
      begin
        b:=VarArrayGet(v, [i]);
        Write(b, 1);
      end;
    finally
      free;
    end;
  finally
    Query1.Close;
  end;
end;

Select allOpen in new window

MSSQLDelphi2.jpg
MSSQLVFox2.jpg
fierro.jpg

The code you posted assumes the field is a variant array of variant array of byte.  This is not the case, thats why it stops early (you are attempting to read the value as something it is not).  I tried downloading a command line utility called ImageMagick to see if it would identify the file type for me.  This is the result.

C:\Program Files\ImageMagick>identify c:\xr.tif
identify: Not a TIFF file, bad magic number 77 (0x4d). `c:\xr.tif' @ tiff.c/TIFF
Errors/323.

Which is what I told you earlier... not a TIFF file.  I figured that this software had a utility called identify.exe that is supposed to compare it against over 100 formats and identify what the format is.  It failed.  The first thing I will need to know is what the format of the graphics in the file is... what is it's normal extension?  The other software you are running already knows to assume this file type and is therefore able to display it.

  Can you tell me more about the images you are storing?  What is the width and height?  What is the color depth?  Are they all black and white?

havent these info... because the commercial app only show the images, its part of the service understand? we no save images only query.

Thanks

done!

  Query1.sql.Text:='select cast (IMAGEN as text)  from I_Fierros where clave=''A500''';
  Query1.Open;
  Query1COLUMN1.SaveTofile('c:\temp\xf.tif');
  Messagedlg('Listo!!!',mtwarning,[mbok],0);


DoneSolveD!!!

I believe that my taking the time to analyze the raw data in the database column led to his final solution.  Having done the work to help get him there I do feel I am due some points on this one.

mmm checked the posts, and not see any portion of code, i too invert time in others question and in any cases not get points, the rule is: Solved the problem then take the points.. if not solved, then how gave you the points?

i am found the solution by other way.

ID: 24326472 and ID: 24329122 identify the problem that the image in the database does not match the image of the TIFF file.  This information led to the idea that it needed to be represented differently.

ID: 24329122 explains why his solution was producing 30 bytes instead of the whole set of data in the field.

I would ask that his soluton be the accepted answer with half the points going to the ID's above as assisted.

developmentguru, the image is a tif image, you can test the code..... have the info....for connect...
the other answer not helped much...

thanks

the question has been answered
the solution (or more solutions) should be accepted

#24326472 is the correct assumption that the image was stored incorrectly in the database.
the solution should be to convert to a binary format, single byte, or written correctly, but this is impossible for NText.
so the solution #24319236 should also be accepted.

Geert Gruwez, i havent problem in accept the answer, but  i gave him all the info necesary for try help, no posted any code, the image stored in the db is a Tif image, possibly is incorrect saved in a ntext but it is a comercial app, i cant change anything in the db only read....

really a friend on IRC, Undernet helped me. named ciuly or Chiu_lee him too are member here.
of course the answers of developmentguru helped but finally, i  get the answer on IRC.

i am awaiting the answer for accept or not.

Thanks

Geert_Gruwez:
yes i tell me about the account disabled not know why, can we help him for again enable the account?

i tested some ways, and i solved the problem,

http://www.delphipages.com/threads/thread.cfm?ID=203096&G=203080

http://www.delphipages.com/threads/thread.cfm?ID=206838&G=206838

  Query1.sql.Text:='select cast (IMAGEN as text)  from I_Fierros where clave=''A500''';
  Query1.Open;
  Query1COLUMN1.SaveTofile('c:\temp\xf.tif');
  Messagedlg('Listo!!!',mtwarning,[mbok],0);

i dont accept the answer, because i have the answer and not by: Geert_Gruwez: or developmentguru: if we want the points and want help.

help me solve the problem via ADO (my solution work only in BDE, By Moment) i want work on ADO.

what do you think?

thanks

forget commend: the field NTEXT is in the database, and isnt mine, i am not designed, the Database, and cant change the field type, thanks

i gave the points to developmentguru:, but I am not convinced.

Thanks

i get the solution by other way, the ppl helped me but finally i get the solution in Undernet #delphi channel ciuly. not convinced gave the points. but is a requisite...

maybe because you don't understand the solution ...

NText is for storing UNICODE characters and not for binary files like images.
Ask the owner of the DB to add a field of type BINARY (small images up to 8000bytes) or VARBINARY or IMAGE
If you can't change the table you can add one of your own
like
CREATE TABLE dbo.IMAGES (
  ID INTEGER NOT NULL PRIMARY KEY NONCLUSTERED,
  PICTURE IMAGE);

Next to that, the images you store in the database are twice the size in the database !

And giving a C grade for getting help ?
Please change the grade.
When you don't like the solution, it is not necessarily a wrong solution.

If that column was intended for images then the database design is flawed !
So the solution is to change the database design !

i tell him for change and not want, him say: in my program, Visual Fox work OK, then why change?

i build a external app, for other things, and i cant change anything,

and the grade, not know how to change, and, not like me the solution that posted you and others, helped yes, but not liked.

Thanks

just an idea ...

select length(imagen)/1024/1024 [sizeinMB] from I_FIERROS where clave=''A500''

and compare the size to the actual file size of your images.
the size in the db should be the size of the image x2

so if you have 400Mb of files you have 800Mb in your database...