SQL Statement doesn't return rows

jdorlon · Post by **jdorlon** » Thu 19 Feb 2009 21:03

Hello,

I am in the process of upgrading to Dephi 2009 from Delphi 7. My ODAC version is 6.70.

I have some code that executes a pl/sql block in a TSmartQuery. When I do, I am seeing the error "SQL statement doesn't return rows". I know that I can solve this by switching to a TOraSQL, but this worked in the last version under Delphi 7 and I don't want to try to find all the places in my code that run pl/sql blocks in TSmartQueries. Is there some option in ODAC I can set so this error will not be raised?

Thanks.

jfudickar · Post by **jfudickar** » Fri 20 Feb 2009 00:06

Did you have a sample of the pl/sql code?

Plash · Post by **Plash** » Fri 20 Feb 2009 08:59

We could not reproduce the problem. Maybe the problem occurs because you call the Open method instead of the Execute. Or maybe it is caused by some options of the TSmartQuery that you have changed.

jdorlon · Post by **jdorlon** » Fri 20 Feb 2009 14:44

No, I am not using smartquery.open, I am calling .execute. I don't think it is an option I changed either because I tried to reproduce it in a sample app and it works fine there.

It happens in something as simple as " Grant select on X to Y". When I step through the code in my app, IsQuery is evaluating to true in the next line and that's what causes the error. (This is in procedure TCustomDADataSet.Execute; in the DBAccess unit)

if IsQuery then begin
Open;

I haven't figured out why IsQuery is evaluating to true though.

jdorlon · Post by **jdorlon** » Fri 20 Feb 2009 15:43

The error happens because in our copy of OraClasses, we commented out the "FSQLType := SQL_UNKNOWN" at the end of TOCICommand.Finish.

We commented this out so we could tell what type of command was executed, do display it to the user.

So, now the question is - how can he know the SQLType without causing the "SQL Statement doesn't return any rows" error.???

Thanks!

jdorlon · Post by **jdorlon** » Fri 20 Feb 2009 16:54

A few last comments (and a solution!)

To reproduce the problem:
1) comment out that line I mentioned in OraClasses
2) run a select statement in TSmartQeury
3) run a non-select statement in the same TSmartQuery

It seems that when we get to the "If IsQuery then" line in TCustomDADataSet.Execute, it evaluates to true because the previous statement was a SELECT (and FSQLTYPE hasn't been cleared out from the previous SQL statement).

So, if we set Options.AutoPrepare = true, the problem goes away because IsQuery now evaluates as it should. If there is a better solution, please let me know.

Plash · Post by **Plash** » Mon 23 Feb 2009 09:06

We have received another request about the SQLType property. So we have already fixed it. In the next ODAC build the SQLType property will work, and you will not need to comment the line in the OraClasses unit.