PHP,SQL,ADODB,API,every,database,accessed,sli
Quick Search for:  in language:    
PHP,SQL,ADODB,API,every,database,accessed,sli
   Code/Articles » |  Newest/Best » |  Community » |  Jobs » |  Other » |  Goto » | 
CategoriesSearch Newest CodeCoding ContestCode of the DayAsk A ProJobsUpload
PHP Stats

 Code: 66,306 lines
 Jobs: 13 postings

 
Sponsored by:

 

You are in:

 
Login



Latest Code Ticker for PHP.
Simple Forum
By Chas Pardee on 6/20


Click here to see a screenshot of this code!Squiggles
By Chas Pardee on 6/20

(Screen Shot)

photo guide
By Chas Pardee on 6/20


Download Script
By YANP on 6/19


Logging IP's
By Klaus Andersen on 6/18


Server Uptime
By Martin C. Conniffe on 6/15


Send a Page/Link to a Friend
By rae the coder on 6/15


Display contents of a directory and include it in a text area
By Daniel Friedman on 6/15


RaSMail
By Alberto Sartori on 6/13


Click here to put this ticker on your site!


Add this ticker to your desktop!


Daily Code Email
To join the 'Code of the Day' Mailing List click here!





Affiliate Sites



 
 
   

A Tutorial on Porting MySQL code to other Databases using ADODB

Print
Email
 

Submitted on: 12/19/2000 8:31:15 AM
By: John Lim  
Level: Intermediate
User Rating: By 2 Users
Compatibility:PHP 4.0

Users have accessed this article 12978 times.
 
(About the author)
 
     In PHP every database is accessed slightly differently. To connect to MySQL, you would use mysql_connect(); when you decide to upgrade to Oracle or Microsoft SQL Server, you would use ocilogon() or mssql_connect() respectively. What is worse is that the parameters you use for the different connect functions are different also.. That is why a database wrapper library such as ADODB comes in handy when you need to ensure portability. It provides you with a common API to communicate with any supported database so you don't have to call things off.

 
 
Terms of Agreement:   
By using this article, you agree to the following terms...   
1) You may use this article in your own programs (and may compile it into a program and distribute it in compiled format for langauges that allow it) freely and with no charge.   
2) You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.   
3) You may link to this article from another website, but ONLY if it is not wrapped in a frame. 
4) You will abide by any additional copyright restrictions which the author may have placed in the article or article's description.
 You say eether and I say eyether, 
    You say neether and I say nyther; 
    Eether, eyether, neether, nyther - 
    Let's call the whole thing off ! 
    
You like potato and I like po-tah-to, You like tomato and I like to-mah-to; Potato, po-tah-to, tomato, to-mah-to - Let's call the whole thing off !

I love this song, especially the version with Louis Armstrong and Ella singing duet. It is all about how hard it is for two people in love to be compatible with each other. It's about compromise and finding a common ground, and that's what this article is all about.

PHP is all about creating dynamic web-sites with the least fuss and the most fun. To create these websites we need to use databases to retrieve login information, to splash dynamic news onto the web page and store forum postings. So let's say we were using the popular MySQL database for this. Your company has done such a fantastic job that the Web site is more popular than your wildest dreams. You find that MySQL cannot scale to handle the workload; time to switch databases.

Unfortunately in PHP every database is accessed slightly differently. To connect to MySQL, you would use mysql_connect(); when you decide to upgrade to Oracle or Microsoft SQL Server, you would use ocilogon() or mssql_connect() respectively. What is worse is that the parameters you use for the different connect functions are different also.. One database says po-tato, the other database says pota-to. Oh-oh.

Let's NOT call the whole thing off

A database wrapper library such as ADODB comes in handy when you need to ensure portability. It provides you with a common API to communicate with any supported database so you don't have to call things off.

ADODB stands for Active Data Objects DataBase (sorry computer guys are sometimes not very original). ADODB currently supports MySQL, PostgreSQL, Oracle, Interbase, Microsoft SQL Server, Access, FoxPro, Sybase, ODBC and ADO. You can download ADODB from http://php.weblogs.com/adodb.

MySQL Example

The most common database used with PHP is MySQL, so I guess you should be familiar with the following code. It connects to a MySQL server at localhost, database mydb, and executes an SQL select statement. The results are printed, one line per row.

