library/adodb/tute.htm

   1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
   2
   3 <html>
   4 <head>
   5         <title>Tutorial: Moving from MySQL to ADODB</title>
   6 </head>
   7
   8 <body bgcolor=white>
   9 <h1>Tutorial: Moving from MySQL to ADODB</h1>
  10
  11 <pre>           You say eether and I say eyether,
  12                 You say neether and I say nyther;
  13                 Eether, eyether, neether, nyther -
  14                 Let's call the whole thing off !
  15 <br>
  16                 You like potato and I like po-tah-to,
  17                 You like tomato and I like to-mah-to;
  18                 Potato, po-tah-to, tomato, to-mah-to -
  19                 Let's call the whole thing off !
  20 </pre>
  21 <p>I love this song, especially the version with Louis Armstrong and Ella singing
  22   duet. It is all about how hard it is for two people in love to be compatible
  23   with each other. It's about compromise and finding a common ground, and that's
  24   what this article is all about.
  25 <p>PHP is all about creating dynamic web-sites with the least fuss and the most
  26   fun. To create these websites we need to use databases to retrieve login information,
  27   to splash dynamic news onto the web page and store forum postings. So let's
  28   say we were using the popular MySQL database for this. Your company has done
  29   such a fantastic job that the Web site is more popular than your wildest dreams.
  30   You find that MySQL cannot scale to handle the workload; time to switch databases.
  31 <p> Unfortunately in PHP every database is accessed slightly differently. To connect
  32   to MySQL, you would use <i>mysql_connect()</i>; when you decide to upgrade to
  33   Oracle or Microsoft SQL Server, you would use <i>ocilogon() </i>or <i>mssql_connect()</i>
  34   respectively. What is worse is that the parameters you use for the different
  35   connect functions are different also.. One database says po-tato, the other
  36   database says pota-to. Oh-oh.
  37 <h3>Let's NOT call the whole thing off</h3>
  38 <p>A database wrapper library such as ADODB comes in handy when you need to ensure portability. It provides
  39   you with a common API to communicate with any supported database so you don't have to call things off. <p>
  40
  41 <p>ADODB stands for Active Data Objects DataBase (sorry computer guys are sometimes
  42   not very original). ADODB currently supports MySQL, PostgreSQL, Oracle, Interbase,
  43   Microsoft SQL Server, Access, FoxPro, Sybase, ODBC and ADO. You can download
  44   ADODB from <a href=http://php.weblogs.com/adodb></a><a href="http://php.weblogs.com/adodb">http://php.weblogs.com/adodb</a>.
  45 <h3>MySQL Example</h3>
  46 <p>The most common database used with PHP is MySQL, so I guess you should be familiar
  47   with the following code. It connects to a MySQL server at <i>localhost</i>,
  48   database <i>mydb</i>, and executes an SQL select statement. The results are
  49   printed, one line per row.
  50 <pre><font color="#666600">$db = <b>mysql_connect</b>(&quot;localhost&quot;, &quot;root&quot;, &quot;password&quot;);
  51 <b>mysql_select_db</b>(&quot;mydb&quot;,$db);</font>
  52 <font color="#660000">$result = <b>mysql_query</b>(&quot;SELECT * FROM employees&quot;,$db)</font><code><font color="#663300">;
  53 if ($result === false) die(&quot;failed&quot;);</font></code>
  54 <font color="#006666"><b>while</b> ($fields =<b> mysql_fetch_row</b>($result)) &#123;
  55  <b>for</b> ($i=0, $max=sizeof($fields); $i &lt; $max; $i++) &#123;
  56                 <b>print</b> $fields[$i].' ';
  57  &#125;
  58  <b>print</b> &quot;&lt;br&gt;\n&quot;;
  59 &#125;</font>
  60 </pre>
  61 <p>The above code has been color-coded by section. The first section is the connection
  62   phase. The second is the execution of the SQL, and the last section is displaying
  63   the fields. The <i>while</i> loop scans the rows of the result, while the <i>for</i>
  64   loop scans the fields in one row.</p>
  65 <p>Here is the equivalent code in ADODB</p>
  66 <pre><b><font color="#666600"> include(&quot;adodb.inc.php&quot;);</font></b><font color="#666600">
  67  $db = <b>NewADOConnection</b>('mysql');
  68  $db-&gt;<b>Connect</b>(&quot;localhost&quot;, &quot;root&quot;, &quot;password&quot;, &quot;mydb&quot;);</font>
  69  <font color="#663300">$result = $db-&gt;<b>Execute</b>(&quot;SELECT * FROM employees&quot;);
  70  </font><font color="#663300"></font><code><font color="#663300">if ($result === false) die(&quot;failed&quot;)</font></code><code><font color="#663300">;</font></code>
  71  <font color="#006666"><b>while</b> (!$result-&gt;EOF) &#123;
  72         <b>for</b> ($i=0, $max=$result-&gt;<b>FieldCount</b>(); $i &lt; $max; $i++)
  73                    <b>print</b> $result-&gt;fields[$i].' ';
  74         $result-&gt;<b>MoveNext</b>();
  75         <b>print</b> &quot;&lt;br&gt;\n&quot;;
  76  &#125;</font> </pre>
  77 <p></p>
  78 <p>Now porting to Oracle is as simple as changing the second line to <code>NewADOConnection('oracle')</code>.
  79   Let's walk through the code...</p>
  80 <h3>Connecting to the Database</h3>
  81 <p></p>
  82 <pre><b><font color="#666600">include(&quot;adodb.inc.php&quot;);</font></b><font color="#666600">
  83 $db = <b>NewADOConnection</b>('mysql');
  84 $db-&gt;<b>Connect</b>(&quot;localhost&quot;, &quot;root&quot;, &quot;password&quot;, &quot;mydb&quot;);</font></pre>
  85 <p>The connection code is a bit more sophisticated than MySQL's because our needs
  86   are more sophisticated. In ADODB, we use an object-oriented approach to managing
  87   the complexity of handling multiple databases. We have different classes to
  88   handle different databases. If you aren't familiar with object-oriented programing,
  89   don't worry -- the complexity is all hidden away in the<code> NewADOConnection()</code>
  90   function.</p>
  91 <p>To conserve memory, we only load the PHP code specific to the database you
  92   are connecting to. We do this by calling <code>NewADOConnection(databasedriver)</code>.
  93   Legal database drivers include <i>mysql, mssql, oracle, oci8, postgres, sybase,
  94   vfp, access, ibase </i>and many others.</p>
  95 <p>Then we create a new instance of the connection class by calling <code>NewADOConnection()</code>.
  96   Finally we connect to the database using <code>$db-&gt;Connect(). </code></p>
  97 <h3>Executing the SQL</h3>
  98 <p><code><font color="#663300">$result = $db-&gt;<b>Execute</b>(&quot;SELECT *
  99   FROM employees&quot;);<br>
 100   if ($result === false) die(&quot;failed&quot;)</font></code><code><font color="#663300">;</font></code>
 101   <br>
 102 </p>
 103 <p>Sending the SQL statement to the server is straight forward. Execute() will
 104   return a recordset object on successful execution. You should check $result
 105   as we do above.
 106 <p>An issue that confuses beginners is the fact that we have two types of objects
 107   in ADODB, the connection object and the recordset object. When do we use each?
 108 <p>The connection object ($db) is responsible for connecting to the database,
 109   formatting your SQL and querying the database server. The recordset object ($result)
 110   is responsible for retrieving the results and formatting the reply as text or
 111   as an array.
 112 <p>The only thing I need to add is that ADODB provides several helper functions
 113   for making INSERT and UPDATE statements easier, which we will cover in the Advanced
 114   section.
 115 <h3>Retrieving the Data<br>
 116 </h3>
 117 <pre><font color="#006666"><b>while</b> (!$result-&gt;EOF) &#123;
 118    <b>for</b> ($i=0, $max=$result-&gt;<b>FieldCount</b>(); $i &lt; $max; $i++)
 119            <b>print</b> $result-&gt;fields[$i].' ';
 120    $result-&gt;<b>MoveNext</b>();
 121    <b>print</b> &quot;&lt;br&gt;\n&quot;;
 122 &#125;</font></pre>
 123 <p>The paradigm for getting the data is that it's like reading a file. For every
 124   line, we check first whether we have reached the end-of-file (EOF). While not
 125   end-of-file, loop through each field in the row. Then move to the next line
 126   (MoveNext) and repeat.
 127 <p>The <code>$result-&gt;fields[]</code> array is generated by the PHP database
 128   extension. Some database extensions do not index the array by field name.
 129   To force indexing by name - that is associative arrays -
 130   use the $ADODB_FETCH_MODE global variable.
 131 <pre>
 132         $<b>ADODB_FETCH_MODE</b> = ADODB_FETCH_NUM;
 133         $rs1 = $db->Execute('select * from table');
 134         $<b>ADODB_FETCH_MODE</b> = ADODB_FETCH_ASSOC;
 135         $rs2 = $db->Execute('select * from table');
 136         print_r($rs1->fields); // shows <i>array([0]=>'v0',[1] =>'v1')</i>
 137         print_r($rs2->fields); // shows <i>array(['col1']=>'v0',['col2'] =>'v1')</i>
 138 </pre>
 139 <p>
 140 As you can see in the above example, both recordsets store and use different fetch modes
 141 based on the $ADODB_FETCH_MODE setting when the recordset was created by Execute().</p>
 142 <h2>ADOConnection<a name="ADOConnection"></a></h2>
 143 <p>Object that performs the connection to the database, executes SQL statements
 144   and has a set of utility functions for standardising the format of SQL statements
 145   for issues such as concatenation and date formats.</p>
 146
 147 <h3>Other Useful Functions</h3>
 148 <p><code>$recordset-&gt;Move($pos)</code> scrolls to that particular row. ADODB supports forward
 149   scrolling for all databases. Some databases will not support backwards scrolling.
 150   This is normally not a problem as you can always cache records to simulate backwards
 151   scrolling.
 152 <p><code>$recordset-&gt;RecordCount()</code> returns the number of records accessed by the
 153   SQL statement. Some databases will return -1 because it is not supported.
 154 <p><code>$recordset-&gt;GetArray()</code> returns the result as an array.
 155 <p><code>rs2html($recordset)</code> is a function that is generates a HTML table based on the
 156   $recordset passed to it. An example with the relevant lines in bold:
 157 <pre>   include('adodb.inc.php');
 158    <b>include('tohtml.inc.php');</b> /* includes the rs2html function */
 159    $conn = &amp;ADONewConnection('mysql');
 160    $conn-&gt;PConnect('localhost','userid','password','database');
 161    $rs = $conn-&gt;Execute('select * from table');
 162   <b> rs2html($rs)</b>; /* recordset to html table */ </pre>
 163 <p>There are many other helper functions that are listed in the documentation available at <a href="http://php.weblogs.com/adodb_manual"></a><a href="http://php.weblogs.com/adodb_manual">http://php.weblogs.com/adodb_manual</a>.
 164 <h2>Advanced Material</h2>
 165 <h3>Inserts and Updates </h3>
 166 <p>Let's say you want to insert the following data into a database.
 167 <p><b>ID</b> = 3<br>
 168   <b>TheDate</b>=mktime(0,0,0,8,31,2001) /* 31st August 2001 */<br>
 169   <b>Note</b>= sugar why don't we call it off
 170 <p>When you move to another database, your insert might no longer work.</p>
 171 <p>The first problem is that each database has a different default date format.
 172   MySQL expects YYYY-MM-DD format, while other databases have different defaults.
 173   ADODB has a function called DBDate() that addresses this issue by converting
 174   converting the date to the correct format.</p>
 175 <p>The next problem is that the <b>don't</b> in the Note needs to be quoted. In
 176   MySQL, we use <b>don\'t</b> but in some other databases (Sybase, Access, Microsoft
 177   SQL Server) we use <b>don''t. </b>The qstr() function addresses this issue.</p>
 178 <p>So how do we use the functions? Like this:</p>
 179 <pre>$sql = &quot;INSERT INTO table (id, thedate,note) values (&quot;
 180    . $<b>ID</b> . ','
 181    . $db-&gt;DBDate($<b>TheDate</b>) .','
 182    . $db-&gt;qstr($<b>Note</b>).&quot;)&quot;;
 183 $db-&gt;Execute($sql);</pre>
 184 <p>ADODB also supports <code>$connection-&gt;Affected_Rows()</code> (returns the
 185   number of rows affected by last update or delete) and <code>$recordset-&gt;Insert_ID()</code>
 186   (returns last autoincrement number generated by an insert statement). Be forewarned
 187   that not all databases support the two functions.<br>
 188 </p>
 189 <h3>MetaTypes</h3>
 190 <p>You can find out more information about each of the fields (I use the words
 191   fields and columns interchangebly) you are selecting by calling the recordset
 192   method <code>FetchField($fieldoffset)</code>. This will return an object with
 193   3 properties: name, type and max_length.
 194 <pre>For example:</pre>
 195 <pre>$recordset = $conn-&gt;Execute(&quot;select adate from table&quot;);<br>$f0 = $recordset-&gt;FetchField(0);
 196 </pre>
 197 <p>Then <code>$f0-&gt;name</code> will hold <i>'adata'</i>, <code>$f0-&gt;type</code>
 198   will be set to '<i>date'</i>. If the max_length is unknown, it will be set to
 199   -1.
 200 <p>One problem with handling different databases is that each database often calls
 201   the same type by a different name. For example a <i>timestamp</i> type is called
 202   <i>datetime</i> in one database and <i>time</i> in another. So ADODB has a special
 203   <code>MetaType($type, $max_length)</code> function that standardises the types
 204   to the following:
 205 <p>C: character and varchar types<br>
 206   X: text or long character (eg. more than 255 bytes wide).<br>
 207   B: blob or binary image<br>
 208   D: date<br>
 209   T: timestamp<br>
 210   L: logical (boolean)<br>
 211   I: integer<br>
 212   N: numeric (float, double, money)
 213 <p>In the above date example,
 214 <p><code>$recordset = $conn-&gt;Execute(&quot;select adate from table&quot;);<br>
 215   $f0 = $recordset-&gt;FetchField(0);<br>
 216   $type = $recordset-&gt;MetaType($f0-&gt;type, $f0-&gt;max_length);<br>
 217   print $type; /* should print 'D'</code> */
 218 <p>
 219 <p><b>Select Limit and Top Support</b>
 220 <p>ADODB has a function called $connection->SelectLimit($sql,$nrows,$offset) that allows
 221 you to retrieve a subset of the recordset. This will take advantage of native
 222 SELECT TOP on Microsoft products and SELECT ... LIMIT with PostgreSQL and MySQL, and
 223 emulated if the database does not support it.
 224 <p><b>Caching Support</b>
 225 <p>ADODB allows you to cache recordsets in your file system, and only requery the database
 226 server after a certain timeout period with $connection->CacheExecute($secs2cache,$sql) and
 227 $connection->CacheSelectLimit($secs2cache,$sql,$nrows,$offset).
 228 <p><b>PHP4 Session Handler Support</b>
 229 <p>ADODB also supports PHP4 session handlers. You can store your session variables
 230   in a database for true scalability using ADODB. For further information, visit
 231   <a href="http://php.weblogs.com/adodb-sessions"></a><a href="http://php.weblogs.com/adodb-sessions">http://php.weblogs.com/adodb-sessions</a>
 232 <h3>Commercial Use Encouraged</h3>
 233 <p>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.<p>
 234
 235 <h2>Conclusion</h2>
 236 <p>As a thank you for finishing this article, here are the complete lyrics for
 237   <i>let's call the whole thing off</i>.<br>
 238   <br>
 239 <pre>
 240    Refrain
 241 <br>
 242                 You say eether and I say eyether,
 243                 You say neether and I say nyther;
 244                 Eether, eyether, neether, nyther -
 245                 Let's call the whole thing off !
 246 <br>
 247                 You like potato and I like po-tah-to,
 248                 You like tomato and I like to-mah-to;
 249                 Potato, po-tah-to, tomato, to-mah-to -
 250                 Let's call the whole thing off !
 251 <br>
 252 But oh, if we call the whole thing off, then we must part.
 253 And oh, if we ever part, then that might break my heart.
 254 <br>
 255                 So, if you like pajamas and I like pa-jah-mas,
 256                 I'll wear pajamas and give up pa-jah-mas.
 257                 For we know we
 258                 Need each other, so we
 259                 Better call the calling off off.
 260                 Let's call the whole thing off !
 261 <br>
 262    Second Refrain
 263 <br>
 264                 You say laughter and I say lawfter,
 265                 You say after and I say awfter;
 266                 Laughter, lawfter, after, awfter -
 267                 Let's call the whole thing off !
 268 <br>
 269                 You like vanilla and I like vanella,
 270                 You, sa's'parilla and I sa's'parella;
 271                 Vanilla, vanella, choc'late, strawb'ry -
 272                 Let's call the whole thing off !
 273 <br>
 274 But oh, if we call the whole thing off, then we must part.
 275 And oh, if we ever part, then that might break my heart.
 276 <br>
 277                 So, if you go for oysters and I go for ersters,
 278                 I'll order oysters and cancel the ersters.
 279                 For we know we
 280                 Need each other, so we
 281                 Better call the calling off off.
 282                 Let's call the whole thing off !
 283   </pre>
 284 <p><font size=2>Song and lyrics by George and Ira Gershwin, introduced by Fred Astaire and Ginger Rogers
 285 in the film "Shall We Dance?"  </font><p>
 286 <p>
 287 (c)2001-2002 John Lim.
 288
 289 </body>
 290 </html>