Solved

SOS,how can dynamically insert images(.jpg,.bmp,.gif)into a blob field in oracle ?

Posted on 2001-07-10
8
481 Views
Last Modified: 2013-11-19
hi,dear experts:
  Here I need your kind help very much.
  Suppose I have created a table,as follows:
  create table imagetest(id number(8) not null primary key,image blob);
  My questions are as follows:
  Now,I don't know how I can dynamically insert the images(.jpg,.bmp,.gif)into the image field,and if done,how I can
display them and how I can get the image's filename of the blobfield image.
  Have you got it?If so,please help me.Some detailed code with delphi is the best.Thank you very much.
0
Comment
Question by:bingohua
  • 3
  • 3
  • 2
8 Comments
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
a sample for jpeg only,
for other formats, you've to include at least the suffix of the filename, where you can then decide which conversion is needed for display, for the filename you must also include a field in your table, which holds this name

unit db_pict_u;

interface

uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
Db, DBTables, ExtCtrls, DBCtrls, StdCtrls, Mask, Menus, jpeg; // JPeg unit is used

type
TForm1 = class(TForm)
  DBEdit1: TDBEdit;   // A other DBField
  DBNavigator1: TDBNavigator;
  Table1: TTable;
  DataSource1: TDataSource;
  PopupMenu1: TPopupMenu;     // a Popup linked to Image1
  LoadPicture1: TMenuItem;    // the MenuItem of the Popup
  OpenDialog1: TOpenDialog;   // OpenDialog
  Image1: TImage;             // Visual Output for the JPeg
  procedure FormCreate(Sender: TObject);
  procedure FormDestroy(Sender: TObject);
  procedure LoadJPEG1Click(Sender: TObject);      // File Load and Store in DB
  procedure Table1AfterScroll(DataSet: TDataSet); // Get JPeg and Display in Image1
private
  { Private-Deklarationen }
public
  { Public-Deklarationen }
end;

var
Form1: TForm1;
JPegImage : TJpegImage;  //A temporary JPeg-Object

implementation

{$R *.DFM}

procedure TForm1.FormCreate(Sender: TObject);
begin
JpegImage := TJPegImage.Create; // Create JPeg-Object
Table1.Open;
end;

procedure TForm1.FormDestroy(Sender: TObject);
begin
Table1.Close;
JPegImage.Free;  // Free JPeg-Object
end;

procedure TForm1.LoadJPEG1Click(Sender: TObject);
Var
MyBlobStream : TBlobStream;      // Streams
MyJPegStream : TMemoryStream;
begin
if Table1.State in [dsEdit,dsInsert] then    // Do only if in edit or insert mode
begin
  If opendialog1.Execute then    // If file selected
  begin
    if Table1.FieldByName('Bild2').IsBlob then   // this line can deleted
    begin
      MyJPegStream := TMemoryStream.Create;      // Handle JPegFile
      JPegImage.LoadFromFile(opendialog1.filename);
      JPegImage.SaveToStream(MyJPegStream);
      // Prepare Blob
      MyBlobStream := TBlobStream.Create(TBlobField(Table1.FieldByName('Bild2')),bmReadWrite);
      MyBlobStream.CopyFrom(MyJPegStream,0);  // Copy JPeg into BlobField
      Image1.Picture.Assign(JPegImage);  // Display
      MyJPegStream.Free;  //Free all
      MyBlobStream.Free;
    end;
  end;
end else ShowMessage('Table is not in Edit Mode!');
end;

procedure TForm1.Table1AfterScroll(DataSet: TDataSet);
Var
MyBlobStream : TBlobStream;
begin
// Do Only if a JPeg available
if (Table1.FieldByName('Bild2').IsBlob) and (not(Table1.FieldByName('Bild2').IsNull)) then
begin
  // Prepare Streams
  MyBlobStream := TBlobStream.Create(TBlobField(Table1.FieldByName('Bild2')),bmRead);
  JPegImage.LoadFromStream(MyBlobStream);
  Image1.Picture.Assign(JPegImage);  //Display JPeg
  MyBlobStream.Free;  // Free Stream
