Problem with Oracle/PHP/Solaris

[Tracy]

Hello All,

I'm an old client-server dog learning new tricks; specifically Web Applications. I'm having a bit of a problem getting a result set back from a remote DB.

Background:

Database Server: Oracle 9iR2 running on Solaris 8 OS
Web Server: Solaris 8 OS with Apache and PHP 4.3.4

The Apache and PHPinfo() web pages come up normally.

Problem: I can log into in the Scott/Tiger Demo schema on the DB Server, but when I send a query to the EMP table, I get zero rows returned. I check the EMP table through SQL*Plus Worksheet and it has 14 rows.

Here's the PHP code I use for the web page:


<?
putenv("ORACLE_HOME=/opt/oracle");

$username = "scott";
$password = "tiger";
$db = "(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = xxx.xxx.xxx.xxx)
(PORT = 1521)
)
)
(CONNECT_DATA = (SID=sssssss) )
)";

$conn = OCILogon($username,$password,$db);

if (!$conn)
{
echo "Connection Failed. ";
echo "Error Message: [" . OCIError($conn) . "] <br><br>n";
die();
}
else
{
echo "Connected: ".$conn."<br><br>n";
}

$stmt = ociparse($conn,"SELECT ENAME, SAL FROM SCOTT.EMP");
ociexecute($stmt,OCI_DEFAULT);

$nrows = ocifetchstatement($stmt, $results);
echo "<br><br>Found: $nrows results<br><br>n";

echo "<table border=1 cellspacing='0' width='50%'>n";

echo "<tr>n";
echo "<td><b>Name</b></td>n";
echo "<td><b>Salary</b></td>n";
echo "<tr>n";

for ($i = 0; $i < $nrows; $i++ )
{
echo "<tr>n";
echo "<td>" . $results["ENAME"][$i] . "</td>";
echo "<td>$ " . number_format($results["SAL"][$i], 2). "</td>";
echo "</tr>n";
}
echo "</table>n";

ocilogoff($conn);
?>


I get zero rows returned after I receive a sucessful connection. I also tried using two other OCI functions: OCIFetch and OCIFetchInto. Same result: zero row count.

I ran the SQL statement in SQL*Plus and it executed.

Anyone see behavior like this before? Thoughts, opinions?


Respectfully,
Tracy-Paul Warrington

 

[deadserious]

I really don't know anything about oracle, but I wonder if selecting everything such as SELECT * FROM SCOTT.EMP will make $nrows display the number of results/rows? Have you tried that?

 

[Tracy]

I'll give a shot tomorrow and see what happens. Although I doubt if it'll change the behavior. It seems to me that OCIFetchStatement would return all rows the query specified; regardless of the number of columns.

But, WTH, couldn't hurt to try...