Oracle SELECT statements require quotes

msimonds

I am writing some simple SELECT statments for use with Oracle. I am more familiar and use MySQL.

I am having some problems with the following:

SELECT "Contact_Id", "Company_Id"
FROM contact
WHERE rownum <= 11

it is requiring me to use quotes around the field names and from all that I read and examples that I looked at, quotes are not required.

So I am having to create variable names to place into the SELECT statement

$contact_id     = '"Contact_Id"';
$company_id     = '"Company_Id"';

SELECT $contact_id, $company_id,......

It is just one extra step and does not have to be done in MYSql. Is there something that I am doing wrong or is it standard. I just started recently working with PHP/Oracle.

Any help would be appreciated!

CanMike

Nope!

Here a small piece of code that accepts an argument ($year) and uses it in a ORACLE Select statement.

<?php
$conn = OCILogon('books', 'books', 'library');

if (!$conn){
  echo '<h1>Could not connect to Oracle</h1>';
  exit;
}

$year = 2005;

$sql ="select count(*)from details where year_pub = $year";

$stmt = OCIParse($conn, $sql);

if (!$stmt){
  echo '<h1>ERROR - Could not parse SQL Statment</h1>';
  exit;
}

OCIExecute($stmt);

OCIFetchInto($stmt, $total_rows);

if (!$total_rows[0]){
  echo '<h1>ERROR - No rows returned</h1>';
  exit;
}

echo 'There are <b> ' .$total_rows[0]. '</b> results,<br />';

OCILogoff($conn);
?>

This piece of code will return the number of books in the library that were published in 2005.

No need to use quotes.

My architecture:

PHP5.0
Oracle 10g
Apache 2
O/S Windows XP Pro (SP2)

Hope this helps
Mike in Toronto

msimonds

Hey thanks Mike for the help and the tips on the Select statement

Hey since you use the same setup as I do! Do you use Adodb at all?

CanMike

If by Adodb you mean an API layer to connect to a database, nope.

I just use OCI. I use a lot of stored procedures and I find that the OCI thru-put is easier on Oracle.

msimonds

That works! the reason that I am asking is that we currently use the AdoDb to connect all our applications to Oracle. Our production servers currently use php 4.*.

To use new functionality with an outside API and the built in Soap functions with php 5, we need to upgrade our servers > testing first of course

One item that was brought up by our developers was that the OCI drivers did not work or do not work well with PHP 5. Have you run into any problems using the OCI drivers? Have you used PHP 5 with Apache and Oracle?

CanMike

[FONT=Tahoma]My architecture:

PHP5.0
Oracle 10g
Apache 2

All sitting on:
O/S Windows XP Pro (SP2)[/FONT]

I've been using PHP5 and OCI and have not experienced any problem so far.

On my Linux machine I running PHP4 and MySQL/Oracle using OCI and have not experience any problems either.

msimonds

CanMike wrote:
[FONT=Tahoma]My architecture:

PHP5.0
Oracle 10g
Apache 2

All sitting on:
O/S Windows XP Pro (SP2)[/FONT]

I've been using PHP5 and OCI and have not experienced any problem so far.

On my Linux machine I running PHP4 and MySQL/Oracle using OCI and have not experience any problems either.

Sorry I probably need to clarify my question > Have you run PHP5/Apache/Oracle on Linux. Reason I ask this is because the OCI drivers are different than those of windows servers or XP > sorry for the confusion!

CanMike

No,

My UNIX/Linix system is reserved for clients and I've only recently upgrade my Window Server to Apache 2/PHP5/MySQL5.0.

My Oracle databases/schemas reside on the Linux machine (only because Oracle and Unix go together like peanut butter and jam) and I resolve which schema I use via the Oracle TNS Listener.

Ninty-Nine percent of my client use MySQL as their default RDBMS, but I have one client who uses Oracle and MAYBE upgrading to PHP5 in the new year. That's why I'm starting to run my code against the new App.

If I find anything and if you like I'll pass on any problems.

If you interested, should I contact you via email?

msimonds

Sure that would be great.

I'll PM you my email addy!

j_70

"Sorry I probably need to clarify my question > Have you run PHP5/Apache/Oracle on Linux. Reason I ask this is because the OCI drivers are different than those of windows servers or XP > sorry for the confusion!"

I and many others run this architecture. I would recommend the Zend Core for Oracle for ease of installation.

CanMike

Ya, I heard that Zend Core was a better application to use, but I haven't really decided whether I'll be using my Linux machine for full on PHP5.0 testing. That's why I just used OCI.

Come the new year I'll have to make a decision.

Thanks for the insight.

msimonds

what about this > http://phpbuilder.com/board/showthread.php?p=10773495#post10773495

Have either of you seen this..... I have tried everything!