CODEWITHSUNDEEP
phporacle-databaseodbc
John Beasley
John Beasley

Reputation: 3075

PHP ODBC query does not execute

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.

Upvotes: 0

Views: 493

Answers (1)

Parfait
Parfait

Reputation: 107567

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

  1. 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

  2. Temporarily extend the default PHP setting during code execution with:

    ini_set('max_execution_time', <SOME NUMBER>);

  3. Permanently extend the global PHP setting in php.ini file (then re-start sever to propagate changes):

    max_execution_time = <SOME NUMBER>

Upvotes: 1

Related Questions