source: sandbox/2.5.1-evolucao/phpgwapi/inc/adodb/docs/tute.htm @ 8222

Revision 8222, 15.5 KB checked in by angelo, 11 years ago (diff)

Ticket #3491 - Compatibilizar Expresso com novas versoes do PHP

  • Property svn:eol-style set to native
  • Property svn:executable set to *
Line 
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">;
53if ($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>
140As you can see in the above example, both recordsets store and use different fetch modes
141based 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 = 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
221you to retrieve a subset of the recordset. This will take advantage of native
222SELECT TOP on Microsoft products and SELECT ... LIMIT with PostgreSQL and MySQL, and
223emulated 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
226server 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>
252But oh, if we call the whole thing off, then we must part.
253And 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>
274But oh, if we call the whole thing off, then we must part.
275And 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
285in the film "Shall We Dance?"  </font><p>
286<p>
287(c)2001-2002 John Lim.
288
289</body>
290</html>
Note: See TracBrowser for help on using the repository browser.