Oracle and PEAR - query problem

regerg

Hi,
I work with Oracle 9i and use PEAR to connect. It works fine, but now I have to extend my simple query by formatting the result.
SQL working:
$sql = "select datum from tabelle";
SQL not working:
$sql = "select to_char(datum,'dd.mm.rrrr') from tabelle";
If I set quotations in the sql-statement I get the error:

Fatal error: Call to undefined function: fetchrow() in D:\Inetpub\ewebs\gaby\ansprechpartner.php on line 43

It's equal which Oracle-function I use, always if I use quotations in select-statement I get the same error;-((

[deleted]

this wont work

SELECT ... to_char(datum,'dd.mm.rrrr') ...

$stmt= OCIResult($stmt,"datum");

But this will:

SELECT ... to_char(datum,'dd.mm.rrrr') AS datum ...

$stmt= OCIResult($stmt,"datum");

Best regards.

regerg

Thank you for answering, but I use the PEAR-Interface! (where OCI is integrated)
There is no need to use an alias to name the result (and it wouldn't change anything, because the error doesn't occur at getting the result).
Problem is the quotation that causes that the Fetch-function is no longer recognized.

guyryan100

The problem is within your query. I'm not sure on the ORACLE syntax, but you're getting the undefined fetchrow() because your query didn't execute. To get more feedback from Oracle, add this code right after you execute your SQL:

if (DB::isError($result)) { // Check the result object in case there
die($result->getMessage()); // was an error, and handle it here.
}

(make sure to change your variable - the $result var should be whatever your result set generated by ->query($sql);

another idea would be to grab the date unformatted and reformat it within PHP rather than via your query...

hope this helps.

regerg

Thank you but it's not an ORACLE-problem (I mentioned that the error only occurs if I use quotes in sql!)
Therefore there is no need to get further feedback from DB, the PEAR-function doesn't reach the database in this constellation.
The sql-syntax is right and why should I format in php if I can get the correct one from the DB.
(Especially this method is independent from NLS-parameter in DB. If I get the date from ORACLE unformatted, I need to know how this parameter is set to know the format I will get).

guyryan100

The trouble is that you're not getting a valid result set from your executed query. That's why your ->fetchrow() is undefined - you will see that your result set is actually an error object and doesn't have fetchrow defined. You can get feeback on the details of the error object by including the code I mentioned before. I did not indicate that the problem was within Oracle - what I said was that it was within your query. The code below will give your Oracle's response to your erroneous query. I agree with the preferred date formatting from the DB outside of PHP. Just as you said, IF you are able to get the proper format from the DB you should use it. At this stage you are unable to do so - my suggestion is a backup if you can't get past the bad query.

Hope this helps... Let us know how it turns out.

toma42

fetchrow() can only be called after a query() call. Otherwise you'd use a getRow() if you don't want to worry about a query resource identifer.

Do a
$res = $db->query($sql);
if (DB::isError($res)) print_r($res);

to see if you're getting a db error. Set your error handling to a custom function that does a print_r of the error object too...that helps a lot when writing pear apps.