#include <sqldb.h>
| class RSqlStatement |
Represents an SQL statement.
An object of this type can be used to execute all types of SQL statements; this includes SQL statements with parameters.
If a SELECT statament is passed to RSqlStatement::Prepare(), then the returned record set is forward only, non-updateable.
There are a number of ways that this object is used; here are some examples.
CASE 1 - the execution of a SQL statement, which does not return record set:
RSqlDatabase database;
.........
RSqlStatement stmt;
TInt err = stmt.Prepare(database, _L("INSERT INTO Tbl1(Fld1) VALUES(:Val)"));
TInt paramIndex = stmt.ParameterIndex(_L(":Val"));
for(TInt i=1;i<=10;++i)
{
err = stmt.BindInt(paramIndex, i);
err = stmt.Exec();
err = stmt.Reset();
}
stmt.Close();
The following pseudo code shows the general pattern:
<RSqlStatement::Prepare()> [begin:] <RSqlStatement::Bind<param_type>()> <RSqlStatement::Exec()> [<RSqlStatement::Reset()>] [<RSqlStatement::Bind<param_type>()>] [<Goto :begin>]
CASE 2 - the execution of a SQL statement, which returns a record set:
RSqlDatabase database;
.........
RSqlStatement stmt;
TInt err = stmt.Prepare(database, _L("SELECT Fld1 FROM Tbl1 WHERE Fld1 > :Val"));
TInt paramIndex = stmt.ParameterIndex(_L(":Val"));
err = stmt.BindInt(paramIndex, 5);
TInt columnIndex = stmt.ColumnIndex(_L("Fld1"));
while((err = stmt.Next()) == KSqlAtRow)
{
TInt val = stmt.ColumnInt(columnIndex);
RDebug::Print(_L("val=%d\n"), val);
}
if(err == KSqlAtEnd)
<OK - no more records>;
else
<process the error>;
stmt.Close();
The following pseudo code shows the general pattern:
<RSqlStatement::Prepare()> [begin:] <while (RSqlStatement::Next() == KSqlAtRow)> <do something with the records> if(err == KSqlAtEnd) <OK - no more records>; else <process the error>; [<RSqlStatement::Reset()>] [<RSqlStatement::Bind<param_type>()>] [<Goto begin>]
CASE 3.1 - SELECT statements: large column data processing, where the data is copied into a buffer supplied by the client:
RSqlDatabase database;
.........
RSqlStatement stmt;
TInt err = stmt.Prepare(database, _L("SELECT BinaryField FROM Tbl1"));
TInt columnIndex = stmt.ColumnIndex(_L("BinaryField"));
while((err = stmt.Next()) == KSqlAtRow)
{
TInt size = stmt. ColumnSize(columnIndex);
HBufC8* buf = HBufC8::NewL(size);
err = stmt.ColumnBinary(columnIndex, buf->Ptr());
<do something with the data>;
delete buf;
}
if(err == KSqlAtEnd)
<OK - no more records>;
else
<process the error>;
stmt.Close();
CASE 3.2 - SELECT statements: large column data processing, where the data is accessed by the client without copying:
RSqlDatabase database;
.........
RSqlStatement stmt;
TInt err = stmt.Prepare(database, _L("SELECT BinaryField FROM Tbl1"));
TInt columnIndex = stmt.ColumnIndex(_L("BinaryField"));
while((err = stmt.Next()) == KSqlAtRow)
{
TPtrC8 data = stmt.ColumnBinaryL(columnIndex);
<do something with the data>;
}
if(err == KSqlAtEnd)
<OK - no more records>;
else
<process the error>;
stmt.Close();
CASE 3.3 - SELECT statements, large column data processing (the data is accessed by the client without copying), leaving-safe processing:
RSqlDatabase database;
.........
RSqlStatement stmt;
TInt err = stmt.Prepare(database, _L("SELECT BinaryField FROM Tbl1"));
TInt columnIndex = stmt.ColumnIndex(_L("BinaryField"));
while((err = stmt.Next()) == KSqlAtRow)
{
TPtrC8 data;
TInt err = stmt.ColumnBinary(columnIndex, data);
if(err == KErrNone)
{
<do something with the data>;
}
}
if(err == KSqlAtEnd)
<OK - no more records>;
else
<process the error>;
stmt.Close();
CASE 3.4 - SELECT statements: large column data processing, where the data is accessed by the client using a stream:
RSqlDatabase database;
.........
RSqlStatement stmt;
TInt err = stmt.Prepare(database, _L("SELECT BinaryField FROM Tbl1"));
TInt columnIndex = stmt.ColumnIndex(_L("BinaryField"));
while((err = stmt.Next()) == KSqlAtRow)
{
RSqlColumnReadStream stream;
err = stream.ColumnBinary(stmt, columnIndex);
<do something with the data in the stream>;
stream.Close();
}
if(err == KSqlAtEnd)
<OK - no more records>;
else
<process the error>;
stmt.Close();
CASE 4 - the execution of a SQL statement with parameter(s), some of which may be large text or binary values:
RSqlDatabase database;
.........
RSqlStatement stmt;
TInt err =
stmt.Prepare(database, _L("UPDATE Tbl1 SET LargeTextField = :LargeTextVal WHERE IdxField = :KeyVal"));
TInt paramIndex1 = stmt.ParameterIndex(_L(":LargeTextVal"));
TInt paramIndex2 = stmt.ParameterIndex(_L(":KeyVal"));
for(TInt i=1;i<=10;++i)
{
RSqlParamWriteStream stream;
err = stream.BindText(stmt, paramIndex1);
<insert large text data into the stream>;
stream.Close();
err = stmt.BindInt(paramIndex2, i);
err = stmt.Exec();
stmt.Reset();
}
stmt.Close();
The following table shows what is returned when the caller uses a specific column data retrieving function on a specific column type.
-------------------------------------------------------------------------------- Column type | ColumnInt() ColumnInt64() ColumnReal() ColumnText() ColumnBinary() -------------------------------------------------------------------------------- Null........|.0...........0.............0.0..........KNullDesC....KNullDesC8 Int.........|.Int.........Int64.........Real.........KNullDesC....KNullDesC8 Int64.......|.clamp.......Int64.........Real.........KNullDesC....KNullDesC8 Real........|.round.......round.........Real.........KNullDesC....KNullDesC8 Text........|.0...........0.............0.0..........Text.........KNullDesC8 Binary......|.0...........0.............0.0..........KNullDesC....Binary --------------------------------------------------------------------------------Note the following definitions:
"clamp": return KMinTInt or KMaxTInt if the value is outside the range that can be represented by the type returned by the accessor function.
"round": the floating point value will be rounded up to the nearest integer. If the result is outside the range that can be represented by the type returned by the accessor function, then it will be clamped.
Note that when handling blob and text data over 2Mb in size it is recommended that the RSqlBlobReadStream and RSqlBlobWriteStream classes or the TSqlBlob class is used instead. These classes provide a more RAM-efficient way of reading and writing large amounts of blob or text data from a database.
See also: KMinTInt KMaxTInt KNullDesC KNullDesC8 RSqlBlobReadStream RSqlBlobWriteStream TSqlBlob
| IMPORT_C | RSqlStatement | ( | ) |
Initialises the pointer to the implementation object to NULL.
| IMPORT_C TBool | AtRow | ( | ) | const |
Tests whether the SQL statement points to a valid record.
Returns: True, if the SQL statement points to a valid record, false otherwise.
Sets the parameter to the specified 8-bit descriptor.
The parameter is identified by the specified index value.
immediately after this SQL statement has been prepared
after a call to Reset()
Note that when the binary data to be bound is over 2Mb in size then it is recommended that the RSqlBlobWriteStream or TSqlBlob class is used instead.
These classes provide a more RAM-efficient way of writing large amounts of binary data to a database. If the binary data is part of a record to be inserted into a database then BindZeroBlob() should be called on the INSERT statement to create a placeholder for the binary data, whose content can then be written using the above classes.
See also: RSqlStatement::Prepare() RSqlStatement::Reset() RSqlStatement::Next() RSqlStatement::Exec() RSqlStatement::BindZeroBlob() RSqlBlobWriteStream TSqlBlob
| Parameter | Description |
|---|---|
| aParameterIndex | The index value identifying the parameter; this is 0 for the first parameter. |
| aParameterData | The 8-bit descriptor whose content is to be assigned to the parameter. |
Returns: KErrNone, the operation completed successfully; KErrNoMemory, an out of memory condition has occurred. One of the other system-wide error codes may also be returned.
Sets the parameter to the specified 32-bit integer value.
The parameter is identified by the specified index value.
immediately after this SQL statement has been prepared
after a call to Reset()
See also: RSqlStatement::Prepare() RSqlStatement::Reset()
| Parameter | Description |
|---|---|
| aParameterIndex | The index value identifying the parameter; this is 0 for the first parameter. |
| aParameterValue | The 32-bit integer value to be assigned to the parameter. |
Returns: KErrNone, the operation completed successfully; KErrNoMemory, an out of memory condition has occurred. One of the other system-wide error codes may also be returned.
Sets the parameter to the specified 64-bit integer value.
The parameter is identified by the specified index value.
immediately after this SQL statement has been prepared
after a call to Reset()
See also: RSqlStatement::Prepare() RSqlStatement::Reset()
| Parameter | Description |
|---|---|
| aParameterIndex | The index value identifying the parameter; this is 0 for the first parameter. |
| aParameterValue | The 64-bit integer value to be assigned to the parameter. |
Returns: KErrNone, the operation completed successfully; KErrNoMemory, an out of memory condition has occurred. One of the other system-wide error codes may also be returned.
Sets the parameter to a NULL value.
The parameter is identified by the specified index value.
immediately after this SQL statement has been prepared
after a call to Reset()
See also: RSqlStatement::Prepare() RSqlStatement::Reset()
| Parameter | Description |
|---|---|
| aParameterIndex | The index value identifying the parameter; this is 0 for the first parameter. |
Returns: KErrNone, the operation completed successfully. One of the other system-wide error codes may also be returned.
Sets the parameter to the specified 64-bit floating point value.
The parameter is identified by the specified index value.
immediately after this SQL statement has been prepared
after a call to Reset()
See also: RSqlStatement::Prepare() RSqlStatement::Reset()
| Parameter | Description |
|---|---|
| aParameterIndex | The index value identifying the parameter; this is 0 for the first parameter. |
| aParameterValue | The 64-bit floating point value to be assigned to the parameter. |
Returns: KErrNone, the operation completed successfully; KErrNoMemory, an out of memory condition has occurred. One of the other system-wide error codes may also be returned.
Sets the parameter to the specified 16-bit descriptor.
The parameter is identified by the specified index value.
immediately after this SQL statement has been prepared
after a call to Reset()
Note that when the text data to be bound is over 2Mb in size then use of the RSqlBlobWriteStream or TSqlBlob class should be considered instead.
These classes provide a more RAM-efficient way of writing large amounts of text data to a database, however no conversions are performed on the text data - it is simply stored as a stream of bytes. If the text data is part of a record to be inserted into a database then BindZeroBlob() should be called on the INSERT statement to create a placeholder for the text data, whose content can then be written using the above classes.
See also: RSqlStatement::Prepare() RSqlStatement::Reset() RSqlStatement::Next() RSqlStatement::Exec() RSqlStatement::BindZeroBlob() RSqlBlobWriteStream TSqlBlob
| Parameter | Description |
|---|---|
| aParameterIndex | The index value identifying the parameter; this is 0 for the first parameter. |
| aParameterText | The 16-bit descriptor whose content is to be assigned to the parameter. |
Returns: KErrNone, the operation completed successfully; KErrNoMemory, an out of memory condition has occurred. One of the other system-wide error codes may also be returned.
Binds a blob of length aBlobSize bytes that is filled with zeroes.
The parameter is identified by the specified index value.
immediately after this SQL statement has been prepared
after a call to Reset()
A zeroblob acts as a placeholder for a blob whose binary content is later written using the RSqlBlobWriteStream or TSqlBlob class.
Using zeroblobs provides a much more RAM-efficient way of creating large blobs than including the blob data in the INSERT statement and it is recommended for blobs that are over 2Mb in size.
Note that a zeroblob should be created in a column after which there are no columns that contain anything other than zeroblobs or NULLs, otherwise the zeroblob must be allocated in full in RAM and its benefit is lost.
When creating a zeroblob it is recommended, where possible, to create the zeroblob and then write the blob content (using the RSqlBlobWriteStream or TSqlBlob class) within the same transaction. Otherwise the zeroblob will have to be journalled before being written to.
See also: RSqlStatement::Prepare() RSqlStatement::Reset() RSqlStatement::Next() RSqlStatement::Exec() RSqlBlobWriteStream TSqlBlob
| Parameter | Description |
|---|---|
| aParameterIndex | The index value identifying the parameter; this is 0 for the first parameter. |
| aBlobSize | The size in bytes of the blob. |
Returns: KErrNone, the operation completed successfully; KErrNoMemory, an out of memory condition has occurred. One of the other system-wide error codes may also be returned.
| IMPORT_C void | Close | ( | ) |
Closes this SQL statement object.
The function frees memory and any allocated resources.
See also: RSqlStatement::Prepare()
Gets the value of the column as an 8-bit descriptor.
The column is identified by the specified index value.
Note that the function can only be called after a successful call to Next(), i.e. after a call to Next() that has completed with a KSqlAtRow return code. Calling this function after an unsuccessful call to Next() raises a panic.
Note that when the binary data to be retrieved is over 2Mb in size then it is recommended that the RSqlBlobReadStream or TSqlBlob class is used instead. These classes provide a more RAM-efficient way of retrieving large amounts of binary data from a database.
See also: KSqlAtRow RSqlStatement::ColumnBinaryL() RSqlStatement::Prepare() RSqlStatement::Next() RSqlBlobReadStream TSqlBlob
| Parameter | Description |
|---|---|
| aColumnIndex | The index value identifying the column; this is 0 for the first column. |
| aPtr | A non-modifiable pointer descriptor. On successful completion of this function, the pointer descriptor represents the 8-bit column data. The descriptor does not change if the function fails. |
Returns: KErrNone, if the function completes successfully, otherwise one of the other system-wide error codes.
Interprets the value of the column as an 8-bit descriptor, and copies the data into an 8-bit modifiable descriptor supplied by the caller.
The column is identified by the specified index value.
Note that the function can only be called after a successful call to Next(), i.e. after a call to Next() that has completed with a KSqlAtRow return code. Calling this function after an unsuccessful call to Next() raises a panic.
Note that when the binary data to be retrieved is over 2Mb in size then it is recommended that the RSqlBlobReadStream or TSqlBlob class is used instead. These classes provide a more RAM-efficient way of retrieving large amounts of binary data from a database.
See also: KSqlAtRow RSqlStatement::Prepare() RSqlStatement::Next() RSqlBlobReadStream TSqlBlob
| Parameter | Description |
|---|---|
| aColumnIndex | The index value identifying the column; this is 0 for the first column. |
| aDest | An 8-bit modifiable descriptor into which the column data is to be copied. |
Returns: KErrNone, the operation has completed successfully; KErrOverflow, the maximum length of the target descriptor supplied by the caller (aDest) is less than the length of the column data - the column data is truncated to fit into the target descriptor. KErrNoMemory, an out of memory condition has occurred.
Gets the value of the column as an 8-bit descriptor (leaves on failure).
The column is identified by the specified index value.
Note that the function can only be called after a successful call to Next(), i.e. after a call to Next() that has completed with a KSqlAtRow return code. Calling this function after an unsuccessful call to Next() raises a panic.
Note that when the binary data to be retrieved is over 2Mb in size then it is recommended that the RSqlBlobReadStream or TSqlBlob class is used instead. These classes provide a more RAM-efficient way of retrieving large amounts of binary data from a database.
See also: KSqlAtRow RSqlStatement::ColumnBinary() RSqlStatement::Prepare() RSqlStatement::Next() RSqlBlobReadStream TSqlBlob
| Parameter | Description |
|---|---|
| aColumnIndex | The index value identifying the column; this is 0 for the first column. |
Returns: A non-modifiable pointer descriptor representing the 8-bit column data.
| IMPORT_C TInt | ColumnCount | ( | ) | const |
Gets the number of columns that are to be returned by this SQL statement.
This function can be called at any time after the SQL statement has been prepared, but it is useful only for SELECT statements. The column count of any other type of statement is always 0.
Returns: The number of columns.
Gets the index (starting from 0) of the column with the given name.
The function does a case insensitive column name search.
This function can be called at any time after the SQL statement has been prepared.
| Parameter | Description |
|---|---|
| aColumnName | The column name. |
Returns: the column index value, if successful - this is a non-negative integer value; KErrNotFound, if no such parameter can be found. One of the other system-wide error codes may also be returned.
Gets the value of the column as a 32-bit integer.
The column is identified by the specified index value.
Note that the function can only be called after a successful call to Next(), i.e. after a call to Next() that has completed with a KSqlAtRow return code. Calling this function after an unsuccessful call to Next() raises a panic.
See also: KSqlAtRow RSqlStatement::Prepare() RSqlStatement::Next()
| Parameter | Description |
|---|---|
| aColumnIndex | The index value identifying the column; this is 0 for the first column. |
Returns: The value of the column as a 32-bit integer.
Gets the value of the column as a 64-bit integer.
The column is identified by the specified index value.
Note that the function can only be called after a successful call to Next(), i.e. after a call to Next() that has completed with a KSqlAtRow return code. Calling this function after an unsuccessful call to Next() raises a panic.
See also: KSqlAtRow RSqlStatement::Prepare() RSqlStatement::Next()
| Parameter | Description |
|---|---|
| aColumnIndex | The index value identifying the column; this is 0 for the first column. |
Returns: The value of the column as a 64-bit integer.
Obtain the name of a column after preparing a query.
| Parameter | Description |
|---|---|
| aColumnIndex | Column index |
| aNameDest | Descriptor which will be set to column name |
Returns: KErrNone if successfull or one of the system-wide error codes on error
Gets the value of the column as a 64-bit floating point value.
The column is identified by the specified index value.
Note that the function can only be called after a successful call to Next(), i.e. after a call to Next() that has completed with a KSqlAtRow return code. Calling this function after an unsuccessful call to Next() raises a panic.
See also: KSqlAtRow RSqlStatement::Prepare() RSqlStatement::Next()
| Parameter | Description |
|---|---|
| aColumnIndex | The index value identifying the column; this is 0 for the first column. |
Returns: The value of the column as a 64-bit floating point value.
Gets the length of the data for the column identified by the specified column index.
The length depends on the column type and is normally in bytes, except for the case where the column type is ESqlText, in which case the function returns the number of characters.
-------------------------------------------------------------- | Column type | Column Size -------------------------------------------------------------- | ESqlInt.....|.4 | ESqlInt64...|.8 | ESqlReal....|.8 | ESqlText....|.the number of characters in the unicode string | ESqlBinary..|.the byte length of the binary data | ESqlNull....|.0 --------------------------------------------------------------
Note that the function can only be called after a successful call to Next(), i.e. after a call to Next() that has completed with a KSqlAtRow return code. Calling this function after an unsuccessful call to Next() raises a panic.
See also: KSqlAtRow TSqlColumnType RSqlStatement::Next() RSqlStatement::ColumnIndex()
| Parameter | Description |
|---|---|
| aColumnIndex | The column index value; this is 0 for the first column. |
Returns: The size of the column, and depends on the type of column.
Gets the value of the column as a 16-bit descriptor.
The column is identified by the specified index value.
Note that the function can only be called after a successful call to Next(), i.e. after a call to Next() that has completed with a KSqlAtRow return code. Calling this function after an unsuccessful call to Next() raises a panic.
Note that when the text to be retrieved is over 2Mb in size then it is recommended that the RSqlBlobReadStream or TSqlBlob class is used instead. These classes provide a more RAM-efficient way of retrieving large amounts of text data from a database.
See also: KSqlAtRow RSqlStatement::ColumnTextL() RSqlStatement::Prepare() RSqlStatement::Next() RSqlBlobReadStream TSqlBlob
| Parameter | Description |
|---|---|
| aColumnIndex | The index value identifying the column; this is 0 for the first column. |
| aPtr | A non-modifiable pointer descriptor. On successful completion of this function, the pointer descriptor represents the 16-bit column text. The descriptor does not change if the function fails. |
Returns: KErrNone, if the function completes successfully, otherwise one of the other system-wide error codes.
Interprets the value of the column as a 16-bit descriptor, and copies the data into a 16-bit modifiable descriptor supplied by the caller.
The column is identified by the specified index value.
Note that the function can only be called after a successful call to Next(), i.e. after a call to Next() that has completed with a KSqlAtRow return code. Calling this function after an unsuccessful call to Next() raises a panic.
Note that when the text to be retrieved is over 2Mb in size then it is recommended that the RSqlBlobReadStream or TSqlBlob class is used instead. These classes provide a more RAM-efficient way of retrieving large amounts of text data from a database.
See also: KSqlAtRow RSqlStatement::Prepare() RSqlStatement::Next() RSqlBlobReadStream TSqlBlob
| Parameter | Description |
|---|---|
| aColumnIndex | The index value identifying the column; this is 0 for the first column. |
| aDest | A 16-bit modifiable descriptor into which the column data is to be copied. |
Returns: KErrNone, the operation has completed successfully; KErrOverflow, the maximum length of the target descriptor supplied by the caller (aDest) is less than the length of the column text - the column data is truncated to fit into the target descriptor. KErrNoMemory, an out of memory condition has occurred.
Gets the value of the column as a 16-bit descriptor (leaves on failure).
The column is identified by the specified index value.
Note that the function can only be called after a successful call to Next(), i.e. after a call to Next() that has completed with a KSqlAtRow return code. Calling this function after an unsuccessful call to Next() raises a panic.
Note that when the text to be retrieved is over 2Mb in size then it is recommended that the RSqlBlobReadStream or TSqlBlob class is used instead. These classes provide a more RAM-efficient way of retrieving large amounts of text data from a database.
See also: KSqlAtRow RSqlStatement::ColumnText() RSqlStatement::Prepare() RSqlStatement::Next() RSqlBlobReadStream TSqlBlob
| Parameter | Description |
|---|---|
| aColumnIndex | The index value identifying the column; this is 0 for the first column. |
Returns: A non-modifiable pointer descriptor representing the 16-bit column text.
| IMPORT_C TSqlColumnType | ColumnType | ( | TInt | aColumnIndex | ) | const |
Gets the runtime type of the column identified by the specified column index.
This function returns the actual runtime datatype of the specified column as opposed to its declared type.
Note that the function can only be called after a successful call to Next(), i.e. after a call to Next() that has completed with a KSqlAtRow return code. Calling this function after an unsuccessful call to Next() raises a panic.
See also: KSqlAtRow TSqlColumnType RSqlStatement::DeclaredColumnType() RSqlStatement::Next() RSqlStatement::ColumnIndex()
| Parameter | Description |
|---|---|
| aColumnIndex | The index value identifying the column; this is 0 for the first column. |
Returns: The column type.
| IMPORT_C TInt | DeclaredColumnType | ( | TInt | aColumnIndex, |
| TSqlColumnType & | aColumnType | |||
| ) | const | |||
Gets the declared type of the column identified by the specified column index.
Note that the function can only be called when executing a SELECT query, and only after a successful call to Prepare().
This function returns the datatype that the specified column was originally declared to have.
- if the column type name contains the string "INT", then the declared column type is ESqlInt; - if the column type name contains any of the strings "CHAR, "TEXT" or "CLOB", then the declared column type is ESqlText; - if the column type name contains any of the strings "BLOB" or "BINARY", then the declared column type is ESqlBinary; - if the column type name contains any of the strings "FLOAT", "REAL" or "DOUBLE", then the declared column type is ESqlReal; - in all other cases the declared column type is assumed to be ESqlInt;
See also: TSqlColumnType RSqlStatement::ColumnType() RSqlStatement::ColumnIndex()
| Parameter | Description |
|---|---|
| aColumnIndex | The index value identifying the column. This is 0 for the first column. |
| aColumnType | Output parameter. If the call completes successfully, aColumnType contains the type of the column, one of TSqlColumnType enum item values. |
Returns: KErrNone, the operation completed successfully; KErrNoMemory, an out of memory condition has occurred. One of the other system-wide error codes may also be returned.
| IMPORT_C TInt | Exec | ( | ) |
Executes the prepared DDL/DML SQL statement.
The function is very useful when the SQL statement contains parameters, because the statement can be prepared once using RSqlStatement::Prepare(), and then bound and executed many times.
parameter values must be bound before calling Exec().
SQL statements that do not have parameters should use RSqlDatabase::Exec() instead.
If the call to this function fails because of a database-specific type error (i.e. the error is categorised as of type ESqlDbError), then a textual description of the error can be obtained calling RSqlDatabase::LastErrorMessage().
Usage pattern:
RSqlStatement stmt;
TInt err = stmt.Prepare(<database>, <SQL statement>);
while(<condition>)
{
err = stmt.Bind<parameter type>(<parameter index>, <value>);
...
err = stmt.Exec();
....
err = stmt.Reset();
}
stmt.Close();
See also: TSqlRetCodeClass::ESqlDbError RSqlStatement::Prepare() RSqlDatabase::Exec() RSqlDatabase::LastErrorMessage()
Returns: >=0, The operation has completed successfully. The number of database rows that were changed/inserted/deleted by the most recently completed DDL/DML sql statement. Exception: If the executed statement is "DELETE FROM <table>", then the function returns 0 if the operation has completed successfully (disregarding the number of the deleted rows); KSqlErrStmtExpired, the SQL statement has expired (if new functions or collating sequences have been registered or if an authorizer function has been added or changed); KErrDiskFull, There is no available disk space to complete the operation. If the executed statement is a DELETE statement, try to use the reserved disk space (if there is a reserved disk space) to complete the operation. In all other cases the statement and database connection should be closed and some disk space freed before reopening the database; KErrNoMemory, an out of memory condition has occurred - the statement will be reset. Note that database specific errors categorised as ESqlDbError can also be returned.
| IMPORT_C void | Exec | ( | TRequestStatus & | aStatus | ) |
Executes the prepared DDL/DML SQL statement asynchronously to allow client to avoid being blocked by server activity.
No other operations can be performed on current RSqlStatement object until the asynchronous operation completes.
The function is very useful when the SQL statement contains parameters, because the statement can be prepared once using RSqlStatement::Prepare(), and then bound and executed many times.
parameter values must be bound before calling Exec().
SQL statements that do not have parameters should use RSqlDatabase::Exec() instead.
If the call to this function fails because of a database-specific type error (i.e. the error is categorised as of type ESqlDbError), then a textual description of the error can be obtained calling RSqlDatabase::LastErrorMessage().
See also: TSqlRetCodeClass::ESqlDbError RSqlStatement::Prepare() RSqlDatabase::Exec() RSqlDatabase::LastErrorMessage()
| Parameter | Description |
|---|---|
| aStatus | Completion status of asynchronous request, one of the following: >=0, The operation has completed successfully. The number of database rows that were changed/inserted/deleted by the most recently completed DDL/DML sql statement. Exception: If the executed statement is "DELETE FROM <table>", then the function returns 0 if the operation has completed successfully (disregarding the number of the deleted rows); KSqlErrStmtExpired, the SQL statement has expired (if new functions or collating sequences have been registered or if an authorizer function has been added or changed); KErrDiskFull, There is no available disk space to complete the operation. If the executed statement is a DELETE statement, try to use the reserved disk space (if there is a reserved disk space) to complete the operation. In all other cases the statement and database connection should be closed and some disk space freed before reopening the database; KErrNoMemory, an out of memory condition has occurred - the statement will be reset. Note that aStatus may be set with database specific errors categorised as ESqlDbError, and other system-wide error codes. |
Tests whether the value of the specified column is NULL.
The column is identified by the specified index value.
Note that the function can only be called after a successful call to Next(), i.e. after a call to Next() that has completed with a KSqlAtRow return code. Calling this function after an unsuccessful call to Next() raises a panic.
See also: KSqlAtRow RSqlStatement::Prepare() RSqlStatement::Next()
| Parameter | Description |
|---|---|
| aColumnIndex | The index value identifying the column; this is 0 for the first column. |
Returns: True, if the value of the column is NULL, false otherwise.
| IMPORT_C TInt | Next | ( | ) |
Retrieves a record.
If the prepared SQL statement is a "SELECT" statement, and is expected to return a set of records, then this function can be used to retrieve that record data.
If the SQL statement contains parameters, then their values must be bound before this function is called.
If the call to this function completes successfully, i.e. it returns with KSqlAtRow, then this RSqlStatement object contains the record data, and this data will remain valid for access until another call is made to any RSqlStatement function.
Note that if this call to Next() fails, as indicated by a return code value other than KSqlAtRow, then calls to these RSqlStatement::Column...() functions will raise a panic.
Returns: KSqlAtRow, the record data is ready for processing by the caller; KSqlAtEnd, there is no more record data; KSqlErrBusy, the database file is locked; KErrNoMemory, an out of memory condition has occurred - the statement will be reset; KSqlErrGeneral, a run-time error has occured - this function must not be called again; KSqlErrMisuse, this function has been called after a previous call returned KSqlAtEnd or KSqlErrGeneral. KSqlErrStmtExpired, the SQL statement has expired (if new functions or collating sequences have been registered or if an authorizer function has been added or changed);
Obtain the name of a parameter after preparing a DML query. The parameter names are returned in exactly the same form as supplied in SQL statement. For example, if the parameter name is ":Prm", then the ":" prefix will not be omitted.
ParamName has the same behaviour as ParameterName. It is provided to maintain source compatibility with previous Symbian releases.
This function can be called at any time after the DML SQL statement has been prepared.
| Parameter | Description |
|---|---|
| aParameterIndex | Parameter index |
| aNameDest | Descriptor which will be set to parameter name |
Returns: KErrNone if successful or one of the system-wide error codes on error
Gets the index (starting from 0) of the parameter with the given name.
The function does a case insensitive parameter name search.
For example, if the parameter name is ":Prm", then the ":" prefix cannot be omitted when you call ParameterIndex().
This function can be called at any time after the SQL statement has been prepared.
| Parameter | Description |
|---|---|
| aParameterName | The parameter name. |
Returns: the parameter index value, if successful - this is a non-negative integer value; KErrNotFound, if no such parameter can be found. One of the other system-wide error codes may also be returned.
Obtain the name of a parameter after preparing a DML query. The parameter names are returned in exactly the same form as supplied in SQL statement. For example, if the parameter name is ":Prm", then the ":" prefix will not be omitted.
This function can be called at any time after the DML SQL statement has been prepared.
| Parameter | Description |
|---|---|
| aParameterIndex | Parameter index |
| aNameDest | Descriptor which will be set to column name |
Returns: KErrNone if successfull or one of the system-wide error codes on error
| IMPORT_C TInt | Prepare | ( | RSqlDatabase & | aDatabase, |
| const TDesC & | aSqlStmt | |||
| ) | ||||
Prepares the supplied 16-bit SQL statement for execution.
An RSqlStatement object can prepare and execute a parameterised SQL statement or an SQL statement without parameters.
The function can only deal with one SQL statement at a time, i.e. if you supply more than one SQL statement, each separated by a ";" character, then the function returns an error.
Note that when the statement is to be used to retrieve or write blob or text data that is over 2Mb in size then it is recommended that the RSqlBlobReadStream and RSqlBlobWriteStream classes or the TSqlBlob class is used instead. These classes provide a more RAM-efficient way of reading and writing large amounts of blob or text data from a database.
See also: TSqlRetCodeClass::ESqlDbError RSqlDatabase RSqlDatabase::LastErrorMessage() RSqlSecurityPolicy RSqlSecurityPolicy::TPolicyType RSqlBlobReadStream RSqlBlobWriteStream TSqlBlob
| Parameter | Description |
|---|---|
| aDatabase | A reference to the RSqlDatabase object that represents the database for which the SQL statement is being prepared. |
| aSqlStmt | A string of 16-bit wide characters containing the SQL statement to be prepared. |
Returns: KErrNone, the SQL statement has been prepared for execution successfully; KErrNoMemory, an out of memory condition has occurred; KErrArgument, the SQL statement is invalid, for example, the supplied string contains more than one SQL statement, or it contains an empty SQL statement. Note that database specific errors categorised as ESqlDbError can also be returned; KSqlErrGeneral, a syntax error has occurred - text describing the problem can be obtained by calling RSqlDatabase::LastErrorMessage(). KErrPermissionDenied, the calling application does not satisfy the relevant database security policies. Note that database specific errors categorised as ESqlDbError, and other system-wide error codes may also be returned.
| IMPORT_C TInt | Prepare | ( | RSqlDatabase & | aDatabase, |
| const TDesC8 & | aSqlStmt | |||
| ) | ||||
Prepares the supplied 8-bit SQL statement for execution.
An RSqlStatement object can prepare and execute a parameterised SQL statement or an SQL statement without parameters.
The function can only deal with one SQL statement at a time, i.e. if you supply more than one SQL statement, each separated by a ";" character, then the function returns an error.
Note that when the statement is to be used to retrieve or write blob or text data that is over 2Mb in size then it is recommended that the RSqlBlobReadStream and RSqlBlobWriteStream classes or the TSqlBlob class is used instead. These classes provide a more RAM-efficient way of reading and writing large amounts of blob or text data from a database.
See also: TSqlRetCodeClass::ESqlDbError RSqlDatabase RSqlDatabase::LastErrorMessage() RSqlSecurityPolicy RSqlSecurityPolicy::TPolicyType RSqlBlobReadStream RSqlBlobWriteStream TSqlBlob
| Parameter | Description |
|---|---|
| aDatabase | A reference to the RSqlDatabase object that represents the database for which the SQL statement is being prepared. |
| aSqlStmt | A string of 8-bit wide characters containing the SQL statement to be prepared. |
Returns: KErrNone, the SQL statement has been prepared for execution successfully; KErrNoMemory, an out of memory condition has occurred; KErrArgument, the SQL statement is invalid, for example, the supplied string contains more than one SQL statement, or it contains an empty SQL statement. Note that database specific errors categorised as ESqlDbError can also be returned; KSqlErrGeneral, a syntax error has occurred - text describing the problem can be obtained by calling RSqlDatabase::LastErrorMessage(). KErrPermissionDenied, the calling application does not satisfy the relevant database security policies. Note that database specific errors categorised as ESqlDbError, and other system-wide error codes may also be returned.
| IMPORT_C void | PrepareL | ( | RSqlDatabase & | aDatabase, |
| const TDesC & | aSqlStmt | |||
| ) | ||||
Prepares the supplied 16-bit SQL statement for execution.
An RSqlStatement object can prepare and execute a parameterised SQL statement or an SQL statement without parameters.
The function can only deal with one SQL statement at a time, i.e. if you supply more than one SQL statement, each separated by a ";" character, then the function returns an error.
Note that when the statement is to be used to retrieve or write blob or text data that is over 2Mb in size then it is recommended that the RSqlBlobReadStream and RSqlBlobWriteStream classes or the TSqlBlob class is used instead. These classes provide a more RAM-efficient way of reading and writing large amounts of blob or text data from a database.
See also: TSqlRetCodeClass::ESqlDbError RSqlDatabase RSqlDatabase::LastErrorMessage() RSqlSecurityPolicy RSqlSecurityPolicy::TPolicyType RSqlBlobReadStream RSqlBlobWriteStream TSqlBlob
| Parameter | Description |
|---|---|
| aDatabase | A reference to the RSqlDatabase object that represents the database for which the SQL statement is being prepared. |
| aSqlStmt | A string of 16-bit wide characters containing the SQL statement to be prepared. |
| IMPORT_C void | PrepareL | ( | RSqlDatabase & | aDatabase, |
| const TDesC8 & | aSqlStmt | |||
| ) | ||||
Prepares the supplied 8-bit SQL statement for execution.
An RSqlStatement object can prepare and execute a parameterised SQL statement or an SQL statement without parameters.
The function can only deal with one SQL statement at a time, i.e. if you supply more than one SQL statement, each separated by a ";" character, then the function returns an error.
Note that when the statement is to be used to retrieve or write blob or text data that is over 2Mb in size then it is recommended that the RSqlBlobReadStream and RSqlBlobWriteStream classes or the TSqlBlob class is used instead. These classes provide a more RAM-efficient way of reading and writing large amounts of blob or text data from a database.
See also: TSqlRetCodeClass::ESqlDbError RSqlDatabase RSqlDatabase::LastErrorMessage() RSqlSecurityPolicy RSqlSecurityPolicy::TPolicyType RSqlBlobReadStream RSqlBlobWriteStream TSqlBlob
| Parameter | Description |
|---|---|
| aDatabase | A reference to the RSqlDatabase object that represents the database for which the SQL statement is being prepared. |
| aSqlStmt | A string of 8-bit wide characters containing the SQL statement to be prepared. |
| IMPORT_C TInt | Reset | ( | ) |
Resets the prepared SQL statement to its initial state and makes it ready to be executed again.
Any SQL statement parameters that had values bound to them, retain their values.
If this object processes a parameterised SQL statement, then the parameter values can be bound after the call to Reset().
If the call to this function fails because of a database-specific type error (i.e. the error is categorised as of type ESqlDbError), then a textual description of the error can be obtained calling RSqlDatabase::LastErrorMessage().
Usage pattern 1:
RSqlStatement stmt;
TInt err = stmt.Prepare(<database>, <SQL statement>);
while(<condition>)
{
err = stmt.Bind<parameter type>(<parameter index>, <value>);
...
err = stmt.Exec();
....
err = stmt.Reset();
}
stmt.Close();
Usage pattern 2:
RSqlStatement stmt;
TInt err = stmt.Prepare(<database>, <SQL statement>);
while(<condition>)
{
err = stmt.Bind<parameter type>(<parameter index>, <value>);
...
while((err = stmt.Next()) == KSqlAtRow)
{
....
}
err = stmt.Reset();
}
stmt.Close();
See also: TSqlRetCodeClass::ESqlDbError RSqlDatabase::LastErrorMessage()
Returns: KErrNone, the reset operation has completed successfully; KSqlErrStmtExpired, the SQL statement has expired (if new functions or collating sequences have been registered or if an authorizer function has been added or changed)