Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 492
  • Last Modified:

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

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
bingohua
Asked:
bingohua
  • 3
  • 3
  • 2
1 Solution
 
kretzschmarCommented:
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
 
alx512Commented:
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
 
alx512Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
kretzschmarCommented:
hello,bingohua, are you here?
0
 
bingohuaAuthor Commented:
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
 
bingohuaAuthor Commented:
And how I can insert a .gif image into the blob field in oracle8i?
0
 
kretzschmarCommented:
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
 
bingohuaAuthor Commented:
Thank you for your instant help.I appreciate it.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now