Big problems with TParam for CLOB columns

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for Oracle in Delphi and C++Builder
Post Reply
silviob
Posts: 1
Joined: Wed 26 Oct 2005 16:23

Big problems with TParam for CLOB columns

Post by silviob » Wed 26 Oct 2005 16:36

Hi!

We try to insert data into a table with two CLOB columns.
If the blob text is shorter than 4000 characters all works fine.

If we try to insert more than 4000 characters into a CLOB field, we
receive different errors depending on the code we use to setup the TParam.

Here are the possible error messages:

ORA-01461 can bind a LONG value only for insert into a LONG column
ORA-12571 // no text!!!
ORA-01460 unimplemented or unreasonable conversion requested
LOB not initialized


We have tried:

p: TParam; // the param from the TSQLDataset
s: string; // a string with 11000 bytes

a)
p.AsBlob:=s;
// error on execsql

b)
p.Datatype:=ftOraCLob;
p.Value:=s;
// error on execsql

c)
p.AsMemo:=s;
// error on execsql

d)
p.Datatype:=ftOraCLOB;
p.SetBlobData(@s[1], Length(s));
// error on execsql

e)
p.Datatype:=ftBlob;
p.SetBlobData(@s[1], Length(s));
// error on execsql

f)
p.Datatype:=ftString;
p.SetBlobData(@s[1], Length(s));
// error on execsql


and so on. None of them works with more than 4000 bytes of data.

What is a working method of setting up this parameter with
more than 400 Bytes ??

Are there any limits on CLOB params ??


Thanks
Silvio

Paul
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Post by Paul » Thu 03 Nov 2005 08:14

We cannot reproduce your problem with Oracle server 9.2.0.1, Oracle server 10.2.0.1, database charset AL32UTF8, DbxOda 100. We tried to insert 15000 of English characters.
Please specify your version of Oracle server, database charset, client charset (in TSQLConnection parameters)

alk
Posts: 11
Joined: Sat 31 Dec 2005 13:35

Re: Big problems with TParam for CLOB columns

Post by alk » Sat 31 Dec 2005 13:38

I've got the same problem and tried also .AsString:= but without an results.

did you find a solution to this problem?

thanx
alex

Paul
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Post by Paul » Tue 03 Jan 2006 12:16

When you write data using TParam.AsString you change parameter data type to ftString (the maximum length is 4000). You have to use TParam.LoadFromStream(BlobStream, ftOraClob) for CLOB parameters.

jim
Posts: 3
Joined: Wed 23 Feb 2005 22:35

Post by jim » Fri 20 Jan 2006 17:34

I'm having a similar problem, however instead of using a TSQLQuery
object for doing parametered UPDATE/INSERT statements I'm using
a TSQLConnection object as described below.

I'm having trouble creating a BlobStream from the TSQLDataSet that I'm getting from TSQLConnection->DataSets[0].

Do you happen to have any other sample code for creating Blob streams?

Thanks in advance...

jim


sqlString = "INSERT INTO EMP (STR,BIG_CLOB) VALUES (?,?)";

// set up a transaction
// sqlConnection is a TSQLConnection object
if (!sqlConnection->InTransaction) {
TD.TransactionID = 1;
TD.IsolationLevel = xilREADCOMMITTED;
sqlConnection->StartTransaction(TD);
}

// bind some parameters
// paramsList = new TParams;
TParam *p = paramsList->CreateParam(ftString,":0",ptInput);
p->AsString = "hello"

p = paramsList->CreateParam(ftOraClob,":1",ptInput);
p->AsString = "really big clob string..."

// then execute the statment.
cnt = sqlConnection->Execute(sqlString,paramsList,NULL);

// now commit
sqlConnection->Commit(TD);

// and we're done.
sqlConnection->Connected = false;

Paul
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Post by Paul » Mon 06 Mar 2006 08:27

The following line converts TParam.DataType to ftString. The maximum value length is 4000.
p->AsString = "really big clob string..."

TParam.SetBlobData converts TParam.DataType to ftBlob. See Db.pas for more details. This corresponds to Oracle LONG RAW type.

Please use TParam.LoadFromStream(Stream, ftOraClob) for loading data to CLOB parameter.

ES2000
Posts: 2
Joined: Tue 02 May 2006 14:42

Post by ES2000 » Tue 02 May 2006 14:54

We also get this error:

Code: Select all

ORA-01460: unimplemented or unreasonable conversion requested
tried above mentioned methods but error occurs even on this dirty workaround:

Code: Select all

SQLDataSetSourceDB.Params[i].DataType := ftOraClob;
TempStream := TMemoryStream.Create;
(SQLDataSetSourceDB.Fields[i] as TMemoField).SaveToStream(TempStream);
TempStream.Position := 0;
SQLDataSetDestinationDB.Params[i].LoadFromStream(TempStream, ftOraClob);
The only thing we didn't test was the returning clause as it is almost impossible to change all the app code for this..

Any suggestions to solve this issue?

MarcosPaulo
Posts: 1
Joined: Tue 01 Aug 2006 17:55

Post by MarcosPaulo » Tue 01 Aug 2006 18:08

Anyone could get a solution for any of these problems ?
I'm having the same problems. With Oracle Net 3.0.4 and Oracle 10g

Post Reply