No tuples available at this result index in […]

Recently I was creating a simple PHP webpage that required an MSSQL connection for a query. I installed the freetds-0.91-15 driver on my Oracle Linux and configured unixODBC-2.2.11-10.el5 appropriately. All went well and I soon had the first results on my page. My next task was to create a simple enough query that would ‘count’ some database rows. Simple enough, right? Well, at least up to the part where I ran into this error:

No tuples available at this result index in […]

All the posts I found referred to multiple result sets in which ‘odbc_next_result($result);’ should be the resolution. The weird part was, pasting of the echoed $sql into the MSSQL query box would give me the desired effect. One result with an count of the rows. The same SQL in the PHP script would give me the ‘no tuples’ error. I nearly fixed it the ugly way (counting the rows in an php while that would work) when I had an insight.

In the mssql output i noticed NULL fields in the table column that I was counting. So i figured, might counting NULL values somehow trigger the ‘No Tuples’ error im getting?

So this is what I ended up testing:

SELECT COUNT([sdk].[Events].[FirstName]) as Counted FROM [sdk].[Events]
WHERE [sdk].[Events].[PeripheralName] like '%Search%'
AND [sdk].[Events].[EventTime] BETWEEN '1' AND '31'

Which resulted in : Warning: odbc_fetch_array(): No tuples available at this result index in /var/www/db.class.php on line 52

SELECT COUNT([sdk].[Events].[FirstName]) as Counted FROM [sdk].[Events]
WHERE [sdk].[Events].[PeripheralName] like '%Search%'
AND [sdk].[Events].[EventTime] BETWEEN '1' AND '31'
AND [sdk].[Events].[FirstName] is not null

Which resulted in an working count query.

About Chris Gralike

Momenteel ben ik manager van de afdeling business continuity bij de zakelijke IT dienstverlener AMIS Services BV. Sinds 2003 ben ik actief in de ICT branche. Tussen 2003 en nu heb ik verschillende rollen vervuld. In de rollen: systeem- en netwerkbeheer, system engineer, servicemanager en nu practice manager ben ik in contact gekomen met uiteenlopende technologieën, methodologieën, ideeën, oplossingen en innovaties. Een rijke ervaring waarmee ik de klanten van Conclusion en AMIS elke dag probeer te ondersteunen. Mijn credo: 'Altijd opzoek een win-win tussen business en technologie.'

Posted on January 28, 2015, in PHP and tagged , , , , , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: