Chapter 6. SQL catalogs
Prev Part I. Core SQL classes Next

Chapter 6. SQL catalogs

The following catalog functions enumerate objects in the database, such as the names of tables and columns. The catalog functions return a statement with a resultset, that's similar to a SELECT resultset. Use fetch(), or the fetch_vectors() to process the resultset.

The columns in the resultset depend on the database driver. Use get_columns() to figure out what they are. Not all database drivers implement every one of the following catalog functions.

In addition to the parameters to the catalog functions described below, each catalog function takes an optional bool parameter, before any of the ones described below. It defaults to a value of false, and the parameters to the catalog functions get interpreted as patterns. Explicitly setting the flag to true generally has the effect of interpreting any given parameters as explicit values, and not wildcards or patterns.

Note

The bool flag sets the SQL_ATTR_METADATA_ID flag, as described in the ODBC documentation; however its intepretation is up to the database drivers', and experimentation showed that the drivers tend to be exhibit behavior inconsistent with this flag, or they ignore it completely. 

#include <x/sql/connection.H>
#include <x/sql/statement.H>

x::sql::connection conn=henv->connect("DSN=dev").first;

std::string catalog, schema, table, type;

x::sql::statement stmt=conn->tables(catalog, schema, table, types);

x::sql::statement stmt=conn->table_privileges(table, catalog, schema);

bool unique_only, force;

x::sql::statement stmt=conn->statistics(table, unique_only, force, catalog, schema);

tables() enumerates the names of tables or other table-like objects in the database. The parameters are interpreted by the database driver. All four parameters are optional (the default values are empty strings), and should default to covering all eligible objects in the database.

table_privileges() enumerates the permissions on a table. The first parameter, the table name, is required, the others are optional. statistics() returns statistics for the table given as its first required parameter. The remaining parameters are optional. Setting unique_only to true returns statistics for unique indexes only; false returns statistics for all indexes (default). optional. Setting force to true generates statistics if they are not available, before returning them; false returns statistics only if they are available (default). 

std::string catalog, schema, table, column;

x::sql::statement stmt=conn->columns(catalog, schema, table, column);

x::sql::statement stmt=conn->column_privileges(catalog, schema, table, column);

columns() returns the list of columns for a particular table. column_privileges() returns permissions for access to columns of a particular table. 

std::string pk_catalog, pk_schema, pk_table;
std::string fk_catalog, fk_schema, fk_table;

x::sql::statement stmt=conn->primary_keys(pk_table, pk_catalog, pk_schema);

x::sql::statement stmt=conn->foreign_keys(pk_catalog, pk_schema, pk_table, fk_catalog, fk_schema, fk_table);

primary_keys() enumerates a table's primary keys. The first pk_table parameter is required. foreign_keys() enumerates a table's foreign keys. Depending on the database driver it enumerates either a a list of foreign keys in a table, or a list of foreign keys in other tables that reference a table's primary key. 

x::sql::rowid_t rowid_type;
x::sql::scope_t scope;

bool nullable;
std::string table;
std::string catalog;
std::string schema;

x::sql::statement stmt=conn->special_columns(rowid_type, scope, table, nullable, catalog, schema);

special_columns() enumerates database-specific special columns for the given table. The rowid_type, scope, and table parameters are required, the rest are optional:

rowid_type

x::sql::rowid_t::unique returns column or columns that uniquely identify each row (typically the table's unique primary keys, or a database driver-specific table row identifier). x::sql::rowid_t::version returns column or columns that serve as each row's version. If any field in the row changes, the version columns' value changes.

scope

The scope of the special columns. Basically specifies the minimum persistent of the values in the special columns (the actual persistence level may exceed the requested one, this specifies the guaranteed minimum): x::sql::scope_t::currow returns column or columns that remain valid as long as a statement resultset is positioned on the requested row; x::sql::scope_t::transaction returns column or columns that remain valid for the duration of the SQL transaction; x::sql::scope_t::session returns column or columns that remain valid for the duration of the connection.

table

Specifies the table's name.

nullable

false specifies special columns that cannot have NULL values; true specifies special columns that may have NULL values.

catalog and schema

Specifies the table's database driver-specific catalog and schema. 

x::sql::statement stmt=conn->type_info();

x::sql::statement stmt=conn->type_info("varchar");

type_info() returns information about the database server's supported datatypes. type_info() takes an optional parameter that names a specific datatype, otherwise information about all datatypes gets returned. 

std::string catalog, schema, proc, column;

x::sql::statement stmt=conn->procedures(catalog, schema, proc);

x::sql::statement stmt=conn->procedure_columns(catalog, schema, proc, column);

procedures() returns the list of stored procedures. procedure_columns() returns the list of parameters and resultset columns for stored procedures.

Prev Up Next
Retrieving blobs Table Of Contents Part II. SQL database interface