Big problems with TParam for CLOB columns

Big problems with TParam for CLOB columns

Postby 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
silviob
 
Posts: 1
Joined: Wed 26 Oct 2005 16:23

Postby 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)
Paul
 
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Re: Big problems with TParam for CLOB columns

Postby 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
alk
 
Posts: 11
Joined: Sat 31 Dec 2005 13:35

Postby 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.
Paul
 
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Postby 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;
jim
 
Posts: 3
Joined: Wed 23 Feb 2005 22:35

Postby 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.
Paul
 
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Postby 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?
ES2000
 
Posts: 2
Joined: Tue 02 May 2006 14:42

Postby 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
MarcosPaulo
 
Posts: 1
Joined: Tue 01 Aug 2006 17:55


Return to dbExpress driver for Oracle