Author: Manuel Lemos (mlemos-at-acm.org)
Version control: @(#) $Id: tutorial.documentation,v 1.16 2005/09/20 23:00:53 mlemos Exp $
Metabase is a package that allows the developers to write database applications in PHP that are independent of the DBMS (DataBase Management System).
The steps that developers have to take to write Metabase based applications are identical to those that are taken while using the set of native commands that are used to talk to each DBMS directly. The main advantage of Metabase is that developers only need to learn and use one set of commands to implement applications that may run with many different DBMS.
The goal of this tutorial is to introduce the basic steps that beginners have to take to start developing database applications with Metabase. For more in depth information, please refer to the Metabase documentation manual.
Before start using Metabase there are a few installation requirements that developers need to know and understand.
PHP is usually run from a Web server, mostly as a server module, but it can also run as CGI program. When PHP is built as CGI program a standalone executable program file is generated. This CGI program can also be used to execute PHP scripts from a command line interface shell.
Although it is possible do the same using PHP as Web server module, it is recommended that you use the CGI program to run the PHP scripts to install or setup databases using Metabase.
If you want to execute the installation script setup_test.php from the command line interface you need to type for instance:
/usr/local/bin/php -q setup_test.php
PHP has several runtime options that are meant to ease the developers' work. The option named magic_quotes_runtime is meant to automatically escape special characters in text literal values before executing SQL queries.
Metabase is already able to escape special characters of query text values using the functions MetabaseGetTextFieldValue and MetabaseQuerySetText. If either any of these functions is used and the magic_quotes_runtime option is On, then some characters may end up being escaped twice.
To avoid duplicated character escaping, please disable the option magic_quotes_runtime and also magic_quotes_sybase by setting them to the value Off in your PHP configuration by for instance having these lines in your php.ini or php3.ini file:
magic_quotes_runtime = Off magic_quotes_sybase = Off
Metabase is made of several PHP files that need to be included from your scripts, depending on what you need to do with Metabase. Here follows the list of the most important files and when they should be included in your scripts.
This is the main API file. It should be included every time you need to interface with a database using Metabase.
This is the file with the definition of the base class for all Metabase database drivers classes. It should also be included every time you need to interface with a database using Metabase.
The other database driver class files are included automatically when you call the function MetabaseSetupDatabase. If the database driver class file that you want to use is not in the current directory, specify its directory with the IncludePath argument of the MetabaseSetupDatabase function.
This is the API file for dealing with large object fields (LOBs). It should be included every time you need to store or retrieve large object field values.
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.
This is the database definition parser class file. It should be included every time you need to parse the a database definition XML file.
This is a generic XML parser class file. It should also be included every time you need to parse the a database definition XML file.
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.
Metabase simplifies this step greatly because it lets the developers design their database schemas in a DBMS 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.
The database schema description XML format is fully described in the Metabase manual, but an example often explains more than an exhaustive manual. Therefore, lets look into the following commented example:
<?xml version="1.0" encoding="ISO-8859-1" ?>
This is a typical header line that all sorts of XML files must have.
<database>
This is the Metabase start tag. All Metabase XML files must start with this tag.
<name>test</name>
<create>1</create>
These are the database main properties. The name property is required. The create property is not required but since we want Metabase to create the database for us when it is installed for the first time, this property has to be set to 1.
<table>
<name>users</name>
Table definitions may have two sections beside its properties: declaration and initialization. The declaration section is required because it must contain the declaration of all the table fields and any indexes you need. The initialization section is not required but it may be used if you need that the table be created and have already added some initial entries.
<declaration>
<field> <name>id</name> <autoincrement>1</autoincrement> </field>
<field> <name>user_name</name> <type>text</type> <length>20</length> <notnull>1</notnull> <default></default> </field>
<field> <name>password</name> <type>text</type> </field>
<field> <name>reminder</name> <type>text</type> </field>
<field> <name>name</name> <type>text</type> </field>
<field> <name>email</name> <type>text</type> </field>
The table declaration section must describe one or more fields. The definition of each field must contain at least the name and the type properties. Auto-incremented key fields are implicitly of type integer, so they type does not need to be explicitly declared. Most common field types are integer and text, but others like date and timestamp are also frequently used.
<index>
<name>user_name_index</name>
<unique>1</unique>
<field> <name>user_name</name> </field>
</index>
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.
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.
Anyway, usually there are fields that are declared as primary or foreign keys for the table. Metabase does not provide support for declaring table keys because not all DBMS 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.
Fields on which is created an index may not have NULL entries. Therefore, Metabase schema description parser requires that such field should be declared with the notnull constraint property. It also requires that the default property be defined with a non-NULL value like in the case of the id field in the example above.
</declaration>
</table>
<table>
<name>groups</name>
<declaration>
<field> <name>name</name> <type>text</type> </field>
<field> <name>owner_id</name> <type>text</type> </field>
<field> <name>description</name> <type>text</type> </field>
</declaration>
</table>
<table>
<name>group_users</name>
<declaration>
<field> <name>group_id</name> <type>text</type> </field>
<field> <name>user_id</name> <type>text</type> </field>
</declaration>
</table>
Other tables could be declared as many as your database application may need.
</database>
Metabase XML files must end with a tag that matches the start tag.
Once you have defined your database schema description file, it is time to install it your DBMS server. Using the Metabase manager class, this procedure becomes very simple.
Lets suppose you have written and saved your database schema to a file named MyDatabase.schema. To install the database for the first time, for instance in a MySQL server, all you need to do is to write a script that creates Metabase manager class object and call its UpdateDatabase function. Then use PHP as standalone executable program to execute the script that should look like this:
<?php
require("xml_parser.php");
require("metabase_parser.php");
require("metabase_interface.php");
require("metabase_database.php");
require("metabase_manager.php");
Include the necessary files.
$schema_file="MyDatabase.schema";
This is the definition of the name of your database schema file.
$variables=array();
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, lets just define the variables list as an empty array.
$arguments=array(
"Type"=>"mysql",
"User"=>"mysql_user_name",
"Password"=>"mysql_user_password"
);
To setup a connection to the database server you need to pass some DBMS specific arguments that are passed to the Metabase manager class SetupDatabase function. The Type 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 specified with the IncludePath argument.
$manager=new metabase_manager_class;
Create the Metabase manager class object.
$success=$manager->UpdateDatabase($schema_file, $schema_file.".before", $arguments, $variables);
Call the Metabase manager class object UpdateDatabase passing the schema file name, the database setup arguments and the schema variables array.
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.
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.
if(!$success)
echo "Error: ".$manager->error."\n";
If the install procedure failed, display the error to figure what went wrong.
if(count($manager->warnings)>0)
echo "WARNING:\n",implode($manager->warnings,"!\n"),"\n";
?>
Even when the install procedure was successfully completed there might have been some issues that you should be warned.
Metabase is meant for interfacing with DBMS using SQL. The database application developers are free to construct and execute the SQL queries that their applications may need to send and retrieve information from the database server.
Metabase provides two equivalent forms of calling its API functions: using a set of global functions or calling driver object API functions directly.
Calling driver API functions is slightly faster and requires the programmers to type less characters in their programs to write calls to Metabase API.
However, this form does not work under PHP 3. Since Metabase development was started only when PHP 3 was available, the form of using global functions was kept to assure backwards compatibility of applications that use Metabase for a long time.
If you want to use Metabase only under PHP 4 or better, you may use the driver object API functions directly. In that case, you may also mix the use of both forms of calling API functions in your programs. To do that you need to use MetabaseSetupDatabaseObject instead of MetabaseSetupDatabase as described below.
The MetabaseSetupDatabaseObject function returns by reference an object of the specified database driver class. You need to be careful when passing objects around. You need to use references to pass an object to a function or assigning another variable with the object value. Passing objects by value may lead to problems that have to avoided because it means that you end up with two distinct driver class objects. This leads to eventual inconsistencies of the information that is stored in the driver objects.
Explaining the concepts of references and objects is beyond the scope of this tutorial document. If you need to pass a driver object to functions or assign its value to another variable but you have difficulty to understand these concepts of objects and references, it is recommended that you just use the API global functions instead.
The first thing you need to do to access to a database is to setup a connection with the DBMS server within your application scripts.
A database connection is set by calling the function MetabaseSetupDatabase. This function does not start a database connection right away. It just sets a few parameters that the DBMS 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.
The MetabaseSetupDatabase function takes as argument an associative array with connection options. The array entry Type is required and must contain the designation of the driver that will handle the calls to interface with the type of DBMS that you talk to. For instance, if you want to connect to a MySQL DBMS server, set the Type entry to mysql.
Other arguments array entry may also have to be specified depending on the driver type that you have chosen. Check the Metabase manual to know the designation of the types of other supported DBMS and the respective additional arguments.
The second argument of the MetabaseSetupDatabase function 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 Metabase functions that are needed to access the database.
The MetabaseSetupDatabase function 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.
Here follows an example of usage of the MetabaseSetupDatabase function:
<?php
require("metabase_interface.php");
require("metabase_database.php");
Include the necessary files.
$error=MetabaseSetupDatabase(array(
"Type"=>"mysql",
"User"=>"mysql_user_name",
"Password"=>"mysql_user_password"
), $database);
if($error!="")
{
echo "Database setup error: $error\n";
exit;
}
The MetabaseSetupDatabase creates an object of the driver class to access the database and initializes some variables. The function initializes the $database argument with an integer number that is an indirect reference to driver class object that is created. This integer works as a database access handle that should be passed as argument to other Metabase functions.
MetabaseSetDatabase($database,"test");
?>
Before you can access to a database server you usually need to specify the name of the database you want to access using the MetabaseSetDatabase function.
If you prefer to call driver object functions directly, you need to use the function MetabaseSetupDatabaseObject instead of MetabaseSetupDatabase. The function MetabaseSetupDatabaseObject returns a driver class object that you should use to call the driver functions directly.
In this tutorial only the API global functions are used. To make the presented examples use direct driver object calls instead, just remove the Metabase prefix and the $database argument and make that an object call using the variable that was returned by the function MetabaseSetupDatabaseObject.
Here follows the above database setup example but using the MetabaseSetupDatabaseObject function:
<?php
require("metabase_interface.php");
require("metabase_database.php");
$error=MetabaseSetupDatabaseObject(array(
"Type"=>"mysql",
"User"=>"mysql_user_name",
"Password"=>"mysql_user_password"
), $db);
if($error!="")
{
echo "Database setup error: $error\n";
exit;
}
$db->SetDatabase("test");
You may still call API global functions by retrieving a driver object variable named database and use it as database access handle. In that case, the above call to set the database to work, would look like this:
MetabaseSetDatabase($db->database,"test");
?>
Metabase provides two ways of constructing and executing database queries: direct queries and prepared queries.
Direct queries are those that are executed simply by passing the SQL statement to the DBMS. To execute a direct query use the function Query. This function takes a database access handle and the SQL query statement as arguments. Here follows an example:
$query="SELECT name,password FROM users";
$result=$db->Query($query);
The return value is an integer that determines if the query execution succeeded. If the value is 0 it failed. For SELECT queries the return value is a result handle. It should be used to retrieve the query results.
Query statements often contain constant values like in following example:
$query="SELECT name,password FROM users WHERE user_name='admin'";
$result=$db->Query($query);
You may build queries like this by manually inserting constant values in the query statement text string. However, different DBMS may require that constant values be represented in different manners.
For instance, text constant values may need to escape special characters like '. Date constant values may be represented as integers rather than date text strings because the underlying DBMS does not support date data types natively.
Fortunately, Metabase provides a set of conversion functions that avoid having you to pass constant values in a format that is suitable for the type of DBMS you are talking too. These functions call the respective driver functions to do any representation conversion that may be needed.
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 DBMS.
For each data type supported by Metabase there is a constant value conversion function, except for integer constants because these are not likely to be represented differently between DBMS. Here is a complete list of constant values conversion functions:
Using the constant value conversion functions for the execution of the query mentioned above, it would be rewritten like this:
$query="SELECT name,password FROM users WHERE user_name=".$db->GetTextFieldValue("admin");
$result=$db->Query($query);
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.
Prepared queries are SQL 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.
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.
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 ?. Consider for instance this example that uses a direct query:
$query="SELECT name,password FROM users WHERE user_name=".$db->GetTextFieldValue("admin");
$result=$db->Query($query);
It may be rewritten using a prepared query like this:
$query="SELECT name,password FROM users WHERE user_name=?";
$prepared_query=$db->PrepareQuery($query);
Get a prepared query handle.
if($prepared_query)
Make sure the query was prepared without error.
{
$db->QuerySetText($prepared_query, 1, "admin");
Set the first query argument with the text constant value admin.
$result=$db->ExecuteQuery($prepared_query);
}
Execute the query. From here on handling query results is the same as for direct queries.
Prepared queries may have many arguments, all identified by the ? mark. Each one has an order number starting from 1.
There is one function to set prepared query arguments for each data type supported by Metabase. In the example QuerySetText was used to set a text argument. QuerySetInteger would have to be used if the argument was an integer, and so on. If the argument is meant to be set to NULL, use the function QuerySetNull.
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.
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 function FreePreparedQuery.
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 SetQuery like functions.
Often database tables have one key field that contains values that must be unique among all the table rows. One way to assure that the key field values are unique is to define them as being auto-incremented keys. That makes the key field of each row that is inserted in the table, be set to a new integer value.
Auto-incremented fields are initialized in ways that may vary from DBMS to DBMS. Metabase provides a DBMS independent solution to initialize auto-incremented key fields and retrieve the inserted key value that consists on using the functions GetNextKey and GetInsertedKey like this:
$db->GetNextKey("users", $key);
if($db->Query("INSERT INTO users (id, user_name, password, reminder, name, email) VALUES (".$key.
", 'administrator', 'some password', 'password reminder', 'SuperUser', 'admin@acme.com')")
&& $db->GetInsertedKey("users", $id))
echo "User record was successfully created with identifier: ", $id, "\n";
else
echo "There was an error: ",$db->Error(),"\n";
Some DBMS support omitting the auto-increment key field in the INSERT query. In that case you do not need to use the GetNextKey function to get the auto-increment field value expression.
if($db->Query("INSERT INTO users (user_name, password, reminder, name, email) VALUES (".
"'administrator', 'some password', 'password reminder', 'SuperUser', 'admin@acme.com')")
&& $db->GetInsertedKey("users", $id))
echo "User record was successfully created with identifier: ", $id, "\n";
else
echo "There was an error: ",$db->Error(),"\n";
Check the Available drivers section of the Metabase documentation to see if the DBMS driver class you want to use supports the OmitInsertKey feature, so you can determine if you can omit the auto-increment field value.
An alternative way to insert table rows with auto-increment fields is to use prepared queries and the functions QuerySetKey and GetInsertedKey like this:
$prepared_query=$db->PrepareQuery(
"INSERT INTO users (id, user_name, password, reminder, name, email) VALUES (?, ?, ?, ?, ?, ?)");
if($prepared_query)
{
$db->QuerySetKey($prepared_query, 1, "users");
$db->QuerySetText($prepared_query, 2, "Administrator");
$db->QuerySetText($prepared_query, 3, "some password");
$db->QuerySetText($prepared_query, 4, "password reminder");
$db->QuerySetText($prepared_query, 5, "SuperUser");
$db->QuerySetText($prepared_query, 6, "admin@acme.com");
if($db->ExecuteQuery($prepared_query)
&& $db->GetInsertedKey("users", $id))
echo "User record was successfully created with identifier: ", $id, "\n";
else
echo "There was an error: ",$db->Error(),"\n";
}
else
echo "There was an error: ",$db->Error(),"\n";
If the current DBMS driver class supports it, you may also omit the auto-increment key field in the INSERT query when using prepared queries.
A successfully executed SELECT returns a value that should be used as result handle. This value must be passed to every Metabase function that you may need to call to access the results return by the queries that your database applications execute.
A result handle is just a number that serves as a reference to a result set returned by the DBMS to the application as response to the query that was executed.
A result set is a sort of table with columns and rows filled with result data. You access each position of a result set using the FetchResult function.
Despite the rows of a result set are not always made available to the database application all at once, Metabase lets you request result data by specify the number of the respective result set row. Result set row numbers start from 0.
The FetchResult function also requires that you pass the identification of the column from which you to fetch the result data. The column identification may either be the column name or the column number starting from 0. The column name is the name of the field or the expression that defines the column in the SELECT query.
Here is simple example of a retrieving the results of a row of a query:
$result=$db->Query("SELECT name,email FROM users");
if($result!=0)
{
$name=$db->FetchResult($result, 0, "name");
$email=$db->FetchResult($result, 0, "email");
}
else
$error=$db->Error();
Despite referencing the result columns by name makes the application code look cleaner, specifying the column by number runs faster. So, the above result fetching statements may be rewritten like this:
$name=$db->FetchResult($result, 0, 0);
$email=$db->FetchResult($result, 0, 1);
The FetchResult function retrieves data just as it is returned by the DBMS. The data representation format for each type of field may vary from DBMS to DBMS.
To avoid the problem of having to handle data representation format differences in your database application, Metabase provides a set of functions that fetch the result data and already convert it to a single representation format defined for each supported data type.
For instance, no matter how each DBMS represents date fields, the function FetchDateResult always returns a text string with the date represented in the ISO 8601 format: YYYY-MM-DD. This simplifies database application development a lot and promotes code portability when using different DBMS.
Not all types of result data need to be converted. For instance, text and integer result data may always be fetched with FetchResult. Here follows the complete list of result fetching and conversion functions:
There is one type of query result that requires special care: NULL. NULL is not data, but rather the absence of data. A NULL may be returned because the selected field position does not contain information or the result value could not be computed like for instance evaluating the maximum value of a field of an empty set of database table rows.
Since NULL means exactly the absence of data, you may not use any of the result fetching functions to figure whether a given result position returned a NULL.
If you are not sure whether a given result position is NULL, you should use the function ResultIsNull. Here follows an example:
if($db->ResultIsNull($result, 0, "name"))
echo "The name is not defined."
else
echo "The name is :".$db->FetchResult($result, 0, "name");
Usually, database applications need to traverse the result set to retrieve all the rows of data that were returned when a query was executed.
Often it is useful to know in advance the total number of returned rows before starting to traverse the result set. The function NumberOfRows is meant for that purpose. A typical result set display routine may look like this:
$result=$db->Query("SELECT name,email FROM users");
if($result!=0)
{
$rows=$db->NumberOfRows($result);
if($rows>0)
{
echo "<TABLE><TR><TH>name</TH><TH>email</TH></TR>";
for($row=0; $row<$rows; $row++)
{
echo "<TR><TD>", $db->FetchResult($result, $row,"name"), "</TD>";
echo "<TD>", $db->FetchResult($result, $row, "email"), "</TD></TR>";
}
echo "</TABLE>";
}
else
echo "<P>There are no rows in the result set.</P>";
}
else
$error=$db->Error();
}
Despite this code looks clean and simple there is one relevant detail that should be considered before using it for queries that return a large number of rows.
Some DBMS, typically high end, start returning query result rows as soon as they are found in the database tables being searched. This means that they can not anticipate the total number of rows in the result set before they finish the query search.
For these DBMS the respective Metabase driver implements the function that obtains the total number of result rows by retrieving all rows at once. This circumstance not only makes this function very slow but it consumes much more memory because it implies that the DBMS driver will have to hold all the rows in memory until the result set is freed with the function FreeResult.
If you really need to know in advance the number of result rows, a possible alternative is to execute first a query that just returns the of rows using the SQL COUNT function like this:
$result=$db->Query("SELECT COUNT(name) FROM users");
if($result)
$rows=$db->FetchResult($result, 0, 0);
else
$error=$db->Error();
If you do not need to know in advance how many rows are returned by in a result set but would like to know when you have traversed all the whole result set, use the function EndOfResult instead. The result set browsing routine would look like this:
$result=$db->Query("SELECT name, email FROM users");
if($result!=0)
{
$end_of_result=$db->EndOfResult($result);
if($end_of_result==0)
{
echo "<TABLE><TR><TH>name</TH><TH>email</TH></TR>";
for($row=0; ($end_of_result=$db->EndOfResult($result))==0; $row++)
{
echo "<TR><TD>",$db->FetchResult($result, $row, "name"),"</TD>";
echo "<TD>", $db->FetchResult($result, $row, "email"), "</TD></TR>";
}
echo "</TABLE>";
}
else
{
if($end_of_result==1)
echo "<P>There are no rows in the result set.</P>";
}
if($end_of_result==-1)
$error=$db->Error();
}
else
$error=$db->Error();
}
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.
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.
With Metabase you should use the FreeResult function and it may be as simple as follows:
$db->FreeResult($result);
Large object fields, also known as LOBs (BLOBs/CLOBs), 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 function calls like with other types of fields.
Metabase provides a separate set of API functions to deal with large object fields. These functions let applications deal with the values of this type of fields by splitting them in smaller chunks of data.
Large object fields may be created like any other type of field, that is declaring them in schema description files as large object fields.
There are two types of large object fields: character fields and binary fields. Character fields could be used when you only intend to store ASCII text on them. If you intend to store other types of data, use binary fields instead.
Character large object fields should declared of being of the type clob. Binary large object fields should declared of being of the type blob. Here follows an example of declaration of a table with a character and a binary large object fields:
<table>
<name>files</name>
<declaration>
<field> <name>id</name> <type>integer</type> </field>
<field> <name>document</name> <type>clob</type> </field>
<field> <name>picture</name> <type>blob</type> </field>
</declaration>
</table>
With Metabase, storing data in large object fields can only be done by executing INSERT or UPDATE prepared queries. The large object values are passed to the database as parameters of the prepared queries. The functions QuerySetCLOB and QuerySetBLOB should be used to specify large object values as parameters of the prepared query.
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.
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.
Here follows an example of how to execute a query that inserts data defined programmatically into a large object character field:
if(($prepared_query=$db->PrepareQuery("INSERT INTO files (id,document,picture) VALUES (1,?,NULL)")))
{
$character_lob=array(
"Database"=>$db->database,
"Error"=>"",
"Data"=>"a lot of character data"
);
if(($success=MetabaseCreateLOB($character_lob, $clob)))
{
$db->QuerySetCLOB($prepared_query, 1, $clob,"document");
if(!$db->ExecuteQuery($prepared_query))
$error=$db->Error();
MetabaseDestroyLOB($clob);
}
else
$error=$character_lob["Error"];
$db->FreePreparedQuery($prepared_query);
}
else
$error=$db->Error();
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 DBMS are not able to update values of large object fields in more than one row per query.
if(($prepared_query=$db->PrepareQuery("UPDATE files SET picture=? WHERE id=1")))
{
$binary_lob=array(
"Database"=>$db->database,
"Error"=>"",
"Type"=>"inputfile",
"FileName"=>"my_image.gif"
);
if(($success=MetabaseCreateLOB($binary_lob, $blob)))
{
$db->QuerySetBLOB($prepared_query, 1, $blob, "picture");
if(!$db->ExecuteQuery($prepared_query))
$error=$db->Error();
MetabaseDestroyLOB($blob);
}
else
$error=$binary_lob["Error"];
$db->FreePreparedQuery($prepared_query);
}
else
$error=$db->Error();
Retrieving data from large object fields is done by executing normal SELECT queries.
The functions FetchCLOBResult and FetchBLOBResult return a value that identifies a large object handler class object. The data may be retrieved using the function MetabaseReadLOB. 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.
Here follows an example of how to select a character large object field to output its data:
if(($result=$db->ReadLOBQuery(,"SELECT document FROM files WHERE id=1")))
{
if($db->EndOfResult($result))
echo "No rows where returned.\n";
else
{
$clob=$db->FetchCLOBResult($result, 0, "document");
if($clob)
{
while(!MetabaseEndOfLOB($clob))
{
if(MetabaseReadLOB($clob, $data,8000)<0)
{
$error=MetabaseLOBError($clob);
break;
}
echo $data;
}
MetabaseDestroyLOB($clob);
}
else
$error=$db->Error();
}
$db->FreeResult($result);
}
else
$error=$db->Error();
Here follows an example of how to select a binary large object field and output its data to a file.
if(($result=$db->Query("SELECT picture FROM files WHERE id=1")))
{
if($db->EndOfResult($result))
echo "No rows where returned.\n";
else
{
$binary_lob=array(
"Type"=>"outputfile",
"Database"=>$db->database,
"Result"=>$result,
"Row"=>0,
"Field"=>"picture",
"Binary"=>1,
"Error"=>"",
"FileName"=>"my_image.gif"
);
if(($success=MetabaseCreateLOB($binary_lob, $blob)))
{
if(MetabaseReadLOB($blob, $data,0)<0)
{
$error=MetabaseLOBError($blob);
$success=0;
}
MetabaseDestroyLOB($blob);
}
else
$error=$binary_lob["Error"];
}
$db->FreeResult($result);
}
else
$error=$db->Error();
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.
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.
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.
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.
Some DBMS Metabase 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 UpdateDatabase function will fail without affecting the database.
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 DBMS server might fail.