EDBEngine with message 'Could not find Object'
Christian Bédard
I try this SQL in a TQuery:
// Query1.SQL :
// SELECT * FROM Table1
// WHERE (Name1 IN
// (SELECT Name2 FROM Table2
// WHERE
// (Name2b IN (SELECT Name3 FROM Table3
// WHERE
// ( Name3b = :Paramtest) )) ));
Query1.ParamByName('Paramtest').value := 'TEST1';
Query1.open;
a raised exception EDBEngine with message ‘Could not find Object’.
But I have the (Paramtest : TParam) correctly define in the
TQuery.Params
If I try the same SQL without the param like (Name3b = “TEST1”) every
thing is ok.
What is the problem with my TParam (:Paramtest) ??
Thank
Christian
Olivier
--
Olivier Dahan
oda...@cybercable.fr
Delphi 32b C/S certified engineer
Christian Bédard <christia...@videotron.ca> a écrit dans le message :
372BD304...@videotron.ca...
Christian Bédard
without the parameters or without inner SELECT the request work fine
Example :
///////////// TEST 1 //////////////////
// Exemple with parameter, OK
// Query1.SQL :
// SELECT * FROM Table1
// WHERE (Name1 = :ParamTest)
Query1.ParamByName('Paramtest').value := 'ExempleName';
Query1.open;
// Query is OK
///////////// TEST 2 //////////////////
// Exemple with inner SELECT without parameter, OK
// Query1.SQL :
// SELECT * FROM Table1
// WHERE (Name1 IN
// (SELECT Name2 FROM Table2
// WHERE
// (Name2b IN (SELECT Name3 FROM Table3
// WHERE
// ( Name3b = "ExempleName") )) ));
Query1.open;
// Query is OK
///////////// TEST 3 //////////////////
// Exemple with inner SELECT and parameter PROBLEM !!!
// Query1.SQL :
// SELECT * FROM Table1
// WHERE (Name1 IN
// (SELECT Name2 FROM Table2
// WHERE
// (Name2b IN (SELECT Name3 FROM Table3
// WHERE
// ( Name3b = :Paramtest) )) ));
Query1.ParamByName('Paramtest').value := 'ExempleName';
Query1.open;
// raise exception EDBEngineError with message 'Could not find object'
Thank
Christian
Christian Bédard
EDBEngineError exception
I think 2 inner SELECT don't work with the TQuery.
I make 3 Tables in Paradox 7.0
Table 1:
-Name1 : string;
-KeyName : string
-----------------------
Table 2:
-Name2 : string;
-refName1 : string
-----------------------
Table 3:
-Name3 : string;
-refName2 : string
-------------------------
I use this example procedure:
Procedure QueryMultiSelect;
begin
Query1.prepare;
Query1.ParamByName('ParamTest').value := 'Example';
Query.open;
end;
------------Test A (Error)-------------
Query1.SQL :
SELECT Name3 FROM Table3
WHERE ( refName2 IN
( SELECT Name2 FROM Table2
WHERE ( refName1 IN
( SELECT Name1 FROM Table1
WHERE ( KeyName = :ParamTest) )));
Raised exception EDBEngineError with message ‘Could not find object’
---------------------------------------------------------------------------------------
------------Test B (1 inner SELECT)-------------
Query1.SQL :
SELECT Name2 FROM Table2
WHERE ( refName1 IN
( SELECT Name1 FROM Table1
WHERE ( KeyName = :ParamTest) ));
Query1 running OK
---------------------------------------------------------
------------Test C (2 inner SELECT without param)-------------
Query1.SQL :
SELECT Name3 FROM Table3
WHERE ( refName2 IN
( SELECT Name2 FROM Table2
WHERE ( refName1 IN
( SELECT Name1 FROM Table1
WHERE ( KeyName = “Exemple”) )));
Query1 running OK
Where is the problem !!???
Thanks in advance for helping my mind
Christian
Brian Bushay TeamB
>I juste assign the DataBaseName to DBDEMOS
>and the Datatype to ftInteger for the parameter ParamTest.
>Good chance for finding the key for this problem !
Sounds like you found a bug to me. Assuming you are using Delphi 4 updated to
patch 3 I suggest you put together a small example and then submit a bug report.
http://www.borland.com/devsupport/bugs/bug_reports.html
--
Brian Bushay (TeamB)
Bbu...@NMPLS.com
Steve Koterski
<christia...@videotron.ca> wrote:
>I give more information about the problem of inner SELECT raising a
>EDBEngineError exception
>I think 2 inner SELECT don't work with the TQuery.
>
>I make 3 Tables in Paradox 7.0
It does appear that either the local SQL parser or the local SQL engine is
erroneous missing that parameter buried deep in that query. The result is
an EDBEngineError (originating at the BDE, not Delphi) with the message
"Cannot find object." I tested this under BDE 5.x using some sample Paradox
tables in the statement below:
SELECT C.CustNo, C.Company
FROM Customer C
WHERE C.CustNo IN
(SELECT O.CustNo
FROM Orders O
WHERE O.OrderNo IN
(SELECT I.OrderNo
FROM Items I
WHERE I.PartNo = :PartParam))
Since this would appear to be incorrect behavior, I would advise you post a
problem report on the Delphi bug-reporting Web page. In the meantime, here
are a couple suggestions (workarounds).
One alternative might be to use an inner join instead of the nested
correlated queries you are now using. This alternative has the added
advantage of being a whole heckuva lot faster in execution that a query
with nested SELECT statements referenced with the IN predicate. For
instance, you query might look more like the following:
SELECT Name3
FROM Table3 T3
INNER JOIN Table2 T2
ON (T3.RefName2 = T2.Name2)
INNER JOIN Table1 T1
ON (T2.refName1 = T1.Name1)
WHERE (T1.KeyName = :ParamTest)
The other suggestion would be to use the properties and methods of string
list objects (of which TQuery.SQL is one) to modify that one line of the
query statement and put a literal where the parameter now is. Assume a
starting statement of:
SELECT Name3 FROM Table3
WHERE ( refName2 IN
( SELECT Name2 FROM Table2
WHERE ( refName1 IN
( SELECT Name1 FROM Table1
WHERE ( KeyName = :ParamTest) )))
The line to be changed is the 6th, so an index of 7 would be used with the
TQuery.SQL property. Assign a new String value with the literal value
embedded in it.
with Query1 do begin
Close;
SQL[7] := ' WHERE ( KeyName = "' + variable + '") )))';
Open;
end;
//////////////////////////////////////////////////////////////////////////
Steve Koterski "My problem lies in reconciling my gross
Technical Publications habits with my net income."
INPRISE Corporation -- Errol Flynn (1909-1959)
http://www.borland.com/delphi