insert blob file with oracledataset

[mugoory]

Hi there
I have some problem.
I'm using DOA version 3.4.6.1, delpfi 6 and oracle 9i
I am in the process of converting a fairly complex BDE-based application to DOA, running against an Oracle 9i database.

Original code(tquery version) is here... it work well...

with TQuery_temp do
begin
Close;
SQL.Clear;
SQL.Add('INSERT INTO table (SIMAGE, CRTEMP )');
SQL.Add(' VALUES SIMAGE, :CRTEMP)');
JpegImage := TJPEGImage.Create;
MS := TMemoryStream.Create;
MS.Position := 0;
JPEGImage.Assign( Temp_Image.Picture.Graphic );
JPEGImage.SaveToStream( MS );
ParamByName( 'SIMAGE' ).LoadFromStream( MS, ftBlob );
ParamByName( 'CRTEMP' ).AsString := UseEmpCd;
ExecSQL;
JPEGImage.Destroy;
MS.Destroy;
end;

and here is oracledataset version

with TOracleDataSet_temp do
begin
Close;
SQL.Clear;
SQL.Add('INSERT INTO table (SIMAGE, CRTEMP )');
SQL.Add(' VALUES SIMAGE, :CRTEMP)');
DeclareVariable('CRTEMP', otString);
DeclareVariable('SIMAGE', otBlob);
fieldbyname('simage').Assign(Temp_Image.Picture.Graphic);
// or TOracleDataSet_tempSIMAGE.Assign(Temp_Image.Picture.Graphic); -- it make same error
setVariable( 'CRTEMP', UseEmpCd );
ExecSQL;
end;

It occur this error message "LOB variable :SIMAGE cannot be nil" !!
I guess that i missed something !! what's wrong????
help me~~

 

[Marco Kalter]

You are using FieldByName, but there are no fields for an Insert statement. To assign a BLOB, use a TLOBLocator like this:

with TOracleDataSet_temp do
begin
  Close;
  SQL.Clear;
  SQL.Add('INSERT INTO table (SIMAGE, CRTEMP )');
  SQL.Add(' VALUES (:SIMAGE, :CRTEMP)');
  DeclareVariable( 'CRTEMP', otString);
  DeclareVariable( 'SIMAGE', otBlob);
  BLOB := TLOBLocator.CreateTemporary( Session, otBLOB, True);
  JPEGImage.Assign( Temp_Image.Picture.Graphic );
  JPEGImage.SaveToStream( BLOB );
  setVariable( 'CRTEMP', UseEmpCd );
  setComplexVariable( 'SIMAGE', BLOB );
  ExecSQL;
  BLOB.Free;
end;

 

[mugoory]

Thanks for your reply!!
but my version(DOA version 3.4.6.1) don't have CreateTemporary method.
it has Create method only..

so i tried like this

SQL.Add('VALUES (empty_blob(), :CRTEMP) returning SIMAGE into :SIMAGE');
JPEGImage.Assign( Temp_Image.Picture.Graphic );
BLOB := TLOBLocator.Create( Session, otBLOB);
DeclareVariable('SIMAGE', otBlob);
Setcomplexvariable('SIMAGE', BLOB);
setVariable( 'CRTEMP', gs_UseEmpCd );
ExecSQL;
JPEGImage.SaveToStream( BLOB );

but it still make problem.
this time occur orcle-error..

message is "can bind a LONG value only for insert into a LONG column"

how can i fix it??

 

[Marco Kalter]

Can you let me know the exact table definition?

 

[mugoory]

This is my table script

SIMAGE BLOB(4000),
CRTEMP CHAR(7) NOT NULL

 

[Marco Kalter]

What is a BLOB(4000)? That does not really exist in Oracle.

 

[mugoory]

oh sorry i copied it dba2000

it just BLOB!!

SIMAGE BLOB,
CRTEMP CHAR(7) NOT NULL

 

[mugoory]

Hello, i'm waiting for your reply....

 

[Marco Kalter]

I don't see any immediate clues why this error would occur. None of the variables or constants are a LONG value. Perhaps you can set TOracleDataSet_temp.Debug to True to verify that no unexpected SQL text or variables are passed?

 

[mugoory]

I am back...^^
I can't find any special thing in debug mode.
So i tested demo source( DOA DEMO - PictureDemo).

I didn't touch anything but it occured same error-( can bind a LONG value only for insert into a LONG column )

Do i need some special setting for oracle or delphi??

 

[Marco Kalter]

Can you verify if TOracleSession.BytesPerCharacter is set to bcAutoDetect?