Would it be difficult to switch from MySQL to Oracle?

Question

Below is an example of code on how I run my mysql queries, I run them through a function, which, I think, can be simplified when switching databases.

Below is an example of the mysql query I'm running, and below, this is the actual function.

Would it be difficult to change to another type of database, for example, oracle or some other, if I ever decided to use this setting?

Could you just change the function, or will 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 adhere to pure proprietary (ANSI) SQL, you will not have to modify any queries. An example of where you can use proprietary extensions is pagination. If you are doing any pagination in your web application, it is very likely that you are using something like this:

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

This query will not work on Oracle, since limit is a proprietary extension for MySql, you will need to use pagination using the Oracle rownum (which can take only one argument), so you will have to rewrite your pagination requests 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 think that you will use one of the Oracle extensions, so your database connection, manipulation and error handling code should also be overwritten ( example from the oci8 extension documents ) and will look more:

 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 you can see that this is not a trivial feat, especially if you have tons of code protected by MySql.

If portability of your application is a serious problem, you should seriously consider using a library that allows you to completely abstract the database provider. I use Zend_Db (which, incidentally, supports Oracle), but there are others.

Answer 2

First, why do you want to be able to switch databases? This is really rarely required.

Secondly, if you want databases to function well, you cannot stick to standard ANSII queries. Almost all of the ways that a database can be optimized for performance are dependent on the database provider. It is much better to learn how to properly use the backend you have than to try to do this so that you can switch to another server at any time.

Third, the Oracle SQl version is significantly different from MySQL, and Oracle database administration is much more complicated. I would not support him until I hired an Oracle expert.

Answer 3

"If portability of your application is a serious problem, you should seriously consider using a library that allows you to completely abstract the database provider."

Sorry, I'm saying a bad idea that doesn't solve anything at all. Shift your dependencies so that a particular DBMS provider is on a particular library provider / provider. The latter turned out to be a much more stable factor in the IT landscape than DBMS vendors, no?

Answer 4

Oracle has a tool to support migration from MySQL to Oracle. This is an extension of their SQL Developer IDE .

Answer 5

"But everyone else needs to form our applications in the database in which they currently work."

I think I turned to this when I said: "If the developer wants to protect himself from such problems." Pay attention to the very first word "IF".

Much owes to another downvote to those who are not really worried TO READ my message CAREFULLY.