I am relatively new to PHP, but with over a decade of experience in C
and other programming languages I can usually pick up a new
programming language relatively quickly (to be reasonably productive
not to know it inside out). My issues I believe may be either solely
concentrated on the platform I am running PHP 5.0.0 on (MS Windows
2000) or just the Oracle OCI8 module (running against an Oracle
8.1.7.4 installation). In either case I am using PHP in more of a
pure scripting environment with no Web-related access as of yet.
The following (with a modifications only for username and password) is
the script I just started developing:
<?php
$os_name = strtolower(PHP_ OS);
if (substr($os_nam e,0,3) == 'win') {
$dirsep = '\\';
$oci8lib = 'php_oci8.dll';
$pathsep = ';';
$libpath = 'PATH';
} else {
$dirsep = '/';
$oci8lib = 'oci8.so';
$pathsep = ':';
$libpath = 'LD_LIBRARY_PAT H';
}
$ORACLE_HOME = getenv('ORACLE_ HOME');
if (strlen($ORACLE _HOME) == 0) {
printf("The ORACLE_HOME environment variable has not been set\n");
exit(1);
}
if (!extension_loa ded('oci8')) {
$path = split($pathsep, getenv($libpath ));
$found = false;
foreach ($path as $dir) {
if (substr($dir,0, strlen($ORACLE_ HOME)) == $ORACLE_HOME) {
$found = true;
break;
}
}
if (!$found) {
printf("The OCI8 PHP module has not been autoloaded and
ORACLE_HOME\n") ;
printf("is not currently found in %-15s; therefore,
the\n",$libpath );
printf("OCI8 PHP module cannot be loaded at runtime.\n");
exit(1);
}
dl($oci8lib);
}
$DBA_HOME = getenv('DBA_HOM E');
if (strlen($DBA_HO ME) == 0) {
printf("The DBA_HOME environment variable has not been set\n");
exit(1);
}
$dfn = "${DBA_HOME}${d irsep}adm${dirs ep}databases.db ";
if ((!file_exists( $dfn)) || (!is_readable($ dfn))) {
printf("The DBA databases file:\n");
printf(" ${dfn}\n");
printf("is not available\n");
exit(1);
}
$dfh = fopen($dfn,'r') ;
if (!$dfh) {
printf("The DBA databases database:\n");
printf(" ${dfn}\n");
printf("is not available\n");
exit(1);
}
$dfd = fgets($dfh);
while (!feof($dfh)) {
$dfd = rtrim($dfd);
if ((strlen($dfd) > 0) && (substr($dfd,0, 1) != '#')) {
$dfa = split(':',$dfd) ;
$user = 'system';
$pass = 'manager';
$host = $dfa[0];
$port = $dfa[1];
$sid = $dfa[2];
$cnxn = oci_connect($us er,$pass,"(DESC RIPTION=(ADDRES S_LIST=(ADDRESS =(HOST=${host}) (PORT=${port})( PROTOCOL=TCP))) (CONNECT_DATA=( SID=${sid})))") ;
$stmt = oci_parse($cnxn ,'SELECT USER FROM dual');
oci_execute($st mt);
while ($data = oci_fetch_array ($stmt,OCI_ASSO C)) {
foreach ($data as $key => $value) {
printf("${host} ${sid} ${key} = ${value}\n");
}
}
oci_free_statem ent($stmt);
oci_close($cnxn );
}
$dfd = fgets($dfh);
}
fclose($dfh);
?>
The script is run on the command line as:
php oracle1.php
The file referenced in the script is in the following format:
host:port:sid:l evel1:level2:no tify
where host is the machine name, port the Oracle TNS listener port, sid
is the ORACLE_SID, level1 and level2 (not currently used in the
script) are percentages, and notify is an email address.
My two issues are as follows:
1. Although not the final purpose of the script, an interesting
phenomena occurs in which using "SELECT USER FROM dual" causes the
script to hang after the fclose. I know this because I have done the
simple debugging of printing messages at various points. If I change
the oci_connect to either oci_pconnect or oci_new_connect the script
completes successfully. Similarly, if the query is changed to
something other than including only the USER function (i.e. - "SELECT
USER, dummy FROM dual", "SELECT dummy FROM dual", "SELECT global_name
FROM global_name", etc.), whether oci_connect or either of the other 2
connection methods is utilized, the script also runs to completion.
My question is: what precisely is the "hanging" factor with regards to
using the simple "SELECT USER FROM dual" and oci_connect?
2. In attempting to deduce what had been going on in my script, I
found that regardless of which of the 3 connection methods I used
oci_close always returns a 0 (presumably indicating a FALSE result)
and oci_free_statem ent returns the expected result of 1 (TRUE). In
essence does oci_close either actually do anything or is it setup to
always return a FALSE? I realize that multiple database connections
in one script may not be the norm and that, as the oci_close
documentation indicates, that it normally is not necessary (as all
connections will be closed on script completion), but if said function
exists or it is expressly stated that multiple database connections
are not supported an oci_close should close the database connection.
I have no issues connecting and any other tool I use, commercial or
not, successfully closes the connection from my machine so it is
bizarre to me as well to always get the FALSE result.
I apologize in advance if this is more a newbie issue than actual
issues, but it is quite perplexing to me as a DBA and programmer as no
other programming environment I've worked on has had an issue as
described above.
Thank you.
and other programming languages I can usually pick up a new
programming language relatively quickly (to be reasonably productive
not to know it inside out). My issues I believe may be either solely
concentrated on the platform I am running PHP 5.0.0 on (MS Windows
2000) or just the Oracle OCI8 module (running against an Oracle
8.1.7.4 installation). In either case I am using PHP in more of a
pure scripting environment with no Web-related access as of yet.
The following (with a modifications only for username and password) is
the script I just started developing:
<?php
$os_name = strtolower(PHP_ OS);
if (substr($os_nam e,0,3) == 'win') {
$dirsep = '\\';
$oci8lib = 'php_oci8.dll';
$pathsep = ';';
$libpath = 'PATH';
} else {
$dirsep = '/';
$oci8lib = 'oci8.so';
$pathsep = ':';
$libpath = 'LD_LIBRARY_PAT H';
}
$ORACLE_HOME = getenv('ORACLE_ HOME');
if (strlen($ORACLE _HOME) == 0) {
printf("The ORACLE_HOME environment variable has not been set\n");
exit(1);
}
if (!extension_loa ded('oci8')) {
$path = split($pathsep, getenv($libpath ));
$found = false;
foreach ($path as $dir) {
if (substr($dir,0, strlen($ORACLE_ HOME)) == $ORACLE_HOME) {
$found = true;
break;
}
}
if (!$found) {
printf("The OCI8 PHP module has not been autoloaded and
ORACLE_HOME\n") ;
printf("is not currently found in %-15s; therefore,
the\n",$libpath );
printf("OCI8 PHP module cannot be loaded at runtime.\n");
exit(1);
}
dl($oci8lib);
}
$DBA_HOME = getenv('DBA_HOM E');
if (strlen($DBA_HO ME) == 0) {
printf("The DBA_HOME environment variable has not been set\n");
exit(1);
}
$dfn = "${DBA_HOME}${d irsep}adm${dirs ep}databases.db ";
if ((!file_exists( $dfn)) || (!is_readable($ dfn))) {
printf("The DBA databases file:\n");
printf(" ${dfn}\n");
printf("is not available\n");
exit(1);
}
$dfh = fopen($dfn,'r') ;
if (!$dfh) {
printf("The DBA databases database:\n");
printf(" ${dfn}\n");
printf("is not available\n");
exit(1);
}
$dfd = fgets($dfh);
while (!feof($dfh)) {
$dfd = rtrim($dfd);
if ((strlen($dfd) > 0) && (substr($dfd,0, 1) != '#')) {
$dfa = split(':',$dfd) ;
$user = 'system';
$pass = 'manager';
$host = $dfa[0];
$port = $dfa[1];
$sid = $dfa[2];
$cnxn = oci_connect($us er,$pass,"(DESC RIPTION=(ADDRES S_LIST=(ADDRESS =(HOST=${host}) (PORT=${port})( PROTOCOL=TCP))) (CONNECT_DATA=( SID=${sid})))") ;
$stmt = oci_parse($cnxn ,'SELECT USER FROM dual');
oci_execute($st mt);
while ($data = oci_fetch_array ($stmt,OCI_ASSO C)) {
foreach ($data as $key => $value) {
printf("${host} ${sid} ${key} = ${value}\n");
}
}
oci_free_statem ent($stmt);
oci_close($cnxn );
}
$dfd = fgets($dfh);
}
fclose($dfh);
?>
The script is run on the command line as:
php oracle1.php
The file referenced in the script is in the following format:
host:port:sid:l evel1:level2:no tify
where host is the machine name, port the Oracle TNS listener port, sid
is the ORACLE_SID, level1 and level2 (not currently used in the
script) are percentages, and notify is an email address.
My two issues are as follows:
1. Although not the final purpose of the script, an interesting
phenomena occurs in which using "SELECT USER FROM dual" causes the
script to hang after the fclose. I know this because I have done the
simple debugging of printing messages at various points. If I change
the oci_connect to either oci_pconnect or oci_new_connect the script
completes successfully. Similarly, if the query is changed to
something other than including only the USER function (i.e. - "SELECT
USER, dummy FROM dual", "SELECT dummy FROM dual", "SELECT global_name
FROM global_name", etc.), whether oci_connect or either of the other 2
connection methods is utilized, the script also runs to completion.
My question is: what precisely is the "hanging" factor with regards to
using the simple "SELECT USER FROM dual" and oci_connect?
2. In attempting to deduce what had been going on in my script, I
found that regardless of which of the 3 connection methods I used
oci_close always returns a 0 (presumably indicating a FALSE result)
and oci_free_statem ent returns the expected result of 1 (TRUE). In
essence does oci_close either actually do anything or is it setup to
always return a FALSE? I realize that multiple database connections
in one script may not be the norm and that, as the oci_close
documentation indicates, that it normally is not necessary (as all
connections will be closed on script completion), but if said function
exists or it is expressly stated that multiple database connections
are not supported an oci_close should close the database connection.
I have no issues connecting and any other tool I use, commercial or
not, successfully closes the connection from my machine so it is
bizarre to me as well to always get the FALSE result.
I apologize in advance if this is more a newbie issue than actual
issues, but it is quite perplexing to me as a DBA and programmer as no
other programming environment I've worked on has had an issue as
described above.
Thank you.