bindparams do not work with SQLNet Connection

mtawk · Post by **mtawk** » Wed 04 May 2005 15:42

hello,
I'm updating a CLOB field in a table using bindparams with an SQLNet connection. The query passes without exception but the field is not updated in the DB.

The field is well updated via a TNS connection.

Is there any way I can use in ODAC in order to update a CLOB field with a SQLNet connection ?

Thx for your help

Alex · Post by **Alex** » Thu 05 May 2005 07:51

We cannot reproduce your problem. We need more detailed description i.e. your ODAC version, Oracle server version and OS version. Also try to reproduce your problem using our Clob demo project.

mtawk · Post by **mtawk** » Thu 05 May 2005 08:27

I have ODAC 5.00 + Oracle10j + Linux RedHat 3.0 with Delphi-Kylix + SQLNet connection.

If the bindparams value is less then 4000 characters, it is well inserted in the CLOB field, more then 4000, nothing is inserted.

Alex · Post by **Alex** » Thu 05 May 2005 15:04

Please send us complete sample to demonstrate it and include script to create server objects to ODAC support address.

mtawk · Post by **mtawk** » Fri 06 May 2005 09:17

here is my code :
StringOfChar( 'B',4000 ) the clob field is updated normally, 4001 characters the field is updated as empty.

var VQuery : TOraQuery ;
VConnection : TOraSession ;

begin
VQuery := TOraQuery.create(NIL) ;
VConnection := TOraSession.create(NIL) ;
try
VConnection.ConnectString := 'user/[email protected]:1521:DB' ;
VConnection.Options.Net := True;
VQuery.Connection := VConnection ;

VQuery.Options.DeferredLobRead:=False ;
VQuery.Options.CacheLobs:=True;
VQuery.Options.DeferredLobRead:=True ;

VQuery.SQL.Text := 'UPDATE MY_TABLE SET CLOB_FIELD = :XML WHERE ID = 1' ;

VQuery.ParamCheck := True ;
VQuery.Prepare ;
VQuery.ParamByName('XML').ParamType:=ptInput;
VQuery.ParamByName('XML').DataType := ftMemo ;
VQuery.ParamByName('XML').value := StringOfChar( 'B',4001 ) ;

VQuery.ExecSQL ;

finally
VQuery.Free ;
VConnection.Free ;
end ;

end;

thx for your help

Alex · Post by **Alex** » Fri 06 May 2005 11:38

There is some Net option limitations in your situation, you can't use LONG (ftMemo) data types on BLOB or CLOB fields with this option, try to use BLOB data type, for additional information see "Working with BLOB and CLOB data types" topic in ODAC help.