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.
KMinTInt
KMaxTInt
KNullDesC
KNullDesC8
RSqlBlobReadStream
RSqlBlobWriteStream
TSqlBlob