Solved

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

Posted on 2009-05-04
30
655 Views
Last Modified: 2012-05-06
hi all
i want extract a tiff image from a mssql server. the field IMAGEN is declared as a ntext that contain a TIFF image.

i want use BlobStream BlobField...

same as a gif image:

var
  Gif: TGifImage;
  BS : TStream;

begin
  Aq.Close;
  Aq.SQL.text:='select * from I_FIERROS where clave=''A500''';
  Aq.Open;
  if not Aq.IsEmpty then
  begin
    if not Aq.Fieldbyname('IMAGEN').isnull then
    begin
     Gif := TGifImage.create;
     bs := Aq.CreateBlobStream(Aq.FieldByName('Imagen'), bmRead);
     try
       bs.seek(0, soFromBeginning);
       bs.Seek(4,0);
       ShowMessage( inttostr(bs.size) );
       Gif.loadfromstream(bs);
       Gif.SaveToFile('c:\temp\xr.gif');
     finally
       bs.free;
       Gif.free;
     end;
    end;
  end;
end;


but remember, the image is a TIFF image.

Thanks

0
Comment
Question by:jamiguel
  • 15
  • 9
  • 5
30 Comments
 
LVL 21

Expert Comment

by:developmentguru
Comment Utility
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;

Open in new window

0
 

Author Comment

by:jamiguel
Comment Utility
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
0
 
LVL 21

Expert Comment

by:developmentguru
Comment Utility
 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.
0
 

Author Comment

by:jamiguel
Comment Utility
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
0
 
LVL 21

Expert Comment

by:developmentguru
Comment Utility
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.
0
 

Author Comment

by:jamiguel
Comment Utility
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.
0
 
LVL 21

Expert Comment

by:developmentguru
Comment Utility
I will check on it with TIF tomorrow.
0
 

Author Comment

by:jamiguel
Comment Utility
i added 100 more points to question, for you, if help you, thanks
0
 
LVL 21

Expert Comment

by:developmentguru
Comment Utility
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.
0
 

Author Comment

by:jamiguel
Comment Utility
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;

Open in new window

MSSQLDelphi2.jpg
MSSQLVFox2.jpg
fierro.jpg
0
 
LVL 21

Expert Comment

by:developmentguru
Comment Utility
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?
0
 

Author Comment

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

Thanks
0
 

Author Comment

by:jamiguel
Comment Utility
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!!!
0
 
LVL 21

Expert Comment

by:developmentguru
Comment Utility
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.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:jamiguel
Comment Utility
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.
0
 
LVL 21

Expert Comment

by:developmentguru
Comment Utility
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.
0
 

Author Comment

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

thanks
0
 
LVL 21

Accepted Solution

by:
developmentguru earned 200 total points
Comment Utility
 In post #24319236 I told you that, "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."

  The way it is stored in the field is NOT TIFF.  I did a basic extraction of the data and this is what was in there (first few bytes showing that it does not match TIFF format).

4D 00 4D 00 00 00 2A 00 00 00 00 00 00 00 08 00

  Your solution was to query the database in a way that caused it to dynamically change the field type.  This caused the database to undo it's field based formatting.  Hmmm... what could have led you to try that?

  I am done trying to debate this.  I didn't even try to say I deserved all of the points, just enough for having helped.  At this point I would settle for the ability to filter out any of your questions in the future if this is how you treat people who took the time to help you.

0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
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.
0
 

Author Comment

by:jamiguel
Comment Utility
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
0
 
LVL 36

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 100 total points
Comment Utility
if you get or don't get the answer here,
you still need to accept the right answer here

posting code is not allways required for solving a problem

you problem is the field type of the column in the database.
you should change it to a single byte blob, not a NTEXT (double byte)

ciuly was a member, yes, his account got disabled, ... maybe he'll tell you more

have you ever tried writing like this: ?
    with tfilestream.create('c:\temp\xcv.txt', fmcreate) do

    try

      Write(Query1.FieldByName('IMAGEN').AsString, Length(Query1.FieldByName('IMAGEN').AsString));

    finally

      free;

    end;

Open in new window

0
 

Author Comment

by:jamiguel
Comment Utility
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


0
 

Author Comment

by:jamiguel
Comment Utility
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
0
 

Author Comment

by:jamiguel
Comment Utility
i gave the points to developmentguru:, but I am not convinced.

Thanks
0
 

Author Closing Comment

by:jamiguel
Comment Utility
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...
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
maybe because you don't understand the solution ...
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
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 !
0
 

Author Comment

by:jamiguel
Comment Utility
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
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
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...


0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now