| [ Index ] |
|
Code source de PHP PEAR 1.4.5 |
1 2 <html> 3 <head> 4 <title>MDB tutorial</title> 5 </head> 6 <body> 7 <center><h1>MDB tutorial</h1></center> 8 <hr /> 9 <ul> 10 <p><b>Authors:</b><br /> 11 Manuel Lemos (<a href="mailto:mlemos@acm.org">mlemos@acm.org</a>)<br /> 12 Lukas Smith (<a href="mailto:smith@backendmedia.com">smith@backendmedia.com</a>)</p> 13 <p><b>Version control:</b> <tt>@(#) $Id: tutorial.html,v 1.8 2003/06/01 12:54:16 lsmith Exp $</tt></p> 14 <h2>Contents</h2> 15 <li><a href="#1.0">MDB tutorial</a></li> 16 <ul> 17 <li><a href="#2.1.1">MDB requirements</a></li> 18 <ul> 19 <li><a href="#3.2.1">PHP program</a></li> 20 <li><a href="#3.2.2">PHP options</a></li> 21 <li><a href="#3.2.3">Include files</a></li> 22 </ul> 23 <li><a href="#4.1.2">Designing a database</a></li> 24 <li><a href="#24.1.3">Installing the database</a></li> 25 <li><a href="#35.1.4">Accessing to the data in the database</a></li> 26 <ul> 27 <li><a href="#37.2.1">Setup database access</a></li> 28 <li><a href="#46.2.3">Executing database queries</a></li> 29 <ul> 30 <li><a href="#47.3.1">Direct queries</a></li> 31 <li><a href="#48.3.2">Prepared queries</a></li> 32 </ul> 33 <li><a href="#50.2.4">Retrieving query results</a></li> 34 <ul> 35 <li><a href="#51.3.1">Fetching result set data</a></li> 36 <li><a href="#53.3.2">Data type conversion</a></li> 37 <li><a href="#70.3.5">Freeing the result set resources</a></li> 38 </ul> 39 <li><a href="#70.2.5">Handling large object fields</a></li> 40 <ul> 41 <li><a href="#71.3.1">Creating large object table fields</a></li> 42 <li><a href="#74.3.2">Storing data in large object fields</a></li> 43 <li><a href="#98.3.3">Retrieving data from large object fields</a></li> 44 </ul> 45 </ul> 46 <li><a href="#140.1.5">Updating the database schema</a></li> 47 </ul> 48 </ul> 49 <hr /> 50 <h1><a name="1.0">MDB tutorial</a></h1> 51 <ul> 52 <p><b>MDB</b> is a package that allows the developers to write database applications in <i>PHP</i> that are independent of the <i>DBMS</i> (<i>DataBase Management System</i>).</p> 53 <p>The steps that developers have to take to write <b>MDB</b> based applications are identical to those that are taken while using the set of native commands that are used to talk to each <i>DBMS</i> directly. The main advantage of <b>MDB</b> is that developers only need to learn and use one set of commands to implement applications that may run with many different <i>DBMS</i>.</p> 54 <p>The goal of this tutorial is to introduce the basic steps that beginners have to take to start developing database applications with <b>MDB</b>. For more in depth information, please refer to the <b>MDB</b> documentation manual.</p> 55 <h2><li><a name="2.1.1">MDB requirements</a></li></h2> 56 <p>Before start using <b>MDB</b> there are a few installation requirements that developers need to know and understand.</p> 57 <ul> 58 <h3><li><a name="3.2.1">PHP program</a></li></h3> 59 <p><i>PHP</i> is usually run from a Web server, mostly as a server module, but it can also run as <i>CGI</i> program. When <i>PHP</i> is built as <i>CGI</i> program a standalone executable program file is generated. This <i>CGI</i> program can also be used to execute <i>PHP</i> scripts from a command line interface shell.</p> 60 <p>Although it is possible do the same using <i>PHP</i> as Web server module, it is recommended that you use the <i>CGI</i> program to run the <i>PHP</i> scripts to install or setup databases using <b>MDB</b>.</p> 61 <p>The required version of PHP is 4.0.4pl1</p> 62 <h3><li><a name="3.2.2">PHP options</a></li></h3> 63 <p><i>PHP</i> has several runtime options that are meant to ease the developers' work. The option named <tt>magic_quotes_runtime</tt> is meant to automatically escape special characters in text literal values before executing <i>SQL</i> queries.</p> 64 <p><b>MDB</b> is already able to escape special characters of query text values using the methods <tt>getTextValue()</tt> and <tt>setParamText()</tt>. If either any of these methods is used and the <tt>magic_quotes_runtime</tt> option is <tt>On</tt>, then some characters may end up being escaped twice.</p> 65 <p>To avoid duplicated character escaping, please disable the option <tt>magic_quotes_runtime</tt> and also <tt>magic_quotes_sybase</tt> by setting them to the value <tt>Off</tt> in your <i>PHP</i> configuration by for instance having these lines in your <tt>php.ini</tt> file:</p> 66 <p><tt>magic_quotes_runtime = Off<br /> 67 magic_quotes_sybase = Off</tt></p> 68 <h3><li><a name="3.2.3">Include files</a></li></h3> 69 <p><b>MDB</b> is made of several <i>PHP</i> files that need to be included from your scripts, depending on what you need to do with <b>MDB</b>. Here follows the list of the most important files and when they should be included in your scripts.</p> 70 Most other files are included on demand by MDB. 71 <ul> 72 <p><li><tt>MDB.php</tt></li></p> 73 <p>This is the main API file. It should be included every time you need to interface with a database using <b>MDB</b>.</p> 74 <p><li><tt>manager.php</tt></li></p> 75 <p>This is the database definition management class file. It should be included every time you need to read or change the definition of a database. 76 It will automatically include the MDB.php file you.</p> 77 </ul> 78 </ul> 79 <h2><li><a name="4.1.2">Designing a database</a></li></h2> 80 <p>Before anything else, you need to design and install a database schema that will hold the information that is needed by the application being developed.</p> 81 <p><b>MDB</b> simplifies this step greatly because it lets the developers design their database schemas in a <i>DBMS</i> independent manner. All you need to do is to write a text file in a custom XML format that describes the tables and fields that you want your database to have.</p> 82 <p>The database schema description XML format is fully described in the <b>MDB</b> manual, but an example often explains more than an exhaustive manual. Therefore, lets look into the following commented example:</p> 83 <p><tt><?xml version="1.0" encoding="ISO-8859-1" ?></tt></p> 84 <p>This is a typical header line that all sorts of XML files must have.</p> 85 <p><tt><database></tt></p> 86 <p>This is the <b>MDB</b> start tag. All <b>MDB</b> XML files must start with this tag.</p> 87 <ul> 88 <p><tt><name>test</name><br /> 89 <create>1</create></tt></p> 90 </ul> 91 <p>These are the database main properties. The <tt>name</tt> property is required. The <tt>create</tt> property is not required but since we want <b>MDB</b> to create the database for us when it is installed for the first time, this property has to be set to <tt>1</tt>.</p> 92 <ul> 93 <p><tt><table></tt></p> 94 <ul> 95 <p><tt><name>users</name></tt></p> 96 </ul> 97 </ul> 98 <p>Table definitions may have two sections beside its properties: <tt>declaration</tt> and <tt>initialization</tt>. The <tt>declaration</tt> section is required because it must contain the declaration of all the table fields and any indexes you need. The <tt>initialization</tt> section is not required but it may be used if you need that the table be created and have already added some initial entries.</p> 99 <ul> 100 <ul> 101 <p><tt><declaration></tt></p> 102 <ul> 103 <p><tt><field> <name>user_id</name> <type>integer</type> <default>0</default> <notnull>1</notnull> </field><br /> 104 <field> <name>user_name</name> <type>text</type> </field><br /> 105 <field> <name>password</name> <type>text</type> </field><br /> 106 <field> <name>reminder</name> <type>text</type> </field><br /> 107 <field> <name>name</name> <type>text</type> </field><br /> 108 <field> <name>email</name> <type>text</type> </field></tt></p> 109 </ul> 110 </ul> 111 </ul> 112 <p>The table declaration section must describe one or more fields. The definition of each field must contain at least the <tt>name</tt> and the <tt>type</tt> properties. Most common field types are <tt>integer</tt> and <tt>text</tt>, but others like <tt>date</tt> and <tt>timestamp</tt> are also frequently used.</p> 113 <ul> 114 <ul> 115 <ul> 116 <p><tt><index></tt></p> 117 <ul> 118 <p><tt><name>users_id_index</name><br /> 119 <unique>1</unique><br /> 120 <field> <name>user_id</name> </field></tt></p> 121 </ul> 122 <p><tt></index></tt></p> 123 </ul> 124 </ul> 125 </ul> 126 <p>The use of indexes is not required but applications always use them to speed up database access. Indexes are usually created on fields that make part of the search clauses of the most important queries that a database application runs.</p> 127 <p>The criteria that should be used to determine on which fields the indexes should be added is highly dependent on what the database application is meant for. So, orienting index creation decisions is beyond the scope of this tutorial.</p> 128 <p>Anyway, usually there are fields that are declared as primary or foreign keys for the table. <b>MDB</b> does not provide support for declaring table keys because not all <i>DBMS</i> support them. However, there is support for creating unique indexes on individual fields which in practice may work as if such fields were declared as primary keys.</p> 129 <p>Fields on which is created an index may not have <tt>NULL</tt> entries. Therefore, <b>MDB</b> schema description parser requires that such field should be declared with the <tt>notnull</tt> constraint property. It also requires that the <tt>default</tt> property be defined with a non-<tt>NULL</tt> value like in the case of the <tt>user_id</tt> field in the example above.</p> 130 <ul> 131 <ul> 132 <p><tt></declaration></tt></p> 133 </ul> 134 <p><tt></table></tt></p> 135 </ul> 136 <ul> 137 <p><tt><sequence></tt></p> 138 <ul> 139 <p><tt><name>user_id</name><br /> 140 <start>1</start><br /> 141 <on> <table>users</table> <field>user_id</field> </on></tt></p> 142 </ul> 143 <p><tt></sequence></tt></p> 144 </ul> 145 <p>Sequence objects are very useful when you need to generate integer values that are guaranteed to be unique every time you need a new identifier number to insert a new entry table.</p> 146 <p>With some <i>DBMS</i> developers often use fields declared with the <tt>auto_increment</tt> property to achieve a similar effect. <b>MDB</b> does not provide support for that property because not all <i>DBMS</i> support them.</p> 147 <p>Database application developers are recommended to use <b>MDB</b> sequences in the place of <tt>auto_increment</tt> fields. An advantage of using sequences is that you may use the sequence values to insert in other tables that you may need to also add related entries.</p> 148 <p>Sequences are standalone database schema objects. However, you should hint the database manager by specifying on which table field the sequence values are meant to be used. This may help to determine an appropriate sequence start value if you add the sequence to the database later than you have added the table field with which it should be synchronized.</p> 149 <ul> 150 <p><tt><table></tt></p> 151 <ul> 152 <p><tt><name>groups</name><br /> 153 <declaration></tt></p> 154 <ul> 155 <p><tt><field> <name>name</name> <type>text</type> </field><br /> 156 <field> <name>owner_id</name> <type>text</type> </field><br /> 157 <field> <name>description</name> <type>text</type> </field></tt></p> 158 </ul> 159 <p><tt></declaration></tt></p> 160 </ul> 161 <p><tt></table></tt></p> 162 </ul> 163 <ul> 164 <p><tt><table></tt></p> 165 <ul> 166 <p><tt><name>group_users</name><br /> 167 <declaration></tt></p> 168 <ul> 169 <p><tt><field> <name>group_id</name> <type>text</type> </field><br /> 170 <field> <name>user_id</name> <type>text</type> </field></tt></p> 171 </ul> 172 <p><tt></declaration></tt></p> 173 </ul> 174 <p><tt></table></tt></p> 175 </ul> 176 <p>Other tables and sequences may be declared as much as your database application may need.</p> 177 <p><tt></database></tt></p> 178 <p><b>MDB</b> XML files must end with a tag that matches the start tag.</p> 179 <h2><li><a name="24.1.3">Installing the database</a></li></h2> 180 <p>Once you have defined your database schema description file, it is time to install it your <i>DBMS</i> server. Using the <b>MDB</b> manager class, this procedure becomes very simple.</p> 181 <p>Lets suppose you have written and saved your database schema to a file named <tt>MyDatabase.schema</tt>. To install the database for the first time, for instance in a <i>MySQL</i> server, all you need to do is to write a script that creates <b>MDB</b> manager class object and call its <tt>updateDatabase</tt> method. Then use PHP as standalone executable program to execute the script that should look like this:</p> 182 <p><tt><?php<br /> 183 </tt></p> 184 <ul> 185 <p><tt>require_once ('MDB.php');<br>MDB::loadFile("Manager");</tt></p> 186 </ul> 187 <p>Include the necessary files.</p> 188 <ul> 189 <p><tt>$schema_file="MyDatabase.schema";</tt></p> 190 </ul> 191 <p>This is the definition of the name of your database schema file.</p> 192 <ul> 193 <p><tt>$variables=array();</tt></p> 194 </ul> 195 <p>If your database schema definition needs any install time variable values, you need to define them here. In the example schema above we have not used any variables. So, we actually do not need to define an array for the variables.</p> 196 <ul> 197 <p><tt>$manager=new MDB_manager;</tt></p> 198 </ul> 199 <p>Create the <b>MDB</b> manager class object.</p> 200 <p><tt>$dbinfo = array(</tt></p> 201 <ul> 202 <p><tt>"phptype"=>"mysql",</tt><br /> 203 <tt>"username"=>"mysql_user_name",</tt><br /> 204 <tt>"password"=>"mysql_user_password"</tt></p> 205 </ul> 206 <p><tt>);</tt></p> 207 </ul> 208 <ul> 209 <p><tt>$manager->connect($dsn, $options);</tt></p> 210 <ul> 211 <p>To setup a connection to the database server you need to pass atleast a DSN in the same format as for MDB with some <i>DBMS</i> specific arguments that are passed to the <b>MDB</b> manager class <tt>connect</tt> method. The <tt>phptype</tt> argument will make the database interface class automatically include the respective database driver class that must be located in the current directory or else its path must be spceified with the <tt>IncludePath</tt> argument.</p> 212 <ul> 213 <p><tt>$success = $manager->updateDatabase($schema_file, $schema_file.".before");</tt></p> 214 </ul> 215 <p>Call the <b>MDB</b> manager class object <tt>updateDatabase</tt> passing the schema file name and if any the schema variables array.</p> 216 <p>The second parameter is of special importance. It defines the name of the file to which the schema file currently being installed will be copied after a successful installation procedure.</p> 217 <p>The copied file will be used later when you want to update the schema of your database. Do not delete this file or else the database manager will not be able to determine what is the schema that was installed.</p> 218 <ul> 219 <p><tt>if(MDB::isError($success))</tt></p> 220 <ul> 221 <p><tt>echo "Error: ".$success->getMessage()."\n";</tt></p> 222 </ul> 223 </ul> 224 <p>If the install procedure failed, display the error to figure what went wrong.</p> 225 <ul> 226 <p><tt>if(count($manager->warnings)>0)</tt></p> 227 <ul> 228 <p><tt>echo "WARNING:\n",implode($manager->getWarnings(),"!\n"),"\n";</tt></p> 229 </ul> 230 </ul> 231 <p><tt>?></tt></p> 232 <p>Even when the install procedure was successfully completed there might have been some issues that you should be warned.</p> 233 <h2><li><a name="35.1.4">Accessing to the data in the database</a></li></h2> 234 <p><b>MDB</b> is meant for interfacing with <i>DBMS</i> using <i>SQL</i>. The database application developers are free to construct and execute the <i>SQL</i> queries that their applications may need to send and retrieve information from the database server.</p> 235 <ul> 236 <h3><li><a name="37.2.1">Setup database access</a></li></h3> 237 <p>The first thing you need to do to access to a database is to setup a connection with the <i>DBMS</i> server within your application scripts.</p> 238 <p>A database connection is set by calling the factory <tt>&MDB::connect()</tt>. This factory does not start a database connection right away. It just sets a few parameters that the <i>DBMS</i> driver needs to know how to communicate with the database server you want. Usually the database connection is only effectively established later when you execute the first query of each of your scripts.</p> 239 <p>The <tt>&MDB::connect()</tt> factory takes as argument an associative array with connection options. The array entry <tt>phptype</tt> is required and must contain the designation of the driver that will handle the calls to interface with the type of <i>DBMS</i> that you talk to. For instance, if you want to connect to a <i>MySQL</i> <i>DBMS</i> server, set the <tt>phptype</tt> entry to <tt>mysql</tt>.</p> 240 <p>Other arguments array entry may also have to be specified depending on the driver type that you have chosen. Check the <b>MDB</b> manual to know the designation of the types of other supported <i>DBMS</i> and the respective additional arguments.</p> 241 <p>The second argument of the <tt>&MDB::connect()</tt> factory takes a reference of a variable that will be used to store a database access handle value. This handle value is important because it has to be passed to all <b>MDB</b> methods that are needed to access the database.</p> 242 <p>The <tt>&MDB::connect()</tt> factory may fail for inconsistency of the argument values that you may have specified. In case of failure it will return a non-empty string that describes the error that made it fail. Make sure you always verify this return value at least when you are debugging your database applications.</p> 243 <p>Here follows an example of usage of the &MDB::connect()</tt> factory:</p> 244 <p><tt><?php<br /> 245 </tt></p> 246 <ul> 247 <p><tt>require_once("MDB.php");</tt></p> 248 </ul> 249 <p>Include the necessary files.</p> 250 <ul> 251 <p><tt>$mdb=&MDB::connect((array(</tt></p> 252 <ul> 253 <p><tt>"phptype"=>"mysql",</tt><br /> 254 <tt>"username"=>"mysql_user_name",</tt><br /> 255 <tt>"password"=>"mysql_user_password"</tt></p> 256 </ul> 257 <p><tt>));</tt><br /> 258 <tt>if (MDB::isError($mdb)) {</tt><br /> 259 <ul> 260 <p><tt>die ($mdb->getMessage());</tt><br /></p> 261 </ul> 262 <p><tt>}</tt></p> 263 </ul> 264 <p>The <tt>MDB::connect()</tt> creates an object of the driver class to access the database and initializes some variables.</p> 265 <ul> 266 <p><tt>$mdb->setDatabase("test");</tt></p> 267 </ul> 268 <p><tt>?></tt></p> 269 <p>Before you can access to a database server you usually need to specify the name of the database you want to access using the <tt>setDatabase</tt> method.</p> 270 <h3><li><a name="46.2.3">Executing database queries</a></li></h3> 271 <p><b>MDB</b> provides two ways of constructing and executing database queries: direct queries and prepared queries.</p> 272 <ul> 273 <h4><li><a name="47.3.1">Direct queries</a></li></h4> 274 <p>Direct queries are those that are executed simply by passing the <i>SQL</i> statement to the <i>DBMS</i>. To execute a direct query use the method <tt>query()</tt>. This method takes a database access handle and the <tt>SQL</tt> query statement as arguments. Here follows an example:</p> 275 <p><tt>$query="SELECT name,password FROM users";</tt><br /> 276 <tt>$result=$mdb->query($query);</tt></p> 277 <p>The return value is either a result set or an MDB error object. If <tt>MDB::isError($result)</tt> is <tt>TRUE</tt> then the query failed. For <tt>SELECT</tt> queries the return value is a result handle. It should be used to retrieve the query results.</p> 278 <p>Query statements often contain constant values like in following example:</p> 279 <p><tt>$query="SELECT name,password FROM users WHERE user_name='admin'";</tt><br /> 280 <tt>$result=$mdb->query($query);</tt></p> 281 <p>You may build queries like this by manually inserting constant values in the query statement text string. However, different <i>DBMS</i> may require that constant values be represented in different manners.</p> 282 <p>For instance, text constant values may need to escape special characters like <tt>'</tt>. Date constant values may be represented as integers rather than date text strings because the underlying <i>DBMS</i> does not support date data types natively.</p> 283 <p>Fortunately, <b>MDB</b> provides a set of conversion methods that avoid having you to pass constant values in a format that is suitable for the type of <i>DBMS</i> you are talking too. These methods call the respective driver methods to do any representation conversion that may be needed.</p> 284 <p>This way you may develop highly portable database applications without having to worry with the different types of constant value conversions that have to be performed when interfacing with different <i>DBMS</i>.</p> 285 <p>For each data type supported by <b>MDB</b> there is a constant value conversion method, except for integer constants because these are not likely to be represented differently between <i>DBMS</i>. Here is a complete list of constant values conversion methods:</p> 286 <ul> 287 <p><li><tt>getTextValue($value)</tt></li></p> 288 <p><li><tt>getBooleanValue($value)</tt></li></p> 289 <p><li><tt>getDateValue($value)</tt></li></p> 290 <p><li><tt>getTimestampValue($value)</tt></li></p> 291 <p><li><tt>getTimeValue($value)</tt></li></p> 292 <p><li><tt>getIntegerValue($value)</tt></li></p> 293 <p><li><tt>getFloatValue($value)</tt></li></p> 294 <p><li><tt>getDecimalValue($value)</tt></li></p> 295 </ul> 296 <p>Using the constant value conversion methods for the execution of the query mentioned above, it would be rewritten like this:</p> 297 <p><tt>$query="SELECT name,password FROM users WHERE user_name=".$mdb->getTextValue("admin");</tt><br /> 298 <tt>$result=$mdb->query($query);</tt></p> 299 <p>Converting constant values before executing queries requires a little extra effort, but considering the flexibility that is gained from the extra portability of your application code, it is an effort that it is worthy and it is thoroughly recommended to you make it.</p> 300 <h4><li><a name="48.3.2">Prepared queries</a></li></h4> 301 <p>Prepared queries are <i>SQL</i> statements that have to be prepared before being executed. Executing a prepared query takes less time than executing a non-prepared query because prepared queries have already been parsed by the time they are executed.</p> 302 <p>Using prepared queries to execute them only once in a script does not take less time than just using direct queries. Prepared queries are recommended when you need to execute a query more than once in the same script.</p> 303 <p>Prepared queries may take arguments. When a prepared query is executed argument values are inserted in special spots in the query statement marked as with <tt>?</tt>. Consider for instance this example that uses a direct query:</p> 304 <p><tt>$query="SELECT name,password FROM users WHERE user_name=".$mdb->getTextValue("admin");</tt><br /> 305 <tt>$result=$mdb->query($query);</tt></p> 306 <p>It may be rewritten using a prepared query like this:</p> 307 <p><tt>$query="SELECT name,password FROM users WHERE user_name=?";</tt><br /> 308 <tt>$prepared_query=$mdb->prepareQuery($query);</tt></p> 309 <p>Get a prepared query handle.</p> 310 <p><tt>if(!MDB::isError($prepared_query))</tt></p> 311 <p>Make sure the query was prepared without error.</p> 312 <p><tt>{</tt></p> 313 <ul> 314 <p><tt>$mdb->setParamText($prepared_query,1,"admin");</tt></p> 315 </ul> 316 <p>Set the first query argument with the text constant value <tt>admin</tt>.</p> 317 <ul> 318 <p><tt>$result=$mdb->executeQuery($prepared_query);</tt></p> 319 </ul> 320 <p><tt>}</tt></p> 321 <p>Execute the query. From here on handling query results is the same as for direct queries.</p> 322 <p>Prepared queries may have many arguments, all identified by the <tt>?</tt> mark. Each one has an order number starting from <tt>1</tt>.</p> 323 <p>There is one method to set prepared query arguments for each data type supported by <b>MDB</b>. In the example <tt>setParamText()</tt> was used to set a text argument. <tt>setParamInteger()</tt> would have to be used if the argument was an integer, and so on. If the argument is meant to be set to <tt>NULL</tt>, use the method <tt>setParamNull()</tt>.</p> 324 <p>Prepared queries may be executed multiple times in the same script and you may change all or part of the argument values before executing them each time.</p> 325 <p>After you have executed all the times you need a prepared query in a script, you have to free the resources that it implicitly allocates by calling the method <tt>freePreparedQuery()</tt>.</p> 326 <p>Despite the use of prepared queries is mostly meant for scripts where you need to execute the same query more than once, you may find it cleaner to also use them even when they are only executed once, as you only start with constant query strings and constant data value conversion is transparently handled for you by the <tt>setParam()</tt> like methods.</p> 327 </ul> 328 <h3><li><a name="50.2.4">Retrieving query results</a></li></h3> 329 <ul> 330 <h4><li><a name="51.3.1">Fetching result set data</a></li></h4> 331 <p>A successfully executed <tt>SELECT</tt> returns a value that should be used as result handle. This value must be passed to every <b>MDB</b> method that you may need to call to access the results return by the queries that your database applications execute.</p> 332 <p>A result handle is just a number that serves as a reference to a result set returned by the <i>DBMS</i> to the application as response to the query that was executed.</p> 333 <p>A result set is a sort of table with columns and rows filled with result data. You can retrieve the results using the <tt>fetch*()</tt> the family of methods (like <tt>fetchRow()</tt>).</p> 334 <p>Despite the rows of a result set are not always made available to the database application all at once, <b>MDB</b> lets you request result data by specify the number of the respective result set row. Result set row numbers start from <tt>0</tt>.</p> 335 <p>Here is simple example of a retrieving the results of a row of a query:</p> 336 <p><tt>$result=$mdb->query("SELECT name,email FROM users");</tt><br /> 337 <tt>if(!MDB::isError($result))</tt><br /> 338 <tt>{</tt></p> 339 <ul> 340 <p><tt>$data=$mdb->fetchRow($result);</tt><br /> 341 <tt>$name=$data[0];</tt><br /> 342 <tt>$email=$data[1];</tt></p> 343 </ul> 344 <p><tt>}</tt><br /> 345 <tt>else</tt></p> 346 <ul> 347 <p><tt>$echo=$result->getMessage();</tt></p> 348 </ul> 349 <h4><li><a name="53.3.2">Data type conversion</a></li></h4> 350 <p>The <tt>fetch*()</tt> method retrieves data just as it is returned by the <i>DBMS</i>. The data representation format for each type of field may vary from <i>DBMS</i> to <i>DBMS</i>.</p> 351 <p>To avoid the problem of having to handle data representation format differences in your database application, <b>MDB</b> provides a set of methods that fetch the result data and already convert it to a single representation format defined for each supported data type.</p> 352 <p>This simplifies database application development a lot and promotes code portability when using different <i>DBMS</i>.</p> 353 <p>Not all types of result data need to be converted. For instance, text and integer result data do not need to be converted. You can either use <tt>convertResult($value, $type)</tt> to convert result data. Optionally you can pass an array to <tt>query()</tt> and <tt>prepareQuery()</tt> that contains the types in the result set. Then the type conversion is handled automatically for you.</p> 354 <h4><li><a name="70.3.5">Freeing the result set resources</a></li></h4> 355 <p>When you are done with the result set of a query, you should make sure that any resources implicitly allocated for it are freed. Freeing allocated resources is important because it reduces the use of memory that a script needs to run by allowing that memory may be reused.</p> 356 <p>Even if your script exits right after you are done with the result of a query, you should always explicitly free query results because the end of a script may not imply that the resources are freed automatically then. This is particularly true if you are using persistent database connections when running PHP as Web server module.</p> 357 <p>With <b>MDB</b> you should use the <tt>freeResult()</tt> method and it may be as simple as follows:</p> 358 <p><tt>$mdb->freeResult($result);</tt></p> 359 </ul> 360 <h3><li><a name="70.2.5">Handling large object fields</a></li></h3> 361 <ul> 362 <p>Large object fields, also known as <i>LOBs</i> (<i>BLOBs/CLOBs</i>), need to have a special treatment. The amount of data stored in this type of fields may be so large that it would require too much memory to be stored and retrieved all at once with single method calls like with other types of fields.</p> 363 <p><b>MDB</b> provides a separate set of API methods to deal with large object fields. These methods let applications deal with the values of this type of fields by splitting them in smaller chunks of data.</p> 364 <h4><li><a name="71.3.1">Creating large object table fields</a></li></h4> 365 <p>Large object fields may be created like any other type of field, that is declaring them in schema description files as large object fields.</p> 366 <p>There are two types of large object fields: character fields and binary fields. Character fields could be used when you only intend to store <i>ASCII</i> text on them. If you intend to store other types of data, use binary fields instead.</p> 367 <p>Character large object fields should declared of being of the type <tt>clob</tt>. Binary large object fields should declared of being of the type <tt>blob</tt>. Here follows an example of declaration of a table with a character and a binary large object fields:</p> 368 <ul> 369 <p><tt><table></tt></p> 370 <ul> 371 <p><tt><name>files</name></tt></p> 372 <p><tt><declaration></tt></p> 373 <ul> 374 <p><tt><field> <name>id</name> <type>integer</type> </field><br /> 375 <field> <name>document</name> <type>clob</type> </field><br /> 376 <field> <name>picture</name> <type>blob</type> </field></tt></p> 377 </ul> 378 <p><tt></declaration></tt></p> 379 </ul> 380 <p><tt></table></tt></p> 381 </ul> 382 <h4><li><a name="74.3.2">Storing data in large object fields</a></li></h4> 383 <p>With <b>MDB</b>, storing data in large object fields can only be done by executing <tt>INSERT</tt> or <tt>UPDATE</tt> prepared queries. The large object values are passed to the database as parameters of the prepared queries. The methods <tt>setParamClob</tt> and <tt>setParamBlob</tt> should be used to specify large object values as parameters of the prepared query.</p> 384 <p>Instead of specifying the large object parameters explicitly by their data values, you need to pass references to objects of special handler classes that know how to retrieve the data to store in the large object fields.</p> 385 <p>The handler classes may be able to retrieve data from strings of defined programmatically like any other type of field, but may also retrieve data from files.</p> 386 <p>Here follows an example of how to execute a query that inserts data defined programmatically into a large object character field:</p> 387 <ul> 388 <p><li>Prepare a query to insert a row with character large object field (<tt>document</tt>). You may insert rows with as many large object fields as you want, but in this example only one is inserted.</li></p> 389 <p><tt>if(($prepared_query=$mdb->prepareQuery("INSERT INTO files (id,document,picture) VALUES (1,?,NULL)")))<br /> 390 {</tt></p> 391 <p><li>Create a large object handler class to supply data defined in the program to insert in the table field.</li></p> 392 <ul> 393 <p><tt>$character_lob=array(</tt></p> 394 <ul> 395 <p><tt>"Error"=>"",<br /> 396 "Data"=>"a lot of character data"</tt></p> 397 </ul> 398 <p><tt>);<br /> 399 if(!MDB::isError($clob=$mdb->createLob($character_lob)))<br /> 400 {</tt></p> 401 </ul> 402 <p><li>Define the character large object field query parameter value. Notice that you need to specify the name of the table field into which will be inserted the large object data value.</li></p> 403 <ul> 404 <ul> 405 <p><tt>$mdb->setParamClob($prepared_query,1,$clob,"document");</tt></p> 406 </ul> 407 </ul> 408 <p><li>Execute the prepared query.</li></p> 409 <ul> 410 <ul> 411 <p><tt>if(!MDB::isError($error = $mdb->executeQuery($prepared_query))</tt></p> 412 <ul> 413 <p><tt>echo $error->getMessage();</tt></p> 414 </ul> 415 </ul> 416 </ul> 417 <p><li>Free the resources allocated by the handler class object.</li></p> 418 <ul> 419 <ul> 420 <p><tt>$mdb->destroyLob($clob);</tt></p> 421 </ul> 422 <p><tt>}</tt></p> 423 </ul> 424 <p><li>If the creation of the handler object failed, retrieve the error message.</li></p> 425 <ul> 426 <p><tt>else</tt></p> 427 <ul> 428 <p><tt>echo $clob->getMessage();</tt></p> 429 </ul> 430 </ul> 431 <p><li>Free the prepared query resources.</li></p> 432 <ul> 433 <p><tt>$mdb->freePreparedQuery($prepared_query);</tt></p> 434 </ul> 435 <p><tt>}<br /> 436 else</tt></p> 437 <ul> 438 <p><tt>echo $prepared_query->getMessage();</tt></p> 439 </ul> 440 </ul> 441 <p>Here follows an example of how to execute a query that updates a large object binary field with data retrieved from a file. Make sure that only one row is affected by the query because some <i>DBMS</i> are not able to update values of large object fields in more than one row per query.</p> 442 <ul> 443 <p><li>Prepare a query to update a row with binary large object field (<tt>picture</tt>).</li></p> 444 <p><tt>if(!MDB::isError($prepared_query=$mdb->prepareQuery(,"UPDATE files SET picture=? WHERE id=1")))<br /> 445 {</tt></p> 446 <p><li>Create a large object handler class to supply data from a specified file.</li></p> 447 <ul> 448 <p><tt>$binary_lob=array(</tt></p> 449 <ul> 450 <p><tt>"Error"=>"",<br /> 451 "FileName"=>"my_image.gif"</tt></p> 452 </ul> 453 <p><tt>);<br /> 454 if(!MDB::isError($blob=$mdb->createLob($binary_lob)))<br /> 455 {</tt></p> 456 </ul> 457 <p><li>Define the binary large object field query parameter value. Specifying the name of the field to update is also necessary for <tt>UPDATE</tt> queries.</li></p> 458 <ul> 459 <ul> 460 <p><tt>$mdb->setParamBlob($prepared_query,1,$blob,"picture");</tt></p> 461 </ul> 462 </ul> 463 <p><li>Execute the prepared query.</li></p> 464 <ul> 465 <ul> 466 <p><tt>if(MDB::isError($error = $mdb->executeQuery($prepared_query)))</tt></p> 467 <ul> 468 <p><tt>$mdb->destroyLOB($blob);</tt></p> 469 </ul> 470 <p><tt>echo $error->getMessage();</tt></p> 471 </ul> 472 <p><tt>}<br /> 473 else</tt></p> 474 <ul> 475 <p><tt>echo $blob->getMessage();</tt></p> 476 </ul> 477 <p><tt>$mdb->freePreparedQuery($prepared_query);</tt></p> 478 </ul> 479 <p><tt>}<br /> 480 else</tt></p> 481 <ul> 482 <p><tt>echo $prepared_query->getMessage();</tt></p> 483 </ul> 484 </ul> 485 <h4><li><a name="98.3.3">Retrieving data from large object fields</a></li></h4> 486 <p>Retriving data from large object fields is done by executing normal <tt>SELECT</tt> queries.</p> 487 <p>The methods <tt>fetchClob</tt> and <tt>fetchBlobt</tt> return a value that identifies a large object handler class object. The data may be retrieved using the method <tt>readLob</tt>. Alternatively, the handler object identifier may be passed to another handler class object that will read the large object result value and process it in a useful way, like for instance storing the data in a file.</p> 488 <p>Here follows an example of how to select a character large object field to output its data:</p> 489 <ul> 490 <p><li>Execute the <tt>SELECT</tt> query. More than one large object field could be selected in the same query.</li></p> 491 <p><tt>if(($result=$mdb->query("SELECT document FROM files WHERE id=1")))<br /> 492 {</tt></p> 493 <p><li>Check whether there are any result rows by verifying if it has reached the end of the result set.</li></p> 494 <ul> 495 <p><tt>if($mdb->endOfResult($result))</tt></p> 496 <ul> 497 <p><tt>echo "No rows where returned.\n";</tt></p> 498 </ul> 499 <p><tt>else<br /> 500 {</tt></p> 501 </ul> 502 <p><li>Retrieve the large object identifier for a given result row column. Usually this always succeed, but if there is an unexpected error, it will return an error object to denote an error. If you are not sure if the selected column is <tt>NULL</tt>, use the method <tt>resultIsNull</tt> here to verify that.</li></p> 503 <ul> 504 <ul> 505 <p><tt>$clob=$mdb->fetchClob($result,0,"document");<br /> 506 if(!MDB::isError($clob))<br /> 507 {</tt></p> 508 </ul> 509 </ul> 510 <p><li>Read the data from the selected large object field until it reaches the end of data.</li></p> 511 <ul> 512 <ul> 513 <ul> 514 <p><tt>while(!$mdb->endOfLOB($clob))<br /> 515 {</tt></p> 516 </ul> 517 </ul> 518 </ul> 519 <p><li>If it is returned a negative length of data read, that is because an error has occurred.</li></p> 520 <ul> 521 <ul> 522 <ul> 523 <ul> 524 <p><tt>if(MDB::isError($error = $mdb->readLob($clob,$data,8000)<0))<br /> 525 {</tt></p> 526 <ul> 527 <p><tt>echo $error->getMessage();<br /> 528 break;</tt></p> 529 </ul> 530 <p><tt>}</tt></p> 531 </ul> 532 </ul> 533 </ul> 534 </ul> 535 <p><li>If otherwise the data was read ok, keep outputting it.</li></p> 536 <ul> 537 <ul> 538 <ul> 539 <ul> 540 <p><tt>echo $data;</tt></p> 541 </ul> 542 <p><tt>}</tt></p> 543 </ul> 544 </ul> 545 </ul> 546 <p><li>Free the resources allocated by the result large object handler class.</li></p> 547 <ul> 548 <ul> 549 <ul> 550 <p><tt>$mdb->destroyLob($clob);</tt></p> 551 </ul> 552 <p><tt>}<br /> 553 else</tt></p> 554 </ul> 555 </ul> 556 <p><li>If it was not possible to retrieve the large object field value, figure why retrieving the error message.</li></p> 557 <ul> 558 <ul> 559 <ul> 560 <p><tt>echo $clob->getMessage();</tt></p> 561 </ul> 562 </ul> 563 <p><tt>}</tt></p> 564 </ul> 565 <p><li>Free the query result.</li></p> 566 <ul> 567 <p><tt>$mdb->freeResult($result);</tt></p> 568 </ul> 569 <p><tt>}<br /> 570 else</tt></p> 571 <ul> 572 <p><tt>echo $result->getMessage();</tt></p> 573 </ul> 574 </ul> 575 <p>Here follows an example of how to select a binary large object field and output its data to a file.</p> 576 <ul> 577 <p><li>Execute the <tt>SELECT</tt> query.</li></p> 578 <p><tt>if(($result=$mdb->query("SELECT picture FROM files WHERE id=1")))<br /> 579 {</tt></p> 580 <ul> 581 <p><tt>if($mdb->endOfResult($result))</tt></p> 582 <ul> 583 <p><tt>echo "No rows where returned.\n";</tt></p> 584 </ul> 585 <p><tt>else<br /> 586 {</tt></p> 587 </ul> 588 <p><li>Create a output file handler class object.</li></p> 589 <ul> 590 <ul> 591 <p><tt>$binary_lob=array(</tt></p> 592 <ul> 593 <p><tt>"Type"=>"outputfile",<br /> 594 "Result"=>$result,<br /> 595 "Row"=>0,<br /> 596 "Field"=>"picture",<br /> 597 "Binary"=>1,<br /> 598 "Error"=>"",<br /> 599 "FileName"=>"my_image.gif"</tt></p> 600 </ul> 601 <p><tt>);<br /> 602 if(!MDB::isError($blob=$mdb->createLOB($binary_lob)))<br /> 603 {</tt></p> 604 </ul> 605 </ul> 606 <p><li>Read the whole large object field data and write it to the specified file by specifying a read length of <tt>0</tt> bytes. No data is returned in the <tt>$data</tt> argument.</li></p> 607 <ul> 608 <ul> 609 <ul> 610 <p><tt>if(MDB::isError($error = $mdb->readLob($blob,$data,0)<0))<br /> 611 {</tt></p> 612 </ul> 613 </ul> 614 </ul> 615 <p><li>If it is returned a negative length of data read, that is because an error has occurred.</li></p> 616 <ul> 617 <ul> 618 <ul> 619 <ul> 620 <p><tt>echo $error->getMessage();</tt></p> 621 </ul> 622 <p><tt>}<br /> 623 $mdb->destroyLob($blob);</tt></p> 624 </ul> 625 <p><tt>}<br /> 626 else</tt></p> 627 </ul> 628 </ul> 629 <p><li>If it was not possible to create the output file handler class object, figure why retrieving the error message.</li></p> 630 <ul> 631 <ul> 632 <ul> 633 <p><tt>echo $blob->getMessage();</tt></p> 634 </ul> 635 </ul> 636 <p><tt>}<br /> 637 $mdb->freeResult($result);</tt></p> 638 </ul> 639 <p><tt>}<br /> 640 else</tt></p> 641 <ul> 642 <p><tt>echo $result->getMessage();</tt></p> 643 </ul> 644 </ul> 645 </ul> 646 </ul> 647 <h2><li><a name="140.1.5">Updating the database schema</a></li></h2> 648 <p>If for some reason you decide that you need to update the schema of your database, the procedure to install your schema changes is as simple as install a database schema for the first time.</p> 649 <p>In fact all you need to do after you edit and change your schema file is to execute the same script like that you used to initially install the database.</p> 650 <p>The database manager class will look for the copy of the previously installed schema file. Both the newer and the previous installed schema descriptions are parsed and compared to build the list of changes.</p> 651 <p>Then it will attempt to install the requested changes without affecting the data that was stored in the database after the database was installed for the first time.</p> 652 <p>Some <i>DBMS</i> <b>MDB</b> drivers are not able to implement all sorts of changes. If you asked for any changes that the are not possible to install at once, the <tt>updateDatabase</tt> method will fail without affecting the database.</p> 653 <p>The attempt to change the database is safe at this level but it is always safer to make a backup of your database data before installing any changes as for some unexpected reason the <i>DBMS</i> server might fail.</p> 654 </ul> 655 656 <hr /> 657 <address>Manuel Lemos (<a href="mailto:mlemos@acm.org">mlemos@acm.org</a>)</address> 658 <address>Lukas Smith (<a href="mailto:smith@backendmedia.com">smith@backendmedia.com</a>)</address> 659 </body> 660 </html>
titre
Description
Corps
titre
Description
Corps
titre
Description
Corps
titre
Corps
| Généré le : Sun Feb 25 14:08:00 2007 | par Balluche grâce à PHPXref 0.7 |