PHP ODBC query does not execute

Question

I have an older site that uses an odbc connection. For some reason, the query is now failing.

I created a test page that calls the same database connection script I've been using:

<?php
  $db_host = "MAINHOST";
  $db_user = "MAINUSER";
  $db_pass = "MAINPASS"; 
  $db_odbc = "MAINDB";

  $connect = odbc_connect($db_odbc, $db_user, $db_pass) or die ("Could not connect to Oracle");
?>

I can test this page and verify there is indeed a good connection.

Here is a sample query:

<?php
include("include/database.php"); 

if($connect)  // just added this. figured I'd make sure the connection is good before proceeding
{
  $queryMain = "SELECT a_bunch_of_records FROM a_table WHERE a_bunch_of_where_clauses";
  $resultMain = odbc_exec($connect, $queryMain);

  if($resultMain)
  {
    echo "query successful";
  } 
  else
  {
    echo "query failed " . odbc_error();
  }
}
else
{
   echo "no connection";
}
?>

I was originally getting a 500 error in the Chrome console before I moved it over to a test page. Now I am getting a "this page is not working" error. It's not spitting out the odbc_error.

I can echo out the query and run it in a PL/SQL window, and it successfully returns data.

Can anyone see the problem? I'm not sure if there's an issue with the server itself. I am perplexed.

* UPDATE *

I am noticing that sometimes the query runs, sometimes it doesn't. Perhaps maybe the connection to the server is choppy. Unsure.

Check apache (or other webserver) error log files to know the actual, specific issue. By default, PHP raises and routes messages there. Do not rely on just the browser result. — Parfait, Oct 18 '18 at 15:13
@Parfait - Ok, I might be getting somewhere. I checked the error log and found this error: "PHP Fatal error: Maximum execution time of 30 seconds exceeded". Thoughts? — John Beasley, Oct 18 '18 at 15:23
Does query run long? We cannot tell with *a_bunch_of_where_clauses*. See this [thread](https://stackoverflow.com/q/5164930/1422451). — Parfait, Oct 18 '18 at 15:26
@Parfait - Yes, the query does take a while to run. I have to update the php.ini file? — John Beasley, Oct 18 '18 at 15:28
Update php.ini for global, permanent change or in the code for temporary changes as the top two accepted answers indicate. Otherwise, optimize your query to run under 30 secs. — Parfait, Oct 18 '18 at 15:30
@Parfait - I think that did the trick. If you create an answer, I will mark that it solved my problem. Thank you. — John Beasley, Oct 18 '18 at 16:17

score 1 · Accepted Answer · answered Oct 18 '18 at 16:44

Since log file indicates maximum time execution exceeded, consider several options:

Optimize Oracle SQL query to run faster than default 30 seconds which can involve:
- Investigating query execution plan with EXPLAIN PLAN FOR SELECT ...
- Adding indexes to table to speed up scans especially for fields in WHERE clause
- Using partitions for very large tables on subsets regularly queried
- Using a materialized view to cache expensive queries and call it in PHP
- Adjust data storage with normalization to avoid redundancy and inefficient query processing
Temporarily extend the default PHP setting during code execution with:
```
ini_set('max_execution_time', <SOME NUMBER>);
```
Permanently extend the global PHP setting in php.ini file (then re-start sever to propagate changes):
```
max_execution_time = <SOME NUMBER>
```

PHP ODBC query does not execute

1 Answers1