Creates the SQLStatement object based on the given Datasource or 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.

SQLStatement::constructor(Datasource $ds)

SQLStatement::constructor(DatasourcePool $ds)

my SQLStatement $stmt($ds);

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

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

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 that 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().

nothingSQLStatement::prepare(string$sql, ...)

$stmt.prepare("select * from table where id = %v and name = %v", $fixed_id);

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 that 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().

nothingSQLStatement::prepare(string$sql)

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

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 that 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.

See also: SQLStatement::bindArgs(), SQLStatement::bindPlaceholders(), SQLStatement::bindPlaceholdersArgs(), SQLStatement::bindValues(), and SQLStatement::bindValuesArgs().

nothingSQLStatement::bind(...)

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

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 that 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.

See also: SQLStatement::bind(), SQLStatement::bindPlaceholders(), SQLStatement::bindPlaceholdersArgs(), SQLStatement::bindValues(), and SQLStatement::bindValuesArgs().

nothingSQLStatement::bindArgs(list$args)

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

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 that 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.

See also: SQLStatement::bind(), SQLStatement::bindArgs(), SQLStatement::bindPlaceholdersArgs(), SQLStatement::bindValues(), and SQLStatement::bindValuesArgs().

nothingSQLStatement::bindPlaceholders(...)

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

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 arguments previously bound will be released when this call is made.

Note that 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.

See also: SQLStatement::bind(), SQLStatement::bindArgs(), SQLStatement::bindPlaceholders(), SQLStatement::bindValues(), and SQLStatement::bindValuesArgs().

nothingSQLStatement::bindPlaceholdersArgs(list$args)

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

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 must be given in the same order as declared in the string given to the SQLStatement::prepare() method. Only bind by value buffer specifications will be processed by this method; placeholder buffer specifications will be skipped.

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

Note that 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.

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

nothingSQLStatement::bindValues(...)

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

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 value buffer specifications must be given in the same order as declared in the string given to the SQLStatement::prepare() method. Only value buffer specifications will be processed by this method; value buffer specifications will be skipped.

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

Note that 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.

See also: SQLStatement::bind(), SQLStatement::bindArgs(), SQLStatement::bindValues(), SQLStatement::bindValues(), and SQLStatement::bindValuesArgs().

nothingSQLStatement::bindValuesArgs(list$args)

$stmt.prepare("insert into table (id, name) values (%v, %v)");
foreach my hash $h in ($l) {
    my list $l = ($h.id, $h.name);
    $stmt.bindValuesArgs($l);
    $stmt.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.

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.

See also: SQLStatement::execArgs().

nothingSQLStatement::exec(...)

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

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.

See also: SQLStatement::exec().

nothingSQLStatement::execArgs(list$args)

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

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

hashSQLStatement::getOutput()

my hash $h = $stmt.getOutput();

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

hashSQLStatement::getOutputRows()

my hash $h = $stmt.getOutputRows();

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

intSQLStatement::affectedRows()

my int $rc = $stmt.affectedRows();

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.

boolSQLStatement::next()

while ($stmt.next()) {
    my hash $h = $stmt.fetchRow();
}

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 onw row at a time.

*hashSQLStatement::fetchRow()

while ($stmt.next()) {
    my hash $h = $stmt.fetchRow();
}

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.

listSQLStatement::fetchRows(softint$rows = -1)

my list $l = $stmt.fetchRows(-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().

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 a hash is returned where each key value is an empty list.

hashSQLStatement::fetchColumns(softint$rows = -1)

my hash $h = $stmt.fetchColumns(-1);

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

nothingSQLStatement::close()

$stmt.close();

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

nothingSQLStatement::beginTransaction()

$stmt.beginTransaction();

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

nothingSQLStatement::commit()

$stmt.commit();

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

nothingSQLStatement::rollback()

$stmt.rollback();

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

*stringSQLStatement::getSQL()

my *string $sql = $stmt.getSQL();

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

boolSQLStatement::active()

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