Solved

Howto change SQL-Statement in BeforeExecute?

Posted on 2004-04-21
13
540 Views
Last Modified: 2010-04-05
Hi,

In my AppServer I have a TDataSetProvider and a TOracleDataSet.
Frequently my client executes an SQL-Statement which is almost always
something like 'select * from some_table'

Now I would like to include access restrictions into this statement, so that
only those rows are queried that should be readable by the user.
Building the statement is no problem, but it seems that TOracleDataSet.SQL
cannot be changed in BeforeExecute.

Ideally, BeforeExecute is the right place for me to change the statement, but
so I have to do it somewhere else?
Someone told me that it has to be done before the statement is 'prepared'.
When would that be?

thx
Wyverex

P.S: I cannot rely on the database to handle such restrictions, I have to do
it in code.
0
Comment
Question by:Wyverex
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 10876980

   on the TDataSetProvider in its Options set poAllowCommandText = True.

   and the from the Client
     YourQuery1.CommandText = ....
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 10877031
why not use views, which are restricted user-depending,
maybe with usage of the oracle-syscontext?
0
 

Author Comment

by:Wyverex
ID: 10877043
Well, that's what I do. The client sends its statement using the
CommandText property.

Perhaps I should have made clearer, that the server
and not the client adds the changes to the SQL Statement.

So I need an event in TDataSetProvider or TOracleDataSet that
lets me change the statement whenever it should be
executed (That's why I tried BeforeExecute first)
0
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 10877067

   what is the problem with OnBeforeExecute. It sounds logical to do it there
0
 

Author Comment

by:Wyverex
ID: 10877165
Ivanov_G wrote:

>> what is the problem with OnBeforeExecute. It sounds logical to do it there

Yes, indeed it is logical. That's why I tried it first ;-)
But changing the statement doesn't seem to have any effect.

Suppose the follwing example:

1) The client sends 'select * from table_a' through CommandText
2) The Server replaces the statement (in this example) with 'select * from table_b'
    using

((Sender as TDataSetProvider).DataSet as TOracleDataSet).SQL.Add( 'select * from table_b' );

There is no error but the server still returns the contents of table_a and not table_b!
So I guess it cannot be done in BeforeExecute


kretzschmar wrote

>> why not use views, which are restricted user-depending,

Thought about that as well, but I still need to alter the 'create view' statement in the server
because my client allows browsing through the database. Whenever a user views a table
the server has to check access dynamically and thus has to alter the statement.
I cannot create ALL views beforehand, that would be more than 100 views and the user
would use only a small fraction of them actually.
0
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 10877182

  ((Sender as TDataSetProvider).DataSet as TOracleDataSet).SQL.Clear;
  ((Sender as TDataSetProvider).DataSet as TOracleDataSet).SQL.Add( 'select * from table_b' );
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 27

Expert Comment

by:kretzschmar
ID: 10877183
>the server has to check access dynamically and thus has to alter the statement

thats why i've appended:
>maybe with usage of the oracle-syscontext
0
 

Author Comment

by:Wyverex
ID: 10877242
Ivanov_G wrote:

>>  ((Sender as TDataSetProvider).DataSet as TOracleDataSet).SQL.Clear;
>>  ((Sender as TDataSetProvider).DataSet as TOracleDataSet).SQL.Add( 'select * from table_b' );

*g* I just posted the most important line in the example.
I'm actually clearing the Statement beforehand...


kretzschmar wrote:

>>  thats why i've appended:
>> >maybe with usage of the oracle-syscontext

Hmm, I'm not very experienced with Oracle Internals. Also, the app should not be
too dependent on one kind of database beacuse it could be changed in the future.

But perhaps I have another solution:
1) The client sends some message via the IAppServer interface containing the query
2) The server then creates the view using the query (That should work now since
    it doesn't happen inside any event handler)
3) Now the TOracleDataSet should have th right data and I just need to make
   the TClientDatsSet in the client get the new data
4) I would have to call TClientDataSet.Refresh then, right?

Do you think that will work?
0
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 10877312

   You are making it too complex. Try this:

   procedure ExecuteQuery(SQLText, TargetProvider : OleVariant );

   you send SQLText like SQL statement and TargetProvider as string.
   example
     SQLText = 'SELECT * FROM TABLE_A'
     TargetProvider = 'DataSetProvide1'

  on the client application you have clientdataset connected to DataSetProvider1...
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 10877321
>Do you think that will work?

looks ugly, but can work,yes.

another question,
why do you not have the possibil to append the query-select
within your client-frontend?
0
 

Author Comment

by:Wyverex
ID: 10877392
kretzschmar wrote:

>> why do you not have the possibil to append the query-select
>> within your client-frontend?

Because there will be other types of client-apps connecting to the server
and I want to handle access-restrictions at one place.
Could be very error-prone if I have to implement access rights
in every client program again.
So I do it in the server.

@Ivanov_G

See above, that should answer why I can't/won't send the
complete sql statement from the client. I have to do it in the server.

0
 
LVL 12

Accepted Solution

by:
Lee_Nover earned 350 total points
ID: 10877779
OnBeforeGetRecords ofcourse :-)

a small example showing this:

--------------------------------------

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Grids, DBGrids, DB, Provider, DBClient;

type
  TForm1 = class(TForm)
    DataSource1: TDataSource;
    ClientDataSet1: TClientDataSet;
    DataSetProvider1: TDataSetProvider;
    ClientDataSet2: TClientDataSet;
    ClientDataSet2recid: TAutoIncField;
    ClientDataSet2name: TStringField;
    DBGrid1: TDBGrid;
    procedure FormCreate(Sender: TObject);
    procedure DataSetProvider1BeforeGetRecords(Sender: TObject;
      var OwnerData: OleVariant);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FormCreate(Sender: TObject);
begin
     with ClientDataSet2 do
     begin
       append;
       ClientDataSet2name.AsString:='startup';
       post;
     end;
     ClientDataSet1.Open;
end;

procedure TForm1.DataSetProvider1BeforeGetRecords(Sender: TObject;
  var OwnerData: OleVariant);
begin
     with ClientDataSet2 do
     begin
       edit;
       ClientDataSet2name.AsString:='before get records';
       post;
     end;
end;

end.

--------------------------

object Form1: TForm1
  Left = 270
  Top = 107
  Width = 355
  Height = 174
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  OnCreate = FormCreate
  PixelsPerInch = 96
  TextHeight = 13
  object DBGrid1: TDBGrid
    Left = 8
    Top = 8
    Width = 320
    Height = 120
    DataSource = DataSource1
    TabOrder = 0
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'MS Sans Serif'
    TitleFont.Style = []
  end
  object DataSource1: TDataSource
    DataSet = ClientDataSet1
    Left = 136
    Top = 64
  end
  object ClientDataSet1: TClientDataSet
    Aggregates = <>
    Params = <>
    ProviderName = 'DataSetProvider1'
    Left = 104
    Top = 64
  end
  object DataSetProvider1: TDataSetProvider
    DataSet = ClientDataSet2
    AfterApplyUpdates = DataSetProvider1AfterApplyUpdates
    BeforeGetRecords = DataSetProvider1BeforeGetRecords
    Left = 136
    Top = 16
  end
  object ClientDataSet2: TClientDataSet
    Active = True
    Aggregates = <>
    Params = <>
    Left = 104
    Top = 16
    Data = {
      6C0000009619E0BD0100000018000000020000000000030000006C0005726563
      6964040001000000010007535542545950450200490008004175746F696E6300
      046E616D65010049000000010005574944544802000200200001000C4155544F
      494E4356414C55450400010001000000}
    object ClientDataSet2recid: TAutoIncField
      FieldName = 'recid'
    end
    object ClientDataSet2name: TStringField
      FieldName = 'name'
      Size = 32
    end
  end
end
0
 

Author Comment

by:Wyverex
ID: 10878043
Yes, BeforeGetRecords works.
Thx alot!

Wyverex
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now