Cannot perform this operation on an open dataset

Cannot perform this operation on an open dataset

Postby 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
 
Posts: 7
Joined: Fri 18 Dec 2009 12:06

Postby 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?
Iliev
 
Posts: 7
Joined: Fri 18 Dec 2009 12:06

Postby 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.
Plash
Devart Team
 
Posts: 2844
Joined: Wed 10 May 2006 07:09

Postby 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.
Iliev
 
Posts: 7
Joined: Fri 18 Dec 2009 12:06

Postby 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.
Plash
Devart Team
 
Posts: 2844
Joined: Wed 10 May 2006 07:09


Return to dbExpress driver for Oracle