bug for oracle in Direct Mode about LoadFromFile

6rl · Post by **6rl** » Wed 30 Mar 2011 05:41

My environment is : delphi7+UniDac3.50.0.12+win2003+oracle10g
pic file size: 42.8kb
table struct:

create table TEMP3
(
  A1 NUMBER,
  A2 TIMESTAMP(6) WITH LOCAL TIME ZONE default sysdate,
  A4 NVARCHAR2(200),
  A5 VARCHAR2(200),
  A6 BLOB,
  A3 CLOB
)

Code: Select all

unicon1 :TUniConnection;
unqry1: TUniQuery;
begin
  with unicon1 do
  begin
    Disconnect;
    ProviderName:='Oracle';
    Server:= edtHost.Text + ':1521:'+ edtSID.Text;
    Username:=edtUserName.Text;
    Password:=edtPassword.Text;
    LoginPrompt:=False;
    SpecificOptions.Clear;
    SpecificOptions.Values['Direct'] := 'true';//Direct Mode  SpecificOptions.Values['UseUnicode'] := 'true';

    Connect;
   // ShowMessage('connect is ok);
 end;
   unqry1.Connection :=unicon1 
   unqry1.Close;
   unqry1.SQL.Text := 'insert into temp3 (a4,a6) values (:a4,:a6)';
   unqry1.ParamByName('a4').AsString := 'pic1';

   unqry1.ParamByName('a6').LoadFromFile('t1.jpg',ftBlob);
   unqry1.Execute;// error: Project Project1.exe raised exception class EOraError with message 'NET: Network error (53)'. Process stopped. Use Step
or Run to continue.
   unqry1.Close;
 
end;

But it works fine in client mode.
how can I solve it? I don't want to use client mode.

AlexP · Post by **AlexP** » Wed 30 Mar 2011 06:54

Hello,

Thank you for the information.
We have already fixed the problem.
Please download the latest version of UniDAC (3.60.0.16).
Also you should use the following code to load blob data:

unqry1.ParamByName('a6').LoadFromFile('t1.jpg',ftOraBlob);

6rl · Post by **6rl** » Wed 30 Mar 2011 09:30

I have try the latest version of UniDAC (3.60.0.16).
Bug is still on.

Code: Select all

unqry1.ParamByName('a6').LoadFromFile('t1.jpg',ftOraBlob);
unqry1.Execute;// ora-24813: cannot send or receive an unsupported LOB

delphi 7(build 4.453)
UniDAC (3.60.0.16)
http://www.devart.com/unidac/unidac7.exe

AlexP · Post by **AlexP** » Wed 30 Mar 2011 09:56

Hello,

Please set the type of blob parameter like:
unqry1.ParamByName('a6').ParamType := ptInput;

6rl · Post by **6rl** » Thu 31 Mar 2011 04:10

Thanks. But it doesn't work;

Code: Select all

unqry1.ParamByName('a6').ParamType := ptInput;
unqry1.ParamByName('a6').LoadFromFile('t1.jpg',ftOraBlob); 
unqry1.Execute;// ora-24813: cannot send or receive an unsupported LOB

AlexP · Post by **AlexP** » Thu 31 Mar 2011 09:09

Hello,

Please try to execute the following code:

var
UniConnection: TUniConnection;
UniQuery: TUniQuery;
begin
UniConnection:= TUniConnection.Create(nil);
UniQuery:= TUniQuery.Create(nil);
UniConnection.ProviderName := 'ORACLE';
UniConnection.SpecificOptions.Clear;
UniConnection.SpecificOptions.Values['Direct'] := 'True';
UniConnection.Server := 'host:port:database';
UniConnection.Username := 'username';
UniConnection.Password := 'passwd';
UniConnection.Connect;
UniQuery.Connection := UniConnection;
UniQuery.SQL.Text := 'insert into temp3 (a4,a6) values (:a4,:a6)';
UniQuery.ParamByName('a4').AsString := 'pic1';
UniQuery.ParamByName('a6').ParamType := ptInput;
UniQuery.ParamByName('a6').LoadFromFile('\1.jpg',ftOraBlob);
UniQuery.Execute;

if the problem doesn't arise, please modify the code to reproduce the problem, and send it me.

6rl · Post by **6rl** » Fri 01 Apr 2011 02:13

I found the reason for the error.

Code: Select all

UniConnection.SpecificOptions.Values['UseUnicode'] := 'True';

If I set the "UseUnicode" is "true", Execute would raise error :"ora-24813: cannot send or receive an unsupported LOB ".

Thanks again.

6rl · Post by **6rl** » Fri 01 Apr 2011 02:17

Code: Select all

var
  UniConnection: TUniConnection;
  UniQuery: TUniQuery;
begin
  UniConnection:= TUniConnection.Create(nil);
  UniQuery:= TUniQuery.Create(nil);
  UniConnection.ProviderName := 'ORACLE';
  UniConnection.SpecificOptions.Clear;
  UniConnection.SpecificOptions.Values['Direct'] := 'True';
  UniConnection.SpecificOptions.Values['UseUnicode'] := 'True';//this cost the error
  UniConnection.Server := 'host:port:database';
  UniConnection.Username := 'username';
  UniConnection.Password := 'passwd';
  UniConnection.Connect;
  UniQuery.Connection := UniConnection;
  UniQuery.SQL.Text := 'insert into temp3 (a4,a6) values (:a4,:a6)';
  UniQuery.ParamByName('a4').AsString := 'pic1';
  UniQuery.ParamByName('a6').ParamType := ptInput;
  UniQuery.ParamByName('a6').LoadFromFile('\1.jpg',ftOraBlob);
  UniQuery.Execute;

AlexP · Post by **AlexP** » Fri 01 Apr 2011 08:01

Hello,

Thank you for the information.
We have reproduced the problem.
This problem arises only with Unicode databases.
We will notify you as soon as we have any results.

AlexP · Post by **AlexP** » Fri 15 Apr 2011 09:31

Hello,

We still can't fix the problem.
And we are still working on it.
Now you can use the following workaround:
you can create a record with an empty blob field like

Code: Select all

  OraQuery.Options.TemporaryLobUpdate := True;
  OraQuery.SQL.Text := 'insert into temp3 (a4,a6) values (:a4, EMPTY_BLOB())';
  OraQuery.ParamByName('a4').AsString := 'pic1';
  OraQuery.Execute;

after that update this record like

Code: Select all

  OraQuery.SQL.Text := 'update temp3 set a6 = :a6 where a4 = :a4';
  OraQuery.ParamByName('a4').AsString := 'pic1';
  OraQuery.ParamByName('a6').ParamType := ptInput;
  OraQuery.ParamByName('a6').DataType := ftOraBlob;
  OraQuery.ParamByName('a6').AsBlobRef.LoadFromFile('C:\Documents and Settings\BorisM\Desktop\test2.jpg');
  OraQuery.Execute;