Would it be hard to switch from MySQL to Oracle?

Question

Below is some example code of how I run my mysql queries, I run them through a function which I think would maybe simpliy switching databases.

Below is an example of a mysql query I run and below that is the actual function.

Would it be difficult to change to a different database type like oracle or some other if I ever decided to using this setup?

Would be able to just modify the function or would I need to change the queries that are on every page?

$sql_photo = "select * from friend_user_photo  where userid='$user_id' and  defaultphoto='yes' order by auto_id desc";
$result_photo = executeQuery($sql_photo);

function executeQuery($sql) {
    $result = mysql_query_2($sql);
    if(mysql_error()){
         $error = '<BR><center><font size="+1" face="arial" color="red">An Internal Error has Occured.<BR> The error has been recorded for review</font></center><br>';
        // If admin is viewing then we show the query code and the error returned
        if($_SESSION['auto_id'] == 1){
        $sql_formatted = highlight_string( stripslashes( $sql ), true );
           $error .= '<b>The MySQL Syntax Used</b><br>' . $sql_formatted . '<br><br><b>The MySQL Error Returned</b><br>' . mysql_error() ;
        }
       die($error);
    }
    return $result;
}

Answer 1

If you stick to pure non-proprietary (ANSI) SQL you won't have to modify any queries. An example of where you might be using proprietary extensions is in pagination. If you're doing any pagination within your web application it is highly likely that you're using something like this:

select id, name, created from thing limit(0,20)

That query won't work on Oracle, as limit is a proprietary extension to MySql, you would have to go about pagination using Oracle's rownum (which can only take one argument), so essentially you will have to rewrite your pagination queries to look like this:

select * 
  from ( select /*+ FIRST_ROWS(n) */ 
  a.*, ROWNUM rnum 
      from ( your_query_goes_here, 
      with order by ) a 
      where ROWNUM <= 
      :MAX_ROW_TO_FETCH ) 
where rnum  >= :MIN_ROW_TO_FETCH;

Also consider that you'll be using one of the Oracle extensions, so your database connection, manipulation and error handling code will have to be rewritten too (example from the oci8 extension docs) and will look more like:

if ($c = oci_connect("hr", "hr_password", "localhost/XE")) {
   echo "Successfully connected to Oracle.";
   oci_close($c);
 } else {
   $err = oci_error();
   echo "Oracle Connect Error " . $err['text'];
 }
   // Select Data...
   $s = oci_parse($c, "select * from tab1");
   oci_execute($s, OCI_DEFAULT);
   while (oci_fetch($s)) {
     echo "COL1 = " . oci_result($s, "COL1") .
        ", COL2 = " . oci_result($s, "COL2") . "<br>\n";
   }

So as you can see it is no trivial feat, especially not if you have tons of MySql-hardwired code.

If the portability of your application is a major concern you should seriously consider using a library that allows you to abstract out the database vendor altogether. I use Zend_Db (which incidentally supports Oracle) but there are others out there.

Answer 2

"If the portability of your application is a major concern you should seriously consider using a library that allows you to abstract out the database vendor altogether."

Sorry, I say that's a bad idea that solves nothing at all. Shift your dependencies from being on some particular DBMS vendor to being on some particular library vendor/supplier. The latter have proven to be a much more stable factor in the IT landscape than the DBMS vendors, no ?

Answer 3

First why do you want to be able to switch database backends? It is truly rarely a needed feature.

Second, if you want databases to function well, you can't stick to ANSII standard queries. Almost oall of the way s that a datbase can be optimized for performance are database vendor specific. Much better to learn how to correctly use the backend you have than to try to make it so you could possibly switch at any time to some other backend.

Third, Oracle's version of SQl is much differnt from MySQL and the administration of Oracle databases is much more complex. I wouldn't consider supporting it unless I hired an Oracle expert.

Answer 4

Oracle do have a tool to support migration from MySQL to Oracle. It's an extension to their SQL Developer IDE.

Answer 5

"But the rest of us should shape our applications to the database they currently run on."

I think I addressed that when I said "If a developer wants to guard himself against such problems". Note the very first word "IF".

Much obliged for another downvote by someone who actually didn't bother to READ my message CAREFULLY.