PHP PDO ODBC unexpected empty result set

Question

I am trying to track down a problem with using PDO via an ODBC connection to a SQL Server database where I am getting an empty result set for a known good query. I would appreciate any guidance from the community. This is part of a large system that I have been working on for about five years; it takes an XML representation of a report, generates SQL from it, runs the query, formats the result set as requested, and generates a web page for presentation. More than you probably needed to know, but I am trying to convey that I understand much of how this is supposed to work and in most cases it works reliably. But I have a customer who wanted something new, and it broke my system.

I refer to this as a known good query in the sense that I can copy and paste the query from my log file into SSMS (SQL Server console) and run it. It yields 62 rows of results. But when I run the same query through PDO, I get a PDOStatement back, no errorInfo(), no exceptions thrown, and so on. But fetchAll() returns an empty array. I was originally using query(), but it seemed safer to use prepare() and execute() in case there was something I was missing in the query. It made no difference.

I realize that there can be type conversion issues, but in the example below, the two retrieved fields are of type nvarchar(128) and nvarchar(32), respectively, which return successfully with other queries.

I should mention that the query is executed exactly once in the app, so it's not a matter of some previous execution interfering with the next one, as far as I can tell. Also, the PDO object has setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);

Here's the PDOStatement returned by execute():

Result Set PDOStatement Object
(
    [queryString] => SELECT [dbo].[Supplier].[SupplierName] AS suppliername,[dbo].[Item].[ItemLookupCode] AS itemlookupcode FROM [dbo].[Order] LEFT JOIN [dbo].[OrderEntry] ON [dbo].[Order].ID=[dbo].[OrderEntry].OrderID LEFT JOIN [dbo].[Item] ON [dbo].[Item].ID=[dbo].[OrderEntry].ItemID,[dbo].[Supplier] WHERE ([dbo].[Order].Time >= '2015-01-01 00:00:00') AND ([dbo].[Order].Time <= '2015-03-31 23:59:59') AND ([dbo].[Item].SupplierID=[dbo].[Supplier].ID) ORDER BY [dbo].[Supplier].[SupplierName]
)

It's not that complex, and other SQL queries work fine against this database. There's just something about this one that fails via PDO, but works inside SSMS.

Any ideas? Has anyone seen this behavior before? Is there some other way to see what's going on here? I have looked at several questions on this theme, but they all seemed to have something wrong that I am not doing.

PHP 5.4.22, by the way.

Answer 1

After breaking your query down into a format such as I am using below, I noticed that you were mixing explicit joins (LEFT JOIN, INNER JOIN, etc) and implicit joins (FROM table1, table2). This is not only considered a very bad practice, but has been known to cause unusual and unexpected query responses on occasion. So, looking at the implicit logic of your joins, I have rewritten the query as below:

SELECT 
    [dbo].[Supplier].[SupplierName] AS suppliername,
    [dbo].[Item].[ItemLookupCode] AS itemlookupcode 
FROM [dbo].[Order] 
INNER JOIN [dbo].[OrderEntry] 
    ON [dbo].[Order].ID=[dbo].[OrderEntry].OrderID 
INNER JOIN [dbo].[Item] 
    ON [dbo].[Item].ID=[dbo].[OrderEntry].ItemID
INNER JOIN [dbo].[Supplier]
    ON [dbo].[Item].SupplierID=[dbo].[Supplier].ID
WHERE ([dbo].[Order].Time >= '2015-01-01 00:00:00') 
    AND ([dbo].[Order].Time <= '2015-03-31 23:59:59') 
ORDER BY [dbo].[Supplier].[SupplierName]

I changed the LEFT JOINs in your query to INNER JOINs, because the [Item].SupplierID and [Supplier].ID had to match in your original query (and thus, to exist, since equals will not return a TRUE value if either or both values are NULL.) Thus, the OrderEntry value also had to exist for a valid response to return. If the row must exist for valid data to be returned, you should always use an INNER JOIN - it simplifies internal logic and can often result in faster query responses.

I realize this is an old question at this point, but a good answer is never wasted.

Answer 2

I don't disagree with your point. If I was hand-crafting SQL queries, they would come out much like yours.

But the context here is diffferent. In this system (http://www.calast.com/DynaCRUX.html), there is an abstraction of the database tables and their relationships, expressed in XML. And, there is an abstraction of the desired report, also in XML. The app that creates SQL has to deal with the inputs it is given. Sometimes there is enough information to generate "good" SQL like you and I would write. And sometimes there isn't, leaving the system to do the best it can. The fallback is at times pre-ANSI join syntax.

Also, I did point out that (ugly as we might think it), the generated query (1) is legal T-SQL, and (2) produced the output the customer wanted, when run inside SSMS. The problem was never in the query, as it turns out. It was just a configuration bug in my system, so I need to close this question out.

That said, I have recently rewritten the SQL generation engine to use a different approach that produces queries that are much more reasonable. That is, ones that look as you and I would write them.

Your answer is a good one, and I suspect it will help others write better queries.