Cannot perform this operation on an open dataset

Iliev · Post by **Iliev** » Fri 18 Dec 2009 12:21

Hi,

I have this situation:
1. I have an oracle function that raises an error.
2. I'm opening a select query to return the result of function - The Error was raised!
3. I'm opening another select query e.g. 'select * from dual' and then this error happens "SQLDataSet1: Cannot perform this operation on an open dataset".

Second dataset works fine with standard Codegear driver!

Here is the code:

oracle function:

Code: Select all

create or replace function scott.fnc_error return varchar2 is
begin
  Raise_Application_Error(-20000,'Test Error');
  return 'dummy';
end;
/

Unit1.dfm

Code: Select all

object Form1: TForm1
  Left = 0
  Top = 0
  Caption = 'Form1'
  ClientHeight = 286
  ClientWidth = 426
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object Button1: TButton
    Left = 180
    Top = 60
    Width = 75
    Height = 25
    Caption = 'Button1'
    TabOrder = 0
    OnClick = Button1Click
  end
  object SQLConnection1: TSQLConnection
    DriverName = 'DevartOracle'
    GetDriverFunc = 'getSQLDriverORA'
    LibraryName = 'dbexpoda40.dll'
    LoginPrompt = False
    Params.Strings = (
      'BlobSize=-1'
      'DataBase=fiona'
      'ErrorResourceFile='
      'LocaleCode=0000'
      'Password=tiger'
      'Oracle TransIsolation=ReadCommited'
      'User_Name=scott'
      'ProductName=Oracle'
      
        'DriverAssemblyLoader=Devart.DbxOda.DriverLoader.TCRDynalinkDrive' +
        'rLoader,Devart.DbxOda.DriverLoader,Version=1.0.0.5001,Culture=ne' +
        'utral,PublicKeyToken=09af7300eec23701')
    VendorLib = 'OCI.DLL'
    Connected = True
    Left = 30
    Top = 35
  end
  object SQLDataSet1: TSQLDataSet
    DbxCommandType = 'Dbx.SQL'
    MaxBlobSize = -1
    Params = 
    SQLConnection = SQLConnection1
    Left = 30
    Top = 80
  end
  object DataSetProvider1: TDataSetProvider
    DataSet = SQLDataSet1
    Options = [poAllowCommandText, poUseQuoteChar]
    Left = 30
    Top = 115
  end
  object ClientDataSet1: TClientDataSet
    Aggregates = 
    Params = 
    ProviderName = 'DataSetProvider1'
    Left = 185
    Top = 95
  end
  object ClientDataSet2: TClientDataSet
    Aggregates = 
    Params = 
    ProviderName = 'DataSetProvider1'
    Left = 220
    Top = 95
  end
end

Unit1.pas

Code: Select all

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, WideStrings, DB, SqlExpr, FMTBcd, StdCtrls, DBClient, Provider;

type
  TForm1 = class(TForm)
    SQLConnection1: TSQLConnection;
    SQLDataSet1: TSQLDataSet;
    DataSetProvider1: TDataSetProvider;
    ClientDataSet1: TClientDataSet;
    Button1: TButton;
    ClientDataSet2: TClientDataSet;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin
  try
    ClientDataSet1.Close;
    ClientDataSet1.CommandText := 'select fnc_error from dual';
    ClientDataSet1.Open;
  except
    on E: Exception do
    begin
      MessageDlg(E.Message,mtError,[mbOK],0);
    end;
  end;

  try
    ClientDataSet2.Close;
    ClientDataSet2.CommandText := 'select * from dual';
    ClientDataSet2.Open;
  except
    on E: Exception do
    begin
      MessageDlg(E.Message,mtError,[mbOK],0);
    end;
  end;
end;

end.

Iliev · Post by **Iliev** » Sat 19 Dec 2009 09:04

When I set FetchAll=True in connection settings, all works like standard CodeGear driver. But I'm not sure if this parameter will affect performance negatively?!?!?

Any ideas?

Plash · Post by **Plash** » Mon 21 Dec 2009 09:07

You need to close TSQLDataSet before setting its SQL. Add the following line

Code: Select all

    SQLDataSet1.Close;

before opening second TClientDataSet.

Iliev · Post by **Iliev** » Mon 21 Dec 2009 09:14

I can't do that in my real-world application because of 3-tire architecture. I mean in this demo application I have access to SQLDataset1 but in my production project SQLDataset1 is in the AppServer and it's controlled by Dataprovider so I can't close it manually.

Plash · Post by **Plash** » Wed 23 Dec 2009 13:04

So you should change your application. Set SQL for TSQLDataset at the server level and don't use the CommandText property of TClientDataSet if this query can raise an error.