Solved

PHP and Oracle for an Idiot

Posted on 2014-09-15
3
154 Views
Last Modified: 2014-09-20
I've worked with MySQL and T-SQL, but I'm brand new to Oracle.

While I'm working through some books and tutorials, I wanted to put it out here on Experts Exchange and ask for some "high altitude" perspective.

I'm looking at sites like this: http://docs.oracle.com/cd/E12151_01/doc.150/e12155/oracle_mysql_compared.htm and I'm seeing differences in terms of features, but how do things differ in terms of:

- connection to the database
- queries / code that retrieves info from the database

Not trying to minimize the obvious amount of study and work that will be needed to make this work, but I was hoping for some "bullets" to guide me in my quest.

Thoughts?
0
Comment
Question by:brucegust
3 Comments
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 150 total points
ID: 40324232
Probably the most important thing you can know is contained in the prominent red warning box on this page:
http://www.php.net/manual/en/ref.pdo-oci.php

After that, it's just like any other of the PHP database extensions.  There are examples with the function documentation.
http://www.php.net/manual/en/book.oci8.php
0
 
LVL 82

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 150 total points
ID: 40324310
Using the OCI8 extension http://php.net/manual/en/book.oci8.php , I was able to easily make the connections and queries to the 'free' developers version of Oracle.  However... setting up the users and databases privileges was Quite different and took some time to figure out.  That part is not like MySQL.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 200 total points
ID: 40324541
Dave's point about security is a really good one.

Regarding "queries / code that retrieves info from the database" here are some things I can think of:

Case sensitivity. By default Oracle is case sensitive.

"FROM DUAL"
  you can't just do "select 1" you MUST use a from clause and there's a special (oddly named) pseudo table "dual"
  "select 1 from dual"

NULL handling
   IFNULL(MySQL,'')
   ISNULL(MSSQL,'')
   NVL(Oracle,'')
   
   or, just use COALESCE(anydb,'')

string functions, these differ
   e.g. CONCAT() only handles 2 parameters,
    and unlike TSQL you can concatenate without cast to varchar first, and you use double pipes for concatenation

     select 'concatenation' || ' differs in more ways than ' || 1 from dual

    LISTAGG() is similar to GROUP_CONCAT()

date/time handling e.g.
   select SYSDATE from dual
   select TRUNC(SYSDATE) from dual -- equivalent to CURDATE()
   to_date('2014-04-21','YYYY-MM-DD')
   to_char(date_field_here,'YYYY-MM-DD')

   &  note that the timestamp data type is more complex to use than the date data type
   
useful function references:
     http://docs.oracle.com/cd/E21901_01/doc/timesten.1122/e21642/function.htm#TTSQL446
     http://www.techonthenet.com/oracle/functions/index.php

PL/SQL
   The procedural language differs in many ways.

DDL
   There are some syntax differences but they aren't extensive.
   BUT you will have to get used to UPPERCASE table and field names in the "dictionary" e.g.

   select * from user_tab_columns where table_name = 'YOUR_TABLE'
 

It isn't that hard & Oracle is a damn fine product.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now