I want to upgrade my web server to newest Ubuntu version and hence also upgrade php5 to php7. I completed the installation and configuration of a VM running lubuntu 16.04 to test things, to see if code changes are needed and also test if it works in general.
In the first application I face a strange issue with oracle (oci8). The application uses server-side paging and also displays stuff like "Total Records" and "Total records (filtered)". Of course these "metrics" are based on simple count queries and they work and display the correct result. However the select statement to get the actually data returns 0 hits. Especially puzzling because it uses the exact same query:
$sQueryInner = "SELECT id, ROW_NUMBER() OVER ($sOrderByClause) R
FROM my_table " . $sWhereClause;
$sQueryFinal = "SELECT id FROM
(" . $sQueryInner . ")
WHERE R BETWEEN :startIndex and :endIndex";
// Total data set length after applying where
$sQueryFilteredCount = "SELECT COUNT(*) as \"totalRowsCount\" FROM (" . $sQueryInner . ")";
Only difference is the ROW_NUMBER() clause but I don't see how that affects things that no results are returned. I also get no error messages or php warnings. oci_fetch_array immediately returns false meaning no further rows found.
while ($row = oci_fetch_array($statementFinal, OCI_ASSOC + OCI_RETURN_NULLS)) {//...
I wanted to find a way to debug oci8 itself. To see the actual SQL sent. But oci_internal_debug seems to be disabled since php5.6.
So I'm pretty lost. Any ideas what could cause this and how I could further debug/search for why it is caused?
EDIT:
Wit wireshark I can actually see the SQL sent to the DB and it's correct. Problem is no rows are returned. Only conclusion is that parameter binding doesn't work properly and hence no results are returned. I don't get it however as the exact same thing works on ubuntu 14.04. Only difference is php7. All other applications also work so the issue is with this specific query. It has a subselect and Row_number() function.
This is another WTF of php. Def. considering rewrite in other language...Seriously php guys, get a grip...
They changed something fundamental which impacts oci_bind_by_name but don't mentioned it anywhere.
The issue can be fixed by reordering your code. Meaning you have to do it in this order if you bind your statements in a function call:
and so forth. If you first bind all statements and then execute it, a later call to bind will overwrite previous value even if the parameter has a completely different name and type. WTF?