Modifying the SQL-Statement (adding index hints)

rbuser

Hi,
using RB7, D6 and MSSQL. I have several reports wich have underlying queries
designed in the query designer. They make use of autoserach criterias.
Everything works fine.

However, I want to fine tune the sql statements that are executed.

e.g. I have this sql executed:
SELECT PERSON.NAME, PERSON.ID FROM PERSON PERSON WHERE PERSONID > 5

I want to add index hints:
SELECT PERSON.NAME, PERSON.ID FROM PERSON PERSON WITH (NOLOCK) WHERE
PERSONID > 5

I managed to get hold of the TdaSQL Object, but fiddling with the aliases
and tablenames is not what I need. Any idea?

Thank you very much!

Bernd

nicocizik

Hi Bernd,

Once you have access to the TdaSQL object, you can use the SQLText property
to change the SQL code manually if you like.

--

rbuser

Does?t this mean, that I loose my auto search fields?

Bernd

nicocizik

Hi Bernd,

No, you should not loose your autosearch fields. You will however loose any
dataview linking you have done in DADE. Unfortunately manually editing the
SQL and linking with DADE are not supported.

--

rbuser

Hi Niko,
this does not work as excpeted. I have a single select statement created
with some auotsearch fields. As soon as I modify the TdaSql.SQLText.Text,
the auotosearch fields produce an AV.

Isn?t there a way to safely replace all ON with WITH (NOLOCK) ON? Maybe when
the queries are opened? I am not going to change any fields, just adding the
index hints....

As an alternative, I?d have to remove all DADE datasources and rebuild them
outside the reports.

Thank you!
Bernd

nicocizik

Hi Bernd,

Sorry, I made a mistake in my last post. When you edit the sql, your sql is
now text and the autosearch criteria is lost. Take a look at the following
example on how to create autosearch fields once you've edited the sql in
DADE.

http://www.digital-metaphors.com/tips/EditSQLAndSearch.zip

--

rbuser

Niko,
this example is not exactly what I want. I already have the autosearch
fields and do not want to recreate them (beware of modifying all reports). I
just want to modify the SQL-Statement prior to executing. Isn?t that
possible?

Maybe another approach would be:
- save the existing autosearch fields
- edit the sql
- recreate the autosearch fields

But this has to be done after the user has entered values in the autosearch
fields. What would be the best place for this to be done?

Thank you,
Bernd

nicocizik

Hi Bernd,

It is definitely on our to-do list to improve on this limitation for the
next release. Unfortunately, in the current release, when you edit the SQL
generated in DADE, you immediately loose all your dataset links and
autosearch fields, so if you need them, you will need to create them
manually in code after the SQL is edited. The method you describe below
could possibly work. I would suggest simply saving the autosearch values
that the user enters, then after you edit the SQL (perhaps in the
OnGetAutoSearchValues event) rebuild the autosearch fields.

--

rbuser

Hi Niko,

I tried this approach:

procedure AddNoLock(aReport: TppReport);
var
i : Integer;
s : string;
aSQL : TdaSQL;
lAutoSearchField: TppAutoSearchField;
list : TList;
begin
list:=TList.Create;
// AutoSearchFields sichern
for i := 0 to aReport.AutoSearchFieldCount-1 do begin // Iterate
lAutoSearchField:=TppAutoSearchField.Create(nil);

lAutoSearchField.AssignSearchValuesFromField(aReport.AutoSearchFields[i]);
list.Add(lAutoSearchField);
end; // for
TSOFReportTools.GetSQLObject(aReport,aSQL);
if Assigned(aSQL) then begin
s:=AnsiUpperCase(aSQL.SQLText.Text);
for i := Length(s) downto 1 do begin // Iterate
if (Copy(s,i,Length(' ON '))=' ON ') then begin
Insert(' WITH (NOLOCK) ',s,i);
end;
end; // for
aSQL.SQLText.Text:=s;
end;
aReport.FreeAutoSearchFields;
TSOFReportTools.SetSQLObject(rbReport,s);
// AutoSearchFields zur?csichern
for i := 0 to list.Count-1 do begin // Iterate

lAutoSearchField:=aReport.CreateAutoSearchField(TppAutoSearchField(list[i]).
TableName,TppAutoSearchField(list[i]).

FieldName,TppAutoSearchField(list[i]).FieldAlias,

TppAutoSearchField(list[i]).DataType,TppAutoSearchField(list[i]).

SearchOperator,TppAutoSearchField(list[i]).FormattedExpression,

TppAutoSearchField(list[i]).Mandatory);

lAutoSearchField.AssignSearchValuesFromField(TppAutoSearchField(list[i]));
end; // for
FreeAndNil(list);
end;
This works ok, however, I have no access to the Datasources in RAP. I used
to call some functions in the RAP accessing data like this: CUSTOMER['NAME']
Editing the Sql-Text seems to remove them too. How can I restore this or how
can I access the data then from RAP?

TIA
Bernd

nicocizik

Hi Bernd,

Were are you trying to access your datasets in RAP. A number of things
could be going wrong here. First the timing of when you try to access the
datasets could be off if you already altered them manually in Delphi code.
Second, you may have freed a reference to your datasets when manually
editing the SQL text before trying to edit them in RAP. You may need to
play around with the timing a bit to get the result you are after.

Moving back, take a look at the following example on the best way to modify
the DADE SQL prior to execution...

----------------------------------------------------------
Tech Tip: Modify DADE SQL prior to execution
----------------------------------------------------------

1. Use the DataDictionary to limit which tables and fields are available to
end-users.

2. To limit specific table records that are accessible requires that you
modify the SQL statement submitted to the database. The best approach is to
customize the DADE plug-in so that you can modify the SQLText that is
assigned to the Query object. That way it is transparent to the end-user.

The flow looks something like this.


Query Tools --> TdaSQL object --> SQL Text --> Query DataSet


1. The end-user uses the query tools to define a SQL query.

2. The TdaSQL object maintains an object based description of the SQL.

3. The TdaSQL object generates SQL Text

4. The SQL Text is assigned to a SQL based TDataSet descendant. This last
step is handled by the DADE plug-in. For the BDE, TQuery is used, for ADO,
TADOQuery is used, etc.

As an example, have a look at the DADE plug-in for ADO. Open
RBuilder\Source\daADO.pas and search for the TdaADOQueryDataView.SQLChanged
method. You can modify the DADE plug-in code directly or create your own
descendants. The registration appears in the initialization section at the
bottom of the unit.


example:

{assign the connection object}
FQuery.Connection := TADOConnection(lDatabase);

{assign the SQL Text}
FQuery.SQL := SQL.MagicSQLText;

{add custom code here to modify the Query.SQL}

--

rbuser

Hi Niko,

this is exactly what I wanted to know! Many thanks!


TdaADOQueryDataView.SQLChanged

One last question:

As I do not want to modify RB-source code, can I safely call
daUnRegisterDataView(TdaADOQueryDataView);
and register my own TdaMyADOQueryDataView? Will the ADOSession use my plugin
automatically?
(BTW: What happens if two ADOQueryDataViews are registered?)

Thank you,
Bernd

nicocizik

Hi Bernd,

If you leave daADO out of your uses clause, it will not get registered in
the first place, so if you add a custom plugin, it will be the only one
registered. I believe that if you try to register two ADO plugins of the
same name, Delphi will keep the first one. If you want to use both plugins
in the same app, you will need to unregister the old one before registering
the new one.

--

rbuser

I see - ok. Many thanks,

Bernd