Board index » delphi » problems with TStoredProc returning blob data types from Oracle 7.3.3

problems with TStoredProc returning blob data types from Oracle 7.3.3

On trying to return a blob data type using a stored procedure in Oracle
7.3.3 we constantly get an error message "Capability not supported."
Below are the steps you can take to recreate the problem.
Can anyone out there help with this one?
Is it a known problem with delphi 3 and 4 ?
thank you.
Ted Ayers

Problem:
Unable to call a stored procedure that returns blob data type using
Delphi 3
or 4 with Oracle 7.3.3.
The error message is "Capability not suported".

Steps to recreate the problem using Oracle 7.3.3 database:
1. Create an oracle table
        create table t_image (
          SEQ_NUM  VARCHAR2(4),
          IMAGE LONG RAW);

2. Create a stored procedure:
        CREATE OR REPLACE PROCEDURE GET_IMAGE
          (IN_SEQ IN VARCHAR2, OUT_IMAGE OUT LONG RAW) AS
        BEGIN
          SELECT IMAGE INTO OUT_IMAGE FROM T_IMAGE WHERE SEQ_NUM =
IN_SEQ;
        END;

3. Create a delphi project that contains two edit boxes and three push
button named as follow (feel free to add labels if they help):
a.      edtSequnceNumber - edit box that contains the sequence number
b.      edtFileName - edit box that contains the file name of the blob
file.

        eg. c:\winnt\winnt.bmp
c.      pbAdd - push button that has the caption as "Add" and its
function
is to insert a row to t_image table.  Here is the code for the onclick
event:
          with TblImage do begin
            Insert;
            TblImageSEQ_NUM.AsString := edtSequenceNumber.Text;
            TblImageIMAGE.LoadFromFile(edtFileName.Text);
            try
              post;
              ShowMessage('Inserted success');
            except
              on E:Exception do
                ShowMessage('Insert Error: ' + E.Message);
             end;
          end;

d.      pbRetrieve  - push button that has the caption of "Retrieve".
This
will show that retrieving using normal select sql works fine.  Before
calling this event, make sure that you have entered an existing sequence

number from t_image table, and the file name is pointed to a new file
location.
        with QryImage do begin
            close;
            Params[0].AsString := edtSequenceNumber.Text;
            try
              open;
              if not eof then begin

TBlobField(FieldByName('Image')).SaveToFile(edtFileName.Text);
                ShowMessage('Retrieve OK');
              end
              else
                ShowMessage('No records found');
            except
              on E:EDatabaseError do
                ShowMessage('Retrieve Error: ' + E.Message);
             end;
          end;

e.      pbCallStoreProc - push button that has the caption as "Call
Stored
Proc".  The code is shown below:
          with StoredProc1 do begin
            Params.Clear;
            Params.CreateParam(ftString,'IN_SEQ',ptInput);
            Params.CreateParam(ftBlob,'OUT_IMAGE',ptOutput);
            Params[0].AsString := edtSequenceNumber.Text;
            try
              Active := true;
              // Always get the error here as "Capability not supported"

            except
              on E:EDatabaseError do
                ShowMessage('StoredProc Error: ' + E.Message);
             end;
          end;
        end;

_________________________________________________
CREATE TABLE T_IMAGE (
ACCT_NUM VARCHAR2(4),
SEQ_NUM VARCHAR2(2),
IMAGE LONG RAW);

CREATE OR REPLACE PROCEDURE GET_IMAGE
  (IN_SEQ IN VARCHAR2, OUT_IMAGE OUT LONG RAW) AS
BEGIN
  SELECT IMAGE INTO OUT_IMAGE FROM T_IMAGE WHERE SEQ_NUM = IN_SEQ;
END;
/

CREATE OR REPLACE PROCEDURE GET_IMAGE_SIMPLE
  (IN_SEQ IN VARCHAR2, OUT_ACCT OUT VARCHAR2) AS
BEGIN
  SELECT ACCT_NUM INTO OUT_ACCT FROM T_IMAGE WHERE SEQ_NUM = IN_SEQ;
END;
/
show errors;

  tayers.vcf
< 1K Download
 

Re:problems with TStoredProc returning blob data types from Oracle 7.3.3


It's been a while, but as I recall you have to use a TQuery and the
TBlobStream object to move blobs in and out with the BDE.  Check the help
and, specifically, the create method of TBlobStream.  I'm using DOA now so
I'm not sure what the current support for blobs in the BDE is.

Theodore W. Ayers <tay...@earthlink.net> wrote in message
news:37CB58B2.54A0FBEB@earthlink.net...

Quote
> On trying to return a blob data type using a stored procedure in Oracle
> 7.3.3 we constantly get an error message "Capability not supported."
> Below are the steps you can take to recreate the problem.
> Can anyone out there help with this one?
> Is it a known problem with delphi 3 and 4 ?
> thank you.
> Ted Ayers

Re:problems with TStoredProc returning blob data types from Oracle 7.3.3


Quote
"Theodore W. Ayers" wrote:
>      try
>             Active := true;

Try with TStoredProc.ExecProc, as you dont return a result set, but only
parameters.

Hope it helps.

Yours
Martin

Re:problems with TStoredProc returning blob data types from Oracle 7.3.3


You cannot return parameter values longer than 255 characters from
Oracle in current Delphi (5 may be different).  Use a ref cursor and
return the values as columns in the cursor.
Regards

Other Threads