PHPLIB and Oracle 8.0 - Won't Work

Anon

Dear All

Does anyone have any experience of using PHPLIB with oracle 8.0 using db_oci8.inc? I can't get it to work and after much testing still don't understand why.

I have the following questions, which relate to the code etc shown below.

Why do I get the Oracle error message in the first instance?

Why do I get no output from the second instance? Looking at the code in db_oci8.inc the class only appears to parse the statement not actuallly do anything with it.

Why does adding the next_record() statement cause everything to life? I notice that having done this the table verify_db() function then returns 1 instead of 0.

It appears there is some step missing in the test page but I don't understand what it is.

I would be grateful for any help you could provide.

Many thanks

Paul

I have installed php with the necessary oci components and can connect to the remote oracle database fine. I have tried the following code (from the phplib manual):

<?php

print "Check the variables are initialised";
print "";

print "This is the Database $db->Database ";

$db = new DB_Example;
print "It works without error messages. \n";
print "This is the Database $db->Database, ";
print "With user $db->User, ";
print "Password $db->Password, ";
print "and Link_ID $db->Link_ID";
print "";

include("table.inc"); // requires include_path to be functioning

print "Execute the Database Query Command (db query) ";
$db->query("select * from work_reasons");
print "Now the Link_ID is $db->Link_ID";
print "";

// print "Print the next record (db next_record()) ";
// print $db->next_record() . " ";

print "Create the new table object ";
$t = new Table;
$t->heading = "on";
print "Execute show result method (tab show_result()) ";
$t->show_result($db);
?>

This produces the following output the first time:

Check the variables are initialised
This is the Database
It works without error messages.
This is the Database fred, With user fred, Password fredpwd, and Link_ID 0

Execute the Database Query Command (db query)

Connecting to edm...

Obtained the Link_ID: Resource id #1

Warning: OCIStmtExecute: ORA-03113: end-of-file on communication channel in /usr/local/apache/php/db_oci8.inc on line 69
Debug: query = select * from work_reasons

ORA-03113: end-of-file on communication channel
Query :"select * from work_reasons"Now the Link_ID is Resource id #1

Create the new table object
Execute show result method (tab show_result())

and the following output subsequent times:

Check the variables are initialised
This is the Database
It works without error messages.
This is the Database fred, With user fred, Password fredpwd, and Link_ID 0

Execute the Database Query Command (db query)

Connecting to edm...

Obtained the Link_ID: Resource id #3
Debug: query = select * from work_reasons
Now the Link_ID is Resource id #3

Create the new table object
Execute show result method (tab show_result())

If I then remove the remark statements from the code to execute the $db->next_record() I get the following output:

Check the variables are initialised
This is the Database
It works without error messages.
This is the Database edm, With user edm, Password edm, and Link_ID 0

Execute the Database Query Command (db query)

Connecting to edm...

Obtained the Link_ID: Resource id #3
Debug: query = select * from work_reasons
Now the Link_ID is Resource id #3

Print the next record (db next_record())
1
Create the new table object
Execute show result method (tab show_result())
id value needs_response
2 For comment 1
3 For approval 1
4 For action 1
5 For distribution 1
6 For allocation 1
7 For reply 1
8 For checking 1
9 For typing 1
10 For sending 1

ID: 3,Rows: 11

Error:

id value needs_response
2 For comment 1
3 For approval 1
4 For action 1
5 For distribution 1
6 For allocation 1
7 For reply 1
8 For checking 1
9 For typing 1
10 For sending 1

The relevent parts of my local.inc are:

putenv("ORACLE_HOME=/oracle/mowlem01/OraHome1");
putenv("TWO_TASK=edm");

class DB_Example extends DB_Sql {
var $Host = "10.90.72.15";
var $Database = "fred";
var $User = "fred";
var $Password = "fredpwd";

}

The relevent parts of db_oci8.inc are:

