I have the following query:

SELECT ID, Name, Abbreviation FROM
 Analyte
WHERE ID NOT IN
 (SELECT AnalyteID FROM AnalyteToTestMethod
 WHERE TestMethodID = '{D2458D68-4308-46A7-B560-1931C93AB456}')

When I execute this in a TEDBQuery component, it works as expected.  Next, I
revised the query to include a parameter (which is a GUID field in another
DataSet) like this:

SELECT ID, Name, Abbreviation FROM
 Analyte
WHERE ID NOT IN
 (SELECT AnalyteID FROM AnalyteToTestMethod
 WHERE TestMethodID = :ID)

If I execute the query with the parameter, my application freezes.

I'm not sure if this is EDB specific or not, but any thoughts on how to get
the parameter to work properly would be appreciated.

I'm using Delphi 7.1 Pro with ElevateDB 1, Build 1

Greg


How are you setting the parameter?

Roy Lambert

I am setting it via the DataSource property of the TEDBQuery component.  The
DataSource I'm using has an ID field.

Greg,

<< When I execute this in a TEDBQuery component, it works as expected.
Next, I revised the query to include a parameter (which is a GUID field in
another DataSet) like this:

SELECT ID, Name, Abbreviation FROM
 Analyte
WHERE ID NOT IN
 (SELECT AnalyteID FROM AnalyteToTestMethod
 WHERE TestMethodID = :ID)

If I execute the query with the parameter, my application freezes. >>

Got it.  It's a hard loop caused by parameters in sub-queries.  Build 2 will
fix it.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

I'm not sure if this is quite fixed yet. The following SQL hangs the ide when active is set to true (:tree_id is set from a datasource attached to a TEDBTable) ...

SELECT * FROM
TreeStructures JOIN TreeBoxes
ON TreeStructures.box_id = TreeBoxes.box_id
WHERE TreeStructures.tree_id = :tree_id

I've checked creation order (for the TEDBTable and the TDataSource) and set an initial value for the parameter as suggested elsewhere. "tree_id" in the master table is autoinc and integer in "TreeStructures". Version is 1.05. A problem with
the JOIN?

Peter

Peter,

<< I'm not sure if this is quite fixed yet. The following SQL hangs the ide
when active is set to true (:tree_id is set from a datasource attached to a
TEDBTable) ...

SELECT * FROM
TreeStructures JOIN TreeBoxes
ON TreeStructures.box_id = TreeBoxes.box_id
WHERE TreeStructures.tree_id = :tree_id

I've checked creation order (for the TEDBTable and the TDataSource) and set
an initial value for the parameter as suggested elsewhere. "tree_id" in the
master table is autoinc and integer in "TreeStructures". Version is 1.05. A
problem with the JOIN? >>

There are two separate issues that are outstanding with 1.05 that could
cause this issue, one with parameters and one with non-sensitive query
result sets (which this is).   Is the application flat out hanging, or is
the CPU still being consumed for the application ?

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

<< There are two separate issues that are outstanding with 1.05 that could
cause this issue, one with parameters and one with non-sensitive query
result sets (which this is).   Is the application flat out hanging, or is
the CPU still being consumed for the application ? >>

It's away with the fairies - flat out. I have to close the IDE through task manager.

--
Tim Young
Elevate Software
www.elevatesoft.com

Peter,

<< It's away with the fairies - flat out. I have to close the IDE through
task manager. >>

So, it is using the CPU ?  I'm assuming that "flat out" means that, but
just checking.

--
Tim Young
Elevate Software
www.elevatesoft.com

No sorry. I should have been clearer. There doesn't appear to be any significant CPU activity due to the query ...

Peter,

<< No sorry. I should have been clearer. There doesn't appear to be any
significant CPU activity due to the query ... >>

Hmmm, it still sounds like the same issue with parameterized execution that
has already been fixed, but if you'd like to send me the query and tables I
can definitely confirm this before 1.06 is released.

--
Tim Young
Elevate Software
www.elevatesoft.com