end
else image1.Picture := Nil;  // No JPeg saved disable display
end;

end.

meikl ;-)))

0
 
LVL 1

Expert Comment

by:alx512
Comment Utility
First create this Oracle package:

create or replace package root.utl as
  blob1 blob;
  blob2 blob;
  blob3 blob;
  function blob_is_null(b in blob) return number;
  PRAGMA RESTRICT_REFERENCES(blob_is_null, WNDS, RNDS, WNPS);
end;
/

create or replace package body root.utl as
  function blob_is_null(b in blob)
    return number is
  begin
    if b is null then
      return 1;
    else
      return 0;
    end if;
  end;
end;
/

grant all on root.utl to public;
create public synonym utl for root.utl;

then use this unit to read and write blobs:

unit blobs;

interface

uses SysUtils, Windows, Classes, dbcore, oci73;

function BlobIsNull(TableName, KeyField, BlobField: String; KeyValue: Double): Boolean;
function LoadBlob(TableName, KeyField, BlobField: String; KeyValue: Double): String;
procedure ClearBlob(TableName, KeyField, BlobField: String; KeyValue: Double);
procedure SaveBlob(Text: String; TableName, KeyField, BlobField: String; KeyValue: Double);

implementation

function BlobIsNull(TableName, KeyField, BlobField: String; KeyValue: Double): Boolean;
var
  FQuery: TNativeQuery;
  IsEmpty: Integer;
begin
  FQuery := Connection.GetQuery;
  try
    FQuery.ParseSQL('select utl.blob_is_null('+BlobField+')'+
    '  from '+TableName+' where '+KeyField+' = '+FloatToStr(KeyValue), nil);
    FQuery.DefInteger(1, IsEmpty);
    FQuery.Execute;
    Result := not FQuery.Fetch or (IsEmpty = 1);
    FQuery.Close;
  finally
    FQuery.Free;
  end;
end;

function LoadBlob(TableName, KeyField, BlobField: String; KeyValue: Double): String;
var
  Stream: TStream;
  FQuery: TNativeQuery;
  Buffer: array [0..4095] of Byte;
  amt, pos: Integer;
begin
  Result := '';
  if BlobIsNull(TableName, KeyField, BlobField, KeyValue) then
    Exit;
   
  FQuery := Connection.GetQuery;
  try
    FQuery.ParseSQL('begin select '+BlobField+' into utl.blob1'+
    '  from '+TableName+' where '+KeyField+' = '+FloatToStr(KeyValue)+'; end;', nil);
    FQuery.Execute;

    Stream := TMemoryStream.Create;
    try
      FQuery.ParseSQL('begin dbms_lob.read(utl.blob1, :amt, :pos, :buffer); end;');
      FQuery.BindInteger('amt', amt);
      FQuery.BindInteger('pos', pos);
      FQuery.BindRaw('buffer', Buffer, sizeof(Buffer));
      pos := 1;
      Stream.Seek(0, 0);
      FQuery.ErrorMode := ociReturnStatus;
      while true do
        begin
          amt := sizeof(Buffer);
          FQuery.Execute;
          if FQuery.Status <> 0 then
            if FQuery.Status = NO_DATA_FOUND then
              break
            else Connection.RaiseOracleException(FQuery.Status);
          Stream.Write(Buffer, amt);
          pos := pos + amt;
        end;
      Stream.Seek(0, 0);
      FQuery.Close;

      SetString(Result, nil, Stream.Size);
      Stream.Read(Pointer(Result)^, Stream.Size);

      Connection.ExecSQL('begin utl.blob1 := null; end;');
    finally
      Stream.Free;
    end;
  finally
    FQuery.Free;
  end;
end;


