Big problems with TParam for CLOB columns
Big problems with TParam for CLOB columns
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
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
Re: Big problems with TParam for CLOB columns
I've got the same problem and tried also .AsString:= but without an results.
did you find a solution to this problem?
thanx
alex
did you find a solution to this problem?
thanx
alex
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;
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;
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.
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.
We also get this error:
tried above mentioned methods but error occurs even on this dirty workaround:
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?
Code: Select all
ORA-01460: unimplemented or unreasonable conversion requested
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);
Any suggestions to solve this issue?
-
- Posts: 1
- Joined: Tue 01 Aug 2006 17:55