- What is Metabase?
Metabase is a PHP package intended to provide DBMS (DataBase Management System) independent means to install, access and maintain SQL based databases.
- Development goals
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.
- Package structure
Metabase is divided in several parts:
- DBMS independent programming interface
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.
- DBMS specific drivers
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.
- DBMS specific schema manager driver extensions
Consists of a set of driver extension classes that implement functions that are called by the main driver class to implement schema management functions.
- Drivers conformance test suite
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.
- Schema description parser
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.
- Database manager
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.
- Credits
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.
- Text data type
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.
- Boolean data 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.
- Integer data 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.
- Decimal data type
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.
- Float data type
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.
- Date data type
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.
- Time data 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.
- Time stamp data 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.
- Large object (file) 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".
- Calling Metabase API
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.
- Calling the API global functions
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.
- Calling the driver object functions directly
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.
- Database interface setup
Synopsis
$error=MetabaseSetupDatabase($arguments, &$database)
Purpose
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.
Usage
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:
- Connection
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
- Type
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:
- ibase - Interbase
- ifx - Informix
- msql - Mini-SQL
- mssql - Microsoft SQL Server
- mysql - MySQL
- odbc - ODBC
- odbc-msaccess - Microsoft Access via ODBC
- oci - Oracle via Oracle Call Interface
- pgsql - PostgreSQL
- sqlite - SQLite
Default value: not specified
- Include (required if the argument Type is missing)
Name of the file of the DBMS driver class to be included if the Type argument is not specified.
Default value: not specified
- IncludedConstant (required if the argument Type is missing)
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
- ClassName (required if the argument Type is missing)
Name of the DBMS driver class. This argument is ignored if the Type argument is specified.
Default value: not specified
- IncludePath
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: ""
- User
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
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: ""
- Host
Address of the host on which the DBMS is running. This argument may be ignored by some DBMS drivers.
Default value: ""
- Database
Optional argument that can be used to specify a initial database name that is equivalent to use the MetabaseSetDatabase function.
Default value: none
- Persistent
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
- AllowNestedTransactions
Boolean option that indicates whether the it should be allowed to start a transaction when another transaction is already in progress.
Default value: 1
- DecimalPlaces
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
- Debug
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: ""
- Options
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
- LOBBufferLength
Integer option that indicates the length of the buffer that is used to read data to store in large object fields.
Default value: 8000
- LogLineBreak
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.
Example
$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!="")
{
}
MetabaseSetDatabase($database,"test");
$result=MetabaseQuery($database,"SELECT id,password FROM users WHERE alias='admin'");
Synopsis
$error=MetabaseSetupDatabaseObject($arguments, &$db)
Purpose
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.
Usage
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.
Example
$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!="")
{
}
$db->SetDatabase("test");
$result=$db->Query("SELECT id,password FROM users WHERE alias='admin'");
Synopsis
$error=MetabaseParseConnectionArguments($connection, $arguments)
Purpose
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.
Usage
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.
Synopsis
MetabaseCloseSetup($database)
Purpose
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.
Usage
The $database argument is a database access handle that was returned by the MetabaseSetupDatabase function.
Synopsis
$previous_name=MetabaseSetDatabase($database, $name)
Purpose
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.
Usage
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.
Synopsis
$error=MetabaseError($database)
Purpose
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.
Usage
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.
Synopsis
$previous_error_handler=MetabaseSetErrorHandler($database, $error_handler)
Purpose
Specify a function that is called when an error occurs.
Usage
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:
- Scope
String that indicates the scope of the driver object class within which the error occurred.
- Message
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.
Synopsis
$supported=MetabaseSupport($database, $feature)
Purpose
Determine if the DBMS driver associated with the given database access handle supports the specified feature.
Usage
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:
- AffectedRows
Ability to determine the number of table rows that were affected by the last INSERT, UPDATE or DELETE query.
- AutoIncrement
Ability to create and use tables auto-incremented key fields. Support for this feature implies supporting primary keys.
- Indexes
Ability to create indexes on table fields.
- IndexSorting
Ability to specify sorting direction on the creation of table indexes.
- GetSequenceCurrentValue
Ability to retrieve the current value of a sequence. Support for this feature implies supporting sequences.
- LOBs
Has Large OBject table fields to store character or binary data.
- OmitInsertKey
Ability to execute INSERT queries omiting the auto-increment key field.
- OrderByText
Ability to order the result of SELECT queries by text fields of unspecified length.
- PatternBuild
Ability to build patterns to match text expressions that may include wildcard characters that should be matched literally.
- PrimaryKey
Ability to create tables primary key fields.
- Replace
Ability to execute REPLACE queries using the function MetabaseReplace even if it is done by emulation.
- SelectRowRanges
Ability to restrict the range of result rows that are returned by the DBMS when executing a SELECT query.
- Sequences
Ability to create and retrieve sequential values.
- SummaryFunctions
Ability to use summary function on queries such as COUNT, MAX, MIN, AVG, etc..
- Transactions
Ability to start atomic multiple query transactions.
- Query execution
Synopsis
$result=MetabaseQuery($database, $query)
Purpose
Execute an arbitrary database query statement.
Usage
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.
Synopsis
$success=MetabaseReplace($database, $table, $fields)
Purpose
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.
Usage
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
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.
- Type
Name of the type of the field. Currently, all types Metabase are supported except for clob and blob.
Default: text
- Null
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
- Key
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.
- Data type conversion
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.
Synopsis
$converted=MetabaseGetTextFieldValue($database, $value)
Purpose
Convert a text value into a DBMS specific format that is suitable to compose query statements.
Usage
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.
Synopsis
$converted_value=MetabaseGetBooleanFieldValue($database, $value)
Purpose
Convert a boolean value into a DBMS specific format that is suitable to compose query statements.
Usage
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.
Synopsis
$converted_value=MetabaseGetDecimalFieldValue($database, $value)
Purpose
Convert a decimal value into a DBMS specific format that is suitable to compose query statements.
Usage
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.
Synopsis
$converted_value=MetabaseGetFloatFieldValue($database, $value)
Purpose
Convert a float value into a DBMS specific format that is suitable to compose query statements.
Usage
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.
Synopsis
$converted_value=MetabaseGetDateFieldValue($database, $value)
Purpose
Convert a date value into a DBMS specific format that is suitable to compose query statements.
Usage
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.
Synopsis
$converted_value=MetabaseGetTimeFieldValue($database, $value)
Purpose
Convert a time value into a DBMS specific format that is suitable to compose query statements.
Usage
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.
Synopsis
$converted_value=MetabaseGetTimestampFieldValue($database, $value)
Purpose
Convert a time stamp value into a DBMS specific format that is suitable to compose query statements.
Usage
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.
Synopsis
$timestamp=MetabaseNow()
Purpose
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.
Usage
The $timestamp return value is a text string that represents the current date and time.
Synopsis
$date=MetabaseToday()
Purpose
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.
Usage
The $date return value is a text string that represents the current date.
Synopsis
$time=MetabaseTime()
Purpose
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.
Usage
The $time return value is a text string that represents the current time.
- Pattern matching
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.
Synopsis
$match=MetabaseBeginsWith($database, $value)
Purpose
Build a pattern that matches the any text expression that starts with the given text value.
Usage
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.
Synopsis
$match=MetabaseEndsWith($database, $value)
Purpose
Build a pattern that matches the any text expression that ends with the given text value.
Usage
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.
Synopsis
$match=MetabaseContains($database, $value)
Purpose
Build a pattern that matches the any text expression that contains the given text value.
Usage
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.
Synopsis
$match=MetabaseMatchPattern($database, $pattern)
Purpose
Build a custom pattern that matches a text expression according to the given pattern definition.
Usage
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.
- Auto-incremented keys
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.
Synopsis
$success=MetabaseGetNextKey($database, $table, $key_sql)
Purpose
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.
Usage
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.
Example
$success=MetabaseGetNextKey($database, "users", $key);
if($success)
{
$result=MetabaseQuery($database, "INSERT INTO users (id, alias, password) VALUES ( ".$key.", 'some user', 'some password')");
}
Synopsis
$success=MetabaseGetInsertedKey($database, $table, $key_value)
Purpose
Retrieve the value of an auto-incremented key of a row just inserted in a given database table.
Usage
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
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.
Synopsis
$prepared_query=MetabasePrepareQuery($database, $query)
Purpose
Create a prepared query. The specified query is parsed and the resulting data is stored for subsequent execution.
Usage
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.
Synopsis
$success=MetabaseFreePreparedQuery($database, $prepared_query)
Purpose
Release resources allocated for the specified prepared query.
Usage
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.
Synopsis
$result=MetabaseExecuteQuery($database, $prepared_query)
Purpose
Execute a prepared query statement.
Usage
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.
Synopsis
$success=MetabaseQuerySet($database, $prepared_query, $parameter, $type, $value, $is_null, $field)
Purpose
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.
Usage
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:
- text
- boolean
- integer
- decimal
- float
- date
- time
- timestamp
- clob
- blob
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.
Synopsis
$success=MetabaseQuerySetNull($database, $prepared_query, $parameter, $type)
Purpose
Set the value of a parameter of a prepared query to NULL.
Usage
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.
Synopsis
$success=MetabaseQuerySetText($database, $prepared_query, $parameter, $value)
Purpose
Set a parameter of a prepared query with a text value.
Usage
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.
Synopsis
$success=MetabaseQuerySetBoolean($database, $prepared_query, $parameter, $value)
Purpose
Set a parameter of a prepared query with a boolean value.
Usage
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.
Synopsis
$success=MetabaseQuerySetInteger($database, $prepared_query, $parameter, $value)
Purpose
Set a parameter of a prepared query with an integer value.
Usage
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.
Synopsis
$success=MetabaseQuerySetDecimal($database, $prepared_query, $parameter, $value)
Purpose
Set a parameter of a prepared query with an decimal value.
Usage
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.
Synopsis
$success=MetabaseQuerySetFloat($database, $prepared_query, $parameter, $value)
Purpose
Set a parameter of a prepared query with a float value.
Usage
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.
Synopsis
$success=MetabaseQuerySetDate($database, $prepared_query, $parameter, $value)
Purpose
Set a parameter of a prepared query with a date value.
Usage
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.
Synopsis
$success=MetabaseQuerySetTime($database, $prepared_query, $parameter, $value)
Purpose
Set a parameter of a prepared query with a date value.
Usage
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.
Synopsis
$success=MetabaseQuerySetTimestamp($database, $prepared_query, $parameter, $value)
Purpose
Set a parameter of a prepared query with a time stamp value.
Usage
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.
Synopsis
$success=MetabaseQuerySetCLOB($database, $prepared_query, $parameter, $value, $field)
Purpose
Set a parameter of a prepared query with a character large object value.
Usage
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.
Synopsis
$success=MetabaseQuerySetBLOB($database, $prepared_query, $parameter, $value, $field)
Purpose
Set a parameter of a prepared query with a binary large object value.
Usage
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.
Synopsis
$success=MetabaseQuerySetKey($database, $prepared_query, $parameter, $table)
Purpose
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.
Usage
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.
Example
$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";
}
Synopsis
$success=MetabaseSetSelectedRowRange($database, $first, $limit)
Purpose
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.
Usage
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.
Query result handling
Synopsis
$success=MetabaseAffectedRows($database, &$affected_rows)
Purpose
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.
Usage
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.
Synopsis
$is_null=MetabaseResultIsNull($database, $result, $row, $field)
Purpose
Determine whether the value of a query result located in given row and field is a NULL.
Usage
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.
Synopsis
$value=MetabaseFetchResult($database, $result, $row, $field)
Purpose
Fetch the value of a query result located in given row and field.
Usage
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.
Synopsis
$value=MetabaseFetchBooleanResult($database, $result, $row, $field)
Purpose
Fetch a boolean value of a query result located in given row and field.
Usage
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.
Synopsis
$value=MetabaseFetchDecimalResult($database, $result, $row, $field)
Purpose
Fetch a decimal value of a query result located in given row and field.
Usage
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.
Synopsis
$value=MetabaseFetchFloatResult($database, $result, $row, $field)
Purpose
Fetch a float value of a query result located in given row and field.
Usage
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.
Synopsis
$value=MetabaseFetchDateResult($database, $result, $row, $field)
Purpose
Fetch a date value of a query result located in given row and field.
Usage
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.
Synopsis
$value=MetabaseFetchTimeResult($database, $result, $row, $field)
Purpose
Fetch a time value of a query result located in given row and field.
Usage
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.
Synopsis
$value=MetabaseFetchTimestampResult($database, $result, $row, $field)
Purpose
Fetch a time stamp value of a query result located in given row and field.
Usage
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.
Synopsis
$lob_handle=MetabaseFetchCLOBResult($database, $result, $row, $field)
Purpose
Fetch the value of a handle to read character large object of a query result located in given row and field.
Usage
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.
Synopsis
$lob_handle=MetabaseFetchBLOBResult($database, $result, $row, $field)
Purpose
Fetch the value of a handle to read binary large object of a query result located in given row and field.
Usage
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.
Synopsis
$end_of_result=MetabaseEndOfResult($database, $result)
Purpose
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.
Usage
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.
Synopsis
$rows=MetabaseNumberOfRows($database, $result)
Purpose
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.
Usage
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.
Synopsis
$columns=MetabaseNumberOfColumns($database, $result)
Purpose
Count the number of columns returned by the DBMS in a query result.
Usage
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.
Synopsis
$success=MetabaseGetColumnNames($database, $result, $column_names)
Purpose
Retrieve the names of of columns returned by the DBMS in a query result.
Usage
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.
Synopsis
$success=MetabaseFreeResult($database, $result)
Purpose
Usage
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.
Large object data manipulation
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.
Synopsis
$success=MetabaseCreateLOB(&$arguments, &$lob)
Purpose
Create a handler object of a specified class with functions to retrieve data from a large object data stream.
Usage
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:
- Type
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.
- The data handler class is the default class. It simply reads data from a given data string.
- The resultlob handler class is meant to read data from a large object retrieved from a query result. This class is
|