procedure SaveBlob(Text: String; TableName, KeyField, BlobField: String; KeyValue: Double);
var
  Query: TNativeQuery;
  Stream: TMemoryStream;
  Buffer: array [0..1024-1] of Byte;
  amt, pos, Size, l: Integer;
  p: PChar;
begin
  Query := Connection.GetQuery;
  try
    // Init blob field
    Query.ParseSQL('begin update '+TableName+' set '+BlobField+'= EMPTY_BLOB()'+
    ' where '+KeyField+' = '+FloatToStr(KeyValue)+
    ' returning '+BlobField+' into utl.blob1; end;', nil);
    Query.Execute;
    Query.Close;

    Stream := TMemoryStream.Create;
    try
      l := length(Text);
      p := PChar(Text);
      Stream.SetSize(l);
      Stream.Write(p^, l);
      Stream.Seek(0, 0);

      Query.ParseSQL('begin dbms_lob.write(utl.blob1, :amt, :pos, :buffer); end;', nil);
      Query.BindInteger('amt', amt);
      Query.BindInteger('pos', pos);
      Query.BindRaw('buffer', Buffer, sizeof(Buffer));
      pos := 1;
      Query.ErrorMode := ociRaiseException;
      Size := Stream.Size;
      while (pos - 1) < Size do
        begin
          amt := Stream.Read(Buffer, sizeof(Buffer));
          Query.Execute;
          pos := pos + amt;
        end;
      Query.Close;
    finally
      Stream.Free;
    end;
  finally
    Query.Free;
  end;
end;

procedure ClearBlob(TableName, KeyField, BlobField: String; KeyValue: Double);
var
  FQuery: TNativeQuery;
begin
  FQuery := Connection.GetQuery;
  try
    FQuery.ParseSQL('update '+TableName+' set '+BlobField+' = null '+
    ' where '+KeyField+' = '+FloatToStr(KeyValue), nil);
    FQuery.Execute;
    FQuery.Close;
  finally
    FQuery.Free;
  end;
end;

end.

0
 
LVL 1

Expert Comment

by:alx512
Comment Utility
Sorry. I forget what you don't have a TNativeQuery.
This is our own libriry for native access to Oracle.

So, you can't use this code :)
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
hello,bingohua, are you here?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:bingohua
Comment Utility
hi,kretzschmar ,thank you for you kind help very much.
Here I also met with some questions in your code:
If I run this program again,the line code as follows always show error(if I delete from this table,that is to say,if this table is empty,the error won't show.why?

JPegImage.LoadFromStream(MyBlobStream);

Plus:There doesn't exist a field which holds the image's name,then how I can cope with different images(.bmp,.gif etc.)?
 Hope to hear from you.By the way,reward is no problem.:))
0
 

Author Comment

by:bingohua
Comment Utility
And how I can insert a .gif image into the blob field in oracle8i?
0
 
LVL 27

Accepted Solution

by:
kretzschmar earned 100 total points
Comment Utility
well,
first, what exact error do you get?
second,
for gif you could use gifimage from www.melander.dk
or
for multiple format the graphicex library from www.lischke-online.de (somewhere i have a sample how to use this library, will post it later, when i've found it)

third, to determine which kind of image the blob holds you need an additional field for holding the suffix like
create table imagetest(id number(8) not null primary key,type VARCAHR2(4), image blob);

except the graphicex-library tries to get the correct type by examining the image-header data
 
meikl ;-)
0
 

Author Comment

by:bingohua
Comment Utility
Thank you for your instant help.I appreciate it.
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

Suggested Solutions

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…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
In this Micro Tutorial viewers will learn how to remove an unwanted object using Photoshop’s feature known as content-aware fill.
In addition to being a great web-based presentation tool, Prezi also makes it easy to save your presentation as a PDF to share with others as well. Learn how in this tutorial. Select the share icon from the top menu in your Prezi editor: Select "D…

772 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

10 Experts available now in Live!

Get 1:1 Help Now