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
– Parfait, Commented Oct 18, 2018 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
– John Beasley, Commented Oct 18, 2018 at 15:23
Does query run long? We cannot tell with a_bunch_of_where_clauses. See this thread. — Parfait
– Parfait, Commented Oct 18, 2018 at 15:26
@Parfait - Yes, the query does take a while to run. I have to update the php.ini file? — John Beasley
– John Beasley, Commented Oct 18, 2018 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
– Parfait, Commented Oct 18, 2018 at 15:30

Parfait · Accepted Answer · 2018-10-18 16:44:17Z

1

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>
```

answered Oct 18, 2018 at 16:44

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP ODBC query does not execute

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related