Qore Programming Language Reference Manual
1.8.0
|
The SQLStatement class provides the most flexibilty for executing SQL on a database server. More...
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... | |
The SQLStatement class provides the most flexibilty for executing SQL on a database server.
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:
The following methods are useful when executing all statements:
The following methods are useful when executing select statements:
The following methods are useful when executing stored procedures, functions, or other non-select SQL statements:
|
virtual |
Returns True if the object is currently active and has a connection or transaction lock allocated to it, or False if not.
Implements Qore::SQL::AbstractSQLStatement.
|
virtual |
Returns the number of rows affected by the last call to SQLStatement::exec()
SQLSTATEMENT-ERROR | No SQL has been set with SQLStatement::prepare() or SQLStatement::prepareRaw() |
Implements Qore::SQL::AbstractSQLStatement.
|
virtual |
Manually starts a transaction and allocates a connection or grabs the transaction lock according to the object used in the SQLStatement::constructor()
Implements Qore::SQL::AbstractSQLStatement.
|
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.
... | Arguments to placeholder specifications (if required by the underlying DBI driver) and bind by value arguments |
SQLSTATEMENT-ERROR | No SQL has been set with SQLStatement::prepare() |
Implements Qore::SQL::AbstractSQLStatement.
|
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.
vargs | Arguments to placeholder specifications (if required by the underlying DBI driver) and bind by value arguments |
SQLSTATEMENT-ERROR | No SQL has been set with SQLStatement::prepare() |
Implements Qore::SQL::AbstractSQLStatement.
|
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.
... | Arguments to placeholder specifications (if required by the underlying DBI driver) |
SQLSTATEMENT-ERROR | No SQL has been set with SQLStatement::prepare() |
Implements Qore::SQL::AbstractSQLStatement.
|
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.
vargs | Arguments to placeholder specifications (if required by the underlying DBI driver) |
SQLSTATEMENT-ERROR | No SQL has been set with SQLStatement::prepare() |
Implements Qore::SQL::AbstractSQLStatement.
|
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.
... | Arguments to bind by value arguments |
SQLSTATEMENT-ERROR | No SQL has been set with SQLStatement::prepare() |
Implements Qore::SQL::AbstractSQLStatement.
|
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.
vargs | Arguments to bind by value arguments |
SQLSTATEMENT-ERROR | No SQL has been set with SQLStatement::prepare() |
Implements Qore::SQL::AbstractSQLStatement.
|
virtual |
Closes the statement if it is open, however this method does not release the connection or transaction lock.
Implements Qore::SQL::AbstractSQLStatement.
|
virtual |
Commits the transaction, releases the connection or the transaction lock according to the object used in the SQLStatement::constructor(), and closes the SQLStatement.
Implements Qore::SQL::AbstractSQLStatement.
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.
ds | The Datasource object to use for the DB connection for the SQLStatement object |
SQLSTATEMENT-ERROR | the DBI driver for the given object does not support the prepared statement API |
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.
dsp | The DatasourcePool object to use for the DB connection for the SQLStatement object |
SQLSTATEMENT-ERROR | the DBI driver for the given object does not support the prepared statement API |
Qore::SQL::SQLStatement::copy | ( | ) |
Throws an exception; objects of this class cannot be copied.
SQLSTATEMENT-COPY-ERROR | SQLStatement objects cannot be copied |
|
virtual |
Returns True if the current thread is in a transaction (i.e. holds the transaction lock), False if not.
Implements Qore::SQL::AbstractSQLStatement.
|
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
Implements Qore::SQL::AbstractSQLStatement.
|
virtual |
Describes columns in the statement result.
"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.
Qore::SQL::SQLStatement::destructor | ( | ) |
Closes the statement if it is open and destroys the object.
|
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.
... | 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 |
SQLSTATEMENT-ERROR | No SQL has been set with SQLStatement::prepare(); the SQLStatement uses a DatasourcePool an the statement was prepared on another connection |
Implements Qore::SQL::AbstractSQLStatement.
|
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.
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 |
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 |
Implements Qore::SQL::AbstractSQLStatement.
|
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.
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 |
SQLSTATEMENT-ERROR | No SQL has been set with SQLStatement::prepare() or SQLStatement::prepareRaw() |
Implements Qore::SQL::AbstractSQLStatement.
|
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
SQLSTATEMENT-ERROR | No SQL has been set with SQLStatement::prepare() or SQLStatement::prepareRaw() |
Implements Qore::SQL::AbstractSQLStatement.
|
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.
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 |
SQLSTATEMENT-ERROR | No SQL has been set with SQLStatement::prepare() or SQLStatement::prepareRaw() |
Implements Qore::SQL::AbstractSQLStatement.
|
virtual |
Retrieves output buffers as a hash; result sets will be returned as hashes of lists.
SQLSTATEMENT-ERROR | No SQL has been set with SQLStatement::prepare() or SQLStatement::prepareRaw() |
Implements Qore::SQL::AbstractSQLStatement.
|
virtual |
Retrieves output buffers as a hash; result sets will be returned as lists of hashes.
SQLSTATEMENT-ERROR | No SQL has been set with SQLStatement::prepare() or SQLStatement::prepareRaw() |
Implements Qore::SQL::AbstractSQLStatement.
|
virtual |
Returns the current SQL string set with the call to SQLStatement::prepare() or SQLStatement::prepareRaw() or NOTHING if no SQL has been set.
Implements Qore::SQL::AbstractSQLStatement.
|
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
SQLSTATEMENT-ERROR | No SQL has been set with SQLStatement::prepare() or SQLStatement::prepareRaw() |
Implements Qore::SQL::AbstractSQLStatement.
auto Qore::SQL::SQLStatement::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.
key | the column name for the value to retrieve |
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 |
|
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
SQLSTATEMENT-ERROR | No SQL has been set with SQLStatement::prepare() or SQLStatement::prepareRaw() |
Implements Qore::SQL::AbstractSQLStatement.
|
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.
%v
); for a version of this method that does not parse the SQL string for placeholders and bind by value tokens, see SQLStatement::prepareRaw().sql | The SQL string to prepare for execution on the DB server |
Implements Qore::SQL::AbstractSQLStatement.
|
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.
%v
); for a version of this method that does parse the SQL string for placeholders and bind by value tokens, see SQLStatement::prepare().sql | The SQL string to prepare for execution on the DB server |
Implements Qore::SQL::AbstractSQLStatement.
|
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.
Implements Qore::SQL::AbstractSQLStatement.
|
virtual |
returns True if the object is currently pointing at a valid element, False if not (use when iterating with SQLStatement::next())
Implements Qore::SQL::AbstractSQLStatement.