class DB_Sql {
var $Debug = 0;
var $sqoe = 1; // sqoe= show query on error

var $Database = "";
var $User = "";
var $Password = "";

var $Link_ID = 0;
var $Record = array();
var $Row;
var $Parse;
var $Error = "";

/ public: constructor /
function DB_Sql($query = "") {
$this->query($query);
}

function connect() {
if ( 0 == $this->Link_ID ) {
if($this->Debug) {
printf(" Connecting to $this->Database... \n");
}
$this->Link_ID=OCIplogon
("$this->User","$this->Password","$this->Database");

      if (!$this->Link_ID) {
          $this->halt("Link-ID == false " .
                      "($this->Link_ID), OCILogon failed");
      } 

      if($this->Debug) {
          printf("<br>Obtained the Link_ID: $this->Link_ID<br>\n");
      }   
  }

}

function query($Query_String) {

/* No empty queries, please, since PHP4 chokes on them. */
if ($Query_String == "")
  /* The empty query string is passed on from the constructor,
   * when calling the class without a query, e.g. in situations
   * like these: '$db = new DB_Sql_Subclass;'
   */
  return 0;


  $this->connect();

   $this->Parse=OCIParse($this->Link_ID,$Query_String);
  if(!$this->Parse) {
       $this->Error=OCIError($this->Parse);
  } else { OCIExecute($this->Parse);
      $this->Error=OCIError($this->Parse); 
  }

  $this->Row=0;

  if($this->Debug) {
      printf("Debug: query = %s<br>\n", $Query_String);
  }

  if ($this->Error["code"]!=1403 && $this->Error["code"]!=0 && $this->sqoe) 
  echo "<BR><FONT color=red><B>".$this->Error["message"]."<BR>Query :\"$Query_String\"</B></FONT>";
  return $this->Parse;

}

The relevent parts of the table.inc are:

#==========================================================================

Table (class)

#--------------------------------------------------------------------------

Creates an HTML table based on either a PHP array or a

database result.

#==========================================================================

class Table
{
var $classname = "Table"; ## Persistence Support

var $check; ## if set, create checkboxes named

to the result of $check[$key]

var $filter = "[A-Za-z][A-Za-z0-9_]*"; ## Regexp: Field names to show
var $fields; ## Array of field names to show
var $heading; ## if set, create <th> section
var $add_extra; ## Call extra cell functions
var $map_cols; ## remap database columns to new names

#==========================================================================

Function : show_result

#--------------------------------------------------------------------------

Purpose : Starts the display of a database query result in an HTML table

format.

Arguments: $db - The database result.

$class - [optional] Used for CSS control.

Returns : The number of rows displayed.

Comments :

History :

#==========================================================================
function show_result($db, $class="")
{
if (!$this->verify_db($db))
return 0;

$rows = 0;

$this->table_open($class);
if ($this->show_table_heading_row_result($db, $class))
  $rows = $this->show_table_rows_result($db, $class);  
$this->table_close($class);

return $rows;

}

#==========================================================================
# Function : verify_db
#--------------------------------------------------------------------------
# Purpose  : Verifies a database object for results.
# Arguments: $db   - The database object to verify.
# Returns  : 1 on success, 0 on error.
# Comments : 
# History  : 
#==========================================================================
function verify_db($db)
{
  if (!isset($db) && !$db)
    return 0;

  if ($db->num_rows() > 0)
    return 1;   

  return 0;
}

#==========================================================================

Function : show_table_heading_row_result

#--------------------------------------------------------------------------

Purpose : Uses the passed database object to create an HTML header row.

Arguments: $db - The database object

$class - [optional] Used for CSS control.

Returns : 1 on success, 0 on error.

Comments :

History :

#==========================================================================
function show_table_heading_row_result($db, $class="")
{
if (!$this->verify_db($db))
return 0;

if ($this->heading)
{

// (Jeff) ------------------------------

// if ($db->num_rows() > 0 && $db->next_record())
// rows are confirmed in $this->verify_db(), so no need to reverify
// -------------------------------------
if ($db->next_record())
{
$this->table_heading_row($db->Record, $class);
$db->seek($db->Row-1);
return 1;
}
else
{
// (Jeff) ------------------------------

// Shouldn't do this! Breaks modularity!
// Call: table_close() instead from
// calling function. Comments to
// be removed in next release.
// -------------------------------------
// $this->table_close($class);
// -------------------------------------
return 0;
}
}
return 1;
}

#==========================================================================

Function : table_heading_row

#--------------------------------------------------------------------------

Purpose : Outputs HTML code to create a table heading row.

Arguments: $data - The array of data that represents cells within a row.

$class - [optional] Used for CSS control.

Returns :

Comments :

History : 990618 - Fixed return on select_colnames (JSG).

#==========================================================================
function table_heading_row($data, $class="")
{
if (!is_array($data))
return;

$d = $this->select_colnames($data);

$this->table_row_open($row, $d, $class);
$this->set_checkbox_heading($class);
$this->show_table_heading_cells($data, $class);

# call virtual function
if ($this->add_extra)
  $this->table_heading_row_add_extra($data, $class);

$this->table_row_close(0, $class);

}

#==========================================================================

Function : show_table_rows_result

#--------------------------------------------------------------------------

Purpose : Walks the passed database object displaying each record as an

HTML table row.

Arguments: $db - The database object

$class - [optional] Used for CSS control.

Returns :

Comments :

History : 990617 - fixed return. Was "row" changed to "$row".

#==========================================================================
function show_table_rows_result($db, $class="")
{
global $debug;

  if ($debug)
    printf("<p>show_table_rows_result()<br>\n");

  if (!$this->verify_db($db))
    return 0;

  $row = 0;

  while($db->next_record())
  {
    ## Process a table row
    $this->table_row($row, $row, $db->Record, $class);
    $row++;
  }

  return $row;
}

toma42

k, there's no way in hell I'm going to wade through all your stuff....too much strait code and not enough snippets saying 'this is the problem'.

Anyway, here's a buch of strait code 🙂 This is my modified oci phplib file....it works great for me.

<?php

/
Oracle/OCI8 accessor based on Session Management for PHP3

(C) Copyright 1999 Stefan Sels phplib@sels.com

based on db_oracle.inc by Luis Francisco Gonzalez Hernandez
contains metadata() from db_oracle.inc 1.10

$Id: db_oci8.inc,v 1.9 1999/08/26 14:59:40 sts Exp $

*/
define ("DATABASE_TYPE", "ORACLE");

class DB_Sql {
var $Debug = 0;
var $sqoe = 1; // sqoe= show query on error

var $Database = "";
var $User = "";
var $Password = "";

var $Link_ID = 0;
var $Record = array();
var $Row;
var $Parse;
var $Error = "";

