Parameters and Update SQLs

[pcz]

Hello

Question about "Update SQLs" and parameters (from "Parameters" tab)

Is it possible to use parameter value from main query SQL code and use it for example inside SQLUpdate?


For example

Code: Select all

SELECT value_1 FROM table_1 WHERE description = :param

And make something similar to

Code: Select all

INSERT INTO table_2(value_2) values (:param)

Trying to write it "just like that" results in
"Not found field corresponding parameter XXX"


Or maybe my way of thinking is wrong and update parameters are totally separate from "basic" parameters?


Thanks for help

[AlexP]

Hello,

Parameter names in SQLInsert, SQLUpdate, etc. must match field names in the main query.

[pcz]

Example based on code from ODAC help ("Master/Detail Relationships")

"...how to bind two TCustomOraDataSet components (TOraQuery, TSmartQuery, TOraTable or even TOraStoredProc) into MD relationship via parameters."

Code: Select all

Master.SQL.Text := 'SELECT * FROM Department';

//...

Detail.SQL.Text := 'SELECT XXXnameXXX FROM Employee WHERE Dept_No = :Dept_No';

// ...

MasterSource.DataSet := Master;
Detail.MasterSource := MasterSource;

Change between original code is that i don't like to "SELECT *"

Code like this works fine

But if I haven't selected field "Dept_No" and want to use "Dept_No" inside "Update SQLs" there is a problem...

I just don't want to select this column at all but use value from "main Params" just because of effectiveness
But it seems that "update Params" are different from "main Params"
And there are all params binded with fields but no params from TOraQuery.Params

So...
1. Am I right and there are different "Params" during update / insert / etc?
2. Is there a way to add this param manually? Or there is some automated solution? Or is it possible to enable using "main Params"?

[AlexP]

Such behavior may be only when using Maser-Detail, since fields are searched for in the master query.

[pcz]

Such behavior may be only when using Maser-Detail, since fields are searched for in the master query.

It was just an easy example that parameter names don't have to match field names in the main query at all.


So there are probably two different and separate "Params", isn't it?
There is no enough explanation in help files - only enigmatic sentence:

Params
Holds the parameters with which the SQL statement will be executed.

Thanks to http://forums.devart.com/viewtopic.php?f=5&t=25859 thread I have found simple solution that is works as I expected

Code: Select all

procedure TForm1.DetailBeforeUpdateExecute(Sender: TDataSet; StatementTypes: TStatementTypes; Params: TDAParams);
var
  Value: Variant;
begin
  if (stUpdate in StatementTypes) and (Sender is TOraQuery) then
  begin
    Value := (Sender as TOraQuery).Params.ParamByName('Dept_No').Value;
    Params.ParamByName('Dept_No').Value := Value;
  end;
end;

but maybe there is more elegant way of doing this??? (or maybe without writing any code?)

[AlexP]

Yes, you can use the BeforeUpdateExecute event to change/set parameters. However, since your INSERT query is not related to SELECT, you can even use a separate component for execution of this INSERT query.

[pcz]

Just to be sure
"Update params" are not strictly related to TOraQuery.Params but are a totally separate "being" which is auto-generated just before insert / update / delete / ..., yes?

However, since your INSERT query is not related to SELECT, you can even use a separate component for execution of this INSERT query.

In my real implementation I have problem with just one of the parameters (all of the other are related ones)
I was wondering if it's possible to avoid fetching constant value to separate column because my table has 50k+ records

[AlexP]

Yes, parameters for these queries are not related to parameters of the main query. Parameter values for these queries are retrieved from corresponding fields of the main query.

[pcz]

Ok
Thank you

[AlexP]

Feel free to contact us if you have any further questions.