Tutorial: going from MySQL to ADODB Everyone loves this track, particularly the variation with Louis Armstrong and Ella duet that is singing.

Tutorial: going from MySQL to ADODB Everyone loves this track, particularly the variation with Louis Armstrong and Ella duet that is singing.

it’s all on how difficult it really is for just two individuals in like to be appropriate for one another. It is about compromise and finding a typical ground, and that is exactly just what this informative article is exactly about.

PHP is focused on producing powerful web-sites because of the minimum hassle additionally the most fun. To generate these sites we have to utilize databases to recover login information, to splash news that is dynamic the net web web page and store forum postings. Therefore let’s imagine we had been utilising the popular MySQL database for this. Your organization has been doing this type of fantastic task that the website is very popular than your wildest ambitions. You will find that MySQL cannot measure to address the workload; time and energy to switch databases.

Let’s NOT call the whole thing off

A database wrapper collection such as for example ADODB is available in handy if you want to make certain portability.

It offers you having a typical api to communicate with any supported database so that you don’t need to call things down.

ADODB is short for Active Data Objects DataBase (sorry computer dudes are often not so initial). ADODB presently supports MySQL, PostgreSQL, Oracle, Interbase, Microsoft SQL Server, Access, FoxPro, Sybase, ODBC and ADO. You can easily install ADODB from.

MySQL Example

The most typical database combined with PHP is MySQL, thus I guess you need to be acquainted with the following rule. It links to a MySQL host at localhost, database mydb, and executes an SQL statement that is select. The outcome are printed, one line per line.

The above mentioned rule was color-coded by area. The section that is first the bond period. the execution associated with SQL, additionally the section that is last showing the areas. The while loop scans the rows of this total result, as the concerning cycle scans the areas in one single line.

This is actually the comparable rule in ADODB

Now porting to Oracle is really as straightforward as changing the line that is second NewADOConnection(‘oracle’) . Let us walk through the rule.

Linking into the Database

The bond rule is a little more advanced than MySQL’s because our requirements are far more advanced. In ADODB, we make use of an object-oriented way of handling the complexity of managing numerous databases. We now have various classes to address databases that are different. If you aren’t knowledgeable about object-oriented programing, don’t be concerned — the complexity is all concealed away in the NewADOConnection() function.

Performing the SQL

Giving the statement that is SQL the host is hassle free.

Execute() will get back a recordset object on effective execution. You should check $result as we do above.

A problem that confuses novices may be the undeniable fact that we’ve 2 kinds of things in ADODB, the bond item additionally the recordset object. Whenever do we make use of each?

The text item ($db) is in charge of linking towards the database, formatting your SQL and querying the database host. The recordset item ($outcome) is in charge of retrieving the outcomes and formatting the response as text or as a selection.

The thing that is only have to include is the fact that ADODB provides several helper functions in making INSERT and MODIFY statements easier, which we are going to protect into the Advanced part.

Retrieving the information

The paradigm to get the information is the fact that it is like reading a file. For each line, we check first whether we’ve reached the end-of-file (EOF). Whilst not end-of-file, cycle through each industry within the line. Then relocate to the next line (MoveNext) and perform.

The $result-fields[] array is produced by the PHP database extension. Some database foreignbride.net/nicaragua-women extensions usually do not index the array by industry title. To force indexing by title – that is associative arrays – utilize the $ADODB_FETCH_MODE global variable.

Both recordsets store and use different fetch modes based on the $ADODB_FETCH_MODE setting when the recordset was created by Execute() as you can see in the above example.


Object that executes the bond to your database, executes statements that are SQL has a couple of energy functions for standardising the structure of SQL statements for dilemmas such as for instance concatenation and date platforms.

Other functions that are useful

$recordset-Move($pos) scrolls compared to that particular line. ADODB supports ahead scrolling for many databases. Some databases will likely not help backwards scrolling. That is typically maybe not really issue as you’re able to constantly cache documents to simulate backwards scrolling.

$recordset-RecordCount() comes back the wide range of documents accessed because of the SQL declaration. Some databases will get back -1 since it is perhaps perhaps maybe not supported.

$recordset-GetArray() comes back the result as a wide range.

rs2html($recordset) is just a function that is produces a HTML table in line with the $recordset passed to it. An illustration utilizing the lines that are relevant bold:

There are lots of other helper functions which can be placed in the paperwork available.

Advanced Material

Inserts and Updates

Let’s imagine you need to insert the data that are following a database.

Once you go on to another database, your insert might no further work.

The very first issue is that each database has a different sort of default date format. MySQL expects YYYY-MM-DD structure, while other databases have actually various defaults. ADODB includes a function called DBDate() that addresses this problem by transforming transforming the date towards the proper structure.

The next issue is that the do not into the Note needs to be quoted. In MySQL, we utilize don\’t but in a few other databases (Sybase, Access, Microsoft SQL Server) we use don”t. The qstr() function addresses this problem.

Just how do the functions are used by us? Similar to this:


You’ll find away additional information about all the areas (I prefer the expressed terms areas and columns interchangebly) you may be choosing by calling the recordset technique FetchField($fieldoffset) . This may get back an item with 3 properties: title, max_length and type.

Then $f0-name will hold ‘adata’, $f0-type would be set to ‘date’. In the event that max_length is unknown, it shall be set to -1.

One issue with managing various databases is the fact that each database frequently calls exactly the same kind by a name that is different. For instance a timestamp kind is named datetime within one time and database an additional. Therefore ADODB has an unique MetaType($kind, $max_length) function that standardises the kinds into the after:

C: character and varchar types X: text or long character (eg. a lot more than 255 bytes wide). B: blob or binary image D: date T: timestamp L: logical (boolean) we: integer N: numeric (float, twice, money)

Within the above date example,

Choose Limit and support that is top

ADODB features a function called $connection-SelectLimit($sql,$nrows,$offset) which allows you to definitely recover a subset for the recordset. This can make the most of indigenous CHOOSE TOP on Microsoft items and choose . LIMIT with PostgreSQL and MySQL, and emulated in the event that database will not help it.

Caching Help

PHP4 Session Handler Help

ADODB additionally supports PHP4 session handlers. You can easily keep your session factors in a database for real scalability making use of ADODB. For more info