      if (!$this->Link_ID) {
          $this->halt("Link-ID == false " .
                      "($this->Link_ID), OCILogon failed");
      } 

      if($this->Debug) {
          printf("<br>Obtained the Link_ID: $this->Link_ID<br>\n");
      }   
  }

}

function query($Query_String) {
$this->connect();

   $this->Parse=OCIParse($this->Link_ID,$Query_String);
  if(!$this->Parse) {
       $this->Error=OCIError($this->Parse);
  } else { OCIExecute($this->Parse);
      $this->Error=OCIError($this->Parse); 
  }

  $this->Row=0;

  if($this->Debug) {
      printf("Debug: query = %s<br>\n", $Query_String);
  }

  if ($this->Error["code"]!=1403 && $this->Error["code"]!=0 && $this->sqoe) 
  echo "<BR><FONT color=red><B>".$this->Error["message"]."<BR>Query :\"$Query_String\"</B></FONT>";
  return $this->Parse;

}

function next_record() {
if(0 == OCIFetchInto($this->Parse,$result,OCI_ASSOC+OCI_RETURN_NULLS)) {
if ($this->Debug) {
printf(" ID: %d,Rows: %d \n",
$this->Link_ID,$this->num_rows());
}
$this->Row +=1;

      $errno=OCIError($this->Parse);
      if(1403 == $errno) { # 1043 means no more records found
          $this->Error="";
          $this->disconnect();
          $stat=0;
      } else {
          $this->Error=OCIError($this->Parse);
          if($this->Debug) {
              printf("<br>Error: %s",
              $this->Error["message"]);
          }
          $stat=0;
      }
  } else { 
      for($ix=1;$ix<=OCINumcols($this->Parse);$ix++) {
          $col=strtoupper(OCIColumnname($this->Parse,$ix));
          $colreturn=strtolower($col);
          $this->Record[ "$colreturn" ] = $result["$col"]; 
          if($this->Debug) echo"<b>[$col]</b>:".$result["$col"]."<br>\n";
      }
      $stat=1;
  }

return $stat;
}

function seek($pos) {
$this->Row=$pos;
}

function metadata($table,$full=false) {
$count = 0;
$id = 0;
$res = array();

/*
 * Due to compatibility problems with Table we changed the behavior
 * of metadata();
 * depending on $full, metadata returns the following values:
 *
 * - full is false (default):
 * $result[]:
 *   [0]["table"]  table name
 *   [0]["name"]   field name
 *   [0]["type"]   field type
 *   [0]["len"]    field length
 *   [0]["flags"]  field flags ("NOT NULL", "INDEX")
 *   [0]["format"] precision and scale of number (eg. "10,2") or empty
 *   [0]["index"]  name of index (if has one)
 *   [0]["chars"]  number of chars (if any char-type)
 *
 * - full is true
 * $result[]:
 *   ["num_fields"] number of metadata records
 *   [0]["table"]  table name
 *   [0]["name"]   field name
 *   [0]["type"]   field type
 *   [0]["len"]    field length
 *   [0]["flags"]  field flags ("NOT NULL", "INDEX")
 *   [0]["format"] precision and scale of number (eg. "10,2") or empty
 *   [0]["index"]  name of index (if has one)
 *   [0]["chars"]  number of chars (if any char-type)
 *   ["meta"][field name]  index of field named "field name"
 *   The last one is used, if you have a field name, but no index.
 *   Test:  if (isset($result['meta']['myfield'])) {} ...
 */

  $this->connect();

  ## This is a RIGHT OUTER JOIN: "(+)", if you want to see, what
  ## this query results try the following:
  ## $table = new Table; $db = new my_DB_Sql; # you have to make
  ##                                          # your own class
  ## $table->show_results($db->query(see query vvvvvv))
  ##
  $this->query("SELECT T.table_name,T.column_name,T.data_type,".
       "T.data_length,T.data_precision,T.data_scale,T.nullable,".
       "T.char_col_decl_length,I.index_name".
       " FROM ALL_TAB_COLUMNS T,ALL_IND_COLUMNS I".
       " WHERE T.column_name=I.column_name (+)".
       " AND T.table_name=I.table_name (+)".
       " AND T.table_name=UPPER('$table') ORDER BY T.column_id");

  $i=0;
  while ($this->next_record()) {
    $res[$i]["table"] =  $this->Record[table_name];
    $res[$i]["name"]  =  strtolower($this->Record[column_name]);
    $res[$i]["type"]  =  $this->Record[data_type];
    $res[$i]["len"]   =  $this->Record[data_length];
    if ($this->Record[index_name]) $res[$i]["flags"] = "INDEX ";
    $res[$i]["flags"] .= ( $this->Record[nullable] == 'N') ? '' : 'NOT NULL';
    $res[$i]["format"]=  (int)$this->Record[data_precision].",".
                         (int)$this->Record[data_scale];
    if ("0,0"==$res[$i]["format"]) $res[$i]["format"]='';
    $res[$i]["index"] =  $this->Record[index_name];
    $res[$i]["chars"] =  $this->Record[char_col_decl_length];
    if ($full) {
            $j=$res[$i]["name"];
            $res["meta"][$j] = $i;
            $res["meta"][strtoupper($j)] = $i;
    }
    if ($full) $res["meta"][$res[$i]["name"]] = $i;
    $i++;
  }
  if ($full) $res["num_fields"]=$i;

$this->disconnect();

return $res;
}

function affected_rows() {
return $this->num_rows();
}

function num_rows() {
return OCIrowcount($this->Parse);
}

function num_fields() {
return OCINumcols($this->Parse);
}

function nf() {
return $this->num_rows();
}

function np() {
print $this->num_rows();
}

function f($Name) {
return $this->Record[$Name];
}

function p($Name) {
print $this->Record[$Name];
}

function disconnect() {
if($this->Debug) {
printf("Disconnecting... \n");
}
OCILogoff($this->Link_ID);
}

function halt($msg) {
printf("</td></tr></table>Database error: %s \n", $msg);
printf("ORACLE Error: %s \n",
$this->Error["message"]);
die("Session halted.");
}

function unlock() {
return $this->query("commit");
}

function table_names() {
$this->connect();
$this->query("
SELECT table_name,tablespace_name
FROM user_tables");
$i=0;
while ($this->next_record())
{
$info[$i]["table_name"] =$this->Record["table_name"];
$info[$i]["tablespace_name"]=$this->Record["tablespace_name"];
$i++;
}
return $info;
}

function add_specialcharacters($query)
{
return str_replace("'","''",$query);
}

function split_specialcharacters($query)
{
return str_replace("''","'",$query);
}
}
?>

Anon

Tom

Many thanks for the quick reply and the code. I checked it, and it doesn't seem much different from the original. However I replaced the original with your code, but still got the same result.

Sorry the question was so muddled. In summary:

If I use the following code (as suggested by the PHPLib docs):

<?php

$db = new DB_Example;

include("table.inc"); // requires include_path to be functioning

$db->query("select * from work_reasons");

$t = new Table;
$t->heading = "on";
$t->show_result($db);
?>

I get no output whatsoever. The $db->query() statement parses and executes the sql query, so why does the instance of table not return any data? I know the connection works. It almost appears something is missing from the script.

I would be grateful for any light you could shed on the problem.

Paul