Oracle BLOBs
Eric Grange
I know this is not the first post on this subject, but I've not seen
any clear answer for this question :
Is it possible to use BLOB, CLOB, LONG or LONG RAW from Delphi/BDE on
an Oracle8 database ?
We cannot use database specific components since Oracle is only one of
the databases we have to access, and we don't want to write a database-
specific version anyway :)
Any time we attempt to write to an Oracle blob, the error is either
an ORA22275 or an ORA01461... but it never works.
(Delphi 5 C/S + Oracle 8.0.5, on NT4, Win95 & Win98. Latest BDE)
The only reference to this question I found is an old article:
http://community.borland.com/article/0,1410,19504,00.html
that only refers to similar problems with D4 and BCB, and tells tales
of a global variable "oracle8blobs" that is no longer available in
Delphi5... too bad... it describes exactly our problem...
Please, let me know if Oracle8 can actually be used with Delphi5 & BDE.
Thanks.
Eric Grange
kevin
an ORA22275 or an ORA01461... but it never works.>>
Can we see your code?
Eric Grange
> an ORA22275 or an ORA01461... but it never works.>>
>
> Can we see your code?
yep, I extracted the table creation code, the TQuery.SQL and the code
used to feed the query's parameters below. This code works without a
glitch with the equivalent InterBase structure.
---------- table creation code
CREATE TABLE DOCUMENT_TYPE (
ID INTEGER NOT NULL PRIMARY KEY,
NOM VARCHAR(60) NOT NULL UNIQUE,
DESCRIPTION VARCHAR(255) NOT NULL,
ICONS blob ,
CLASS CHAR(50) ,
RUD_RULES BLOB ,
DUREE_ARCHIVAGE INTEGER NOT NULL,
GESTION BLOB ,
RIGHTS BLOB ,
PARAMETERS BLOB ,
INACTIF INTEGER ,
CLASSIFICATION BLOB ,
TIME_STAMP DATE
);
---------- the TQuery.SQL
insert into document_type (id, nom, description, icons, duree_archivage,
gestion, rights, rud_rules, class, parameters, inactif,
classification, time_stamp)
values (:id, :nom, :description, :icons, :duree_archivage,
:gestion, :rights, :rud_rules, :class, :parameters, :inactif,
:classification, :time_stamp)
---------- the code using the TQuery
with QUAddDocType do begin
ParamByName('ID').AsInteger:=newID;
nomDescr.WriteToParams(Params);
icons.WriteToParams(Params);
ParamByName('DUREE_ARCHIVAGE').AsInteger:=DureeArchivage;
ParamByName('GESTION').AsBlob:=gestion.SaveToString;
ParamByName('RIGHTS').AsBlob:=rights.SaveToString;
ParamByName('RUD_RULES').AsBlob:=RUDRule.SaveToString;
ParamByName('CLASS').AsString:=DocClass;
ParamByName('PARAMETERS').AsBlob:=parameters.SaveToString;
ParamByName('INACTIF').AsInteger:=inactif;
ParamByName('CLASSIFICATION').AsBlob:=classification.SaveToString;
ParamByName('TIME_STAMP').AsDateTime:=Now;
ExecSQL;
end;
The "SaveToString" returns a binary string (ie. that may contain
non-character codes, like 0 (zero), etc. "CLOB" is not an option).
We also have many tables that use more than 1 (one) blob-type
field, making the Oracle types "LONG" and "LONG RAW" inapplicable.
For all the ParamByName() calls that use ".AsBlob", I tried redefining
the DataType to ftOraBlob just after the AsBlob assignment, but it just
changes the error from
"ORA-01461: can bind a LONG value only for insert into a LONG column'."
to
"ORA-22275: invalid LOB locator specified'"
this is perhaps a step in the right direction, but it falls short
(and requires some Oracle-specific coding).
Oh, and I was forced to set ObjectView to True and UniDirectionnal to
False or the TQuery would fail, even on simple queries like :
"select * from document_type"
(with the table still empty...)
Eric Grange
Eric Grange
(no error), but does with a 8000 bytes long one...
so I guess this is the same 4K limit as stated in the article.
Eric Grange
John Pierce
Eric Grange wrote:
>
> CREATE TABLE DOCUMENT_TYPE (
> ID INTEGER NOT NULL PRIMARY KEY,
> NOM VARCHAR(60) NOT NULL UNIQUE,
> DESCRIPTION VARCHAR(255) NOT NULL,
> ICONS blob ,
> CLASS CHAR(50) ,
> RUD_RULES BLOB ,
> DUREE_ARCHIVAGE INTEGER NOT NULL,
> GESTION BLOB ,
> RIGHTS BLOB ,
> PARAMETERS BLOB ,
> INACTIF INTEGER ,
> CLASSIFICATION BLOB ,
> TIME_STAMP DATE
> );
>
This is off your topic, but you should not use INTEGER as a datatype in
Oracle, since it is interpreted as NUMBER(38), and will be read as a float in
Delphi; use NUMBER(10) or less to get a Delphi integer. The VARCHAR datatype
should never be used; use VARCHAR2. Unless you have some particlular reason,
which I cannot imagine, you should not use CHAR(50); use VARCHAR2(50).
John Pierce
Eric Grange
> Oracle, since it is interpreted as NUMBER(38), and will be read as a float in
> Delphi; use NUMBER(10) or less to get a Delphi integer.
We use a single database scheme for all DBs, and an SQL generator for
DB specifics.
I guess I'll have to update the SQL generator :)
> The VARCHAR datatype
> should never be used; use VARCHAR2.
Well, read that in the doc, but type names with a "2" appended look
so ugly... kind of a wart to me...
>Unless you have some particlular reason,
> which I cannot imagine, you should not use CHAR(50); use VARCHAR2(50).
CHAR() helps reducing page fragmentation on some databases.
But basicly, that's the SQL Generator lazy once more.
Thanks for your tips.
Eric Grange
Ramesh Theivendran ( Borland )
be done with in a transaction.
insert into TESTBLOB (ICOMPONENTID, GIMAGE)
values(:CID, empty_blob() ) returning GIMAGE into :GIMAGE
Make sure you have the param GIMAGE type as ftOraBlob.
T.Ramesh
Eric Grange wrote:
> Hi all,
>
> I know this is not the first post on this subject, but I've not seen
> any clear answer for this question :
>
> Is it possible to use BLOB, CLOB, LONG or LONG RAW from Delphi/BDE on
> an Oracle8 database ?
>
> We cannot use database specific components since Oracle is only one of
> the databases we have to access, and we don't want to write a database-
> specific version anyway :)
>
> Any time we attempt to write to an Oracle blob, the error is either
> an ORA22275 or an ORA01461... but it never works.
>
kevin
so ugly... kind of a wart to me...>>
Well, looks is more important over functionality. Good reason to go with it! <g>
kevin
which I cannot imagine, you should not use CHAR(50); use VARCHAR2(50).>>
Depends. In general, I agree, and we only use VarChar2 because of the way Oracle
stores the Char vs. VarChar2 datatypes. However, VarChar stores it's data
differently than Char and if you have tons of updates, this will create a lot of
fragmentation in the database as the VarChar field expands.
Kevin
Eric Grange
with 100% Oracle specific code ?
Is it the same deal when reading big BLOBs ?
Will this issue be fixed with upcoming BDE replacement in Delphi6 ?
Is there any "Standard SQL" to "Oracle dialect" translator for queries
out there ? ...arg... ...glub...
Eric Grange
Eric Grange
Ok, I no longer get an error but it is just inserting an empty blob in
the blob field, not my data (Oracle doc states "returning" should be
used to place the value in a PL/SQL variable, not to define the field).
Straight from the BDE readme
"Delphi 5 and C++Builder 5 (and later products) are certified for use
with Oracle 8.0.5 client and server."
That's just what I'm using... :(
toddh...@my-deja.com
I have been looking at this also for the last day.
Sort of have something working... but not quite.
some ugly test code below.
procedure TForm1.Button6Click(Sender: TObject);
var sqlString : string;
aTBlobType : TBlobType;
buff : string;
amt : integer;
aTBlobField : TBlobField;
begin
sqlString := 'UPDATE pwr.neta_message_log SET
xml_message_lob = EMPTY_CLOB( ) where
neta_message_log_id = 20';
sqlString := ' RETURNING xml_message_lob
INTO :clob_locator';
Database1.StartTransaction;
try
Query2.sql.clear;
Query2.sql.add(sqlString);
Query2.paramByName('clob_locator').datatype :=
ftOraClob;
Query2.prepare;
Query2.execSql;
{sqlString := 'dbms_lob.write(' +
Query2.fieldByName
(clob_locator,amt,offset,buff)';}
Database1.commit;
except
Database1.rollback;
Database1.close;
end;
end;
as far as I know the insert of the "EMPTY_CLOB( )"
creates the lob locator. which then should be
returned by the RETURNING clause.
Then you write to it using the 'dbms_lob.write'
procedure from the oracle dbms_lob package.
(maybe)
unfortunatley, I get an "Access violation in
OraClient8.dll, write of address...".
and have not yet been able to get it to work.
this access violation happens when the Query
is executed after setting the parameter to a
ftOraClob.
running with 8i and Delphi5.
regards
Todd
Sent via Deja.com http://www.deja.com/
Before you buy.
Todd Hunter
a copy and paste error, then sqlString line should read:
sqlString := 'UPDATE pwr.neta_message_log SET xml_message_lob =
EMPTY_CLOB( ) where neta_message_log_id = 20 RETURNING xml_message_lob
INTO :clob_locator';
Ramesh Theivendran ( Borland )
D4 was certified with ORACLE 8.0.4 and
D5 was certified with ORACLE 8.1.5.
With ORACLE blobs you need locators before inserting into the BLOB or CLOB and
that's why you have this new ORACLE specific SQL syntax with a RETURNING clause.
Also ORACLE lob locators can't span transactions and you need to perform the
insert/update as part of a transaction.
Are you trying to insert a single record or multiple records with BLOBs. There
was a known bug in the ORACLE 8 driver when you prepare once and try to execute
many times only the first execute puts the blob data correct. This bug is now
fixed.
T.Ramesh.
Robert
Kevin
Eric Grange
> D4 was certified with ORACLE 8.0.4 and
> D5 was certified with ORACLE 8.1.5.
hehe... If D5 really can't do 8.0.5, that will be a show-stopper for
us, the only reason we where doing Oracle was to use an existing Oracle
server at a customer's.
> With ORACLE blobs you need locators before inserting into the BLOB or
> CLOB and that's why you have this new ORACLE specific SQL syntax with
> a RETURNING clause.
>
> Also ORACLE lob locators can't span transactions and you need to
> perform the insert/update as part of a transaction.
Yes, I understand that, but how can you make use of what was
returned? The TQuery just fails in RequestLive=True mode, an, with
your SQL code, when using "Open", the BDE complains about not
receiving any cursor, and with ExecSQL, well, nothing is changed
(in the params) and no fields are available...
Anyway, it looks like a functionnality failure since using "AsBlob"
before the call to ExecSQL works just right for all other databases.
> Are you trying to insert a single record or multiple records with BLOBs. There
> was a known bug in the ORACLE 8 driver when you prepare once and try to execute
> many times only the first execute puts the blob data correct. This bug is now
> fixed.
Just trying to insert one records with only one BLOB. There are more
complex tasks down our apps with insertions of multiple records,
each record with multiple BLOBs, but I'm still nailed on the basic
one record/one BLOB case...
Eric Grange
Todd Hunter
going.
ended up with something kinda like
first start a transaction
//then execute the SQL from delphi
insert into testTable(thePrimaryKey, xmlClob)
values(1, empty_clob())
then
//select back a live query containing the record you just created
aQuery.requestLive := true;
//THE SQL HAD TO BE IN UPPERCASE FOR ORACLE TO RETURN A LIVE DATASET
aQuery.sql.add('SELECT * FROM TESTTABLE WHERE THEPRIMARYKEY = 1');
aQuery.open;
//the query must be LIVE now
aQuery.edit;
aQuery.fieldByName('xmlClob').asString := 'THE VERY LONG XML STRING...'
aQuery.post;
then commit the transaction