Author: Manuel Lemos (mlemos-at-acm.org)
Version control: @(#) $Id: metabase.documentation,v 1.68 2006/07/11 19:49:03 mlemos Exp $
Metabase is a PHP package intended to provide DBMS (DataBase Management System) independent means to install, access and maintain SQL based databases.
The development of Metabase has two main goals. One is to provide a unified programming interface to access distinct SQL based DBMS. Using the same interface, programmers may develop database applications that may run with DBMS of different vendors: Oracle, Sybase, MySQL, Microsoft SQL Server, etc..
The second goal is to provide a tool to install and maintain database schemas using the same DBMS independent database interface. Using this tool, developers may design and evolve database schemas that serve the needs of their applications without having to worry on how to install or upgrade them on each type of DBMS that is used.
Metabase is divided in several parts:
Consists of a class that is able to instantiate the driver classes. There is also a set of functions that the applications should call to execute several different types of operations needed to access to the databases.
Consists of a set of driver classes that implement functions that are called by the interface functions to implement database access aspects that are DBMS specific.
Consists of a set of driver extension classes that implement functions that are called by the main driver class to implement schema management functions.
Consists of a script that performs a set of tests to verify if the DBMS specific drivers work in conformance with the way they are expected to work, thus without any bugs that may affect the Metabase applications that use them.
Class that is able to parse database schema description defined in a custom XML based format. The class produces a data structure that has the information of the different database objects described in the schema description file.
Class that is able to take the schema description data structure generated by the parser class and then create a database with the specified DBMS using the interface functions.
This class is able to compare the data structures of two versions of the same schema description (the currently installed in the DBMS and a new one to upgrade to) and execute the necessary operations to alter the database schema without disturbing any data stored after the database was installed or its schema was upgrade for the last time.
Design, implementation and documentation of most of Metabase.
Informix driver.
Interbase driver.
EditPlus templates for editing database schema XML files.
Query result set metadata retrieval functions.
smith-at-dybnet.de
clinn-at-dybnet.de
Query result set bulk data retrieval functions.
Schema XML style sheet
Database schema reverse engineering and SQLite driver
Microsoft Access ODBC sub-driver
SQLite driver auto-increment and primary key support
All DBMS provide multiple choice of data types for the information that can be stored in their database table fields. However, the set of data types made available varies from DBMS to DBMS.
To simplify the interface with the DBMS supported by Metabase it was defined a base set of data types that applications may access independently of the underlying DBMS.
The Metabase applications programming interface takes care of mapping data types when managing database options. It is also able to convert that is sent to and received from the underlying DBMS using the respective driver.
The text data type is available with two options for the length: one that is explicitly length limited and another of undefined length that should be as large as the database allows.
The length limited option is the most recommended for efficiency reasons. The undefined length option allows very large fields but may prevent the use of indexes and may not allow sorting on fields of its type.
The fields of this type should be able to handle 8 bit characters. Drivers take care of DBMS specific escaping of characters of special meaning with the values of the strings to be converted to this type.
The boolean data type represents only two values that can be either 1 or 0. Do not assume that these data types are stored as integers because some DBMS drivers may implement this type with single character text fields for a matter of efficient. Ternary logic is possible by using NULL as the third possible value that may be assigned to fields of this type.
The integer data type may store integer values as large as each DBMS may handle. Fields of this type may be created optionally as unsigned integers but not all DBMS support it. Therefore, such option may be ignored. Truly portable applications should not rely on the availability of this option.
The decimal data type may store decimal numbers accurately with a fixed number of decimal places. This data type is suitable for representing accurate values like currency amounts.
Some DBMS drivers may emulate the decimal data type using integers. Such drivers need to know in advance how many decimal places that should be used to perform eventual scale conversion when storing and retrieving values from a database. Despite this, applications may use arithmetic expressions and functions with the values stored on decimal type fields as long as any constant values that are used in the expressions are also converted with the respective Metabase conversion functions.
The number of places that are used to the left and the right of the decimal point is pre-determined and fixed for all decimal values stored in the same database. By default, Metabase uses 2 places to the right of the decimal point, but this may be changed when setting the database connection. The number of places available to the right of the decimal point depend on the DBMS.
It is not recommended to change the number places used to represent decimal values in database after it is installed. Metabase does not keep track of changes in the number of decimal places.
The float data type may store floating point decimal numbers. This data type is suitable for representing numbers within a large scale range that do not require high accuracy. The scale and the precision limits of the values that may be stored in a database depends on the DBMS that it is used.
The date data type may represent dates with year, month and day. DBMS independent representation of dates is accomplished by using text strings formatted according to the IS0 8601 standard.
The format defined by the ISO 8601 standard for dates is YYYY-MM-DD where YYYY is the number of the year (Gregorian calendar), MM is the number of the month from 1 to 12 and DD is the number of the day from 1 to 31. Months or days numbered below 10 should be padded on the left with 0.
Some DBMS have native support for date formats, but for others the DBMS driver may have to represent them as integers or text values. In any case, it is always possible to make comparisons between date values as well sort query results by fields of this type.
The time data type may represent the time of a given moment of the day. DBMS independent representation of the time of the day is also accomplished by using text strings formatted according to the IS0 8601 standard.
The format defined by the ISO 8601 standard for the time of the day is HH:MI:SS where HH is the number of hour the day from 0 to 23 and MI and SS are respectively the number of the minute and of the second from 0 to 59. Hours, minutes and seconds numbered below 10 should be padded on the left with 0.
Some DBMS have native support for time of the day formats, but for others the DBMS driver may have to represent them as integers or text values. In any case, it is always possible to make comparisons between time values as well sort query results by fields of this type.
The time stamp data type is a mere combination of the date and the time of the day data types. The representation of values of the time stamp type is accomplished by joining the date and time string values in a single string joined by a space. Therefore, the format template is YYYY-MM-DD HH:MI:SS. The represented values obey the same rules and ranges described for the date and time data types.
The large object data types are meant to store data of undefined length that may be to large to store in text fields, like data that is usually stored in files.
Metabase supports two types of large object fields: Character Large OBjects (CLOBs) and Binary Large OBjects (BLOBs). CLOB fields are meant to store only data made of printable ASCII characters. BLOB fields are meant to store all types of data.
Large object fields are usually not meant to be used as parameters of query search clause (WHERE) unless the underlying DBMS supports a feature usually known as "full text search".
The applications programming interface consists of one set of functions that call the appropriate DBMS specific functions to implement several database access functions.
The Metabase API function can be called in two different forms: calling the API global functions or calling the driver object functions directly. Both forms are always available and may be used at the same time in the programs that use Metabase.
The original form to call Metabase API functions is through a set of global functions that have their names starting with the Metabase prefix.
These functions make indirect calls to the chosen driver class object. They always take as first parameter an integer value that works as a handle to identify the driver class object that is meant to be called.
If for some reason you need to use Metabase under PHP 3 or you need write code that works under PHP 3, using these functions is your only option.
To use this form of calling Metabase API you need to use first the function MetabaseSetupDatabase described below.
Another form to call Metabase API functions is calling the driver class object functions directly. The driver class functions to call have the exact same names as the API global functions, except that they do not have the Metabase prefix nor need the database handler integer as first argument.
To use this form you need to use first the function MetabaseSetupDatabaseObject described below. This function only works under PHP 4 or better because it returns a reference to a driver class object that is stored in a private global variable.
Calling driver functions directly works slightly faster. However, you need to be careful to always pass references to the object to other functions or objects because when you pass objects by value you end with two distinct copies of the same driver object that may lead to inconsistencies in the use of the driver class functions.
If you do not want or you are not sure of how to safely pass or assign objects by reference, just call the driver object functions from your main scripts or use the API global functions instead.
$error=MetabaseSetupDatabase($arguments, &$database)
Setup a database interface instance. Applications need call this function first to obtain a database interface instance handle. This handle is needed to call all the other database interface functions.
Usually, this function does not establish a database connection. Database connections are established by the DBMS drivers whenever they are needed.
The $arguments argument is an associative array that takes pairs of tag names and values that define which DBMS driver is meant to be used and the parameters needed to setup that driver. Currently, the types of supported arguments are as follows:
The database connection setup arguments may be specified individually or all at once with the Connection argument. This argument consists of a connection string like an URL of the following format:
Type://User:Password@Host:Port/Database?
Options/option1=value1&Options/option2=value2...
You may use this argument to replace all or part of the other arguments. Values specified in this connection string prevail over values specified in the separate arguments.
The Type argument is required. If the User argument is specified, the @ character is necessary. If the Password is specified, the : character before it is required. If the Port argument is specified, the : before it is required.
The Options argument forms an option array where the indexes are the names of the database specific options specified after the respective / character. The values of the respective options follow the = character until the & character or the end of the Connection URL.
Keep in mind that argument values with characters that have special meaning in URLs like &, =, ? and % need to be properly escaped using a % followed by the hexadecimal representation of the ASCII code of the respective characters, so they become respectively: %26, %3D, %3F and %25. If you may have characters like these in an argument value, it may be simpler to encode them with the PHP function UrlEncode.
Default value: none
Example: mysql://root@localhost/driver_test?
Options/Port=/var/lib/mysql/mysql.sock
Designation of the type of DBMS driver that is meant to be used. The designation of the currently built-in supported drivers is as follows:
Default value: not specified
Name of the file of the DBMS driver class to be included if the Type argument is not specified.
Default value: not specified
Name of the constant that is defined within the specified DBMS driver class file. This function will check if the specified name is defined to prevent multiple inclusions of the driver class file. This argument is ignored if the Type argument is specified.
Default value: not specified
Name of the DBMS driver class. This argument is ignored if the Type argument is specified.
Default value: not specified
Path of the directory where the driver class include file may be found. This argument is required if Metabase include files are not located in the same directory as the current script directory that is running.
Default value: ""
Access name of the user that has to login to have access to the DBMS. This argument may be ignored by some DBMS drivers.
Default value: ""
Password of the user that is has to login to have access to the DBMS. This argument may be ignored by some DBMS drivers.
Default value: ""
Address of the host on which the DBMS is running. This argument may be ignored by some DBMS drivers.
Default value: ""
Optional argument that can be used to specify a initial database name that is equivalent to use the MetabaseSetDatabase function.
Default value: none
Boolean option that indicates whether the connections to the DBMS should be persistent. A persistent connection may be reused multiple times from the same application when using the same connection context (user, password, DBMS host, etc.).
Persistent connections are important to minimize DBMS connection overhead that may be avoided, especially when running a PHP as Web server module.
Default value: 1
Integer option that indicates the number of places to the right of the decimal point that should be used to represent values of the decimal data type. The default value is suitable to represent currency amounts in most applications, but in some markets it is required by law to represent currency values with 3 or more decimal places.
Default value: 2
Name of a global function that is meant to be called to output debugging information. If this option is not set or is set an empty string, no debug information is output.
Default value: ""
Associative array that takes pairs of tag names and values that define the DBMS driver specific options. Consult the respective driver documentation to learn about the available options.
Default value: empty array
Integer option that indicates the length of the buffer that is used to read data to store in large object fields.
Default value: 8000
String option that indicates the characters that will be used to break the lines of the messages that are outputted to the log stream with the MetabaseDebug function. The default value is suitable for outputting the log lines when using Unix like OS based computers. For Windows like OS based computers you may want to set this option to "\r\n".
Default value: \n
The $database argument is a reference to a variable that will hold a unique integer value that works as database access handle. This handle value should be passed as argument to the other database interface functions.
The $error return value contains an error message if this function call did not succeed. Otherwise it contains an empty string. This return value may be safely ignored if the function arguments are correctly defined.
$error=MetabaseSetupDatabase(array(
"Type"=>"oci",
"User"=>"oracle_user",
"Password"=>"oracle_user_password",
"Options"=>array(,
"SID"=>"dboracle",
"HOME"=>"/home/oracle/u01",
"DBAUser"=>"SYS",
"DBAPassword"=>"change_on_install"
),
), $database);
if($error!="")
{
echo "Error while setting database access instance: $error";
exit;
}
MetabaseSetDatabase($database,"test");
$result=MetabaseQuery($database,"SELECT id,password FROM users WHERE alias='admin'");
$error=MetabaseSetupDatabaseObject($arguments, &$db)
The purpose of this function is the same as the MetabaseSetupDatabaseObject function. The only difference is that the $database integer handle argument is replaced by the $db object argument that should be used to call the driver class object functions directly instead of calling the Metabase API global functions.
This function can not be used under PHP 3 because it takes variable references internally, which is only supported starting from PHP 4.
The $arguments argument is an associative array that with the exact same usage as when calling MetabaseSetupDatabase function.
The $db argument is a reference to a variable that will hold a driver class object value. This object value should be used to call the driver object functions directly.
The driver object functions have exactly the same name of the correspondent API global functions, except that they do not have the Metabase prefix. If you still need to call the API global functions, you may retrieve the database access integer handle by accessing the database driver object variable ($db->database).
The $error return value contains an error message if this function call did not succeed. Otherwise it contains an empty string. This return value may be safely ignored if the function arguments are correctly defined.
$error=MetabaseSetupDatabaseObject(array(
"Type"=>"oci",
"User"=>"oracle_user",
"Password"=>"oracle_user_password",
"Options"=>array(,
"SID"=>"dboracle",
"HOME"=>"/home/oracle/u01",
"DBAUser"=>"SYS",
"DBAPassword"=>"change_on_install"
),
), $db);
if($error!="")
{
echo "Error while setting database access instance: $error";
exit;
}
$db->SetDatabase("test");
$result=$db->Query("SELECT id,password FROM users WHERE alias='admin'");
$error=MetabaseParseConnectionArguments($connection, $arguments)
Parse a connection string, as the one that may be passed to the Connection argument of the function MetabaseSetupDatabase, and return array of arguments populated with the arguments that would be passed to that same function if their values were passed separately.
This function can be used to validate a connection string and extract the respective argument values if the string is correctly formatted.
The $connection argument is a URL string that defines the database connection arguments as defined for the Connection argument for the function MetabaseSetupDatabase.
The $arguments argument is a reference to an array variable that will be populated with the connection arguments. This function does not initialize this array. An already initialized array variable must be passed to the function.
The $error return value contains an error message if the connection string is not valid. Otherwise it contains an empty string.
MetabaseCloseSetup($database)
Release any resources allocated during the access of the specified database instance. Applications MUST call this function before exit or else incomplete operations may be canceled.
If there is a transaction in progress when this function is called, it is canceled by calling the MetabaseRollbackTransaction. Other DBMS specific resources may be freed depending on the associated driver.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
$previous_name=MetabaseSetDatabase($database, $name)
Set the name of the database that is meant to be accessed. This function does not establish a new database connection or closes any previously opened database connection. It just defines the name of the database that is meant to be access in subsequent operations that necessarily require database access.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name of the database that is meant to be accessed.
The $previous_name return value is the name of the database that was set before calling this function.
$error=MetabaseError($database)
Retrieve the error message text associated with the last operation that failed. Some functions may fail but they do not return the reason that makes them to fail. This function is meant to retrieve a textual description of the failure cause.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $error return value is the error message text associated with the last failure.
$previous_error_handler=MetabaseSetErrorHandler($database, $error_handler)
Specify a function that is called when an error occurs.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $error_handler argument is a string with the name of the function that will be called on error. If an empty string is specified, no handler function is called on error.
The error handler function receives two arguments. The first argument a reference to the driver class object that triggered the error. The second argument is a reference to an associative array that provides details about the error that occurred. These details provide more information than it is returned by the MetabaseError function.
Here follows the list of currently supported error detail entries:
String that indicates the scope of the driver object class within which the error occurred.
Error message as is returned by the MetabaseError function.
The $previous_error_handler return value is name of the function that was previously defined as error handler.
$supported=MetabaseSupport($database, $feature)
Determine if the DBMS driver associated with the given database access handle supports the specified feature.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $feature argument is the name of the feature that is intended to be queried for support. Developers should consult the DMBS driver specific documentation to determine which support the features they need to develop their applications.
The name of the features that currently may be queried is as follows:
Ability to determine the number of table rows that were affected by the last INSERT, UPDATE or DELETE query.
Ability to create and use tables auto-incremented key fields. Support for this feature implies supporting primary keys.
Ability to create indexes on table fields.
Ability to specify sorting direction on the creation of table indexes.
Ability to retrieve the current value of a sequence. Support for this feature implies supporting sequences.
Has Large OBject table fields to store character or binary data.
Ability to execute INSERT queries omiting the auto-increment key field.
Ability to order the result of SELECT queries by text fields of unspecified length.
Ability to build patterns to match text expressions that may include wildcard characters that should be matched literally.
Ability to create tables primary key fields.
Ability to execute REPLACE queries using the function MetabaseReplace even if it is done by emulation.
Ability to restrict the range of result rows that are returned by the DBMS when executing a SELECT query.
Ability to create and retrieve sequential values.
Ability to use summary function on queries such as COUNT, MAX, MIN, AVG, etc..
Ability to start atomic multiple query transactions.
$result=MetabaseQuery($database, $query)
Execute an arbitrary database query statement.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $query argument is the query statement to be executed. The values used in the statement should be already in a DBMS specific representation format.
Use the data type conversion functions to convert DBMS independent data values into DBMS specific values to use within this function's query argument. Alternatively, you may want to use prepared queries to avoid having to compose statements with DBMS specific values.
The $result return value determines if this function succeeded. A value of 0 indicates that the query failed. For queries that return selected information, the $result return value is a handle that should be used access to the query result information. In this case, the MetabaseFreeResult function should be called when the result information is no longer needed.
$success=MetabaseReplace($database, $table, $fields)
Execute a SQL REPLACE query. A REPLACE query is identical to a INSERT query, except that if there is already a row in the table with the same key field values, the REPLACE query just updates its values instead of inserting a new row.
The REPLACE type of query does not make part of the SQL standards. Since practically only MySQL implements it natively, this type of query is emulated through this Metabase function for other DBMS using standard types of queries inside a transaction to assure the atomicity of the operation.
Use the MetabaseSupport function to figure if the current driver class object implements the REPLACE query even if it is emulated.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $table argument is the name of the table on which the REPLACE query will be executed.
The $fields argument is an associative array that describes the fields and the values that will be inserted or updated in the specified table. The indexes of the array are the names of all the fields of the table. The values of the array are also associative arrays that describe the values and other properties of the table fields.
Here follows a list of field properties that need to be specified:
Value to be assigned to the specified field. This value may be of specified in database independent type format as this function can perform the necessary datatype conversions.
Default: this property is required unless the Null property is set to 1.
Name of the type of the field. Currently, all types Metabase are supported except for clob and blob.
Default: text
Boolean property that indicates that the value for this field should be set to NULL.
The default value for fields missing in INSERT queries may be specified the definition of a table. Often, the default value is already NULL, but since the REPLACE may be emulated using an UPDATE query, make sure that all fields of the table are listed in this function argument array.
Default: 0
Boolean property that indicates that this field should be handled as a primary key or at least as part of the compound unique index of the table that will determine the row that will updated if it exists or inserted a new row otherwise.
This function will fail if no key field is specified or if the value of a key field is set to NULL because fields that are part of unique index they may not be NULL.
Default: 0
The $success return value determines if this function succeeded. A value of 0 indicates that the query failed.
Metabase defines a set of base data types that are supported somehow in all DBMS for which there is a driver. However, not all data types are directly supported by each DBMS.
The DBMS drivers are responsible for converting the values of the base datatypes to a representation format that is suitable to emulate their behavior without loss of the values that each type is intended to represent.
$converted=MetabaseGetTextFieldValue($database, $value)
Convert a text value into a DBMS specific format that is suitable to compose query statements.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $value argument is the text string value that is intended to be converted.
The $converted_value return value is a text string that already contains any DBMS specific escaped character sequences.
$converted_value=MetabaseGetBooleanFieldValue($database, $value)
Convert a boolean value into a DBMS specific format that is suitable to compose query statements.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $value argument is the boolean value that is intended to be converted.
The $converted_value return value is a text string that represents the given argument value in a DBMS specific format.
$converted_value=MetabaseGetDecimalFieldValue($database, $value)
Convert a decimal value into a DBMS specific format that is suitable to compose query statements.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $value argument is the decimal value that is intended to be converted.
The $converted_value return value is a text string that represents the given argument value in a DBMS specific format.
$converted_value=MetabaseGetFloatFieldValue($database, $value)
Convert a float value into a DBMS specific format that is suitable to compose query statements.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $value argument is the float value that is intended to be converted.
The $converted_value return value is a text string that represents the given argument value in a DBMS specific format.
$converted_value=MetabaseGetDateFieldValue($database, $value)
Convert a date value into a DBMS specific format that is suitable to compose query statements.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $value argument is the date value that is intended to be converted. The value should be represent in the ISO 8601 format "YYYY-MM-DD".
The $converted_value return value is a text string that represents the given argument value in a DBMS specific format.
$converted_value=MetabaseGetTimeFieldValue($database, $value)
Convert a time value into a DBMS specific format that is suitable to compose query statements.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $value argument is the time value that is intended to be converted. The value should be represent in the ISO 8601 format "HH-MI-SS".
The $converted_value return value is a text string that represents the given argument value in a DBMS specific format.
$converted_value=MetabaseGetTimestampFieldValue($database, $value)
Convert a time stamp value into a DBMS specific format that is suitable to compose query statements.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $value argument is the time stamp value that is intended to be converted. The value should be represent in the ISO 8601 format "YYYY-MM-DD HH:MM:SS".
The $converted_value return value is a text string that represents the given argument value in a DBMS specific format.
$timestamp=MetabaseNow()
Generate a text string that represents the actual date and time on the moment the function is called. The generated string is in the ISO 8601, so it is suitable to pass to other Metabase functions that take timestamp values.
The $timestamp return value is a text string that represents the current date and time.
$date=MetabaseToday()
Generate a text string that represents the actual date on the moment the function is called. The generated string is in the ISO 8601, so it is suitable to pass to other Metabase functions that take date values.
The $date return value is a text string that represents the current date.
$time=MetabaseTime()
Generate a text string that represents the current time on the moment the function is called. The generated string is in the ISO 8601, so it is suitable to pass to other Metabase functions that take date values.
The $time return value is a text string that represents the current time.
All DBMS provide means to search for patterns in text fields using the LIKE operator. It can be used to match a text field value with a pattern that may include wildcard characters.
Patterns may use the character wildcard character % to match 0 or more occurrences of any character. The wildcard character _ may be used to match a single occurence of any character.
The characters % and _ need to be escaped in a special way to be matched literally. Some DBMS also consider other wildcard characters that need to be escaped so they can be used be matched literally.
Metabase provides several functions to build patterns and escaping character literals that otherwise would be handled as wildcards.
$match=MetabaseBeginsWith($database, $value)
Build a pattern that matches the any text expression that starts with the given text value.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $value argument is the text string value that should match the beginning of the text expression.
The $match return value is a text string with an SQL expression to match the given pattern. It starts with the SQL LIKE operator.
$match=MetabaseEndsWith($database, $value)
Build a pattern that matches the any text expression that ends with the given text value.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $value argument is the text string value that should match the end of the text expression.
The $match return value is a text string with an SQL expression to match the given pattern. It starts with the SQL LIKE operator.
$match=MetabaseContains($database, $value)
Build a pattern that matches the any text expression that contains the given text value.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $value argument is the text string value that should match any part of the text expression.
The $match return value is a text string with an SQL expression to match the given pattern. It starts with the SQL LIKE operator.
$match=MetabaseMatchPattern($database, $pattern)
Build a custom pattern that matches a text expression according to the given pattern definition.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $pattern argument is an array of string values that defines the pattern to match.
The array position 0 and any other values even positions represent strings that should be matched literally. The array position 1 and any other values in odd positions must be strings with the wildcard characters % and _.
For instance, to match text expressions that start with the literal string begin, the pattern array should be defined as: array( 'begin', '%'). To match text expressions that end with the literal string end, the pattern array should be defined as: array( '', '%', 'end'). To match text expressions that contain the literal string contains, the pattern array should be defined as: array( '', '%', 'contains', '%').
The $match return value is a text string with an SQL expression to match the given pattern. It starts with the SQL LIKE operator.
Metabase supports the insertion of table rows with special key fields, known as auto-increment fields. These table fields store automatically generated sequential values.
Although not all DBMS natively support table fields with auto-incremented keys, Metabase provides a DBMS independent set of functions to insert and retrieve this kind of key values.
With certain DBMS that do not support native auto-incremented key fields, the respective Metabase DBMS driver may provide a suitable emulation that makes it work the same way with all DBMS.
If a DBMS driver does not support auto-incremented key fields, not even through emulation, the alternative is to use database sequence objects, if possible. Use the MetabaseSupport function to determine whether a driver supports either auto-incremented key fields or database sequence objects.
Note that to implement auto-incremented table keys with DBMS that do not support them natively, it is necessary that the table is installed by the function MetabaseCreateDetailedTable or by Metabase database schema manager class.
$success=MetabaseGetNextKey($database, $table, $key_sql)
Retrieve an SQL expression to be used in the place of the value of an auto-incremented key field in SQL INSERT queries to insert new rows in a given table.
If the current DBMS supports omitting the auto-increment key field, it is recommended that it is omitted from the INSERT query. In that case the function MetabaseGetNextKey is not necessary.
Use the MetabaseSupport function to determine whether the current driver class supports omitting the auto-increment key field in INSERT queries.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $table argument is the name of the table into which a new row will be inserted.
The $key_sql argument is a reference to a string variable into which it is returned the SQL expression to be used in the place of the value of an auto-incremented key field in the SQL INSERT query.
The $success return value determines if this function succeeded. A value of 0 indicates that it was not possible to retrieve the key SQL expression.
$success=MetabaseGetNextKey($database, "users", $key);
if($success)
{
$result=MetabaseQuery($database, "INSERT INTO users (id, alias, password) VALUES ( ".$key.", 'some user', 'some password')");
}
$success=MetabaseGetInsertedKey($database, $table, $key_value)
Retrieve the value of an auto-incremented key of a row just inserted in a given database table.
This function must be called right after calling MetabaseQuery or MetabaseExecuteQuery that executed the SQL INSERT query to insert a row in the table with an auto-incremented key field.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $table argument is the name of the table into which the new row was inserted.
The $key_value is a reference to an integer variable into which it is returned the value of the auto-incremented key value of the row inserted in the specified table.
The $success return value determines if this function succeeded. A value of 0 indicates that it was not possible to retrieve the key value.
Prepared queries are queries that have passed through a pre-parsing process. They may take less time to run when they are intended to run if they are intended to be run multiple times from the same application.
Prepared queries may have marks that identify the position in the statement where parameter values will be inserted. Insertion positions are marked by the character ?. Every time a prepared query is executed the insertion marks are substituted by the values defined for the respective parameter. Parameter values may be changed before each time the query is executed.
Not all DBMS support prepared queries. For those DBMS that do not support them, prepared queries are emulated by substituting the insertion marks in the query with the parameter values converted to the DBMS specific format using the respective data type conversion functions.
$prepared_query=MetabasePrepareQuery($database, $query)
Create a prepared query. The specified query is parsed and the resulting data is stored for subsequent execution.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $query argument is the query statement to be prepared. ? character should be used to mark the positions of query parameters in the statement.
The $prepared_query return value is a handle that should be passed as argument to the prepared query parameterization and execution functions. If this function fails the return value is set to 0.
$success=MetabaseFreePreparedQuery($database, $prepared_query)
Release resources allocated for the specified prepared query.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $prepared_query argument is a handle that was returned by the function MetabasePrepareQuery.
The $success return value indicates whether the function was able to successfully free the resources allocated for the given $prepared_query. This return value may be safely ignored if the $prepared_query argument corresponds to a valid prepared query handle that was not yet freed.
$result=MetabaseExecuteQuery($database, $prepared_query)
Execute a prepared query statement.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $prepared_query argument is a handle that was returned by the function MetabasePrepareQuery.
The $result return value determines if this function succeeded. A value of 0 indicates that there are some undefined parameter values or that the query failed.
For queries that return selected information, the $result return value is a handle that should be used access to the query result information. In this case, the MetabaseFreeResult function should be called when the result information is no longer needed.
$success=MetabaseQuerySet($database, $prepared_query, $parameter, $type, $value, $is_null, $field)
Set the value of a parameter of a prepared query. Note that this function does not perform any value escaping or data type conversions, like would be done when calling MetabaseQuerySet<type> like functions.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $prepared_query argument is a handle that was returned by the function MetabasePrepareQuery.
The $parameter argument is the order number of the parameter in the query statement. The order number of the first parameter is 1.
The $type argument is designation of the type of the parameter to be set. The designation of the currently supported types is as follows:
The $value argument is the value that is meant to be assigned to specified parameter. The type of the value depends on the $type argument.
The $is_null argument is a boolean flag that indicates whether whether the parameter is a NULL.
The $field argument is the name of the field that is meant to be assigned with this parameter value when it is of type clob or blob.
The $success return value indicates whether the function was able to successfully set the specified parameter. This return value may be safely ignored if the $prepared_query argument corresponds to a valid prepared query and the $value argument is a valid value of the specified type.
$success=MetabaseQuerySetNull($database, $prepared_query, $parameter, $type)
Set the value of a parameter of a prepared query to NULL.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $prepared_query argument is a handle that was returned by the function MetabasePrepareQuery.
The $parameter argument is the order number of the parameter in the query statement. The order number of the first parameter is 1.
The $type argument is designation of the type of the parameter to be set. The designation of the currently supported types is list in the usage of the function MetabasePrepareQuery.
The $success return value indicates whether the function was able to successfully set the specified parameter. This return value may be safely ignored if the $prepared_query argument corresponds to a valid prepared query.
$success=MetabaseQuerySetText($database, $prepared_query, $parameter, $value)
Set a parameter of a prepared query with a text value.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $prepared_query argument is a handle that was returned by the function MetabasePrepareQuery.
The $parameter argument is the order number of the parameter in the query statement. The order number of the first parameter is 1.
The $value argument is a text value that is meant to be assigned to specified parameter.
The $success return value indicates whether the function was able to successfully set the specified parameter. This return value may be safely ignored if the $prepared_query argument corresponds to a valid prepared query and the $value argument is a valid text value.
$success=MetabaseQuerySetBoolean($database, $prepared_query, $parameter, $value)
Set a parameter of a prepared query with a boolean value.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $prepared_query argument is a handle that was returned by the function MetabasePrepareQuery.
The $parameter argument is the order number of the parameter in the query statement. The order number of the first parameter is 1.
The $value argument is a boolean value that is meant to be assigned to specified parameter.
The $success return value indicates whether the function was able to successfully set the specified parameter. This return value may be safely ignored if the $prepared_query argument corresponds to a valid prepared query and the $value argument is a valid boolean value.
$success=MetabaseQuerySetInteger($database, $prepared_query, $parameter, $value)
Set a parameter of a prepared query with an integer value.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $prepared_query argument is a handle that was returned by the function MetabasePrepareQuery.
The $parameter argument is the order number of the parameter in the query statement. The order number of the first parameter is 1.
The $value argument is an integer value that is meant to be assigned to specified parameter.
The $success return value indicates whether the function was able to successfully set the specified parameter. This return value may be safely ignored if the $prepared_query argument corresponds to a valid prepared query and the $value argument is a valid integer value.
$success=MetabaseQuerySetDecimal($database, $prepared_query, $parameter, $value)
Set a parameter of a prepared query with an decimal value.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $prepared_query argument is a handle that was returned by the function MetabasePrepareQuery.
The $parameter argument is the order number of the parameter in the query statement. The order number of the first parameter is 1.
The $value argument is an decimal value that is meant to be assigned to specified parameter.
The $success return value indicates whether the function was able to successfully set the specified parameter. This return value may be safely ignored if the $prepared_query argument corresponds to a valid prepared query and the $value argument is a valid decimal value.
$success=MetabaseQuerySetFloat($database, $prepared_query, $parameter, $value)
Set a parameter of a prepared query with a float value.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $prepared_query argument is a handle that was returned by the function MetabasePrepareQuery.
The $parameter argument is the order number of the parameter in the query statement. The order number of the first parameter is 1.
The $value argument is a float value that is meant to be assigned to specified parameter.
The $success return value indicates whether the function was able to successfully set the specified parameter. This return value may be safely ignored if the $prepared_query argument corresponds to a valid prepared query and the $value argument is a valid float value.
$success=MetabaseQuerySetDate($database, $prepared_query, $parameter, $value)
Set a parameter of a prepared query with a date value.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $prepared_query argument is a handle that was returned by the function MetabasePrepareQuery.
The $parameter argument is the order number of the parameter in the query statement. The order number of the first parameter is 1.
The $value argument is a date value that is meant to be assigned to specified parameter.
The $success return value indicates whether the function was able to successfully set the specified parameter. This return value may be safely ignored if the $prepared_query argument corresponds to a valid prepared query and the $value argument is a valid date value.
$success=MetabaseQuerySetTime($database, $prepared_query, $parameter, $value)
Set a parameter of a prepared query with a date value.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $prepared_query argument is a handle that was returned by the function MetabasePrepareQuery.
The $parameter argument is the order number of the parameter in the query statement. The order number of the first parameter is 1.
The $value argument is a time value that is meant to be assigned to specified parameter.
The $success return value indicates whether the function was able to successfully set the specified parameter. This return value may be safely ignored if the $prepared_query argument corresponds to a valid prepared query and the $value argument is a valid time value.
$success=MetabaseQuerySetTimestamp($database, $prepared_query, $parameter, $value)
Set a parameter of a prepared query with a time stamp value.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $prepared_query argument is a handle that was returned by the function MetabasePrepareQuery.
The $parameter argument is the order number of the parameter in the query statement. The order number of the first parameter is 1.
The $value argument is a time stamp value that is meant to be assigned to specified parameter.
The $success return value indicates whether the function was able to successfully set the specified parameter. This return value may be safely ignored if the $prepared_query argument corresponds to a valid prepared query and the $value argument is a valid time stamp value.
$success=MetabaseQuerySetCLOB($database, $prepared_query, $parameter, $value, $field)
Set a parameter of a prepared query with a character large object value.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $prepared_query argument is a handle that was returned by the function MetabasePrepareQuery.
The $parameter argument is the order number of the parameter in the query statement. The order number of the first parameter is 1.
The $value argument is a handle of large object created with MetabaseCreateLOB function from which it will be read the data value that is meant to be assigned to specified parameter.
The $field argument is the name of the field of a INSERT or UPDATE query to which it will be assigned the value to specified parameter.
The $success return value indicates whether the function was able to successfully set the specified parameter. This return value may be safely ignored if the $prepared_query argument corresponds to a valid prepared query.
$success=MetabaseQuerySetBLOB($database, $prepared_query, $parameter, $value, $field)
Set a parameter of a prepared query with a binary large object value.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $prepared_query argument is a handle that was returned by the function MetabasePrepareQuery.
The $parameter argument is the order number of the parameter in the query statement. The order number of the first parameter is 1.
The $value argument is a handle of large object created with MetabaseCreateLOB function from which it will be read the data value that is meant to be assigned to specified parameter.
The $field argument is the name of the field of a INSERT or UPDATE query to which it will be assigned the value to specified parameter.
The $success return value indicates whether the function was able to successfully set the specified parameter. This return value may be safely ignored if the $prepared_query argument corresponds to a valid prepared query.
$success=MetabaseQuerySetKey($database, $prepared_query, $parameter, $table)
Set a parameter of an INSERT prepared query to be bound to the auto-incremented key field of a given table. When the prepared query is executed, Metabase will assign the specified query parameter to the given table auto-incremented key SQL expression as it is returned by the MetabaseGetNextKey function.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $prepared_query argument is a handle that was returned by the function MetabasePrepareQuery.
The $parameter argument is the order number of the parameter in the query statement. The order number of the first parameter is 1.
The $table argument is the name of the table with the auto-incremented key field to be bound to the specified query parameter.
The $success return value indicates whether the function was able to successfully set the specified parameter. This return value may be safely ignored if the $prepared_query argument corresponds to a valid prepared query and the $table argument corresponds to a valid table with an auto-incremented key field.
$prepared_query=MetabasePrepareQuery($database, "INSERT INTO articles (id, title, body) VALUES(?, ?, ?)"); if($prepared_query) { MetabaseQuerySetKey($database, $prepared_query, 1, "articles"); MetabaseQuerySetText($database, $prepared_query, 2, "Some title"); MetabaseQuerySetText($database, $prepared_query, 3, "Some body text"); if(MetabaseExecuteQuery($database, $prepared_query) && MetabaseGetInsertedKey($database, "articles", $article_id)) echo "The article was successfully inserted with identifier", $article_id, "\n"; else echo "An error occurred: ", MetabaseError($database), "\n"; }
$success=MetabaseSetSelectedRowRange($database, $first, $limit)
Restrict the range of result rows that are returned by the DBMS when executing a SELECT query.
The most obvious use for this function is to display query results split into pages with a fixed number of result rows per page to be given by the $limit argument. The $first argument would be set to the number of first the record to displayed in each page. Its value would always be a multiple of the value set to the $limit argument starting from 0 for the first page.
This function may also be used to browse individual database records in pages displaying each record with controls to go forward or backwards. In this case the $limit argument would be set to 1 and the $first argument value would be increased or decreased depending on whether it would be browsing forward or backwards respectively.
This function should be called right before executing a query using the MetabaseQuery function or creating a prepared query with the MetabasePrepareQuery function.
After calling these functions the select range values will be reset as if they were never set before. So, to activate select range restriction, this function has to be called every time before executing a query or creating a prepared query.
The select range may be changed for prepared queries by calling this function before the queries are actually executed with MetabaseExecuteQuery. The range values are maintained between subsequent executions of each prepared query with the values that were defined initially or before the last time the prepared query was executed.
The number of rows returned by a query restricted by a range defined by this function is always smaller or equal to the value of the $limit argument. The result row numbers start from 0 but the returned rows correspond to those that would appear in the unrestricted result set displaced by the number specified by the $first argument.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $first argument is number of the first row to be returned when the next SELECT query is executed. Row numbers start from 0. If the total number of rows that result from the query is less or equal to the range first row number, the result set will be empty. The $first argument should be equal or greater than 0 or else this function fails.
The $limit argument is the maximum number of rows to be returned when the next SELECT query is executed. The result set may return a smaller number of rows if there are not as many rows in the query result set, starting from the row specified by the $first argument. The $limit argument should be equal or greater than 1 or else this function fails.
The $success return value indicates whether the function was able to successfully set the range of rows to be returned by the next SELECT query. This return value may be safely ignored if the respective DBMS driver supports selected range rows and $first and $limit arguments specify valid values.
$success=MetabaseAffectedRows($database, &$affected_rows)
Determine the number of table rows that were affected by the last INSERT, UPDATE or DELETE query. Not all DBMS can to determine the number of affected rows by a query. Use the function MetabaseSupport to determine whether the DBMS driver has this ability.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $affected_rows argument is a reference to a variable that will hold the number of affected rows.
The $success return value indicates whether the function was able to successfully determine the number of affected rows. This function will always fail if the DBMS driver does not can determine the number of affected rows or the last query was not supposed to affect database table rows.
$is_null=MetabaseResultIsNull($database, $result, $row, $field)
Determine whether the value of a query result located in given row and field is a NULL.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $row argument is the number of the row from which it is intended to check the result value. Row numbers start from 0.
The $field argument is either the name or the number of the field column from which it is intended to check the result value. Column numbers start from 0.
The $is_null result value is a boolean flag that indicates whether the result value in the given position is a NULL. Applications should not fetch the values of NULL result positions.
$value=MetabaseFetchResult($database, $result, $row, $field)
Fetch the value of a query result located in given row and field.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $row argument is the number of the row from which it is intended to fetch the result value. Row numbers start from 0.
The $field argument is either the name or the number of the field column from which it is intended to fetch the result value. Column numbers start from 0.
Referencing the result columns by name makes the application code look cleaner. Specifying the column by number runs faster. If you are sure about the order of the columns in the result set, you may want to pass column numbers. One possible compromise is to variable names for readability like this:
$name=0;
$email=1;
$name=MetabaseFetchResult($database, $result,0, $name);
$email=MetabaseFetchResult($database, $result,0, $email);
However, do not use column numbers when you run queries like SELECT * FROM some_table because the order that the columns may come is not guaranteed to be always the same, specially after making table alterations.
The $value result value is a text string that with the value as it was returned by the DBMS. Text or integer field result may be used as they are returned, but fields of other types need to be retrieved using data type specific result fetching functions.
$value=MetabaseFetchBooleanResult($database, $result, $row, $field)
Fetch a boolean value of a query result located in given row and field.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $row argument is the number of the row from which it is intended to fetch the result boolean value. Row numbers start from 0.
The $field argument is either the name or the number of the field column from which it is intended to fetch the result boolean value. Column numbers start from 0.
The $value result value is a text string with the fetched boolean value.
$value=MetabaseFetchDecimalResult($database, $result, $row, $field)
Fetch a decimal value of a query result located in given row and field.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $row argument is the number of the row from which it is intended to fetch the result boolean value. Row numbers start from 0.
The $field argument is either the name or the number of the field column from which it is intended to fetch the result boolean value. Column numbers start from 0.
The $value result value is a text string that with the fetched decimal value.
$value=MetabaseFetchFloatResult($database, $result, $row, $field)
Fetch a float value of a query result located in given row and field.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $row argument is the number of the row from which it is intended to fetch the result boolean value. Row numbers start from 0.
The $field argument is either the name or the number of the field column from which it is intended to fetch the result boolean value. Column numbers start from 0.
The $value result value is a text string with the fetched float.
$value=MetabaseFetchDateResult($database, $result, $row, $field)
Fetch a date value of a query result located in given row and field.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $row argument is the number of the row from which it is intended to fetch the result date value. Row numbers start from 0.
The $field argument is either the name or the number of the field column from which it is intended to fetch the result date value. Column numbers start from 0.
The $value result value is a text string with the date value converted to the normalized ISO format.
$value=MetabaseFetchTimeResult($database, $result, $row, $field)
Fetch a time value of a query result located in given row and field.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $row argument is the number of the row from which it is intended to fetch the result date value. Row numbers start from 0.
The $field argument is either the name or the number of the field column from which it is intended to fetch the result date value. Column numbers start from 0.
The $value result value is a text string with the time value converted to the normalized ISO format.
$value=MetabaseFetchTimestampResult($database, $result, $row, $field)
Fetch a time stamp value of a query result located in given row and field.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $row argument is the number of the row from which it is intended to fetch the result time stamp value. Row numbers start from 0.
The $field argument is either the name or the number of the field column from which it is intended to fetch the result time stamp value. Column numbers start from 0.
The $value result value is a text string with the time stamp value converted to the normalized ISO format.
$lob_handle=MetabaseFetchCLOBResult($database, $result, $row, $field)
Fetch the value of a handle to read character large object of a query result located in given row and field.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $row argument is the number of the row from which it is intended to fetch the result value. Row numbers start from 0.
The $field argument is either the name or the number of the field column from which it is intended to fetch the result value. Column numbers start from 0.
The $lob_handle return value is a integer handle that has to be used when retrieving the contents of the specified large object field using the function MetabaseReadLOB. Each application is responsible for freeing the resources associated with the retrieved large object by calling the function MetabaseFreeLOB. If this return value is 0 it means that there was an error.
$lob_handle=MetabaseFetchBLOBResult($database, $result, $row, $field)
Fetch the value of a handle to read binary large object of a query result located in given row and field.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $row argument is the number of the row from which it is intended to fetch the result value. Row numbers start from 0.
The $field argument is either the name or the number of the field column from which it is intended to fetch the result value. Column numbers start from 0.
The $lob_handle return value is a integer handle that has to be used when retrieving the contents of the specified large object field using the function MetabaseReadLOB. Each application is responsible for freeing the resources associated with the retrieved large object by calling the function MetabaseFreeLOB. If this return value is 0 it means that there was an error.
$end_of_result=MetabaseEndOfResult($database, $result)
Determine whether the highest numbered row that was fetched with MetabaseFetchResult like functions is the last row available in the given result set. If no rows have been fetched yet, this function determines whether the result set contains any rows.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $end_of_result return value indicates whether there are any rows after the highest numbered row that was fetched from the given result set. If the return value is 0 it means that there is at least one row to be fetched. If the return value is 1 it means that there no more rows to be fetched. If the return value is -1 it means that there was an error.
$rows=MetabaseNumberOfRows($database, $result)
Count the number of rows returned by the DBMS in a query result. If the query was executed with the range of result rows restricted by the function MetabaseSetSelectedRowRange, the value returned by the MetabaseNumberOfRows function never exceeds the range $limit value. The number of rows may be 0 if the $first row range value is higher than the the number of rows that the query would return when it is not restricted to a given range.
For performance reasons, avoid using this function with queries that return many rows. Some DBMS do not provide a means to obtain the total number of rows contained in a result set. The reason for this is that such DBMS start returning result data as soon as it is found on the database. So, the total number of rows can not be anticipated when the DBMS starts returning result data.
The drivers for these DBMS have to count the number of rows by fetching the whole result data into memory. Since the applications may still need to fetch the result rows, the data retrieved from the DBMS still has to be hold in memory until the result is freed. Therefore, using this function may also demand a great amount of memory.
Alternatively, if you really need to know in advance the number of rows that a query will return, you should first run a query that use the SQL COUNT() function.
If you just need to know whether all rows in the given result set have been fetched, use the MetabaseEndOfResult instead.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
$columns=MetabaseNumberOfColumns($database, $result)
Count the number of columns returned by the DBMS in a query result.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $columns return value indicates the number of columns returned in the specified result set. Some DBMS may not return any columns when the result set does not contain any rows. If it is specified an invalid result set, the function will return -1.
$success=MetabaseGetColumnNames($database, $result, $column_names)
Retrieve the names of of columns returned by the DBMS in a query result.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $column_names argument is a reference to an associative array variable that will hold the names of columns. The indexes of the array are the column names mapped to lower case and the values are the respective numbers of the columns starting from 0. Some DBMS may not return any columns when the result set does not contain any rows.
The $success return value indicates whether the function was able to successfully retrieve the column names associated to the given query result handle. This return value may be safely ignored if the $result argument corresponds to a valid result handle.
$success=MetabaseFreeResult($database, $result)
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $success return value indicates whether the function was able to successfully free the resources associated to the given query result handle. This return value may be safely ignored if the $result argument corresponds to a valid result handle that was not already freed.
The data of large objects table fields should not be stored or retrieved all at once because that may require a large amount of memory. The alternative is to retrieve or store data splitting it in blocks of limited length.
Metabase provides a set of special purpose classes that are meant to manage the access to blocks of data to be stored or retrieved in large object fields. The following functions should be used to create and access objects of such classes.
Storing data in large object fields should be done by executing INSERT or UPDATE prepared queries. The functions MetabaseSetQueryCLOB and MetabaseSetQueryBLOB should be used to specify the large object input stream objects from which the data to be stored will be read when the prepared queries are executed. The function MetabaseCreateLOB should be used to create input stream objects that define how to read the data to be stored in large object fields.
Data already stored in large object fields should be retrieved by executing SELECT queries. The functions MetabaseFetchCLOBResult and MetabaseFetchBLOBResult should be used to retrieve the data of the selected large object fields. These functions return large object handle values that should be used to subsequently read the data using the function MetabaseReadLOB.
$success=MetabaseCreateLOB(&$arguments, &$lob)
Create a handler object of a specified class with functions to retrieve data from a large object data stream.
The $arguments argument is reference to an associative array with parameters to create the handler object. The array indexes are the names of the parameters and the array values are the respective parameter values.
Some parameters are specific of the class of each type of handler object that is created. The following parameters are common to all handler object classes:
Name of the type of the built-in supported class that will be used to create the handler object. There are currently four built-in types of handler object classes: data, resultlob, inputfile and outputfile.
Name of the class of the handler object that will be created if the Type argument is not specified. This argument should be used when you need to specify a custom handler class.
Database connection handler as returned by the MetabaseSetupDatabase. This is an option argument needed by some handler classes like resultlob.
Optional error entry that will hold the error message returned by this function when it fails.
The following argument is specific of the data handler class:
String of data that will be returned by the class when it requested with the MetabaseReadLOB function.
The following argument is specific of the resultlob handler class:
Integer handle value of a large object result row field.
The following arguments are specific of the inputfile handler class:
Integer handle value of a file already opened for reading.
Name of a file to be opened for reading if the File argument is not specified.
The following arguments are specific of the outputfile handler class:
Integer handle value of a file already opened for writing.
Name of a file to be opened for writing if the File argument is not specified.
Integer value that specifies the length of a buffer that will be used to read from the specified large object.
Integer handle value that specifies a large object from which the data to be stored in the output file will be written.
Integer handle value as returned by the function MetabaseQuery or MetabaseExecuteQuery that specifies the result set that contains the large object value to be retrieved. If the LOB argument is specified, this argument is ignored.
Integer value that specifies the number of the row of the result set that contains the large object value to be retrieved. If the LOB argument is specified, this argument is ignored.
Integer or string value that specifies the number or the name of the column of the result set that contains the large object value to be retrieved. If the LOB argument is specified, this argument is ignored.
Boolean value that specifies whether the large object column to be retrieved is of binary type (blob) or otherwise is of character type (clob). If the LOB argument is specified, this argument is ignored.
The $lob argument is a reference to a variable that will hold an integer handle value that should be passed as argument in subsequent calls to functions that retrieve data from the large object input stream.
The $success return value indicates whether the function was able to successfully create the large object handler object.
$read_length=MetabaseReadLOB($lob, &$data, $length)
Read data from large object input stream.
The $lob argument is a integer handle value that is returned by the MetabaseCreateLOB function.
The $data argument is a reference to a variable that will hold data to be read from the large object input stream.
The $length argument is a integer value that indicates the largest amount of data to be read from the large object input stream.
The $read_length return value indicates the effective number of bytes read from the large object input stream.
If this function succeeded, the return value may range from 0 up to the value of the $length argument. Use the function MetabaseEndOfLOB to determine if there is still more data to be retrieved.
If this function fails, the return value is -1. Use the function MetabaseLOBError to retrieve the associated error message.
$end_of_lob=MetabaseEndLOB($lob)
Determine whether it was reached the end of the large object and therefore there is no more data to be read for the its input stream.
The $lob argument is a integer handle value that is returned by the MetabaseCreateLOB function.
The $end_of_lob return value is a boolean flag that indicates whether it was reached the end of the large object input stream.
MetabaseDestroyLOB($lob)
Free any resources allocated during the lifetime of the large object handler object.
The $lob argument is a integer handle value that is returned by the MetabaseCreateLOB function.
$error=MetabaseLOBError($lob)
Retrieve the error message text associated with the last operation on the large object input stream that failed.
The $lob argument is a integer handle value that is returned by the MetabaseCreateLOB function.
The $error return value is the error message string.
$end_of_lob=MetabaseEndLOB($lob)
Determine whether it was reached the end of the large object and therefore there is no more data to be read for the its input stream.
The $lob argument is a integer handle value that is returned by the MetabaseCreateLOB function.
The $end_of_lob return value is a boolean flag that indicates whether it was reached the end of the large object input stream.
MetabaseDestroyLOB($lob)
Free any resources allocated during the lifetime of the large object handler object.
The $lob argument is a integer handle value that is returned by the MetabaseCreateLOB function.
The most common use of database applications is to retrieve information that is stored table rows. The process to retrieve data from a database is repetitive: execute a query, iterate over the result set and free it.
To simplify this repetitive process, Metabase provides an additional set of functions that is meant to reduce the number of calls to the API when retrieving result set data in the most common ways.
There are two main types of functions that are provided for retrieving data in bulk with a single call. The simplest type of functions just retrieves the data into a given variable from the result set of an already executed query and then frees the result set.
The second type of functions wraps around the first functions and besides that also executes a given immediate query before retrieving the result set data.
The first type of functions is more appropriate to use with prepared queries because the second type of functions performs the same actions and also execute immediate queries.
These types of functions retrieve data in four different manners:
Besides these main types of functions there is also a function that fetches data from a given row of a result set and another function that is meant to define the data types that should be associated to each result set column, so that the necessary datatype conversions are already performed before returning data.
$success=MetabaseSetResultTypes($database, $result, &$types)
Define the list of types to be associated with the columns of a given result set.
This function may be called before invoking MetabaseFetchResultArray, MetabaseFetchResultField, MetabaseFetchResultRow, MetabaseFetchResultColumn and MetabaseFetchResultAll so that the necessary data type conversions are performed on the data to be retrieved by them. If this function is not called, the type of all result set columns is assumed to be text, thus leading to not perform any conversions.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $types argument is a reference to an array variable that lists the data types to be expected in the result set columns. If this array contains less types than the number of columns that are returned in the result set, the remaining columns are assumed to be of the type text. Currently, the types clob and blob are not fully supported.
The $success return value indicates whether the function call succeeded in associating the types list to the given result set. This return value may be safely ignored if the function arguments are correctly defined.
$success=MetabaseFetchResultArray($database, $result, &$array, $row)
Fetch a specified result set row into a given array variable.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions.
The $array argument is a reference to an array variable into which the specified result set row values are fetched. The array positions are filled according to the position of the result set columns starting from 0. Columns with NULL values are not assigned.
The $row argument is the number of the row from which it is intended to fetch the result set row. Row numbers start from 0.
The $success return value indicates whether the function call succeeded in retrieving the given row result set data.
$success=MetabaseFetchResultField($database, $result, &$field)
Fetch the value from the first column of the first row of the specified result set into a given variable and then frees the result set.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions. The specified result set is always freed even if retrieving the field value fails for any reason. This function assumes that no data was already retrieved from the result set or else it may fail stating the result set is empty.
The $field argument is a reference to a variable into which the specified result set field is fetched. If it is NULL this variable is unset.
The $success return value indicates whether the function call succeeded in retrieving the result set field value.
$success=MetabaseFetchResultRow($database, $result, &$row)
Fetch the first row of the specified result set row into a given array variable and then frees the result set.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions. The specified result set is always freed even if retrieving the row values fails for any reason. This function assumes that no data was already retrieved from the result set or else it may fail stating the result set is empty.
The $row argument is a reference to an array variable into which the specified result set row values are fetched. The array positions are filled according to the position of the result set columns starting from 0. Columns with NULL values are not assigned.
The $success return value indicates whether the function call succeeded in retrieving the result set row data.
$success=MetabaseFetchResultColumn($database, $result, &$column)
Fetch the value from the first column of all the rows of the specified result set into a given array variable and then frees the result set.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions. The specified result set is always freed even if retrieving the column values fails for any reason. This function assumes that no data was already retrieved from the result set or else it may not return any data.
The $column argument is a reference to an array variable into which the specified result set column is fetched. The rows on which the first column is NULL are ignored. Therefore, do not rely on the count of entries of the array variable to assume that it is the number of rows in the result set.
The $success return value indicates whether the function call succeeded in retrieving the result set column data.
$success=MetabaseFetchResultAll($database, $result, &$all)
Fetch all rows of the specified result set row into a given two dimension array variable and then frees the result set.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $result argument is a handle for the query result set that was returned by either the MetabaseQuery or MetabaseExecuteQuery functions. The specified result set is always freed even if retrieving the its values fails for any reason. This function assumes that no data was already retrieved from the result set or else it may not return any data.
The $all argument is a reference to a two dimension array variable into which the specified result set rows and column values are fetched. The array positions are filled according to the position of the result set columns and rows starting from 0. Columns with NULL values are not assigned.
The $success return value indicates whether the function call succeeded in retrieving the result set row data.
$success=MetabaseQueryField($database, $query , &$field, $type)
Execute the specified query, fetch the value from the first column of the first row of the result set into a given variable and then frees the result set.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $query argument is the SELECT query statement to be executed.
The $field argument is a reference to a variable into which the result set field is fetched. If it is NULL this variable is unset.
$type is an optional argument that specifies the expected datatype of the result set field, so that an eventual conversion may be performed. The default datatype is text, meaning that no conversion is performed.
The $success return value indicates whether the function call succeeded in executing the query and retrieving the result set field value.
$success=MetabaseQueryRow($database, $query, &$row, $types)
Execute the specified query, fetch the first row of the result set row into a given array variable and then frees the result set.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $query argument is the SELECT query statement to be executed.
The $row argument is a reference to an array variable into which the result set row values are fetched. The array positions are filled according to the position of the result set columns starting from 0. Columns with NULL values are not assigned.
$types is an optional array argument that specifies a list of expected datatypes of the result set columns, so that the eventual conversions may be performed. The default list of datatypes is empty, meaning that no conversion is performed.
The $success return value indicates whether the function call succeeded in executing the query and retrieving the result set row data.
$success=MetabaseQueryColumn($database, $query, &$column, $type)
Execute the specified query, fetch the value from the first column of all the rows of the result set into a given array variable and then frees the result set.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $query argument is the SELECT query statement to be executed.
The $column argument is a reference to an array variable into which the result set column is fetched. The rows on which the first column is NULL are ignored. Therefore, do not rely on the count of entries of the array variable to assume that it is the number of rows in the result set.
$type is an optional argument that specifies the expected datatype of the result set column, so that an eventual conversion may be performed. The default datatype is text, meaning that no conversion is performed.
The $success return value indicates whether the function call succeeded in executing the query and retrieving the result set column data.
$success=MetabaseQueryAll($database, $query, &$all, $types)
Execute the specified query, fetch all rows of the result set row into a given two dimension array variable and then frees the result set.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $query argument is the SELECT query statement to be executed.
The $all argument is a reference to a two dimension array variable into which the result set rows and column values are fetched. The array positions are filled according to the position of the result set columns and rows starting from 0. Columns with NULL values are not assigned.
$types is an optional array argument that specifies a list of expected datatypes of the result set columns, so that the eventual conversions may be performed. The default list of datatypes is empty, meaning that no conversion is performed.
The $success return value indicates whether the function call succeeded in executing the query and retrieving the result set data.
A transaction is a set of operations that are executed atomically by the DBMS. The operations that are executed during a transaction by a database user are not affected by other operations being executed at the same time during the access of other users.
In practice this means that the DBMS behaves as if the database is being accessed by one user at a time while a transaction takes place. The DBMS may set implicit locks that prevent simultaneous accesses to affects the same data.
 The commit command tells the DBMS to make definite the changes done on the database. The rollback command tells the DBMS to cancel the changes done on the database since the transaction begun.
If for some reason a transaction is not completed before a database connection is terminated either ending the application normally or abnormally, the DBMS execute an implicit rollback operation. This includes situations when the application is terminated by the operating system or the computer where the application or the DBMS is running is shutdown by a power interruption.
An application script may be terminated without ending a transaction. The function MetabaseCloseSetup ends an uncommitted transaction that may be in progress when it is called, but if a script exits without calling this function, for instance due to a syntax error, the transaction could be left pending until the application process ends.
Since Metabase supports persistent database connections, there would be a chance that an uncommitted transaction be left pending until the Web server process is terminated, therefore holding implicit database access locks.
To prevent this situation, Metabase implicitly registers a shutdown handler function with PHP that assures that any pending transactions are canceled before the script exits.
Not all DBMS support transactions. Use the function MetabaseSupport to determine whether the DBMS driver support transactions.
$success=MetabaseAutoCommitTransactions($database, $auto_commit)
Define whether database changes done on the database be automatically committed. This function may also implicitly start or end a transaction.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $auto_commit argument is a boolean flag that indicates whether the database changes should be committed right after executing every query statement. If this argument is 0 a transaction implicitly started. Otherwise, if a transaction is in progress it is ended by committing any database changes that were pending.
The $success return value indicates whether the function call succeeded in either starting a transaction or committing any changes and ending a transaction in progress.
$success=MetabaseCommitTransaction($database)
Commit the database changes done during a transaction that is in progress. This function may only be called when auto-committing is disabled, otherwise it will fail. Therefore, a new transaction is implicitly started after committing the pending changes.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $success return value indicates whether the function call succeeded in committing the database changes and starting a new transaction.
$success=MetabaseRollbackTransaction($database)
Cancel any database changes done during a transaction that is in progress. This function may only be called when auto-committing is disabled, otherwise it will fail. Therefore, a new transaction is implicitly started after canceling the pending changes.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $success return value indicates whether the function call succeeded in canceling the database changes and starting a new transaction.
$success=MetabaseCreateDatabase($database, $name)
Create a database space within which may be created database objects like tables, indexes and sequences. The implementation of this function is highly DBMS specific and may require special permissions to run successfully. Consult the documentation or the DBMS drivers that you use to be aware of eventual configuration requirements.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name of the database that is intended to be created.
The $success return value indicates whether the function call succeeded in creating the new database.
$success=MetabaseDropDatabase($database, $name)
Drop an existing database space. The implementation of this function is highly DBMS specific and may require special permissions to run successfully. Consult the documentation or the DBMS drivers that you use to be aware of eventual configuration requirements.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name of the database that is intended to be dropped.
The $success return value indicates whether the function call succeeded in dropping the specified database.
$declaration=MetabaseGetTextFieldTypeDeclaration($database, $name, &$field)
Obtain DBMS specific SQL code portion needed to declare a text type field to be used in statements like CREATE TABLE.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name the field to be declared.
The $field argument is an associative array with the name of the properties of the field being declared as array indexes. Currently, the types of supported field properties are as follows:
Integer value that determines the maximum length of the text field. If this argument is missing the field should be declared to have the longest length allowed by the DBMS.
Text value to be used as default for this field.
Boolean flag that indicates whether this field is constrained to not be set to NULL.
The $declaration return value is the DBMS specific SQL code portion that should be used to declare the specified field.
$declaration=MetabaseGetBooleanFieldTypeDeclaration($database, $name, &$field)
Obtain DBMS specific SQL code portion needed to declare a boolean type field to be used in statements like CREATE TABLE.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name the field to be declared.
The $field argument is an associative array with the name of the properties of the field being declared as array indexes. Currently, the types of supported field properties are as follows:
Boolean value to be used as default for this field.
Boolean flag that indicates whether this field is constrained to not be set to NULL.
The $declaration return value is the DBMS specific SQL code portion that should be used to declare the specified field.
$declaration=MetabaseGetIntegerFieldTypeDeclaration($database, $name, &$field)
Obtain DBMS specific SQL code portion needed to declare an integer type field to be used in statements like CREATE TABLE.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name the field to be declared.
The $field argument is an associative array with the name of the properties of the field being declared as array indexes. Currently, the types of supported field properties are as follows:
Boolean flag that indicates whether the field should be declared as unsigned integer if possible.
Integer value to be used as default for this field.
Boolean flag that indicates whether this field is constrained to not be set to NULL.
The $declaration return value is the DBMS specific SQL code portion that should be used to declare the specified field.
$declaration=MetabaseGetDecimalFieldTypeDeclaration($database, $name, &$field)
Obtain DBMS specific SQL code portion needed to declare a decimal type field to be used in statements like CREATE TABLE.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name the field to be declared.
The $field argument is an associative array with the name of the properties of the field being declared as array indexes. Currently, the types of supported field properties are as follows:
Integer value to be used as default for this field.
Boolean flag that indicates whether this field is constrained to not be set to NULL.
The $declaration return value is the DBMS specific SQL code portion that should be used to declare the specified field.
$declaration=MetabaseGetFloatFieldTypeDeclaration($database, $name, &$field)
Obtain DBMS specific SQL code portion needed to declare a float type field to be used in statements like CREATE TABLE.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name the field to be declared.
The $field argument is an associative array with the name of the properties of the field being declared as array indexes. Currently, the types of supported field properties are as follows:
Integer value to be used as default for this field.
Boolean flag that indicates whether this field is constrained to not be set to NULL.
The $declaration return value is the DBMS specific SQL code portion that should be used to declare the specified field.
$declaration=MetabaseGetDateFieldTypeDeclaration($database, $name, &$field)
Obtain DBMS specific SQL code portion needed to declare a date type field to be used in statements like CREATE TABLE.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name the field to be declared.
The $field argument is an associative array with the name of the properties of the field being declared as array indexes. Currently, the types of supported field properties are as follows:
Date value to be used as default for this field.
Boolean flag that indicates whether this field is constrained to not be set to NULL.
The $declaration return value is the DBMS specific SQL code portion that should be used to declare the specified field.
$declaration=MetabaseGetTimeFieldTypeDeclaration($database, $name, &$field)
Obtain DBMS specific SQL code portion needed to declare an time type field to be used in statements like CREATE TABLE.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name the field to be declared.
The $field argument is an associative array with the name of the properties of the field being declared as array indexes. Currently, the types of supported field properties are as follows:
Date value to be used as default for this field.
Boolean flag that indicates whether this field is constrained to not be set to NULL.
The $declaration return value is the DBMS specific SQL code portion that should be used to declare the specified field.
$declaration=MetabaseGetTimestampFieldTypeDeclaration($database, $name, &$field)
Obtain DBMS specific SQL code portion needed to declare an time stamp type field to be used in statements like CREATE TABLE.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name the field to be declared.
The $field argument is an associative array with the name of the properties of the field being declared as array indexes. Currently, the types of supported field properties are as follows:
Time stamp value to be used as default for this field.
Boolean flag that indicates whether this field is constrained to not be set to NULL.
The $declaration return value is the DBMS specific SQL code portion that should be used to declare the specified field.
$declaration=MetabaseGetCLOBFieldTypeDeclaration($database, $name, &$field)
Obtain DBMS specific SQL code portion needed to declare a character large object type field to be used in statements like CREATE TABLE.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name the field to be declared.
The $field argument is an associative array with the name of the properties of the field being declared as array indexes. Currently, the types of supported field properties are as follows:
Integer value that determines the maximum length of the large object field. If this argument is missing the field should be declared to have the longest length allowed by the DBMS.
Boolean flag that indicates whether this field is constrained to not be set to NULL.
The $declaration return value is the DBMS specific SQL code portion that should be used to declare the specified field.
$declaration=MetabaseGetBLOBFieldTypeDeclaration($database, $name, &$field)
Obtain DBMS specific SQL code portion needed to declare a binary large object type field to be used in statements like CREATE TABLE.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name the field to be declared.
The $field argument is an associative array with the name of the properties of the field being declared as array indexes. Currently, the types of supported field properties are as follows:
Integer value that determines the maximum length of the large object field. If this argument is missing the field should be declared to have the longest length allowed by the DBMS.
Boolean flag that indicates whether this field is constrained to not be set to NULL.
The $declaration return value is the DBMS specific SQL code portion that should be used to declare the specified field.
$success=MetabaseCreateTable($database, $name, $fields)
Create a new database table. This function is deprecated in favour of the MetabaseCreateDetailedTable function, as this allows the creation of more complex tables, for instance with primary keys and auto-incremented key fields.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name of the table that is intended to be created.
The $fields argument is an associative array that contains the definition of each field of the new table. The indexes of the array entries are the names of the fields of the table an the array entry values are associative arrays like those that are meant to be passed with the field definitions to MetabaseGetTypeFieldTypeDeclaration functions.
The $success return value indicates whether the function call succeeded in creating the new table.
$table_name="users";
$success=MetabaseCreateTable($database, "users", array(
"id"=>array(
"type"=>"integer",
"unsigned"=>1
"notnull"=>1
"default"=>0
),
"name"=>array(
"type"=>"text",
"length"=>12
),
"password"=>array(
"type"=>"text",
"length"=>12
)
));
if(!$success)
{
echo "Error while creating the database table "users": ",MetabaseError($database);
exit;
}
$success=MetabaseCreateDetailedTable($database, $table, $check)
Create a new database table that may include other details besides the fields list, such as the table primary key. This function may optionally simulate the creation of a table to determine whether the current DBMS driver class is capable of creating a table with the given definition.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $table argument is a reference to an associative array variable that specifies the definition of the table that is intended to be created.
Here follow the list of entries of the definition associative array:
The name argument entry is the name of the table that is intended to be created.
The FIELDS argument entry is an associative array that contains the definition of each field of the new table. This is the same as the $fields parameter of the MetabaseCreateTable function.
The PRIMARYKEY argument entry is an associative array that contains the parameters of the key. It must have an entry named FIELDS that is another associative array that lists fields that compose the table primary key, just like the FIELDS definition for the MetabaseCreateIndex function.
The $check argument is a boolean flag value that determines whether the function should simulate the creation of the table or create it for real. If this argument is 1, this function will not create the table but may fail in case the DBMS driver class would not be able to create a table with the requested definition.
The $success return value indicates whether the function call succeeded in creating the new table, or whether the function could create a table with the requested definition if the $check variable is 1.
$table=array( "name"=>"users", "FIELDS"=>array( "id"=>array( "type"=>"integer", "autoincrement"=>1 ), "name"=>array( "type"=>"text", "length"=>12 ), "password"=>array( "type"=>"text", "length"=>12 ) ), "PRIMARYKEY"=>array( "FIELDS"=>array( "id"=>array("sorting"=>"ascending") ) ) ); $success=MetabaseCreateDetailedTable($database, $table, 0); if(!$success) { echo "Error while creating the database table 'users': ",MetabaseError($database),"\n" exit; }
$success=MetabaseDropTable($database, $name)
Drop an existing database table. This function is deprecated in favour of the MetabaseDropDetailedTable function, as this allows dropping more complex tables, for instance with primary keys and auto-incremented key fields.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name of the table that is intended to be dropped.
The $success return value indicates whether the function call succeeded in dropping the specified table.
$success=MetabaseDropDetailedTable($database, $table, $check)
Drop an existing database table that may include other details besides the fields list, such as the table primary key. This function may optionally simulate dropping the table to determine whether the current DBMS driver class is capable of dropping table with the given definition.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $table argument is reference to an associative array variable that specifies the definition of the table that is intended to be dropped, just like the $table argument of the function MetabaseCreateDetailedTable.
The $check argument is a boolean flag value that determines whether the function should simulate dropping the table or drop it for real. If this argument is 1, this function will not drop the table but may fail in case the DBMS driver class would not be able to drop a table with the given definition.
The $success return value indicates whether the function call succeeded in dropping the new table, or whether the function could drop a table with the given definition if the $check variable is 1.
$success=MetabaseAlterTable($database, $name, $changes, $check)
Perform a list of changes in a database table. This function is also able to check if the DBMS driver is able to perform the listed changes without actually making them.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name of the table that is intended to be changed.
The $changes argument is an associative array that contains the details of each type of change that is intended to be performed. The types of changes that are currently supported are defined as follows:
New name for the table.
Associative array with the names of fields to be added as indexes of the array. The value of each entry of the array should be set to another associative array with the properties of the fields to be added. The properties of the fields should be the same as defined by the Metabase parser.
Additionally, there should be an entry named Declaration that is expected to contain the portion of the field declaration already in DBMS specific SQL code as it is used in the CREATE TABLE statement.
Associative array with the names of fields to be removed as indexes of the array. Currently the values assigned to each entry are ignored. An empty array should be used for future compatibility.
Associative array with the names of fields to be renamed as indexes of the array. The value of each entry of the array should be set to another associative array with the entry named name with the new field name and the entry named Declaration that is expected to contain the portion of the field declaration already in DBMS specific SQL code as it is used in the CREATE TABLE statement.
Associative array with the names of the fields to be changed as indexes of the array. Keep in mind that if it is intended to change either the name of a field and any other properties, the ChangedFields array entries should have the new names of the fields as array indexes.
The value of each entry of the array should be set to another associative array with the properties of the fields to that are meant to be changed as array entries. These entries should be assigned to the new values of the respective properties. The properties of the fields should be the same as defined by the Metabase parser.
If the default property is meant to be added, removed or changed, there should also be an entry with index ChangedDefault assigned to 1. Similarly, if the notnull constraint is to be added or removed, there should also be an entry with index ChangedNotNull assigned to 1.
Additionally, there should be an entry named Declaration that is expected to contain the portion of the field changed declaration already in DBMS specific SQL code as it is used in the CREATE TABLE statement.
The $check argument is a boolean flag value that indicates whether the function should just check if the DBMS driver can perform the requested table alterations if the value is 1 or actually perform them otherwise.
The $success return value indicates whether the DBMS driver is able to attempt to perform the requested changes if the $check argument is 1 or if the function call succeeded in altering the table otherwise.
$table_name="users";
$success=MetabaseAlterTable($database, $name, array(
"name"=>"userlist",
"AddedFields"=>array(
"quota"=>array(
"type"=>"integer",
"unsigned"=>1
"Declaration"=>"quota INT"
)
),
"RemovedFields"=>array(
"file_limit"=>array(),
"time_limit"=>array()
),
"ChangedFields"=>array(
"gender"=>array(
"default"=>"M",
"ChangeDefault"=>1,
"Declaration"=>"gender CHAR(1) DEFAULT 'M'"
)
),
"RenamedFields"=>array(
"sex"=>array(
"name"=>"gender",
"Declaration"=>"gender CHAR(1) DEFAULT 'M'"
)
)
), 0);
if(!$success)
{
echo "Error while altering the database table $table_name: ",MetabaseError($database);
exit;
}
Sequences are database objects that store integer numbers. A sequence value is automatically incremented when it is accessed. Sequences are useful for applications that need to have table fields on which each row has to have a unique value.
For this purpose, auto-incremented key fields are more recommended. However, not all Metabase drivers support auto-incremented fields.
Not all DBMS support database sequence objects either. However, for those DBMS that do not support sequences, if possible, some DBMS drivers emulate them by creating separate tables with fields with the AUTO_INCREMENT property. Use the function MetabaseSupport to determine whether the DBMS driver support either database sequence objects or auto-incremented key fields.
$success=MetabaseCreateSequence($database, $name, $start)
Create a database sequence object.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name of the sequence to be created.
The $start argument is the initial integer value of the sequence to be created. Usually, this value is set to 1.
The $success return value indicates whether the function call succeeded in creating the database sequence object.
$support=MetabaseDropSequence($database, $name)
Drop an existing database sequence object.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name of the sequence to be dropped.
The $success return value indicates whether the function call succeeded in dropping the database sequence object.
$success=MetabaseGetSequenceNextValue($database, $name, &$value)
Get the next value of a sequence and increment its current value. Both operation are execute atomically by the DBMS. If more than one application attempts to access simultaneously to a sequence, it is assured that each one will get a different value without corrupting the sequence.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name of the sequence to be accessed.
The $value argument is a reference to an integer variable on which it will be stored the next sequence value.
The $success return value indicates whether the function call succeeded in accessing to the sequence next value.
$success=MetabaseGetSequenceCurrentValue($database, $name, &$value)
Get the current value of a sequence. The value returned by this function may not be useful if there may be multiple applications simultaneously accessing the specified sequence.
Not all DBMS drivers that support sequences are able to determine the current value of the sequences. Use the function MetabaseSupport to determine whether the DBMS driver has this ability.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $name argument is the name of the sequence to be accessed.
The $value argument is a reference to an integer variable on which it will be stored the current sequence value.
The $success return value indicates whether the function call succeeded in accessing to the sequence current value.
Indexes are database objects intended make more efficient the searches for information in tables. Although it does not require any changes in the way the information is accessed, the use of indexes may drastically reduce the amount of time that is necessary to look for information specified in the query statements search clause.
An index is created by specifying a set of one or more fields of a table. The fields to be picked for the index creation should be the same that are used in the search clause of each query that is intended to be optimized.
The decision to create indexes for each table should not be made lightly. While using indexes may reduce the time that a query on a table may take, it also increases the time that takes to insert new rows on that table, as that causes all its indexes to be regenerated. Also, each new index requires more disk space and memory to be managed. Therefore, database designers have to balance these aspects before deciding which indexes are worthy to be created.
Not all DBMS support indexes, despite most do. Use the function MetabaseSupport to determine whether the DBMS driver in use can manage indexes.
$success=MetabaseCreateIndex($database, $table, $name, $definition)
Create a new table index.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $table argument is the name of the table on which the index is to be created.
The $name argument is the name of the index to be created.
The $definition argument is an associative array that defines properties of the index to be created. It must defined one property named FIELDS that is an associative with the names of the index fields as array indexes. Each entry of this array is set to another type of associative array that specifies properties of the index that are specific to each field.
Currently, only the sorting property is supported. It should be used to define the sorting direction of the index. It may be set to either ascending or descending.
Not all DBMS support index sorting direction configuration. The DBMS drivers of those that do not support it ignore this property. Use the function MetabaseSupport to determine whether the DBMS driver can manage indexes.
The index definition argument may contain also a boolean property named unique. When it is set to 1 it specifies that the combination of the values of the index fields on each row of the table should unique.
The $success return value indicates whether the function call succeeded in creating the specified index.
$table_name="users";
$success=MetabaseCreateIndex($database, $table_name, "users_index", array(
"FIELDS"=>array(
"user_name"=>array(
"sorting"=>"ascending"
),
"last_login"=>array()
)
));
if(!$success)
{
echo "Error creating a database index for table $table_name: ",MetabaseError($database);
exit;
}
$success=MetabaseDropIndex($database, $table, $name)
Drop an existing table index.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $table argument is the name of the table on which the index was created.
The $name argument is the name of the index to be dropped.
The $success return value indicates whether the function call succeeded in dropping the specified index.
Despite Metabase provides schema creation and maintenance support functions, the Metabase API functions can also access databases that were created by some other process. However, it is recommended that you install database schemas with Metabase manager to use with your database applications based on Metabase API.
If you have a database application that you want to migrate to start using Metabase, there is a set of functions that you can use to migrate your database schemas by performing reverse engineering.
Keep in mind that the current schema reverse engineering support is experimental. This means that what is provided is not a complete solution. You may need to correct manually the reverse engineered schemas generated by Metabase. Depending on some reverse engineering decisions, any data that may already exist in the legacy database may need to be converted. However, there is not yet support to migrate data from a database with a reverse engineered schema. Future versions of Metabase API will address these limitations.
Metabase API only provides functions that are needed to retrieve schema information for the reverse engineering process. The actual assembly of the information to produce a reverse engineered schema is made by the GetDefinitionFromDatabase of the database manager class.
$success=MetabaseListTables($database, $tables)
Retrieve the list of tables in the current database.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $tables argument is a reference to an array variable in which it will be returned the list of database tables.
The $success return value indicates whether the function call succeeded in retrieving the database tables.
$success=MetabaseListTableFields($database, $table, $fields)
Retrieve the list of fields of a given table of the current database.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $table argument is the name of the table to list its fields.
The $fields argument is a reference to an array variable in which it will be returned the list of table fields.
The $success return value indicates whether the function call succeeded in retrieving the list of table fields.
$success=MetabaseGetTableFieldDefinition($database, $table, $field, $definition)
Retrieve the definition of a given table field of the database. This function performs a mapping between the field native definition and the database independent field definition used by Metabase.
Sometimes the field definition mapping may be ambiguous because the developer that created the original schema may have used some data types to emulate other data types. For instance, often it is used a text field of one character of length to emulate boolean fields.
In case of ambiguity, this function will return multiple definitions to describe all the possible field definitions mappings that may be valid. The first definition that is returned in $definition argument array is the one that seems to be more likely, so it can be used by applications that are only interested in the best guess definition.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $table argument is the name of the table of the field to get its definition.
The $field argument is the name of the table field to get its definition.
The $definition argument is a reference to an array variable in which it will be returned one or more possible definitions of the table field. Each entry of the array is an associative array that contains the definition of the properties of the field. The names and values of the properties in the definition array are the same defined in the schema description data structure for table fields.
The $success return value indicates whether the function call succeeded in retrieving the definition of the table field.
$success=MetabaseListTableKeys($database, $table, $primary, $keys)
Retrieve the list of keys of a given table of the current database.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $table argument is the name of the table to list its keys.
The $primary argument is boolean flag that determines whether it should be returned the table primary key or others.
The $keys argument is a reference to an array variable in which it will be returned the list of table keys.
The $success return value indicates whether the function call succeeded in retrieving the list of table keys.
$success=MetabaseGetTableKeyDefinition($database, $table, $key, $primary, $definition)
Retrieve the definition of a given table key of the database.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $table argument is the name of the table of the index to get its definition.
The $key argument is the name of the table key to get its definition.
The $primary argument is boolean flag that indicates whether the specified key is the table primary key or of other type.
The $definition argument is a reference to an array variable in which it will be returned the definition of the table key. The associative array contains the definition of the properties of the key. The names and values of the properties in the definition array are the same defined in the schema description data structure for table keys.
The $success return value indicates whether the function call succeeded in retrieving the table key definition.
$success=MetabaseListTableIndexes($database, $table, $indexes)
Retrieve the list of indexes of a given table of the current database.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $table argument is the name of the table to list its indexes.
The $indexes argument is a reference to an array variable in which it will be returned the list of table indexes.
The $success return value indicates whether the function call succeeded in retrieving the list of table indexes.
$success=MetabaseGetTableIndexDefinition($database, $table, $index, $definition)
Retrieve the definition of a given table index of the database.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $table argument is the name of the table of the index to get its definition.
The $index argument is the name of the table index to get its definition.
The $definition argument is a reference to an array variable in which it will be returned the definition of the table index. The associative array contains the definition of the properties of the index. The names and values of the properties in the definition array are the same defined in the schema description data structure for table indexes.
The $success return value indicates whether the function call succeeded in retrieving the table index definition.
$success=MetabaseListSequences($database, $sequences)
Retrieve the list of sequences of the current database.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $sequences argument is a reference to an array variable in which it will be returned the list of sequences.
The $success return value indicates whether the function call succeeded in retrieving the list of sequences.
$success=MetabaseGetSequenceDefinition($database, $sequence, $definition)
Retrieve the definition of a given sequence of the database.
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
The $sequence argument is the name of the sequence to get its definition.