Setting TIBDataSet SQL parameters?
Brent Rose
Can anyone supply an example of HOW and WHERE to set parameters for
TIBDataSets SQL properties? ie ones that are NOT supplied from the dataset
itself. I'm wanting to set parameters rather than replacing the (prepared)
SQL statements...(which the TString objects of ModifySQL etc wont allow).
I have tried publishing the QDelete, QInsert, QRefresh, QSelect, QModify
properties and then accessing their respective ParamByName properties, but
I'm still not getting the desired result...(probably getting re-set before
SQL executed?)...
The only other option as far as I can see is to include "dummy" fields in my
SelectSQL for the sole purpose of passing my parameters back in the other
SQL properties... Being able to set the parameters would be easier, of
course.
--
Brent Rose
br...@voyager.co.nz
Craig Stuntz (TeamB)
Brent Rose wrote:
>
> I have tried publishing the QDelete, QInsert, QRefresh, QSelect, QModify
> properties and then accessing their respective ParamByName properties, but
> I'm still not getting the desired result...(probably getting re-set before
> SQL executed?)...
Don't do this. Even if you can get it to work today, it *will* break
in the future.
> The only other option as far as I can see is to include "dummy" fields in my
> SelectSQL for the sole purpose of passing my parameters back in the other
> SQL properties... Being able to set the parameters would be easier, of
> course.
Can you explain why you need different params for Modify, etc., than
for Select? It might help solve the problem.
-Craig
--
Craig Stuntz (TeamB) · Vertex Systems Corp. · Columbus, OH
We're hiring: http://www.vertexsoftware.com/careerops.htm#sd
Delphi/InterBase WebLog: http://delphi.weblogs.com
Brent Rose
s/procs in the xxxSQL properties.
My SelectSQL uses a s/proc to return a "highly processed" dataset (a single
record related to a master/detail structure). When I update this dataset,
the SQL in the xxxSQL
properties needs to write back to this master/detail structure (ie to
multiple tables, together with some additional checking/processing) which
can only be implemented in s/procs. (The fact that I CAN do this is
absolutely fabulous, of course - it opens up a whole arena of powerful
possibilities). It so happens that these s/procs actually require some
additional parameters that are NOT represented by dataset fields...
The easiest solution to this (I thought) would be to be able to set these
parameters independently as required (since they could not and would not be
set on the basis of dataset fields)....hence my question.
The alternative (which seems to be where I need to head at the moment) is to
return some "dummy fields" in the SelectSQL s/proc for the sole purpose of
passing back these extra parameter values to the other xxxSQL properties (ie
I would simple set the field values to set the parameters for the s/proc).
Certainly, I would argue that being able to use s/procs in the xxxSQL
properties is a definite PLUS, and that as a logical extension of this, the
ability to intercept and set parameters (that are NOT related to dataset
fields) would round out the functionality rather nicely! This feature would
by no means be the most "common" usage, of course, so maybe "dummy fields as
required" is best!
Brent Rose
br...@voyager.co.nz
Michael Cervantes
SelectSQL --
Select FirstName, LastName
From Profile
Where UserId = :id
ModifySQL --
Update Profile
Set FirstName = :fname
, LastName = :lname
Where UserId = :id
The code to get the data looks like:
IBDataSet1.Transaction.StartTransaction;
IBDataSet1.ParamByName('id').AsString := SomeUserId;
IBDataSet1.Open;
FirstName := IBDataSet1.FieldByName('FirstName').AsString;
LastName := IBDataSet1.FieldByName('LastName').AsString;
IBDataSet1.Close;
IBDataSet1.Transaction.Commit;
Later, to modify:
IBDataSet1.Transaction.StartTransaction;
IBDataSet1.Edit;
IBDataSet1.ParamByName('id').AsString := SomeUserId; <-- OK
IBDataSet1.ParamByName('fname').AsString := SomeFirstName;
(the last line throws an excetion with the message 'Field "FNAME" not found')
What am I missing, or, what's the preferred method here?
Thanks!
Michael
"Craig Stuntz (TeamB)" wrote:
<snip>
Craig Stuntz (TeamB)
Brent Rose wrote:
>
> The alternative (which seems to be where I need to head at the moment) is to
> return some "dummy fields" in the SelectSQL s/proc for the sole purpose of
> passing back these extra parameter values to the other xxxSQL properties (ie
> I would simple set the field values to set the parameters for the s/proc).
What I wouldn't like about that is if the server has to return extra
fields there's extra network overhead. What about putting the "dummy"
params in the WHERE clause, i.e.:
SELECT
WHATEVER
FROM
EMPLOYEE
WHERE
:DUMMY > 0;
...Then set Dummy to some positive value before Opening the dataset.
You could do it in the BeforeOpen to be certain.
>
> Certainly, I would argue that being able to use s/procs in the xxxSQL
> properties is a definite PLUS, and that as a logical extension of this, the
> ability to intercept and set parameters (that are NOT related to dataset
> fields) would round out the functionality rather nicely! This feature would
> by no means be the most "common" usage, of course, so maybe "dummy fields as
> required" is best!
It would be a good feature, I agree.
Craig Stuntz (TeamB)
Michael Cervantes wrote:
>
> Later, to modify:
>
> IBDataSet1.Transaction.StartTransaction;
> IBDataSet1.Edit;
> IBDataSet1.ParamByName('id').AsString := SomeUserId; <-- OK
> IBDataSet1.ParamByName('fname').AsString := SomeFirstName;
>
> (the last line throws an excetion with the message 'Field "FNAME" not found')
>
> What am I missing, or, what's the preferred method here?
Just make the param names the same as the field names. In your case,
that would be:
ModifySQL --
Update Profile
Set FirstName = :FirstName
, LastName = :LastName
Where UserId = :OLD_UserID
Then user FieldByName instead of ParamByName to set the values.
I recommend reading the documentation on IBX in the InterBase
developer's guide, or just let the Dataset Editor set the values for
you.
HTH,
Brent Rose
> fields there's extra network overhead.
That's what I hoped to avoid.
I think you have misunderstood what I meant by using "dummy fields"....the
SelectSQL needs to RETURN this field so that it can be passed back in, say,
the ModifySQL (it is of no use in the SelectSQL where clause because it is
not accessible to the other xxxSQL properties).
eg SelectSQL:
Select Field1, Field2, DummyField1
From MySelectProc(:Param1, :Param2)
MySelectProc just returns (integer) DummyField1 as "0", say, in all cases.
eg ModifySQL:
execute procedure UpdateMyDataSetProc(:Field1, :Field2, :DummyField1)
Now, all three field "parameters" are set in the normal course TIBDataSet
operation, only "DummyField1" has no meaning beyond providing s/proc
UpdateMyDataSetProc with a necessary parameter (which I set eg BeforePost to
an appropriate value just like any other dataset field). UpdateMyDataSetProc
can then make whatever changes to as many tables and with as much processing
as required....ie the ModifySQL can quite happily insert or delete or
whatever as well as update.
Brent Rose
br...@voyager.co.nz
Michael Cervantes
Thanks, Craig; I appreciate your time.
Michael