$db = mysql_connect("localhost", "root", "password");
    mysql_select_db("mydb",$db);
    $result = mysql_query("SELECT * FROM employees",$db);
    if ($result === false) die("failed"); 
    while ($fields = mysql_fetch_row($result)) {
    for ($i=0, $max=sizeof($fields); $i < $max; $i++) {
    print $fields[$i].' ';
    }
    print "
\\n"; }

The above code has been color-coded by section. The first section is the connection phase. The second is the execution of the SQL, and the last section is displaying the fields. The while loop scans the rows of the result, while the for loop scans the fields in one row.

Here is the equivalent code in ADODB

 include("adodb.inc.php");
    ADOLoadCode('mysql');
    $db = NewADOConnection();
    $db->Connect("localhost", "root", "password", "mydb");
    $result = $db->Execute("SELECT * FROM employees");
    if ($result === false) die("failed"); 
    while (!$result->EOF) {
    for ($i=0, $max=$result->FieldCount(); $i < $max; $i++)
    print $result->fields[$i].' ';
    $result->MoveNext();
    print "
\\n"; }

Now porting to Oracle is as simple as changing the second line to ADOLoadCode('oracle'). Let's walk through the code...

Connecting to the Database

include("adodb.inc.php");
    ADOLoadCode('mysql');
    $db = NewADOConnection();
    $db->Connect("localhost", "root", "password", "mydb");

The connection code is a bit more sophisticated than MySQL's because our needs are more sophisticated. In ADODB, we use an object-oriented approach to managing the complexity of handling multiple databases. We have different classes to handle different databases. If you aren't familiar with object-oriented programing, don't worry -- the complexity is all hidden away in the NewADOConnection() function.

To conserve memory, we only load the PHP code specific to the database you are connecting to. We do this by calling ADOLoadCode(databasedriver). Legal database drivers include mysql, mssql, oracle, oci8, postgres, sybase, vfp, access, ibase and many others.

Then we create a new instance of the connection class by calling NewADOConnection(). Finally we connect to the database using $db->Connect().

Executing the SQL

$result = $db->Execute("SELECT * FROM employees");
if ($result === false) die("failed")
;

Sending the SQL statement to the server is straight forward. Execute() will return a recordset object on successful execution. You should check $result as we do above.

An issue that confuses beginners is the fact that we have two types of objects in ADODB, the connection object and the recordset object. When do we use each?

The connection object ($db) is responsible for connecting to the database, formatting your SQL and querying the database server. The recordset object ($result) is responsible for retrieving the results and formatting the reply as text or as an array.

The only thing I need to add is that ADODB provides several helper functions for making INSERT and UPDATE statements easier, which we will cover in the Advanced section.

Retrieving the Data

while (!$result->EOF) {
    for ($i=0, $max=$result->FieldCount(); $i < $max; $i++)
    print $result->fields[$i].' ';
    $result->MoveNext();
    print "
\\n"; }

The paradigm for getting the data is that it's like reading a file. For every line, we check first whether we have reached the end-of-file (EOF). While not end-of-file, loop through each field in the row. Then move to the next line (MoveNext) and repeat.

The $result->fields[] array is generated by the PHP database extension. Some database extensions do not index the array by field name (unlike MySQL). To guarantee portability, use $result->Fields($fieldname). Note that this is a function, not an array.

Other Useful Functions

$recordset->Move($pos) scrolls to that particular row. ADODB supports forward scrolling for all databases. Some databases will not support backwards scrolling. This is normally not a problem as you can always cache records to simulate backwards scrolling.

$recordset->RecordCount() returns the number of records accessed by the SQL statement. Some databases will return -1 because it is not supported.

$recordset->GetArray() returns the result as an array.

rs2html($recordset) is a function that is generates a HTML table based on the $recordset passed to it. An example with the relevant lines in bold:

 include('adodb.inc.php'); 
    include('tohtml.inc.php'); /* includes the rs2html function */
    ADOLoadCode('mysql'); 
    $conn = &ADONewConnection;(); 
    $conn->PConnect('localhost','userid','password','database');
    $rs = $conn->Execute('select * from table');
     rs2html($rs); /* recordset to html table */ 

There are many other helper functions that are listed in the documentation available at http://php.weblogs.com/adodb_manual.

Advanced Material

Inserts and Updates

Let's say you want to insert the following data into a database.

ID = 3
TheDate=mktime(0,0,0,8,31,2001) /* 31st August 2001 */
Note= sugar why don't we call it off

When you move to another database, your insert might no longer work.

The first problem is that each database has a different default date format. MySQL expects YYYY-MM-DD format, while other databases have different defaults. ADODB has a function called DBDate() that addresses this issue by converting converting the date to the correct format.

The next problem is that the don't in the Note needs to be quoted. In MySQL, we use don\\'t but in some other databases (Sybase, Access, Microsoft SQL Server) we use don''t. The qstr() function addresses this issue.

So how do we use the functions? Like this:

$sql = "INSERT INTO table (id, thedate,note) values (" 
    . $ID . ','
    . $db->DBDate($TheDate) .','
    . $db->qstr($Note).")";
    $db->Execute($sql);

ADODB also supports $connection->Affected_Rows() (returns the number of rows affected by last update or delete) and $recordset->Insert_ID() (returns last autoincrement number generated by an insert statement). Be forewarned that not all databases support the two functions.

MetaTypes

You can find out more information about each of the fields (I use the words fields and columns interchangebly) you are selecting by calling the recordset method FetchField($fieldoffset). This will return an object with 3 properties: name, type and max_length.

For example:
$recordset = $conn->Execute("select adate from table");
$f0 = $recordset->FetchField(0);

Then $f0->name will hold 'adata', $f0->type will be set to 'date'. If the max_length is unknown, it will be set to -1.

One problem with handling different databases is that each database often calls the same type by a different name. For example a timestamp type is called datetime in one database and time in another. So ADODB has a special MetaType($type, $max_length) function that standardises the types to the following:

C: character type
B: blob or long character type (eg. more than 255 bytes wide).
D: date
T: timestamp
L: logical (boolean)
N: numeric (float, double, integer)

In the above date example,

$recordset = $conn->Execute("select adate from table");
$f0 = $recordset->FetchField(0);
$type = $recordset->MetaType($f0->type, $f0->max_length);
print $type; /* should print 'D'
*/

PHP4 Session Handler Support

ADODB also supports PHP4 session handlers. You can store your session variables in a database for true scalability using ADODB. For further information, visit http://php.weblogs.com/adodb-sessions

Commercial Use Encouraged

If you plan to write commercial PHP applications that you want to resell, you should consider ADODB. It has been released using the lesser GPL, which means you can legally include it in commercial applications, while keeping your code proprietary. Commercial use of ADODB is strongly encouraged! We are using it internally for this reason.

Conclusion

As a thank you for finishing this article, here are the complete lyrics for let's call the whole thing off.

    Refrain 
    
You say eether and I say eyether, You say neether and I say nyther; Eether, eyether, neether, nyther - Let's call the whole thing off !
You like potato and I like po-tah-to, You like tomato and I like to-mah-to; Potato, po-tah-to, tomato, to-mah-to - Let's call the whole thing off !
But oh, if we call the whole thing off, then we must part. And oh, if we ever part, then that might break my heart.
So, if you like pajamas and I like pa-jah-mas, I'll wear pajamas and give up pa-jah-mas. For we know we Need each other, so we Better call the calling off off. Let's call the whole thing off !
Second Refrain
You say laughter and I say lawfter, You say after and I say awfter; Laughter, lawfter, after, awfter - Let's call the whole thing off !
You like vanilla and I like vanella, You, sa's'parilla and I sa's'parella; Vanilla, vanella, choc'late, strawb'ry - Let's call the whole thing off !
But oh, if we call the whole thing off, then we must part. And oh, if we ever part, then that might break my heart.
So, if you go for oysters and I go for ersters, I'll order oysters and cancel the ersters. For we know we Need each other, so we Better call the calling off off. Let's call the whole thing off !

Song and lyrics by George and Ira Gershwin, introduced by Fred Astaire and Ginger Rogers in the film "Shall We Dance?"


Other 1 submission(s) by this author

 

 
Report Bad Submission
Use this form to notify us if this entry should be deleted (i.e contains no code, is a virus, etc.).
Reason:
 
Your Vote!

What do you think of this article(in the Intermediate category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor See Voting Log
 
Other User Comments
2/14/2001 6:11:41 AM:Pete
I think your looking for www.planetgershwin.com
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
Add Your Feedback!
Note:Not only will your feedback be posted, but an email will be sent to the code's author in your name.

NOTICE: The author of this article has been kind enough to share it with you.  If you have a criticism, please state it politely or it will be deleted.

For feedback not related to this particular article, please click here.
 
Name:
Comment:

 

Categories | Articles and Tutorials | Advanced Search | Recommended Reading | Upload | Newest Code | Code of the Month | Code of the Day | All Time Hall of Fame | Coding Contest | Search for a job | Post a Job | Ask a Pro Discussion Forum | Live Chat | Feedback | Customize | PHP Home | Site Home | Other Sites | About the Site | Feedback | Link to the Site | Awards | Advertising | Privacy

Copyright© 1997 by Exhedra Solutions, Inc. All Rights Reserved.  By using this site you agree to its Terms and Conditions.  Planet Source Code (tm) and the phrase "Dream It. Code It" (tm) are trademarks of Exhedra Solutions, Inc.