When I run the following SQL it works fine:

SELECT E.ID, E.Name, E.Position
FROM employee E
WHERE NOT E.ID IN (SELECT employeeID FROM sessiondetails WHERE sessionID IN (SELECT ID FROM session WHERE topicID= :topicID))
ORDER BY E.Position, E.name

When I change the parameter and reopen the query it returns the first result.  Unprepare doesn't seem to help.  I have had to resort to replacing the parameter string in the query and that work fine.  Is there an error in my logic or expectations?

Greg,

Try:

SELECT E.ID, E.Name, E.Position
FROM employee E
WHERE E.ID NOT IN (SELECT employeeID FROM sessiondetails WHERE sessionID IN (SELECT ID FROM session WHERE topicID= :topicID))
ORDER BY E.Position, E.name

--
Fernando Dias
[Team Elevate]

Greg


Without your tables I can't try and reproduce. What it sounds like is that the parameter is not being replaced. Can you try running in EDBManager where you can see the parameters. If it works there its something you're doing and "all" we have to do is figure out what, if it doesn't it needs reporting to Tim as a bug.

Roy Lambert

Fernando, that was the original syntax that I had used so it is very well tested with the same results.

Roy, the error does exist in DBManager.  the first query works fine but any subsequent query after a parameter change will provide the results of the first query again.  The unprepare button does correct the situation and provide the correct results.  I will have to check the use of prepare/unprepare in DBManager to the tests that I had done on the same subject as we got different results.  Regardless having to unprepare kind of defeats the purpose of using parameters doesn't it?

Hi Greg,

There was an issue back in 2.31 that behaved like this.  ( https://www.elevatesoft.com/incident?action=viewaddr&category=edb&release=2.35&incident=4767  ).  Any chance you are running 2.31?

Terry Swiers wrote:

Hi Greg,

There was an issue back in 2.31 that behaved like this.  ( https://www.elevatesoft.com/incident?action=viewaddr&category=edb&release=2.35&incident=4767  ).  Any chance you are running 2.31?

Server is 2.34 Build 1
Client is 2.31 Build 10

I will upgrade and recheck.  That's embarrassing . . .

Version 236 Build 1 still has the issue in DB Manager

Hi Greg,

I can reproduce this.  

Have you reported this?  If not, I can get a reproducible test case set up and get it sent in.

Hi Greg,

I went ahead and report this as I was able to get a test case created rather easily.

Thanks Terry, it would have been difficult for me to separate the tables I was using into a test case.