#13 Problem with multi-join dynamic SQL in TMDOQuery

[Elias Sabbagh]

Using Delphi 2009, and a recent CVS update of MDO that handles Unicode strings...

Is there a known problem with setting the TMDOQuery.SQL property to be a SELECT statement containing multiple JOINs? What if the middle join is a selectable stored procedure? I am seeing strange behavior with such a SELECT statement, where the query only returns one record. When I perform the same query on a Firebird database with, say, the FlameRobin tool, everything works, and I see the number of records I expect. One potential problem area might seem to be that the selectable stored procedure performs some manipulations on a global temporary table, which is a newer feature in Firebird, but I have been cautious to load the temporary table and perform my query in the same transaction. I am very confident that there is something curious happening in the TMDOQuery object.

Thanks for your help!

[Elias Sabbagh]

Additional info: The same behavior occurs whether or not the selectable stored procedure references a global temporary table or a regular table, so that avenue is a dead end.

Also, I debugged the code, and it seems to be doing a correct sequence of API calls. The SQL type is SQLSelect, as it should be. The number of records returned is always 1, however.

[Elias Sabbagh]

I have discovered a workaround. I believe that the TMDOQuery was always working, as it turns out. I had been using some TMDOSQL statements to perform inserts into the temporary table I had mentioned above. When I replaced the simple TMDOSQL component with a larger TMDODataSet, and included SelectSQL, DeleteSQL, and RefreshSQL, and then opened it and Inserted and Posted the records, the later TMDOQuery worked without a hitch, and displayed all of the records! Clearly the TMDOQuery does not see records inserted by TMDOSQL statements in the same transaction without a commit first occurring, but it is able to see records inserted by TMDODataSets without having to do the commit. Now that I know this, I can consider this issue resolved to my satisfaction...