IBPP::Statement

The IBPP::Statement Interface is used to issue queries and other statements to a database, in the context of a transaction. So, along with IBPP::Database and IBPP::Transaction, it is the core of IBPP. You need at least one instance of each to do anything against the data stored in a database.

Definition

See the file ibpp.h for an up-to-date definition of the interface. Lookup the IStatement class.

Like the other building blocks of IBPP, the actual interface class is a pure virtual class (here named IBPP::IStatement). This is the closest thing in C++ to a pure interface concept. The real working object 'behind' (IBPP internals) is derived from this 'contract' class. As long as the signature of this IStatement class does not change, your code is binary compatible with IBPP, even if internal details of the real work-class behind have changed.

As with other IBPP building blocks, you cannot instantiate this class by yourself (due to its pure virtual methods). You also can't derive your own class from this one, due to the protected constructor and destructor. You have to get an instance from IBPP through the corresponding Factory function call.

StatementFactory

StatementFactory is used to build and get access to a Statement object.

Statement StatementFactory(Database db, Transaction tr, const std::string& sql);
Statement StatementFactory(Database db, Transaction tr);

You need a connected database and a started transaction to get a statement object.

Statement members (preparation and execution)

void Prepare(const std::string& sql)

Prepares a SQL statement for later (eventually repeated) execution. You can prepare a new statement at any time (after execution of another one for instance). A statement object can be re-used for different sql statements as long as the database is connected and the transaction started.

void Execute() and void Execute(const std::string& sql)

Executes the statement either:

  • previously passed to Prepare() or
  • passed in the StatementFactory() call or
  • passed as a parameter to this call.

Both these forms of Execute() are suitable for any statements including those returning a result set or a single row.

void ExecuteImmediate(const std::string& sql)

This is a shortcut path to execute a statement that bypasses some steps and uses another Firebird API targeted at such “executes this and forget about it” tasks. This is traditionally used for submitting DDL statements to Firebird.

ExecuteImmediate() can only be used for statements not returning a result set.

void CursorExecute(const std::string& cursor) and void CursorExecute(const std::string& cursor, const std::string& sql)

These are special forms of the Execute() methods, taking an additional initial parameter, a cursor name. Like their Execute() counterparts, they execute the statement, but attach it to the context a new 'cursor' whose name is specified. This special form is only valid for SELECT statements ending in 'FOR UPDATE' are meant to allow to reference the current row position using the syntax WHERE CURRENT OF 'cursor' in later UPDATES statements. You really should read the Firebird documentation about this.

int AffectedRows()

After a successful execution of a statement, returns the number of rows which were affected by the statement. This is only valid for INSERT, UPDATE and DELETE statements. You cannot use this to get the number of rows a SELECT will return. The optimisation design of Firebird makes this information unavailable. The rows (and so their count) are NOT known until you actually fetch them.

STT Type()

Returns an enumeration constant to indicate the kind of statement which was prepared or executed.

STT : Statement Type

The STT enumeration provides constants to identify the type of statement : stUnknown, stUnsupported, stSelect, stInsert, stUpdate, stDelete, stDDL, stExecProcedure, stSelectUpdate, stOther.

std::string& Sql()

Returns the last SQL statement which was prepared or executed.

void Plan(std::string& plan)

Returns the PLAN computed by the engine for the execution of the statement.

void Close()

Closes a statement (free its resources). This is only useful if you want to immediately free such resources before re-using later this same statement object. In all cases, the resources management is all automatic using IBPP.

Methods (fetching results, reading values)

bool Fetch()

After a successul Execute() or CursorExecute(), use Fetch() to retrieve rows of the result set, one at a time. You have to call Fetch() to access your result set (when you expect one) even though your result set is known to consist of a single row.

Fetch() returns true as long as it actually fetched a new row. It returns false when there are no more new rows to fetch.

When a row has been put in context by calling Fetch(), you can read its columns through the Get() methods.

bool IsNull(), <br/>bool IsNull(const std::string& column)

Test wether a column, identified either by position (counted from 1) or by name, is a SQL NULL. Returns true when the column IS NULL, false if there is a value.

bool Get(''type''&), <br/>bool Get(const std::string& column, ''type''&)

There is a whole collection of these Get methods, one for each type supported by IBPP. You should read the article on type conversions to discover what type in your C++ code is compatible with what SQL type.

type can be any of: bool, std::string, int16_t, int32_t, int64_t, float, double, Timestamp, Date, Time, DBKey, Blob, Array.

When the column is NULL (no value), the variable is left intact (unchanged) and the method returns true to signal the NULL.

int ColumnNum(const std::string& column)

Get the position of a column in the row, from its name.

const char* ColumnName(int)

Get the name of a column.

const char* ColumnAlias(int)

Get the alias name of a column.

const char* ColumnTable(int)

Get the table name of a column.

SDT ColumnType(int)

Get the SQL data type of a column.

int ColumnSubtype(int)

Get the SQL data subtype of a column (only meaningfull for Blob and Array columns).

int ColumnSize(int)

Get the column size.

int ColumnScale(int)

Get the scaling factor of a NUMERIC/DECIMAL column.

int Columns()

Get the number of columns in the result set.

Methods (setting parameters)

bool Set(int ''pos'', ''type'')

There is a whole collection of these Set methods, one for each type supported by IBPP. You should read the article on type conversions to discover what type in your C++ code is compatible with what SQL type.

pos is the integer position, counted from 1, of the parameter to set.

type can be any of: bool, std::string, int16_t, int32_t, int64_t, float, double, Timestamp, Date, Time, DBKey, Blob, Array.

These methods are used to set the parameters of a statement, after prepare and before execute.

st->Prepare("SELECT * FROM my_table WHERE this_column = ?");
st->Set(1, "abc");
st->Execute();

You might wonder why IBPP will agree to set any parameter to SQL NULL (through SetNull()) even though the column eventually corresponding to an input parameter is defined NOT NULL? Well, because of triggers. A trigger might be ready to accept a NULL for a NOT NULL column, turning it to some NON NULL value before the actuel INSERT or UPDATE (for instance) happens. Note that this is a new, more correct behaviour, starting with IBPP version 2.5.

SDT ParameterType(int)

Get the SQL data type of a parameter to a statement.

int ParameterSubtype(int)

Get the SQL data subtype of a parameter to a statement.

int ParameterSize(int)

Get the SQL size of a parameter to a statement.

int ParameterScale(int)

Get the scaling factor of a NUMERIC/DECIMAL parameter to a statement.

int Parameters()

Get the number of parameters of a prepared statement.

Varia

IBPP::Database DatabasePtr() and IBPP::Transaction TransactionPtr()

Used to get easy access to the Database and Transaction this Statement is linked to.

reference/statement.txt · Last modified: 2013/03/07 22:33 by olivier
 
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Noncommercial-Share Alike 3.0 Unported
Recent changes RSS feed Donate Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki