Qore::SQL::SQLStatement Class Reference

The SQLStatement class provides the most flexibilty for executing SQL on a database server. More...

#include <QC_SQLStatement.dox.h>

Inheritance diagram for Qore::SQL::SQLStatement:

Public Member Methods
bool	active ()
	Returns True if the object is currently active and has a connection or transaction lock allocated to it, or False if not. More...
int	affectedRows ()
	Returns the number of rows affected by the last call to SQLStatement::exec() More...
nothing	beginTransaction ()
	Manually starts a transaction and allocates a connection or grabs the transaction lock according to the object used in the SQLStatement::constructor() More...
nothing	bind (...)
	Binds placeholder buffer specifications and values to buffers defined in SQLStatement::prepare() More...
nothing	bindArgs (softlist< auto > vargs)
	Binds placeholder buffer specifications and values given as a list in the single argument to the method to buffers defined in SQLStatement::prepare() More...
nothing	bindPlaceholders (...)
	Binds placeholder buffer specifications to buffers defined in SQLStatement::prepare() More...
nothing	bindPlaceholdersArgs (softlist< auto > vargs)
	Binds placeholder buffer specifications given as a list in the single argument to the method to buffers defined in SQLStatement::prepare() More...
nothing	bindValues (...)
	Binds values to value buffer specifications to buffers defined in SQLStatement::prepare() More...
nothing	bindValuesArgs (softlist< auto > vargs)
	Binds values to value buffer specifications given as a list in the single argument to the method to value buffers defined in SQLStatement::prepare() More...
nothing	close ()
	Closes the statement if it is open, however this method does not release the connection or transaction lock. More...
nothing	commit ()
	Commits the transaction, releases the connection or the transaction lock according to the object used in the SQLStatement::constructor(), and closes the SQLStatement. More...
	constructor (Datasource ds)
	Creates the SQLStatement object based on the given Datasource object that provides the connection to the database. More...
	constructor (DatasourcePool dsp)
	Creates the SQLStatement object based on the given DatasourcePool object that provides the connection to the database. More...
	copy ()
	Throws an exception; objects of this class cannot be copied. More...
bool	currentThreadInTransaction ()
	Returns True if the current thread is in a transaction (i.e. holds the transaction lock), False if not. More...
nothing	define ()
	Performs an explicit define operation on the SQLStatement. More...
hash< auto >	describe ()
	Describes columns in the statement result. More...
	destructor ()
	Closes the statement if it is open and destroys the object. More...
nothing	exec (...)
	Executes the bound statement with any bound buffers, also optionally allows binding placeholder buffer specifications and values to buffers defined in SQLStatement::prepare() before executing the statement. More...
nothing	execArgs (softlist< auto > vargs)
	Executes the bound statement with any bound buffers, also optionally allows binding placeholder buffer specifications and values given as a list in the single argument to the method to buffers defined in SQLStatement::prepare() More...
hash< auto >	fetchColumns (softint rows=-1)
	Retrieves a block of rows as a hash of lists with the maximum number of rows determined by the argument passed; automatically advances the row pointer; with this call it is not necessary to call SQLStatement::next(). More...
*hash< auto >	fetchRow ()
	Retrieves the current row as a hash where the keys are the column names and the values are the column values. More...
list< auto >	fetchRows (softint rows=-1)
	Retrieves a block of rows as a list of hashes with the maximum number of rows determined by the argument passed; automatically advances the row pointer; with this call it is not necessary to call SQLStatement::next() More...
hash< auto >	getOutput ()
	Retrieves output buffers as a hash; result sets will be returned as hashes of lists. More...
hash< auto >	getOutputRows ()
	Retrieves output buffers as a hash; result sets will be returned as lists of hashes. More...
*string	getSQL ()
	Returns the current SQL string set with the call to SQLStatement::prepare() or SQLStatement::prepareRaw() or NOTHING if no SQL has been set. More...
*hash< auto >	getValue ()
	Retrieves the current row as a hash where the keys are the column names and the values are the column values. More...
auto	memberGate (string key)
	This method allows SQLStatement objects to be dereferenced directly as a hash for the current row being iterated, as memberGate methods are called implicitly when an unknown member is accessed from outside the class. More...
bool	next ()
	Increments the row pointer when retrieving rows from a select statement; returns True if there is a row to retrieve, False if not. More...
nothing	prepare (string sql,...)
	Saves an SQL statement that will be prepared and executed later, along with optional arguments. More...
nothing	prepareRaw (string sql)
	Saves an SQL statement that will be prepared and executed later. More...
nothing	rollback ()
	Closes the SQLStatement, performs a transaction rollback, and releases the connection or the transaction lock according to the object used in the SQLStatement::constructor(), and closes the SQLStatement. More...
bool	valid ()
	returns True if the object is currently pointing at a valid element, False if not (use when iterating with SQLStatement::next()) More...
abstract bool	active ()
	Returns True if the object is currently active and has a connection or transaction lock allocated to it, or False if not. More...
abstract int	affectedRows ()
	Returns the number of rows affected by the last call to AbstractSQLStatement::exec() More...
abstract nothing	beginTransaction ()
	Manually starts a transaction and allocates a connection or grabs the transaction lock according to the object used in the AbstractSQLStatement::constructor() More...
abstract nothing	bind (...)
	Binds placeholder buffer specifications and values to buffers defined in AbstractSQLStatement::prepare() More...
abstract nothing	bindArgs (softlist< auto > vargs)
	Binds placeholder buffer specifications and values given as a list in the single argument to the method to buffers defined in AbstractSQLStatement::prepare() More...
abstract nothing	bindPlaceholders (...)
	Binds placeholder buffer specifications to buffers defined in AbstractSQLStatement::prepare() More...
abstract nothing	bindPlaceholdersArgs (softlist< auto > vargs)
	Binds placeholder buffer specifications given as a list in the single argument to the method to buffers defined in AbstractSQLStatement::prepare() More...
abstract nothing	bindValues (...)
	Binds values to value buffer specifications to buffers defined in AbstractSQLStatement::prepare() More...
abstract nothing	bindValuesArgs (softlist< auto > vargs)
	Binds values to value buffer specifications given as a list in the single argument to the method to value buffers defined in AbstractSQLStatement::prepare() More...
abstract nothing	close ()
	Closes the statement if it is open, however this method does not release the connection or transaction lock. More...
abstract nothing	commit ()
	Commits the transaction, releases the connection or the transaction lock according to the object used in the AbstractSQLStatement::constructor(), and closes the SQLStatement. More...
abstract bool	currentThreadInTransaction ()
	Returns True if the current thread is in a transaction (i.e. holds the transaction lock), False if not. More...
abstract nothing	define ()
	Performs an explicit define operation on the SQLStatement. More...
abstract hash< auto >	describe ()
	Describes columns in the statement result. More...
abstract nothing	exec (...)
	Executes the bound statement with any bound buffers, also optionally allows binding placeholder buffer specifications and values to buffers defined in AbstractSQLStatement::prepare() before executing the statement. More...
abstract nothing	execArgs (softlist< auto > vargs)
	Executes the bound statement with any bound buffers, also optionally allows binding placeholder buffer specifications and values given as a list in the single argument to the method to buffers defined in AbstractSQLStatement::prepare() More...
abstract hash< auto >	fetchColumns (softint rows=-1)
	Retrieves a block of rows as a hash of lists with the maximum number of rows determined by the argument passed; automatically advances the row pointer; with this call it is not necessary to call AbstractSQLStatement::next(). More...
abstract *hash< auto >	fetchRow ()
	Retrieves the current row as a hash where the keys are the column names and the values are the column values. More...
abstract list< auto >	fetchRows (softint rows=-1)
	Retrieves a block of rows as a list of hashes with the maximum number of rows determined by the argument passed; automatically advances the row pointer; with this call it is not necessary to call AbstractSQLStatement::next() More...
abstract hash< auto >	getOutput ()
	Retrieves output buffers as a hash; result sets will be returned as hashes of lists. More...
abstract hash< auto >	getOutputRows ()
	Retrieves output buffers as a hash; result sets will be returned as lists of hashes. More...
abstract *string	getSQL ()
	Returns the current SQL string set with the call to AbstractSQLStatement::prepare() or AbstractSQLStatement::prepareRaw() or NOTHING if no SQL has been set. More...
abstract *hash< auto >	getValue ()
	Retrieves the current row as a hash where the keys are the column names and the values are the column values. More...
abstract auto	memberGate (string member)
	Returns the value of the given column for the current row. More...
abstract bool	next ()
	Increments the row pointer when retrieving rows from a select statement; returns True if there is a row to retrieve, False if not. More...
abstract nothing	prepare (string sql,...)
	Saves an SQL statement that will be prepared and executed later, along with optional arguments. More...
abstract nothing	prepareRaw (string sql)
	Saves an SQL statement that will be prepared and executed later. More...
abstract nothing	rollback ()
	Closes the SQLStatement, performs a transaction rollback, and releases the connection or the transaction lock according to the object used in the AbstractSQLStatement::constructor(), and closes the SQLStatement. More...
abstract bool	valid ()
	returns True if the object is currently pointing at a valid element, False if not (use when iterating with AbstractSQLStatement::next()) More...
abstract auto	getValue ()
	returns the current value More...
abstract bool	next ()
	Moves the current position to the next element; returns False if there are no more elements. More...
abstract bool	valid ()
	returns True if the iterator is currently pointing at a valid element, False if not More...

Detailed Description

The SQLStatement class provides the most flexibilty for executing SQL on a database server.

Restrictions:

Qore::PO_NO_DATABASE

This class allows statements to be executed and result sets to be iteratively returned in all formats supported by Qore (single row at a time as a hash or blocks of rows as either hashes of lists or lists of hashes). The same flexibility of choosing the output format for result sets also applies to output values from stored procedure or function execution, for example.

This class does not differentiate between executing select statements, stored procedures, functions, or other SQL code; the transaction lock for Datasource objects and a dedicated connection for DatasourcePool objects is allocated to the object when the connection is necessary, and it is not automatically released by this class. The transaction lock or connection must be manually released by calling SQLStatement::commit() or SQLStatement::rollback() (or the methods with the same name in the parent Datasource or DatasourcePool object).

Here is an example executing a select statement:

{
    SQLStatement stmt(ds);
    # release transaction lock on exit
    on_exit stmt.commit();
    stmt.prepare("select * from table");
    while (stmt.next()) {
        hash<auto> row = stmt.fetchRow();
        do_something(row);
    }
}

Note

• Most commands are executed implicitly; for example, in the example above there is no call to SQLStatement::exec() as it is executed implicitly in the initial call to SQLStatement::next().
• Current column values in query results iterated with SQLStatement::next() as above can also be dereferenced directly from the SQLStatement object by using the column name in lower case as a member name (using SQLStatement::memberGate(), see that method for an example)
• Query results can also be returned in blocks using SQLStatement::fetchRows() and SQLStatement::fetchColumns() ("rows" and "columns" in this case refer to the output data format; also using these methods there is no need to call SQLStatement::next())
• When using an SQLStatement object with a DatasourcePool, the statement will be automatically closed when the connection is returned to the pool (for example, by committing or rolling back the transaction).

The following methods are useful when executing all statements:

• SQLStatement::prepare()
• SQLStatement::prepareRaw()
• SQLStatement::bind()
• SQLStatement::bindArgs()
• SQLStatement::bindPlaceholders()
• SQLStatement::bindPlaceholdersArgs()
• SQLStatement::bindValues()
• SQLStatement::bindValuesArgs()
• SQLStatement::exec()
• SQLStatement::execArgs()
• SQLStatement::beginTransaction()
• SQLStatement::commit()
• SQLStatement::rollback()
• SQLStatement::close()
• SQLStatement::getSQL()
• SQLStatement::active()

The following methods are useful when executing select statements:

• SQLStatement::next()
• SQLStatement::fetchRow()
• SQLStatement::fetchRows()
• SQLStatement::fetchColumns()

The following methods are useful when executing stored procedures, functions, or other non-select SQL statements:

• SQLStatement::getOutput()
• SQLStatement::getOutputRows()

Note

This class is not available with the PO_NO_DATABASE parse option

Member Function Documentation

active()

bool Qore::SQL::SQLStatement::active ( )

virtual

Returns True if the object is currently active and has a connection or transaction lock allocated to it, or False if not.

Returns

True if the object is currently active and has a connection or transaction lock allocated to it, or False if not

Code Flags:

CONSTANT

Example:

if (stmt.active())
    stmt.commit();

Implements Qore::SQL::AbstractSQLStatement.

affectedRows()

int Qore::SQL::SQLStatement::affectedRows ( )

virtual

Returns the number of rows affected by the last call to SQLStatement::exec()

Returns

the number of rows affected by the last call to SQLStatement::exec()

Example:

int rc = stmt.affectedRows();

Exceptions

SQLSTATEMENT-ERROR

No SQL has been set with SQLStatement::prepare() or SQLStatement::prepareRaw()

Note

Exceptions could be thrown by the DBI driver when the statement is prepared or when attempting to bind the given arguments to buffer specifications or when the statement is executed; see the relevant DBI driver docs for more information

Implements Qore::SQL::AbstractSQLStatement.

beginTransaction()

nothing Qore::SQL::SQLStatement::beginTransaction ( )

virtual

Manually starts a transaction and allocates a connection or grabs the transaction lock according to the object used in the SQLStatement::constructor()

Example:

stmt.beginTransaction();

Implements Qore::SQL::AbstractSQLStatement.

bind()

nothing Qore::SQL::SQLStatement::bind (   ... )

virtual

Binds placeholder buffer specifications and values to buffers defined in SQLStatement::prepare()

If the statement has not previously been prepared with the DB API, it will be implicitly prepared by this method call. This means that this call will cause a connection to be dedicated from a DatasourcePool object or the transaction lock to be grabbed with a Datasource object, depending on the argument to SQLStatement::constructor().

Arguments to buffer specifications must be given in the same order as declared in the string given to the SQLStatement::prepare() method.

Any arguments previously bound will be released when this call is made.

Note

You can also bind directly when calling SQLStatement::exec() or SQLStatement::execArgs() as a shortcut as well, in which case it's not necessary to make an extra call to this method.

Parameters

...	Arguments to placeholder specifications (if required by the underlying DBI driver) and bind by value arguments

Example:

stmt.prepare("insert into table (id, name) values (%v, %v)");
foreach hash<auto> h in (l) {
    stmt.bind(h.id, h.name);
    stmt.exec();
}

Exceptions

SQLSTATEMENT-ERROR

No SQL has been set with SQLStatement::prepare()

Note

Exceptions could be thrown by the DBI driver when the statement is prepared or when attempting to bind the given arguments to buffer specifications; see the relevant DBI driver docs for more information

See also

SQLStatement::bindArgs(), SQLStatement::bindPlaceholders(), SQLStatement::bindPlaceholdersArgs(), SQLStatement::bindValues(), and SQLStatement::bindValuesArgs()

Implements Qore::SQL::AbstractSQLStatement.

bindArgs()

nothing Qore::SQL::SQLStatement::bindArgs ( softlist< auto >  vargs )

virtual

Binds placeholder buffer specifications and values given as a list in the single argument to the method to buffers defined in SQLStatement::prepare()

If the statement has not previously been prepared with the DB API, it will be implicitly prepared by this method call. This means that this call will cause a connection to be dedicated from a DatasourcePool object or the transaction lock to be grabbed with a Datasource object, depending on the argument to SQLStatement::constructor().

Arguments to buffer specifications must be given in the same order as declared in the string given to the SQLStatement::prepare() method.

Any arguments previously bound will be released when this call is made.

Note

You can also bind directly when calling SQLStatement::exec() or SQLStatement::execArgs() as a shortcut as well, in which case it's not necessary to make an extra call to this method.

Parameters

vargs

Arguments to placeholder specifications (if required by the underlying DBI driver) and bind by value arguments

Example:

stmt.prepare("insert into table (id, name) values (%v, %v)");
foreach hash<auto> h in (l) {
    list<auto> args = (h.id, h.name);
    stmt.bindArgs(args);
    stmt.exec();
}

Exceptions

SQLSTATEMENT-ERROR

No SQL has been set with SQLStatement::prepare()

Note

Exceptions could be thrown by the DBI driver when the statement is prepared or when attempting to bind the given arguments to buffer specifications; see the relevant DBI driver docs for more information

See also

SQLStatement::bind(), SQLStatement::bindPlaceholders(), SQLStatement::bindPlaceholdersArgs(), SQLStatement::bindValues(), and SQLStatement::bindValuesArgs()

Implements Qore::SQL::AbstractSQLStatement.

bindPlaceholders()

nothing Qore::SQL::SQLStatement::bindPlaceholders (   ... )

virtual

Binds placeholder buffer specifications to buffers defined in SQLStatement::prepare()

If the statement has not previously been prepared with the DB API, it will be implicitly prepared by this method call. This means that this call will cause a connection to be dedicated from a DatasourcePool object or the transaction lock to be grabbed with a Datasource object, depending on the argument to SQLStatement::constructor().

Arguments to buffer specifications must be given in the same order as declared in the string given to the SQLStatement::prepare() method. Only placeholder buffer specifications will be processed; value buffer specifications will be skipped by this method.

Any buffer specifications previously defined will be released when this call is made.

Note

You can also bind buffer specifications directly when calling SQLStatement::exec() or SQLStatement::execArgs() as a shortcut as well, in which case it's not necessary to make an extra call to this method.

Not all DBI drivers require binding placeholders specification.

Parameters

...	Arguments to placeholder specifications (if required by the underlying DBI driver)

Example:

stmt.prepare("begin select sysdate into :sd from dual", Type::Date); end;
stmt.bindPlaceholders(Type::Date);
date d = stmt.getOutput().sd;

Exceptions

SQLSTATEMENT-ERROR

No SQL has been set with SQLStatement::prepare()

Note

Exceptions could be thrown by the DBI driver when the statement is prepared or when attempting to bind the given arguments to buffer specifications; see the relevant DBI driver docs for more information

See also

SQLStatement::bind(), SQLStatement::bindArgs(), SQLStatement::bindPlaceholdersArgs(), SQLStatement::bindValues(), and SQLStatement::bindValuesArgs()

Implements Qore::SQL::AbstractSQLStatement.

bindPlaceholdersArgs()

nothing Qore::SQL::SQLStatement::bindPlaceholdersArgs ( softlist< auto >  vargs )

virtual

Binds placeholder buffer specifications given as a list in the single argument to the method to buffers defined in SQLStatement::prepare()

If the statement has not previously been prepared with the DB API, it will be implicitly prepared by this method call. This means that this call will cause a connection to be dedicated from a DatasourcePool object or the transaction lock to be grabbed with a Datasource object, depending on the argument to SQLStatement::constructor().

Arguments to buffer specifications must be given in the same order as declared in the string given to the SQLStatement::prepare() method. Only placeholder buffer specifications will be processed; value buffer specifications will be skipped by this method.

Any buffer specifications previously defined will be released when this call is made.

Note

You can also bind buffer specifications directly when calling SQLStatement::exec() or SQLStatement::execArgs() as a shortcut as well, in which case it's not necessary to make an extra call to this method.

Not all DBI drivers require binding placeholders specification.

Parameters

vargs

Arguments to placeholder specifications (if required by the underlying DBI driver)

Example:

stmt.prepare("begin select sysdate into :sd from dual", Type::Date); end;
list<auto> l = list(Type::Date);
stmt.bindPlaceholdersArgs(l);
date d = stmt.getOutput().sd;

Exceptions

SQLSTATEMENT-ERROR

No SQL has been set with SQLStatement::prepare()

Note

Exceptions could be thrown by the DBI driver when the statement is prepared or when attempting to bind the given arguments to buffer specifications; see the relevant DBI driver docs for more information

See also

SQLStatement::bind(), SQLStatement::bindArgs(), SQLStatement::bindPlaceholders(), SQLStatement::bindValues(), and SQLStatement::bindValuesArgs()

Implements Qore::SQL::AbstractSQLStatement.

bindValues()

nothing Qore::SQL::SQLStatement::bindValues (   ... )

virtual

Binds values to value buffer specifications to buffers defined in SQLStatement::prepare()

If the statement has not previously been prepared with the DB API, it will be implicitly prepared by this method call. This means that this call will cause a connection to be dedicated from a DatasourcePool object or the transaction lock to be grabbed with a Datasource object, depending on the argument to SQLStatement::constructor().

Arguments to buffer specifications must be given in the same order as declared in the string given to the SQLStatement::prepare() method.

Any values previously bound will be released when this call is made.

Note

You can also bind directly when calling SQLStatement::exec() or SQLStatement::execArgs() as a shortcut as well, in which case it's not necessary to make an extra call to this method.

Parameters

...	Arguments to bind by value arguments

Example:

stmt.prepare("insert into table (id, name) values (%v, %v)");
foreach hash<auto> h in (l) {
    stmt.bindValues(h.id, h.name);
    stmt.exec();
}

Exceptions

SQLSTATEMENT-ERROR

No SQL has been set with SQLStatement::prepare()

Note

Exceptions could be thrown by the DBI driver when the statement is prepared or when attempting to bind the given arguments to buffer specifications; see the relevant DBI driver docs for more information

See also

SQLStatement::bind(), SQLStatement::bindArgs(), SQLStatement::bindPlaceholders(), SQLStatement::bindPlaceholdersArgs(), and SQLStatement::bindValuesArgs().

Implements Qore::SQL::AbstractSQLStatement.

bindValuesArgs()

nothing Qore::SQL::SQLStatement::bindValuesArgs ( softlist< auto >  vargs )

virtual

Binds values to value buffer specifications given as a list in the single argument to the method to value buffers defined in SQLStatement::prepare()

If the statement has not previously been prepared with the DB API, it will be implicitly prepared by this method call. This means that this call will cause a connection to be dedicated from a DatasourcePool object or the transaction lock to be grabbed with a Datasource object, depending on the argument to SQLStatement::constructor().

Arguments to buffer specifications must be given in the same order as declared in the string given to the SQLStatement::prepare() method.

Any values previously bound will be released when this call is made.

Note

You can also bind directly when calling SQLStatement::exec() or SQLStatement::execArgs() as a shortcut as well, in which case it's not necessary to make an extra call to this method.

Parameters

vargs

Arguments to bind by value arguments

Example:

stmt.prepare("insert into table (id, name) values (%v, %v)");
foreach hash<auto> h in (l) {
    list<auto> args = (h.id, h.name);
    stmt.bindValuesArgs(args);
    stmt.exec();
}

Exceptions

SQLSTATEMENT-ERROR

No SQL has been set with SQLStatement::prepare()

Note

Exceptions could be thrown by the DBI driver when the statement is prepared or when attempting to bind the given arguments to buffer specifications; see the relevant DBI driver docs for more information

Implements Qore::SQL::AbstractSQLStatement.

close()

nothing Qore::SQL::SQLStatement::close ( )

virtual

Closes the statement if it is open, however this method does not release the connection or transaction lock.

Example:

stmt.close();

Implements Qore::SQL::AbstractSQLStatement.

commit()

nothing Qore::SQL::SQLStatement::commit ( )

virtual

Commits the transaction, releases the connection or the transaction lock according to the object used in the SQLStatement::constructor(), and closes the SQLStatement.

Example:

stmt.commit();

Note

For possible exceptions; see DBI driver docs for the commit() method

Implements Qore::SQL::AbstractSQLStatement.

constructor() [1/2]

Qore::SQL::SQLStatement::constructor

(

Datasource

ds

)

Creates the SQLStatement object based on the given Datasource object that provides the connection to the database.

This method will throw an exception only if the object passed as an argument uses a driver that does not support the prepared statement interface added in Qore version 0.8.1.

Parameters

ds	The Datasource object to use for the DB connection for the SQLStatement object

Example:

SQLStatement stmt(db);

Exceptions

SQLSTATEMENT-ERROR

the DBI driver for the given object does not support the prepared statement API

Since

Qore 0.8.1

constructor() [2/2]

Qore::SQL::SQLStatement::constructor

(

DatasourcePool

dsp

)

Creates the SQLStatement object based on the given DatasourcePool object that provides the connection to the database.

This method will throw an exception only if the object passed as an argument uses a driver that does not support the prepared statement interface added in Qore version 0.8.1.

Parameters

dsp	The DatasourcePool object to use for the DB connection for the SQLStatement object

Example:

SQLStatement stmt(db);

Exceptions

SQLSTATEMENT-ERROR

the DBI driver for the given object does not support the prepared statement API

Since

Qore 0.8.1

copy()

Qore::SQL::SQLStatement::copy

(

)

Throws an exception; objects of this class cannot be copied.

Exceptions

SQLSTATEMENT-COPY-ERROR

SQLStatement objects cannot be copied

currentThreadInTransaction()

bool Qore::SQL::SQLStatement::currentThreadInTransaction ( )

virtual

Returns True if the current thread is in a transaction (i.e. holds the transaction lock), False if not.

Returns

True if the current thread is in a transaction (i.e. holds the transaction lock), False if not

Code Flags:

CONSTANT

Example:

bool b = stmt.currentThreadInTransaction();

Since

Qore 0.8.12

Implements Qore::SQL::AbstractSQLStatement.

define()

nothing Qore::SQL::SQLStatement::define ( )

virtual

Performs an explicit define operation on the SQLStatement.

It is not encessary to call this method manually; define operations are implicitly executed when needed when retrieving values from a select statement

Example:

{
    SQLStatement stmt(ds);
    # release transaction lock on exit
    on_exit stmt.commit();
    stmt.prepare("select * from table");
    stmt.exec();
    stmt.define();
    # note that the SQLStatement::next() would implicitly execute exec() and define()
    while (stmt.next()) {
        hash<auto> row = stmt.fetchRow();
        do_something(row);
    }
}

Implements Qore::SQL::AbstractSQLStatement.

describe()

hash< auto > Qore::SQL::SQLStatement::describe ( )

virtual

Describes columns in the statement result.

Returns

a hash with (column_name: description_hash) format, where each description_hash has the following keys:

• "name": (string) the column name
• "type": (integer) the column type code (as returned by <value>::typeCode())
• "maxsize": (integer) the maximum size of the column
• "native_type": (string) the database-specific name of the type
• "internal_id": (integer) the database-specific type code of the type

Implements Qore::SQL::AbstractSQLStatement.

destructor()

Qore::SQL::SQLStatement::destructor

(

)

Closes the statement if it is open and destroys the object.

Example:

delete stmt;

exec()

nothing Qore::SQL::SQLStatement::exec (   ... )

virtual

Executes the bound statement with any bound buffers, also optionally allows binding placeholder buffer specifications and values to buffers defined in SQLStatement::prepare() before executing the statement.

If the statement has not previously been prepared with the DB API, it will be implicitly prepared by this method call. This means that this call will cause a connection to be dedicated from a DatasourcePool object or the transaction lock to be grabbed with a Datasource object, depending on the argument to SQLStatement::constructor().

Optional arguments to buffer specifications must be given in the same order as declared in the string given to the SQLStatement::prepare() method.

If bind arguments are provided, any arguments previously bound will be released when this call is made.

After calling this method to execute the statement, to retrieve information about the call or output values bound in the call, call SQLStatement::affectedRows(), SQLStatement::getOutput(), or SQLStatement::getOutputRows() as needed.

To retrieve rows from a select statement call either SQLStatement::next() and SQLStatement::fetchRow(), or SQLStatement::fetchRows() or SQLStatement::fetchColumns() as needed.

Parameters

...	Optional arguments to placeholder specifications (if required by the underlying DBI driver) and bind by value arguments can be given in the call to the method; if present, arguments are bound before the statement is executed

Example:

stmt.prepare("insert into table (id, name) values (%v, %v)");
foreach hash<auto> h in (l) {
    stmt.exec(h.id, h.name);
}

Exceptions

SQLSTATEMENT-ERROR

No SQL has been set with SQLStatement::prepare(); the SQLStatement uses a DatasourcePool an the statement was prepared on another connection

Note

Exceptions could be thrown by the DBI driver when the statement is prepared or when attempting to bind the given arguments to buffer specifications or when the statement is executed; see the relevant DBI driver docs for more information

See also

SQLStatement::execArgs()

Implements Qore::SQL::AbstractSQLStatement.

execArgs()

nothing Qore::SQL::SQLStatement::execArgs ( softlist< auto >  vargs )

virtual

Executes the bound statement with any bound buffers, also optionally allows binding placeholder buffer specifications and values given as a list in the single argument to the method to buffers defined in SQLStatement::prepare()

If the statement has not previously been prepared with the DB API, it will be implicitly prepared by this method call. This means that this call will cause a connection to be dedicated from a DatasourcePool object or the transaction lock to be grabbed with a Datasource object, depending on the argument to SQLStatement::constructor().

Optional arguments to buffer specifications must be given in the same order as declared in the string given to the SQLStatement::prepare() method.

If bind arguments are provided, any arguments previously bound will be released when this call is made.

After calling this method to execute the statement, to retrieve information about the call or output values bound in the call, call SQLStatement::affectedRows(), SQLStatement::getOutput(), or SQLStatement::getOutputRows() as needed.

To retrieve rows from a select statement call either SQLStatement::next() and SQLStatement::fetchRow(), or SQLStatement::fetchRows() or SQLStatement::fetchColumns() as needed.

Parameters

vargs

Optional arguments to placeholder specifications (if required by the underlying DBI driver) and bind by value arguments can be given in the call to the method; if present, arguments are bound before the statement is executed

Example:

stmt.prepare("insert into table (id, name) values (%v, %v)");
foreach hash<auto> h in (l) {
    list<auto> args = (h.id, h.name);
    stmt.execArgs(args);
}

Exceptions

SQLSTATEMENT-ERROR

No SQL has been set with SQLStatement::prepare() or SQLStatement::prepareRaw(); the SQLStatement uses a DatasourcePool an the statement was prepared on another connection

Note

Exceptions could be thrown by the DBI driver when the statement is prepared or when attempting to bind the given arguments to buffer specifications or when the statement is executed; see the relevant DBI driver docs for more information

See also

SQLStatement::exec()

Implements Qore::SQL::AbstractSQLStatement.

fetchColumns()

hash< auto > Qore::SQL::SQLStatement::fetchColumns ( softint  rows = -1 )

virtual

Retrieves a block of rows as a hash of lists with the maximum number of rows determined by the argument passed; automatically advances the row pointer; with this call it is not necessary to call SQLStatement::next().

If the argument passed is omitted or less than or equal to zero, then all available rows from the current row position are retrieved, also if fewer rows are available than requested then only the rows available are retrieved.

Parameters

rows	The maximum number of rows to retrieve, if this argument is omitted, negative, or equal to zero, then all available rows from the current row position are retrieved

Returns

a hash (giving column names) of lists (giving row values for each column) of data returned; each list will have at most rows elements (unless rows is negative, in which case all available rows are returned). If the total number of rows available is less than rows (if rows is positive), then the last data returned by this method may return short lists. If no more rows are available, then an empty hash is returned

Example:

hash<auto> h = stmt.fetchColumns(-1);

Exceptions

SQLSTATEMENT-ERROR

No SQL has been set with SQLStatement::prepare() or SQLStatement::prepareRaw()

Note

• There is no need to call SQLStatement::next() when calling this method; the method automatically iterates through the given number of rows
• Exceptions could be thrown by the DBI driver when the statement is prepared or when attempting to bind the given arguments to buffer specifications or when the statement is executed or when row values are retrieved; see the relevant DBI driver docs for more information

Implements Qore::SQL::AbstractSQLStatement.

fetchRow()

*hash< auto > Qore::SQL::SQLStatement::fetchRow ( )

virtual

Retrieves the current row as a hash where the keys are the column names and the values are the column values.

Use with SQLStatement::next() to iterate through the results of a select statement one row at a time

Returns

the current row as a hash where the keys are the column names and the values are the column values

Example:

while (stmt.next()) {
    hash<auto> h = stmt.fetchRow();
}

Exceptions

SQLSTATEMENT-ERROR

No SQL has been set with SQLStatement::prepare() or SQLStatement::prepareRaw()

Note

Exceptions could be thrown by the DBI driver when the statement is prepared or when attempting to bind the given arguments to buffer specifications or when the statement is executed or when row values are retrieved; see the relevant DBI driver docs for more information

Implements Qore::SQL::AbstractSQLStatement.

fetchRows()

list< auto > Qore::SQL::SQLStatement::fetchRows ( softint  rows = -1 )

virtual

Retrieves a block of rows as a list of hashes with the maximum number of rows determined by the argument passed; automatically advances the row pointer; with this call it is not necessary to call SQLStatement::next()

If the argument passed is omitted or less than or equal to zero, then all available rows from the current row position are retrieved, also if fewer rows are available than requested then only the rows available are retrieved.

If no more rows are available then an empty list is returned.

Parameters

rows	The maximum number of rows to retrieve, if this argument is omitted, negative, or equal to zero, then all available rows from the current row position are retrieved

Example:

list<auto> l = stmt.fetchRows(-1);

Exceptions

SQLSTATEMENT-ERROR

No SQL has been set with SQLStatement::prepare() or SQLStatement::prepareRaw()

Note

• There is no need to call SQLStatement::next() when calling this method; the method automatically iterates through the given number of rows
• Exceptions could be thrown by the DBI driver when the statement is prepared or when attempting to bind the given arguments to buffer specifications or when the statement is executed or when row values are retrieved; see the relevant DBI driver docs for more information

Implements Qore::SQL::AbstractSQLStatement.

getOutput()

hash< auto > Qore::SQL::SQLStatement::getOutput ( )

virtual

Retrieves output buffers as a hash; result sets will be returned as hashes of lists.

Returns

Returns a hash of output buffers; result sets will be returned as hashes of lists. Each key in the hash is the same as the name given to the placeholder specification in the call to SQLStatement::prepare() or SQLStatement::prepareRaw()

Example:

hash<auto> h = stmt.getOutput();

Exceptions

SQLSTATEMENT-ERROR

No SQL has been set with SQLStatement::prepare() or SQLStatement::prepareRaw()

Note

Exceptions could be thrown by the DBI driver when the statement is prepared or when attempting to bind the given arguments to buffer specifications or when the statement is executed or when output values are retrieved; see the relevant DBI driver docs for more information

Implements Qore::SQL::AbstractSQLStatement.

getOutputRows()

hash< auto > Qore::SQL::SQLStatement::getOutputRows ( )

virtual

Retrieves output buffers as a hash; result sets will be returned as lists of hashes.

Returns

Retrieves output buffers as a hash; result sets will be returned as lists of hashes. Each key in the hash is the same as the name given to the placeholder specification in the call to SQLStatement::prepare() or SQLStatement::prepareRaw()

Example:

hash<auto> h = stmt.getOutputRows();

Exceptions

SQLSTATEMENT-ERROR

No SQL has been set with SQLStatement::prepare() or SQLStatement::prepareRaw()

Note

Exceptions could be thrown by the DBI driver when the statement is prepared or when attempting to bind the given arguments to buffer specifications or when the statement is executed or when output values are retrieved; see the relevant DBI driver docs for more information

Implements Qore::SQL::AbstractSQLStatement.

getSQL()

*string Qore::SQL::SQLStatement::getSQL ( )

virtual

Returns the current SQL string set with the call to SQLStatement::prepare() or SQLStatement::prepareRaw() or NOTHING if no SQL has been set.

Returns

Returns the current SQL string set with the call to SQLStatement::prepare() or SQLStatement::prepareRaw() or NOTHING if no SQL has been set

Example:

*string sql = stmt.getSQL();

Implements Qore::SQL::AbstractSQLStatement.

getValue()

*hash< auto > Qore::SQL::SQLStatement::getValue ( )

virtual

Retrieves the current row as a hash where the keys are the column names and the values are the column values.

Use with SQLStatement::next() to iterate through the results of a select statement one row at a time

