Problem with Oracle 8 and Lob fields

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for Oracle in Delphi and C++Builder
Post Reply
romeu
Posts: 10
Joined: Wed 09 Jan 2008 13:06
Location: Belo Horizonte, MG, Brazil
Contact:

Problem with Oracle 8 and Lob fields

Post by romeu » Mon 03 Aug 2009 20:03

Problem with Oracle 8 and Lob fields

Error Message: Database Server Error: ORA-01461: can bind a LONG value only for insert into a LONG column

Can sameone help us?
dbexpoda.dll: Version 4.20.0.11
Oracle: Version 8 (8.0.5.0.0), 9i and 10 (XE)

Sample code above (see comments in BlobTest procedure for more details):


// ------------------------------------------------------------------------------
// Start of "uDbExpOdaError.pas"
// ------------------------------------------------------------------------------

unit uDbExpOdaError;

interface

uses
Classes, SqlExpr;

procedure BlobTest(SqlConnection: TSQLConnection; InputStream: TStream);

implementation

uses
DB, SysUtils;

procedure CreateTestTable(SqlConnection: TSQLConnection; UseClobField: Boolean);
const
CREATE_TEST_TABLE =
'CREATE TABLE CLOB_TEST ( ' +
' ID_CLOB_TEST NUMBER(20), ' +
' NOTE %s)';
var
CommandValue: String;
begin
if (UseClobField) then begin
CommandValue := Format(CREATE_TEST_TABLE, ['CLOB']); // Tested with CLOB and BLOB fields
end else begin
CommandValue := Format(CREATE_TEST_TABLE, ['LONG']); // LONG FIELD
end;

SqlConnection.Execute(CommandValue, nil);
end;

procedure DropTestTable(SqlConnection: TSQLConnection);
const
DROP_TEST_TABLE = 'DROP TABLE CLOB_TEST';
begin
SqlConnection.Execute(DROP_TEST_TABLE, nil);
end;

procedure IsertValueIntoTestTable(
SqlConnection: TSQLConnection; InputStream: TStream);
const
INSERT_VALUE_INTO_TEST_TABLE =
'INSERT INTO CLOB_TEST (ID_CLOB_TEST, NOTE) ' +
'VALUES (:ID_CLOB_TEST, :NOTE)';
var
SqlDataSet: TSQLDataSet;
begin
SqlDataSet := TSQLDataSet.Create(nil);
try
SqlDataSet.SQLConnection := SqlConnection;
SqlDataSet.GetMetadata := False;
SqlDataSet.CommandText := INSERT_VALUE_INTO_TEST_TABLE;

SqlDataSet.ParamByName('ID_CLOB_TEST').AsInteger := 1;
SqlDataSet.ParamByName('NOTE').LoadFromStream(InputStream, ftMemo);
SqlDataSet.ExecSQL;
finally
SqlDataSet.Free;
end;
end;

(**
* Behaviors:
* 1) In the Oracle 9 or 10, the program AWAYS work fine;
* 2) In the Oracle 8...
* 2.1) (NOTE field = CLOB or BLOB) and (InputStream.Size = 4000) then [FAIL]
* 2.3) (NOTE field = LONG) and (InputStream.Size = 4000) then [OK]
**)
procedure BlobTest(SqlConnection: TSQLConnection; InputStream: TStream);
begin
try
// If the last param is TRUE, the program works only in oracle 9 or later
// If the last param is FALSE, the program works in any version of Oracle
CreateTestTable(SqlConnection, True);

IsertValueIntoTestTable(SqlConnection, InputStream);
finally
DropTestTable(SqlConnection);
end;
end;

end.

// ------------------------------------------------------------------------------
// End of "uDbExpOdaError.pas"
// ------------------------------------------------------------------------------

Plash
Devart Team
Posts: 2844
Joined: Wed 10 May 2006 07:09

Post by Plash » Tue 04 Aug 2009 06:54

You should change your SQL statement to the following:

Code: Select all

INSERT INTO CLOB_TEST (ID_CLOB_TEST, NOTE) 
VALUES (:ID_CLOB_TEST, empty_clob())
RETURNING NOTE INTO :NOTE

Also you need to use ftOraClob in LoadFromStream:

Code: Select all

      SqlDataSet.ParamByName('NOTE').LoadFromStream(InputStream, ftOraClob);

romeu
Posts: 10
Joined: Wed 09 Jan 2008 13:06
Location: Belo Horizonte, MG, Brazil
Contact:

Post by romeu » Tue 04 Aug 2009 11:46

Now it works perfectly!
Thank you!

Post Reply