Subject: Delphi Bug List : pb with using BLOB with Delphi Date: 29 Jan 97 06:09:27 EST From: Olivier STEINBERG <100303.120@CompuServe.COM> To: Reinier STERKENBURG Attention to : Reinier STERKENBURG Delphi Bug List E-mail : r.p.sterkenburg@dataweb.nl >From : Olivier STEINBERG E-mail : 100303,120@compuerve.com Tel : (33) 01 46 04 29 29 (France) Boulogne, 29 January 1997 Sir, Thanks for maintaining this bug list, here is the description of what I suspect could be a bug with SQL Links for ORACLE concerning blob using with ORACLE. I can say that Borland technical support is not very responsive to problems you submit. I hope your list have better listening from Borland than my reports. Olivier STEINBERG BUG REPORT Product : DELPHI 2.01 C/S EDITION; BDE 3.50; SQL LINKS FOR ORACLE OS (CLIENT) : WINDOWS NT 3.51 OS (SERVER) : WINDOWS NT 3.51 DATABASE ORACLE 7.3.2.2.1 NATURE OF THE PROBLEM When I use a table or live query with fields larger than 255 characters (e.g. : BLOB field like Oracle varchar2(1000)) and I try to retrieve a single record on a page using a WHERE clause and logged to ORACLE with a account other than the creator of the object, I got an exception violation access when I display the blob in a memo field. In the memo control instead I have the following message (Memo too large). I have discovered the origin of the problem which I suspect could a bug. There is a difference of SQL code generation either youre logged as the tables creator or as a table's user. As the tables creator account, everything works fine. As the tables user account with all the rights on the table (i.e. SELECT, UPDATE, INSERT, DELETE), the problem occurs, (exception violation access). The problem comes from the fact that when Delphi try to retrieve the BLOB field when logged in another account than the creator, the criteria of the where clause is of the form : WHERE BLOB_FIELD = :1. Of course because of the size of the where clause (>255) it retrieves nothing, letting the TBlobField pointing nowhere. Thus the exception violation. Where as logging with the creator account, the where clause to retrieve the record is of the form : WHERE ROWID = :1 which works fine. To test the bug, just make two forms, one withe a simple grid and a second (detail view form) with all the fields. Link the table of the detail form to the table of the grid's first form, and just write an event handler on a button that open the detail form having selected a reocrd on the grid. Try to run this (with SQL MONITOR open) two times : one logged as the creator of the table, one logged as a user with all the rights on the table (of course). Here is the trace from SQL Monitor of the two sessions (the first session logged as the creator of the table, the second one logged in a different account) which do the same thing : - connecting to ORACLE - display the grid with all the records of the table (4 records) - and then accessing to a selected record in the detail form - then disconnecting from ORACLE I dont understand why when logged with a different account the where clause is the not of the same as when logged as creator of the object because in both cases, the rowid is public ? (I.e. WHERE ROWID = :1) Structure of the table of the test: CREATE TABLE OLIVIER.TEST_BLOB ( REF_TBL UMBER(3,0) NOT NULL, NOM RCHAR2(20), BLOB_FLD ARCHAR2(1000), CONSTRAINT PK_TEST_BLOB PRIMARY KEY (REF_TBL) ) Extract from SQL TRACE : First session logged as the tables creator : RETRIEVE THE RECORD with a link to a master DataSource 41 17:44:49 SQL Prepare: ORACLE - SELECT "REF_TBL" ,"NOM" ,"BLOB_FLD" ,"ROWID" FROM "TEST_BLOB" WHERE "REF_TBL"=:1 ORDER BY "REF_TBL" ASC 42 17:44:49 SQL Execute: ORACLE - SELECT "REF_TBL" ,"NOM" ,"BLOB_FLD" ,"ROWID" FROM "TEST_BLOB" WHERE "REF_TBL"=:1 ORDER BY "REF_TBL" ASC 43 17:44:49 SQL Stmt: ORACLE - Fetch 44 17:44:49 SQL Prepare: ORACLE - SELECT "BLOB_FLD" FROM "TEST_BLOB" WHERE "ROWID"=:1 45 17:44:49 SQL Execute: ORACLE - SELECT "BLOB_FLD" FROM "TEST_BLOB" WHERE "ROWID"=:1 46 17:44:49 SQL Stmt: ORACLE - Fetch 47 17:44:49 SQL Stmt: ORACLE - Close 48 17:44:53 SQL Stmt: ORACLE - Close 49 17:44:56 SQL Connect: ORACLE - Disconnect GENERAL Second session logged as a different account : RETRIEVE NOTHING because of " BLOB_FLD " = :1 87 17:45:24 SQL Prepare: ORACLE - SELECT "REF_TBL" ,"NOM" ,"BLOB_FLD" FROM "TEST_BLOB" WHERE "REF_TBL"=:1 ORDER BY "REF_TBL" ASC 88 17:45:24 SQL Execute: ORACLE - SELECT "REF_TBL" ,"NOM" ,"BLOB_FLD" FROM "TEST_BLOB" WHERE "REF_TBL"=:1 ORDER BY "REF_TBL" ASC 89 17:45:24 SQL Stmt: ORACLE - Fetch 90 17:45:24 SQL Prepare: ORACLE - SELECT "BLOB_FLD" FROM "TEST_BLOB" WHERE "BLOB_FLD"=:1 91 17:45:24 SQL Execute: ORACLE - SELECT "BLOB_FLD" FROM "TEST_BLOB" WHERE "BLOB_FLD"=:1 92 17:45:29 SQL Stmt: ORACLE - Close 93 17:45:31 SQL Connect: ORACLE - Disconnect GENERAL 94 17:45:31 Log started for: Delphi