Returns

the current row as a hash where the keys are the column names and the values are the column values

Example:

while (stmt.next()) {
    hash<auto> h = stmt.getValue();
}

Exceptions

SQLSTATEMENT-ERROR

No SQL has been set with SQLStatement::prepare() or SQLStatement::prepareRaw()

Note

• Equivalent to SQLStatement::fetchRow()
• Exceptions could be thrown by the DBI driver when the statement is prepared or when attempting to bind the given arguments to buffer specifications or when the statement is executed or when row values are retrieved; see the relevant DBI driver docs for more information

Since

Qore 0.8.5

Implements Qore::SQL::AbstractSQLStatement.

memberGate()

auto Qore::SQL::SQLStatement::memberGate ( string  key )

virtual

This method allows SQLStatement objects to be dereferenced directly as a hash for the current row being iterated, as memberGate methods are called implicitly when an unknown member is accessed from outside the class.

Code Flags:

RET_VALUE_ONLY

Parameters

key	the column name for the value to retrieve

Returns

the current column value of the given row

Example:

SQLStatement stmt(ds);
# release transaction lock on exit
on_exit stmt.commit();
stmt.prepare("select name, birthdate from table");
while (stmt.next()) {
    printf("name: %s birthdate: %y", stmt.name, stmt.birthdate);
}

Exceptions

SQLSTATEMENT-ERROR	No SQL has been set with SQLStatement::prepare() or SQLStatement::prepareRaw()
SQLSTATEMENT-ITERATION-ERROR	Cannot dereference statement iteration context; make sure and call SQLStatement::next() before trying to dereference the current row being iterated
ENCODING-CONVERSION-ERROR	this error is thrown if the given key cannot be converted to the default character encoding
SQLSTATEMENT-COLUMN-ERROR	The given column name does not exist in the current row data

Note

Column values can only be dereferenced using the automatic SQLStatement::memberGate() method while iterating a result set with SQLStatement::next(); the hash that will be dereferenced is equivalent to that returned by SQLStatement::fetchRow()

Since

Qore 0.8.6

Implements Qore::SQL::AbstractSQLStatement.

next()

bool Qore::SQL::SQLStatement::next ( )

virtual

Increments the row pointer when retrieving rows from a select statement; returns True if there is a row to retrieve, False if not.

If this method returns True, then call SQLStatement::fetchRow() afterwards to retrieve the row

Returns

True if there is a row to retrieve, False if not (no more rows to be retrieved)

Example:

while (stmt.next()) {
    hash<auto> h = stmt.fetchRow();
}

Exceptions

SQLSTATEMENT-ERROR

No SQL has been set with SQLStatement::prepare() or SQLStatement::prepareRaw()

Note

Exceptions could be thrown by the DBI driver when the statement is prepared or when attempting to bind the given arguments to buffer specifications or when the statement is executed; see the relevant DBI driver docs for more information

Implements Qore::SQL::AbstractSQLStatement.

prepare()

nothing Qore::SQL::SQLStatement::prepare ( string  sql,   ...  )

virtual

Saves an SQL statement that will be prepared and executed later, along with optional arguments.

The statement is actually only prepared when used for the first time, this is so that SQLStatement objects created with DatasourcePool objects use the DatasourcePool more efficiently, as many drivers require the actual DB API prepare call to be made on the same connection as the connection the statement will be executed on as well.

Note

This method parses the SQL string for placeholders and bind by value tokens (%v); for a version of this method that does not parse the SQL string for placeholders and bind by value tokens, see SQLStatement::prepareRaw().

Parameters

sql	The SQL string to prepare for execution on the DB server

Example:

stmt.prepare("select * from table where id = %v");

Implements Qore::SQL::AbstractSQLStatement.

prepareRaw()

nothing Qore::SQL::SQLStatement::prepareRaw ( string  sql )

virtual

Saves an SQL statement that will be prepared and executed later.

The statement is actually only prepared when used for the first time, this is so that SQLStatement objects created with DatasourcePool objects use the DatasourcePool more efficiently, as many drivers require the actual DB API prepare call to be made on the same connection as the connection the statement will be executed on as well.

Note

This method does not parse the SQL string for placeholders and bind by value tokens (%v); for a version of this method that does parse the SQL string for placeholders and bind by value tokens, see SQLStatement::prepare().

Parameters

sql	The SQL string to prepare for execution on the DB server

Example:

stmt.prepareRaw("select * from table");

Implements Qore::SQL::AbstractSQLStatement.

rollback()

nothing Qore::SQL::SQLStatement::rollback ( )

virtual

Closes the SQLStatement, performs a transaction rollback, and releases the connection or the transaction lock according to the object used in the SQLStatement::constructor(), and closes the SQLStatement.

Example:

stmt.rollback();

Note

For possible exceptions; see DBI driver docs for the rollback() method

Implements Qore::SQL::AbstractSQLStatement.

valid()

bool Qore::SQL::SQLStatement::valid ( )

virtual

returns True if the object is currently pointing at a valid element, False if not (use when iterating with SQLStatement::next())

Returns

True if the object is currently pointing at a valid element, False if not

Code Flags:

CONSTANT

Example:

if (i.valid())
    printf("current value: %y\n", i.getValue());

Implements Qore::SQL::AbstractSQLStatement.