1 // Copyright (c) 2005-2009 Nokia Corporation and/or its subsidiary(-ies).

     2 // All rights reserved.

     3 // This component and the accompanying materials are made available

     4 // under the terms of "Eclipse Public License v1.0"

     5 // which accompanies this distribution, and is available

     6 // at the URL "http://www.eclipse.org/legal/epl-v10.html".

     7 //

     8 // Initial Contributors:

     9 // Nokia Corporation - initial contribution.

    10 //

    11 // Contributors:

    12 //

    13 // Description:

    14 // SQL Client side API header

    15 // 

    16 //

    17 

    18 /**

    19  @file

    20  @publishedAll

    21  @released

    22 */

    23 #ifndef __SQLDB_H__

    24 #define __SQLDB_H__

    25 

    26 #ifndef __S32STD_H__

    27 #include <s32std.h>	//RReadStream, RWriteStream

    28 #endif

    29 

    30 #ifndef SYMBIAN_ENABLE_SPLIT_HEADERS 

    31 	#include <sqlresourcetester.h>

    32 #endif

    33 

    34 //Forward declarations

    35 class CSqlSecurityPolicy;

    36 class RSqlDatabase;

    37 class CSqlDatabaseImpl;

    38 class RSqlStatement;

    39 class CSqlStatementImpl;

    40 class RSqlColumnReadStream;

    41 class RSqlParamWriteStream;

    42 class TSqlScalarFullSelectQuery;

    43 class RSqlBlob;

    44 class RSqlBlobReadStream;

    45 class RSqlBlobWriteStream;

    46 class TSqlResourceProfiler;

    47 

    48 /**

    49 Used to specify that the ROWID of the most recently inserted record

    50 from the specified database connection should be used as the ROWID 

    51 in a call to directly access a blob.

    52 

    53 @see RSqlBlobReadStream

    54 @see RSqlBlobWriteStream

    55 @see TSqlBlob

    56 

    57 @publishedAll

    58 @released

    59 */

    60 const TInt KSqlLastInsertedRowId = -1;

    61 

    62 /**

    63 A container for the security policies for a shared SQL database.

    64 

    65 The container can contain:

    66 - security policies that apply to the database.

    67 - security policies that apply to individual database objects, i.e. database tables.

    68 

    69 For the database, you use RSqlSecurityPolicy::SetDbPolicy() to apply a separate

    70 security policy to:

    71 - the database schema.

    72 - read activity on the database.

    73 - write activity on the database.

    74 

    75 For database tables, you use RSqlSecurityPolicy::SetPolicy() to apply a separate

    76 security policy to:

    77 - write activity on each named database table.

    78 - read activity on each named database table.

    79 

    80 A client uses a RSqlSecurityPolicy object to create a secure database. It does this by:

    81 - creating a RSqlSecurityPolicy object.

    82 - setting all the appropriate security policies into it.

    83 - passing the object as an argument to RSqlDatabase::Create().

    84 - closing the RSqlSecurityPolicy object on return from RSqlDatabase::Create().

    85 

    86 Once a secure shared database has been created with specific security policies,

    87 these policies are made persistent and cannot be changed during the life of

    88 that database.

    89 

    90 Security policies are encapsulated by TSecurityPolicy objects.

    91 The general usage pattern is to create the security policies container object

    92 (RSqlSecurityPolicy) using a default security policy (TSecurityPolicy), and then

    93 to assign more specific 'overriding' security policies.

    94 

    95 The following code fragment shows how you do this:

    96    

    97 @code

    98 TSecurityPolicy defaultPolicy;

    99 RSqlSecurityPolicy securityPolicy;

   100 RSqlDatabase database;

   101 TInt err;

   102 

   103 // Create security policies container object using a default security policy.

   104 securityPolicy.Create(defaultPolicy); 

   105 

   106 // Set up policy to apply to database schema

   107 // and assign it

   108 TSecurityPolicy schemaPolicy;

   109 ...

   110 err = securityPolicy.SetDbPolicy(RSqlSecurityPolicy::ESchemaPolicy, schemaPolicy);

   111 

   112 // Set up policy to apply to write activity on the database

   113 // and assign it

   114 TSecurityPolicy writePolicy;

   115 ...

   116 err = securityPolicy.SetDbPolicy(RSqlSecurityPolicy::EWritePolicy, writePolicy);

   117 

   118 // Set up policy to apply to write activity to the database table named "Table1"

   119 // and assign it

   120 TSecurityPolicy tablePolicy1;

   121 ...

   122 err = securityPolicy.SetPolicy(RSqlSecurityPolicy::ETable, _L("Table1"), RSqlSecurityPolicy::EWritePolicy, tablePolicy1);

   123 

   124 // Set up policy to apply to read activity to the database table named "Table2"

   125 TSecurityPolicy tablePolicy2;

   126 err = securityPolicy.SetPolicy(RSqlSecurityPolicy::ETable, _L("Table2"), RSqlSecurityPolicy::EReadPolicy, tablePolicy2);

   127 

   128 // Create the database, passing the security policies

   129 err = database.Create(KDatabaseName, securityPolicy);

   130 

   131 // We can close the RSqlSecurityPolicy object.

   132 securityPolicy.Close();

   133 @endcode

   134 

   135 Note that in this example code fragment, the client has not assigned specific

   136 overriding policies for all possible cases; for example, no overriding policy

   137 has been assigned to control read activity on the database, read activity

   138 on "Table1", nor write activity on "Table2".

   139 For these cases, the default security policy will apply.

   140 

   141 A client can also retrieve a database's security policies by calling

   142 RSqlDatabase::GetSecurityPolicy(); this returns a RSqlSecurityPolicy object

   143 containing the security policies. Note that it is the client's responsibility

   144 to close the RSqlSecurityPolicy object when the client no longer needs it. The

   145 following code fragment suggests how you might do this:

   146 

   147 @code

   148 RSqlDatabase database;

   149 RSqlSecurityPolicy securityPolicy;

   150 

   151 // Retrieve the security policies; on return from the call to 

   152 // GetSecurityPolicy(), the RSqlSecurityPolicy object passed 

   153 // to this function will contain the security policies.

   154 database.GetSecurityPolicy(securityPolicy);

   155 ...

   156 // This is the security policy that applies to database schema

   157 TSecurityPolicy schemaPolicy = securityPolicy.DbPolicy(RSqlSecurityPolicy::ESchemaPolicy);

   158 ...

   159 // This is the security policy that applies to write activity to the database

   160 // table named "Table1".

   161 TSecurityPolicy writePolicy = securityPolicy.Policy(RSqlSecurityPolicy::ETable, _L("Table1"), RSqlSecurityPolicy::EWritePolicy);

   162 ...

   163 // Close the RSqlSecurityPolicy object when no longer needed.

   164 securityPolicy.Close();

   165 @endcode

   166 

   167 Note that in the cases where an 'overriding' security policy was not originally assigned,

   168 then the security policy returned will simply be the default security policy.

   169 

   170 @see TSecurityPolicy

   171 @see RSqlDatabase

   172 @see RSqlSecurityPolicy::SetDbPolicy()

   173 @see RSqlSecurityPolicy::SetPolicy()

   174 

   175 @publishedAll

   176 @released

   177 */

   178 class RSqlSecurityPolicy

   179 	{

   180 	friend class RSqlDatabase;

   181 	

   182 public:

   183 	/**

   184 	Defines a set of values that represents the database security policy types.

   185 	Each database security policy type refers to a set of capabilities encapsulated in 

   186 	a TSecurityPolicy object. The TSecurityPolicy object defines what capabilities the calling

   187 	application must have in order to perform partiqular database operation.

   188 	@see TSecurityPolicy

   189 	*/

   190 	enum TPolicyType 

   191 		{

   192 		/**

   193 		Schema database security policy. An application with schema database security policy can 

   194 		modify the database schema, write to database, read from database.

   195 		*/

   196 		ESchemaPolicy, 

   197 		/**

   198 		Read database security policy. An application with read database security policy can 

   199 		read from database.

   200 		*/

   201 		EReadPolicy, 

   202 		/**

   203 		Write database security policy. An application with write database security policy can 

   204 		write to database.

   205 		*/

   206 		EWritePolicy

   207 		};

   208 	/**

   209 	Not currently supported.

   210 

   211 	Defines a set of values that represents the database objects which can be protected by 

   212 	database security policy types.

   213 	*/

   214 	enum TObjectType 

   215 		{

   216 		ETable

   217 		};

   218 	IMPORT_C RSqlSecurityPolicy();

   219 	IMPORT_C TInt Create(const TSecurityPolicy& aDefaultPolicy);

   220 	IMPORT_C void CreateL(const TSecurityPolicy& aDefaultPolicy);

   221 	IMPORT_C void Close();

   222 	IMPORT_C TInt SetDbPolicy(TPolicyType aPolicyType, const TSecurityPolicy& aPolicy);

   223 	IMPORT_C TInt SetPolicy(TObjectType aObjectType, const TDesC& aObjectName, TPolicyType aPolicyType, const TSecurityPolicy& aPolicy);

   224 	IMPORT_C TSecurityPolicy DefaultPolicy() const;

   225 	IMPORT_C TSecurityPolicy DbPolicy(TPolicyType aPolicyType) const;

   226 	IMPORT_C TSecurityPolicy Policy(TObjectType aObjectType, const TDesC& aObjectName, TPolicyType aPolicyType) const;

   227 	

   228 	IMPORT_C void ExternalizeL(RWriteStream& aStream) const;

   229 	IMPORT_C void InternalizeL(RReadStream& aStream);

   230 		

   231 private:	

   232 	void Set(CSqlSecurityPolicy& aImpl);

   233 	CSqlSecurityPolicy& Impl() const;

   234 					

   235 private:

   236 	CSqlSecurityPolicy* iImpl;

   237 	};

   238 	

   239 /**

   240 A handle to a SQL database.

   241 

   242 A RSqlDatabase object is, in effect, a handle to the SQL database. A client can:

   243 - create a SQL database by calling RSqlDatabase::Create().

   244 - open an existing SQL database by calling RSqlDatabase::Open().

   245 - close a SQL database by calling RSqlDatabase::Close().

   246 - copy a SQL database by calling RSqlDatabase::Copy().

   247 - delete a SQL database by calling RSqlDatabase::Delete().

   248 - attach a SQL database to current database connection by calling RSqlDatabase::Attach().

   249 - detach a SQL database from current database connection by calling RSqlDatabase::Detach().

   250 

   251 The RSqlDatabase handles are not thread-safe.

   252 

   253 A client can create either a non-secure database or a secure database,

   254 depending on the variant of RSqlDatabase::Create() that is used.

   255 - a non-secure database is created if the RSqlDatabase::Create(const TDesC&) variant is used.

   256 - a secure database is created if the RSqlDatabase::Create(const TDesC&, const RSqlSecurityPolicy&)

   257 variant is used. In this case, a container containing a collection of security

   258 policies needs to be set up first and passed to this Create() function.

   259 See references to RSqlSecurityPolicy for more information on security policies.

   260 

   261 A client can also specify how it wants a transaction to interact with

   262 other transactions that may be running concurrently. The various ways in which

   263 transactions can interact (i.e. how one transaction can affect another) are

   264 referred to as "transaction isolation levels", and are defined by the values

   265 of the TIsolationLevel enum. A client specifies this by calling RSqlDatabase::SetIsolationLevel().

   266 

   267 Each of the various flavours of Open and Create allows the optional provision of a

   268 configuration string. It is acceptable for this string to be missing.

   269 In the case where the string is missing, the config in the SqlServer.sql file

   270 will be used. If that does not exist then the MMH macro definitions will be used.

   271 

   272 The config string is in the format PARAM=VALUE; PARAM=VALUE;...

   273 

   274 Allowed parameters are:

   275 	cache_size=nnnn

   276 	page_size=nnnn

   277 	encoding=UTF8|UTF16

   278 

   279 Badly formed config strings are reported as KErrArgument

   280 

   281 The string may not exceed 255 characters.

   282 

   283 Please note that a database can only be accessed within the thread where it has been created. It is then not possible

   284 to create a database from thread1 and access it from thread2.

   285 

   286 A client calls RSqlDatabase::Exec() to execute SQL statements.

   287 @see RSqlDatabase::Create()

   288 @see RSqlDatabase::Open()

   289 @see RSqlDatabase::Close()

   290 @see RSqlDatabase::Copy()

   291 @see RSqlDatabase::Delete()

   292 @see RSqlDatabase::Attach()

   293 @see RSqlDatabase::Detach()

   294 @see RSqlDatabase::SetIsolationLevel()

   295 @see RSqlDatabase::Exec()

   296 @see TIsolationLevel

   297 @see RSqlSecurityPolicy

   298 

   299 @publishedAll

   300 @released

   301 */

   302 class RSqlDatabase

   303 	{

   304 	friend class RSqlStatement;

   305 	friend class TSqlScalarFullSelectQuery;

   306 	friend class RSqlBlob;

   307 	friend class RSqlBlobReadStream;

   308 	friend class RSqlBlobWriteStream;

   309 	friend class TSqlResourceProfiler;

   310 	

   311 public:

   312 	/**

   313 	Defines a set of values that represents the transaction isolation level.

   314 	

   315 	A transaction isolation level defines the way in which a transaction

   316 	interacts with other transactions that may be in progress concurrently.

   317 	

   318 	A client sets the transaction isolation level by calling SetIsolationLevel()

   319 	

   320 	@see RSqlDatabase::SetIsolationLevel()

   321 	*/

   322 	enum TIsolationLevel 

   323 		{

   324 		/**

   325 		A transaction can read uncommitted data, i.e. data that is being changed

   326 		by another transaction, which is still in progress.

   327 		

   328 		This means that

   329 		- a 'database read' transaction will not block 'database write' transactions

   330 		being performed by different database connections on the same shared database.

   331 		- a 'database read' transaction will not be blocked by 'database write'

   332 		transactions performed by the same database connection.

   333 		- concurrent 'database write' transactions are prevented.

   334 		

   335 		This transaction isolation level can be set at any time during

   336 		the lifetime of the database.

   337 		

   338 		@see TIsolationLevel

   339 		@see RSqlDatabase::SetIsolationLevel()

   340 		*/

   341 		EReadUncommitted, 

   342 

   343 		/**

   344 		Not currently supported.

   345 		

   346 		A transaction cannot read uncommitted data. "Dirty reads" are prevented.

   347 		

   348 		"Dirty read" is a data inconsistency type which can be described with the following example:

   349 		- Transaction A updates TableA.Column1 value from 1 to 2;

   350 		- Transaction B reads TableA.Column1 value;

   351 		- Transaction A rolls back and restores the original value of TableA.Column1 (1);

   352 		- Transaction B ends showing that TableA.Column1 value is 2, even though, logically and transactionally, 

   353 		  this data never really even existed in the database because Transaction A never committed that change 

   354 		  to the database;

   355 		

   356 		@see TIsolationLevel

   357 		@see RSqlDatabase::SetIsolationLevel()

   358 		*/

   359 		EReadCommitted, 

   360 		

   361 		/**

   362 		Not currently supported.

   363 		

   364 		A transaction cannot change data that is being read by a different transaction. 

   365 		"Dirty reads" and "non-repeatable reads" are prevented.

   366 

   367 		"Non-repeatable reads" is a data inconsistency type which can be described with the following example:

   368 		- Transaction A reads TableA.Column1 value which is 1;

   369 		- Transaction B updates TableA.Column1 value from 1 to 2;

   370 		- Transaction B commits the chages;

   371 		- Transaction A reads TableA.Column1 value again. Transaction A has inconsistent data because TableA.Column1 

   372 		  value now is 2 instead of 1, all within the scope of the same Transaction A;

   373 		

   374 		@see TIsolationLevel

   375 		@see RSqlDatabase::SetIsolationLevel()

   376 		*/

   377 		ERepeatableRead, 

   378 		

   379 		/**

   380 		Any number of 'database read' transactions can be performed concurrently

   381 		by different database connections on the same shared database.

   382 		

   383 		Only one 'database write' transaction can be performed at any one time. If a

   384 		'database write' transaction is in progress, then any attempt to start

   385 		another 'database read' or 'database write' transaction will be blocked

   386 		until the first 'database write' transaction has completed.

   387 		

   388 		This is the default isolation level, if no isolation level is

   389 		explicitly set.

   390 		

   391 		"Dirty reads", "non-repeatable" reads and "phantom reads" are prevented.

   392 		

   393 		"Phantom reads" is a data inconsistency type which can be described with the following example:

   394 		- Transaction A reads all rows that have Column1 = 1;

   395 		- Transaction B inserts a new row which has Column1 = 1;

   396 		- Transaction B commits;

   397 		- Transaction A updates all rows that have Column1 = 1. This will also update the row that 

   398 		  Transaction B inserted, because Transaction A must read the data again in order to update it.

   399 		- Transaction A commits;

   400 		

   401 		@see TIsolationLevel

   402 		@see RSqlDatabase::SetIsolationLevel()

   403 		*/

   404 		ESerializable

   405 		};

   406 	/**

   407 	This structure is used for retrieving the database size and database free space.

   408 	@see RSqlDatabase::Size(TSize&)

   409 	*/

   410 	struct TSize

   411 		{

   412 		/** The database size in bytes*/

   413 		TInt64	iSize;

   414 		/** The database free space in bytes*/

   415 		TInt64	iFree;

   416 		};

   417 

   418 	/** If this value is used as an argument of RSqlDatabase::Compact() (aSize argument), then all free space will be removed */

   419 	enum {EMaxCompaction = -1};

   420 		

   421 	IMPORT_C RSqlDatabase();

   422 	

   423 	IMPORT_C TInt Create(const TDesC& aDbFileName, const TDesC8* aConfig=NULL);

   424 	IMPORT_C TInt Create(const TDesC& aDbFileName,

   425 			const RSqlSecurityPolicy& aSecurityPolicy, const TDesC8* aConfig=NULL);

   426 	IMPORT_C TInt Open(const TDesC& aDbFileName, const TDesC8* aConfig=NULL);

   427 	IMPORT_C void CreateL(const TDesC& aDbFileName, const TDesC8* aConfig=NULL);

   428 	IMPORT_C void CreateL(const TDesC& aDbFileName,

   429 			const RSqlSecurityPolicy& aSecurityPolicy, const TDesC8* aConfig=NULL);

   430 	IMPORT_C void OpenL(const TDesC& aDbFileName, const TDesC8* aConfig=NULL);

   431 	

   432 	IMPORT_C void Close();

   433 	

   434 	IMPORT_C TInt Attach(const TDesC& aDbFileName, const TDesC& aDbName);

   435 	IMPORT_C TInt Detach(const TDesC& aDbName);

   436 	

   437 	IMPORT_C static TInt Copy(const TDesC& aSrcDbFileName, const TDesC& aDestDbFileName);

   438 	IMPORT_C static TInt Delete(const TDesC& aDbFileName);

   439 	

   440 	IMPORT_C TInt GetSecurityPolicy(RSqlSecurityPolicy& aSecurityPolicy) const;

   441 	IMPORT_C void GetSecurityPolicyL(RSqlSecurityPolicy& aSecurityPolicy) const;

   442 	

   443 	IMPORT_C TInt SetIsolationLevel(TIsolationLevel aIsolationLevel);

   444 	

   445 	IMPORT_C TInt Exec(const TDesC& aSqlStmt);

   446 	IMPORT_C TInt Exec(const TDesC8& aSqlStmt);

   447 	

   448 	IMPORT_C void Exec(const TDesC& aSqlStmt, TRequestStatus& aStatus);

   449 	IMPORT_C void Exec(const TDesC8& aSqlStmt, TRequestStatus& aStatus);

   450 	

   451 	IMPORT_C TPtrC LastErrorMessage() const;

   452 	IMPORT_C TInt64 LastInsertedRowId() const; 

   453 	

   454 	IMPORT_C TBool InTransaction() const;

   455 	IMPORT_C TInt Size() const;

   456 	IMPORT_C TInt Size(TSize& aSize, const TDesC& aDbName = KNullDesC) const;

   457 

   458 	IMPORT_C TInt Compact(TInt64 aSize, const TDesC& aDbName = KNullDesC);

   459 	IMPORT_C void Compact(TInt64 aSize, TRequestStatus& aStatus, const TDesC& aDbName = KNullDesC);

   460 	

   461 	IMPORT_C TInt ReserveDriveSpace(TInt aSize);

   462 	IMPORT_C void FreeReservedSpace();

   463 	IMPORT_C TInt GetReserveAccess();

   464 	IMPORT_C void ReleaseReserveAccess();

   465 	

   466 private:

   467 	CSqlDatabaseImpl& Impl() const;

   468 

   469 private:

   470 	CSqlDatabaseImpl* iImpl;

   471 	};

   472 

   473 /**

   474 TSqlScalarFullSelectQuery interface is used for executing SELECT sql queries, which 

   475 return a single row consisting of a single column value.

   476 

   477 Examples.

   478 

   479 CASE 1 - retrieving records count of a table:

   480 @code

   481 RSqlDatabase db;

   482 //initialize db object....

   483 .......

   484 TSqlScalarFullSelectQuery fullSelectQuery(db);

   485 TInt recCnt = fullSelectQuery.SelectIntL(_L("SELECT COUNT(*) FROM PersonTbl"));

   486 @endcode

   487 

   488 CASE 2 - retrieving specific column value using a condition in the SELECT statement:

   489 @code

   490 RSqlDatabase db;

   491 //initialize db object....

   492 .......

   493 TSqlScalarFullSelectQuery fullSelectQuery(db);

   494 TInt personId = fullSelectQuery.SelectIntL(_L("SELECT ID FROM PersonTbl WHERE Name = 'John'"));

   495 @endcode

   496 

   497 CASE 3 - retrieving a text column value, the receiving buffer is not big enough:

   498 @code

   499 RSqlDatabase db;

   500 //initialize db object....

   501 .......

   502 TSqlScalarFullSelectQuery fullSelectQuery(db);

   503 HBufC* buf = HBufC::NewLC(20);

   504 TPtr name = buf->Des();

   505 TInt rc = fullSelectQuery.SelectTextL(_L("SELECT Name FROM PersonTbl WHERE Id = 1"), name);

   506 TEST(rc >= 0); //the function may return only non-negative values

   507 if(rc > 0)

   508 	{

   509 	buf = buf->ReAllocL(rc);

   510 	CleanupStack::Pop();	

   511 	CleanupStack::PushL(buf);

   512 	name.Set(buf->Des());

   513 	rc = fullSelectQuery.SelectTextL(_L("SELECT Name FROM PersonTbl WHERE Id = 1"), name);

   514 	TEST(rc == 0);

   515 	}

   516 CleanupStack::PopAndDestroy();//buf

   517 @endcode

   518 

   519 @see RSqlDatabase

   520 

   521 @publishedAll

   522 @released

   523 */

   524 class TSqlScalarFullSelectQuery

   525 	{

   526 public:

   527 	IMPORT_C TSqlScalarFullSelectQuery();

   528 	IMPORT_C TSqlScalarFullSelectQuery(RSqlDatabase& aDatabase);

   529 	IMPORT_C void SetDatabase(RSqlDatabase& aDatabase);

   530 

   531 	IMPORT_C TInt SelectIntL(const TDesC& aSqlStmt);

   532 	IMPORT_C TInt64 SelectInt64L(const TDesC& aSqlStmt);

   533 	IMPORT_C TReal SelectRealL(const TDesC& aSqlStmt);

   534 	IMPORT_C TInt SelectTextL(const TDesC& aSqlStmt, TDes& aDest);

   535 	IMPORT_C TInt SelectBinaryL(const TDesC& aSqlStmt, TDes8& aDest);

   536 

   537 	IMPORT_C TInt SelectIntL(const TDesC8& aSqlStmt);

   538 	IMPORT_C TInt64 SelectInt64L(const TDesC8& aSqlStmt);

   539 	IMPORT_C TReal SelectRealL(const TDesC8& aSqlStmt);

   540 	IMPORT_C TInt SelectTextL(const TDesC8& aSqlStmt, TDes& aDest);

   541 	IMPORT_C TInt SelectBinaryL(const TDesC8& aSqlStmt, TDes8& aDest);

   542 	

   543 private:

   544 	inline CSqlDatabaseImpl& Impl() const;

   545 	

   546 private:	

   547 	CSqlDatabaseImpl* iDatabaseImpl;

   548 	};

   549 

   550 /**

   551 An enumeration whose values represent the supported database column types.

   552 

   553 

   554 @see RSqlStatement::ColumnType()

   555 

   556 @publishedAll

   557 @released

   558 */

   559 enum TSqlColumnType 

   560 	{

   561 	/**

   562 	Null column value.

   563 	*/

   564 	ESqlNull,

   565 	

   566  	/**

   567  	32-bit integer column value.

   568  	*/  

   569 	ESqlInt, 

   570 	

   571  	/**

   572  	64-bit integer column value.

   573  	*/

   574 	ESqlInt64, 

   575 	

   576 	/**

   577 	64-bit floating point column value.

   578 	*/

   579 	ESqlReal, 

   580 	

   581 	/**

   582 	Unicode text, a sequence of 16-bit character codes.

   583 	*/

   584 	ESqlText, 

   585 	

   586 	/**

   587 	Binary data, a sequence of bytes.

   588 	*/

   589 	ESqlBinary

   590 	};

   591 

   592 /**

   593 Represents an SQL statement.

   594 

   595 An object of this type can be used to execute all types of SQL statements; this

   596 includes SQL statements with parameters.

   597 

   598 If a SELECT statament is passed to RSqlStatement::Prepare(), then the returned record set 

   599 is forward only, non-updateable.

   600 

   601 There are a number of ways that this object is used; here are some examples.

   602 

   603 CASE 1 - the execution of a SQL statement, which does not return record set:

   604 

   605 @code

   606 RSqlDatabase database;

   607 .........

   608 RSqlStatement stmt;

   609 TInt err = stmt.Prepare(database, _L("INSERT INTO Tbl1(Fld1) VALUES(:Val)"));

   610 TInt paramIndex = stmt.ParameterIndex(_L(":Val"));

   611 for(TInt i=1;i<=10;++i)

   612 	{

   613 	err = stmt.BindInt(paramIndex, i);

   614 	err = stmt.Exec();

   615 	err = stmt.Reset();

   616 	}

   617 stmt.Close();

   618 @endcode

   619 

   620 The following pseudo code shows the general pattern:

   621 

   622 @code

   623 <RSqlStatement::Prepare()>

   624 [begin:]

   625 <RSqlStatement::Bind<param_type>()>

   626 <RSqlStatement::Exec()>

   627 [<RSqlStatement::Reset()>]

   628 [<RSqlStatement::Bind<param_type>()>]

   629 [<Goto :begin>]

   630 @endcode

   631 

   632 CASE 2 - the execution of a SQL statement, which returns a record set:

   633 

   634 @code

   635 RSqlDatabase database;

   636 .........

   637 RSqlStatement stmt;

   638 TInt err = stmt.Prepare(database, _L("SELECT Fld1 FROM Tbl1 WHERE Fld1 > :Val"));

   639 TInt paramIndex = stmt.ParameterIndex(_L(":Val"));

   640 err = stmt.BindInt(paramIndex, 5);

   641 TInt columnIndex = stmt.ColumnIndex(_L("Fld1"));

   642 while((err = stmt.Next()) == KSqlAtRow)

   643 	{

   644 	TInt val = stmt.ColumnInt(columnIndex);

   645 	RDebug::Print(_L("val=%d\n"), val);

   646 	}

   647 if(err == KSqlAtEnd)

   648 	<OK - no more records>;

   649 else

   650 	<process the error>;

   651 stmt.Close();

   652 @endcode

   653 

   654 The following pseudo code shows the general pattern:

   655 

   656 @code

   657 <RSqlStatement::Prepare()>

   658 [begin:]

   659 <while (RSqlStatement::Next() == KSqlAtRow)>

   660 	<do something with the records>

   661 if(err == KSqlAtEnd)

   662 	<OK - no more records>;

   663 else

   664 	<process the error>;

   665 [<RSqlStatement::Reset()>]

   666 [<RSqlStatement::Bind<param_type>()>]

   667 [<Goto begin>]

   668 @endcode

   669 

   670 CASE 3.1 - SELECT statements: large column data processing, where the data is

   671 copied into a buffer supplied by the client:

   672 

   673 @code

   674 RSqlDatabase database;

   675 .........

   676 RSqlStatement stmt;

   677 TInt err = stmt.Prepare(database, _L("SELECT BinaryField FROM Tbl1"));

   678 TInt columnIndex = stmt.ColumnIndex(_L("BinaryField"));

   679 while((err = stmt.Next()) == KSqlAtRow)

   680 	{

   681 	TInt size = stmt. ColumnSize(columnIndex);

   682 	HBufC8* buf = HBufC8::NewL(size);

   683 	err = stmt.ColumnBinary(columnIndex, buf->Ptr());

   684 	<do something with the data>;

   685 	delete buf;

   686 	}

   687 if(err == KSqlAtEnd)

   688 	<OK - no more records>;

   689 else

   690 	<process the error>;

   691 stmt.Close();

   692 @endcode

   693 

   694 CASE 3.2 - SELECT statements: large column data processing, where the data is

   695 accessed by the client without copying:

   696 

   697 @code

   698 RSqlDatabase database;

   699 .........

   700 RSqlStatement stmt;

   701 TInt err = stmt.Prepare(database, _L("SELECT BinaryField FROM Tbl1"));

   702 TInt columnIndex = stmt.ColumnIndex(_L("BinaryField"));

   703 while((err = stmt.Next()) == KSqlAtRow)

   704 	{

   705 	TPtrC8 data = stmt.ColumnBinaryL(columnIndex);

   706 	<do something with the data>;

   707 	}

   708 if(err == KSqlAtEnd)

   709 	<OK - no more records>;

   710 else

   711 	<process the error>;

   712 stmt.Close();

   713 @endcode

   714 

   715 CASE 3.3 - SELECT statements, large column data processing (the data is accessed by 

   716 the client without copying), leaving-safe processing:

   717 

   718 @code

   719 RSqlDatabase database;

   720 .........

   721 RSqlStatement stmt;

   722 TInt err = stmt.Prepare(database, _L("SELECT BinaryField FROM Tbl1"));

   723 TInt columnIndex = stmt.ColumnIndex(_L("BinaryField"));

   724 while((err = stmt.Next()) == KSqlAtRow)

   725 	{

   726 	TPtrC8 data;

   727 	TInt err = stmt.ColumnBinary(columnIndex, data);

   728 	if(err == KErrNone)

   729 		{

   730 		<do something with the data>;

   731 		}

   732 	}

   733 if(err == KSqlAtEnd)

   734 	<OK - no more records>;

   735 else

   736 	<process the error>;

   737 stmt.Close();

   738 @endcode

   739 

   740 CASE 3.4 - SELECT statements: large column data processing, where the data is

   741 accessed by the client using a stream:

   742 

   743 @code

   744 RSqlDatabase database;

   745 .........

   746 RSqlStatement stmt;

   747 TInt err = stmt.Prepare(database, _L("SELECT BinaryField FROM Tbl1"));

   748 TInt columnIndex = stmt.ColumnIndex(_L("BinaryField"));

   749 while((err = stmt.Next()) == KSqlAtRow)

   750 	{

   751 	RSqlColumnReadStream stream;

   752 	err = stream.ColumnBinary(stmt, columnIndex);

   753 	<do something with the data in the stream>;

   754 	stream.Close();

   755 	}

   756 if(err == KSqlAtEnd)

   757 	<OK - no more records>;

   758 else

   759 	<process the error>;

   760 stmt.Close();

   761 @endcode

   762 

   763 CASE 4 - the execution of a SQL statement with parameter(s), some of which may

   764 be large text or binary values:

   765 

   766 @code

   767 RSqlDatabase database;

   768 .........

   769 RSqlStatement stmt;

   770 TInt err = 

   771 	stmt.Prepare(database, _L("UPDATE Tbl1 SET LargeTextField = :LargeTextVal WHERE IdxField = :KeyVal"));

   772 TInt paramIndex1 = stmt.ParameterIndex(_L(":LargeTextVal"));

   773 TInt paramIndex2 = stmt.ParameterIndex(_L(":KeyVal"));

   774 for(TInt i=1;i<=10;++i)

   775 	{

   776 	RSqlParamWriteStream stream;

   777 	err = stream.BindText(stmt, paramIndex1);

   778 	<insert large text data into the stream>;

   779 	stream.Close();

   780 	err = stmt.BindInt(paramIndex2, i);

   781 	err = stmt.Exec();

   782 	stmt.Reset();

   783 	}

   784 stmt.Close();

   785 @endcode

   786 

   787 The following table shows what is returned when the caller uses a specific

   788 column data retrieving function on a specific column type.

   789 

   790 @code

   791 --------------------------------------------------------------------------------

   792 Column type | ColumnInt() ColumnInt64() ColumnReal() ColumnText() ColumnBinary()

   793 --------------------------------------------------------------------------------

   794 Null........|.0...........0.............0.0..........KNullDesC....KNullDesC8

   795 Int.........|.Int.........Int64.........Real.........KNullDesC....KNullDesC8 

   796 Int64.......|.clamp.......Int64.........Real.........KNullDesC....KNullDesC8 

   797 Real........|.round.......round.........Real.........KNullDesC....KNullDesC8 

   798 Text........|.0...........0.............0.0..........Text.........KNullDesC8   

   799 Binary......|.0...........0.............0.0..........KNullDesC....Binary

   800 --------------------------------------------------------------------------------

   801 @endcode

   802 Note the following definitions:

   803 - "clamp": return KMinTInt or KMaxTInt if the value is outside the range that can be 

   804 represented by the type returned by the accessor function.

   805 - "round": the floating point value will be rounded up to the nearest integer.

   806 If the result is outside the range that can be represented by the type returned

   807 by the accessor function, then it will be clamped.

   808 

   809 Note that when handling blob and text data over 2Mb in size it is recommended that the 

   810 RSqlBlobReadStream and RSqlBlobWriteStream classes or the TSqlBlob class is used instead. 

   811 These classes provide a more RAM-efficient way of reading and writing large amounts of 

   812 blob or text data from a database.

   813 

   814 @see KMinTInt

   815 @see KMaxTInt

   816 @see KNullDesC

   817 @see KNullDesC8

   818 @see RSqlBlobReadStream

   819 @see RSqlBlobWriteStream

   820 @see TSqlBlob

   821 

   822 @publishedAll

   823 @released

   824 */

   825 class RSqlStatement

   826 	{

   827 	friend class RSqlColumnReadStream;

   828 	friend class RSqlParamWriteStream;

   829 

   830 public:

   831 	IMPORT_C RSqlStatement();

   832 	IMPORT_C TInt Prepare(RSqlDatabase& aDatabase, const TDesC& aSqlStmt);

   833 	IMPORT_C TInt Prepare(RSqlDatabase& aDatabase, const TDesC8& aSqlStmt);

   834 	IMPORT_C void PrepareL(RSqlDatabase& aDatabase, const TDesC& aSqlStmt);

   835 	IMPORT_C void PrepareL(RSqlDatabase& aDatabase, const TDesC8& aSqlStmt);

   836 	IMPORT_C void Close();

   837 	IMPORT_C TBool AtRow() const;

   838 	IMPORT_C TInt Reset();

   839 	IMPORT_C TInt Exec();

   840 	IMPORT_C void Exec(TRequestStatus& aStatus);

   841 	IMPORT_C TInt Next();

   842 	

   843 	IMPORT_C TInt ParameterIndex(const TDesC& aParameterName) const;

   844 	IMPORT_C TInt ColumnCount() const;

   845 	IMPORT_C TInt ColumnIndex(const TDesC& aColumnName) const;

   846 	IMPORT_C TSqlColumnType ColumnType(TInt aColumnIndex) const;

   847 	IMPORT_C TInt DeclaredColumnType(TInt aColumnIndex, TSqlColumnType& aColumnType) const;

   848 	IMPORT_C TInt ColumnSize(TInt aColumnIndex) const;

   849 	

   850 	IMPORT_C TInt BindNull(TInt aParameterIndex);

   851 	IMPORT_C TInt BindInt(TInt aParameterIndex, TInt aParameterValue);

   852 	IMPORT_C TInt BindInt64(TInt aParameterIndex, TInt64 aParameterValue);

   853 	IMPORT_C TInt BindReal(TInt aParameterIndex, TReal aParameterValue);

   854 	IMPORT_C TInt BindText(TInt aParameterIndex, const TDesC& aParameterText);

   855 	IMPORT_C TInt BindBinary(TInt aParameterIndex, const TDesC8& aParameterData);

   856 	IMPORT_C TInt BindZeroBlob(TInt aParameterIndex, TInt aBlobSize);

   857 	

   858 	IMPORT_C TBool IsNull(TInt aColumnIndex) const;

   859 	IMPORT_C TInt ColumnInt(TInt aColumnIndex) const;

   860 	IMPORT_C TInt64 ColumnInt64(TInt aColumnIndex) const;

   861 	IMPORT_C TReal ColumnReal(TInt aColumnIndex) const;

   862 	

   863 	IMPORT_C TPtrC ColumnTextL(TInt aColumnIndex) const;

   864 	IMPORT_C TInt ColumnText(TInt aColumnIndex, TPtrC& aPtr) const;

   865 	IMPORT_C TInt ColumnText(TInt aColumnIndex, TDes& aDest) const;

   866 	

   867 	IMPORT_C TPtrC8 ColumnBinaryL(TInt aColumnIndex) const;

   868 	IMPORT_C TInt ColumnBinary(TInt aColumnIndex, TPtrC8& aPtr) const;

   869 	IMPORT_C TInt ColumnBinary(TInt aColumnIndex, TDes8& aDest) const;

   870 	

   871 	IMPORT_C TInt ColumnName(TInt aColumnIndex, TPtrC& aNameDest);

   872 	IMPORT_C TInt ParameterName(TInt aParameterIndex, TPtrC& aNameDest);

   873 	IMPORT_C TInt ParamName(TInt aParameterIndex, TPtrC& aNameDest);

   874 private:

   875 	CSqlStatementImpl& Impl() const;

   876 	

   877 private:

   878 	CSqlStatementImpl* 	iImpl;

   879 	

   880 	};

   881 

   882 /**

   883 The read stream interface.

   884 

   885 The class is used for reading the content of a column containing either 

   886 binary data or text data.

   887 

   888 The class derives from RReadStream, which means that all RReadStream public

   889 member functions and predefined stream operators \>\> can be used to deal

   890 with column data.

   891 

   892 If the blob or text data is over 2Mb in size then it is recommended that the 

   893 RSqlBlobReadStream or TSqlBlob class is used instead. These classes provide 

   894 a more RAM-efficient way of reading large amounts of blob or text data from

   895 a database.

   896 

   897 The following two cases are typical:

   898 

   899 CASE 1 - processing large binary column data.

   900 

   901 @code

   902 RSqlDatabase db;

   903 <open/create "db" object>;

   904 RSqlStatement stmt;

   905 <prepare "stmt" object>;

   906 TInt rc = stmt.Next();

   907 if(rc == KSqlAtRow)

   908 	{

   909 	RSqlColumnReadStream colStream;

   910 	CleanupClosePushL(colStream);

   911 	User::LeaveIfError(colStream.ColumnBinary(stmt, <column_number>));

   912 	TInt size = stmt.ColumnSize(<column_number>);

   913 	//read the column data in a buffer ("buf" variable).

   914 	//(or the column data can be retrieved in a smaller portions)

   915 	colStream.ReadL(buf, size);

   916 	//Close the stream

   917 	CleanupStack::PopAndDestroy(&colStream);

   918 	}

   919 else

   920 	{

   921 	...

   922 	}

   923 @endcode

   924 

   925 CASE 2 - processing large text column data.

   926 

   927 @code

   928 RSqlDatabase db;

   929 <open/create "db" object>;

   930 RSqlStatement stmt;

   931 <prepare "stmt" object>;

   932 TInt rc = stmt.Next();

   933 if(rc == KSqlAtRow)

   934 	{

   935 	RSqlColumnReadStream colStream;

   936 	CleanupClosePushL(colStream);

   937 	User::LeaveIfError(colStream.ColumnText(stmt, <column_number>));

   938 	TInt size = stmt.ColumnSize(<column_number>);

   939 	//read the column data in a buffer ("buf" variable).

   940 	//(or the column data can be retrieved in a smaller portions)

   941 	colStream.ReadL(buf, size);

   942 	//Close the stream

   943 	CleanupStack::PopAndDestroy(&colStream);

   944 	}

   945 else

   946 	{

   947 	...

   948 	}

   949 @endcode

   950 

   951 @see RSqlBlobReadStream

   952 @see TSqlBlob

   953 

   954 @publishedAll

   955 @released

   956 */

   957 class RSqlColumnReadStream : public RReadStream

   958 	{

   959 public:	

   960 	IMPORT_C TInt ColumnText(RSqlStatement& aStmt, TInt aColumnIndex);

   961 	IMPORT_C TInt ColumnBinary(RSqlStatement& aStmt, TInt aColumnIndex);

   962 	IMPORT_C void ColumnTextL(RSqlStatement& aStmt, TInt aColumnIndex);

   963 	IMPORT_C void ColumnBinaryL(RSqlStatement& aStmt, TInt aColumnIndex);

   964 

   965 	};

   966 

   967 /**

   968 The write stream interface.

   969 

   970 The class is used to set binary data or text data into a parameter. 

   971 This is a also known as binding a parameter.

   972 

   973 The class derives from RWriteStream, which means that all RWriteStream public

   974 member functions and predefined stream operators \<\< can be used to deal with

   975 the parameter data.

   976 

   977 If the blob or text data is over 2Mb in size then it is recommended that the 

   978 RSqlBlobWriteStream or TSqlBlob class is used instead. These classes provide 

   979 a more RAM-efficient way of writing large amounts of blob or text data to

   980 a database.

   981 

   982 The following two cases are typical:

   983 

   984 CASE 1 - binding a large binary parameter.

   985 

   986 @code

   987 RSqlDatabase db;

   988 <open/create "db" object>;

   989 RSqlStatement stmt;

   990 <prepare "stmt" object>;//The SQL statement references large binary parameter

   991 RSqlParamWriteStream paramStream;

   992 CleanupClosePushL(paramStream);

   993 User::LeaveIfError(paramStream.BindBinary(stmt, <parameter_number>));

   994 //Write out the parameter data

   995 paramStream.WriteL(..);

   996 paramStream << <data>;

   997 ...

   998 //Commit the stream

   999 paramStream.CommitL();

  1000 //Continue with the statement processing issuing Next() or Exec().

  1001 TInt rc = stmt.Next();//rc = stmt.Exec()

  1002 //Close the stream

  1003 CleanupStack::PopAndDestroy(&paramStream);

  1004 @endcode

  1005 

  1006 CASE 2 - binding a large text parameter.

  1007 

  1008 @code

  1009 RSqlDatabase db;

  1010 <open/create "db" object>;

  1011 RSqlStatement stmt;

  1012 <prepare "stmt" object>;//The SQL statement references large text parameter

  1013 RSqlParamWriteStream paramStream;

  1014 CleanupClosePushL(paramStream);

  1015 User::LeaveIfError(paramStream.BindText(stmt, <parameter_number>));

  1016 //Write out the parameter data

  1017 paramStream.WriteL(..);

  1018 paramStream << <data>;

  1019 ...

  1020 //Commit the stream

  1021 paramStream.CommitL();

  1022 //Continue with the statement processing issuing Next() or Exec().

  1023 TInt rc = stmt.Next();//rc = stmt.Exec()

  1024 //Close the stream

  1025 CleanupStack::PopAndDestroy(&paramStream);

  1026 @endcode

  1027 

  1028 @see RSqlBlobWriteStream

  1029 @see TSqlBlob

  1030 

  1031 @publishedAll

  1032 @released

  1033 */

  1034 class RSqlParamWriteStream : public RWriteStream

  1035 	{

  1036 public:	

  1037 	IMPORT_C TInt BindText(RSqlStatement& aStmt, TInt aParameterIndex);

  1038 	IMPORT_C TInt BindBinary(RSqlStatement& aStmt, TInt aParameterIndex);

  1039 	IMPORT_C void BindTextL(RSqlStatement& aStmt, TInt aParameterIndex);

  1040 	IMPORT_C void BindBinaryL(RSqlStatement& aStmt, TInt aParameterIndex);

  1041 

  1042 	};

  1043 

  1044 /**

  1045 A direct handle to a blob, used for reading the content of the blob via a streaming interface.

  1046 

  1047 The target blob is identified using the relevant database connection, table name, 

  1048 column name and ROWID of the record to which the blob belongs (also the attached

  1049 database name if the blob is contained in an attached database).

  1050 

  1051 A blob in this context refers to the content of a BLOB or TEXT column, 

  1052 and a read handle can be opened on both types of column.

  1053 For TEXT columns it is important to note that no conversions are performed on 

  1054 data retrieved using this class - the data is returned as a stream of bytes.

  1055 

  1056 The class derives from RReadStream and provides all of its streaming methods.

  1057 The SizeL() method can be used to check the total size of the blob, in bytes.

  1058 

  1059 It is strongly recommended to use this class for reading the content of large blobs 

  1060 because it significantly reduces the amount of RAM that is used when compared to using the 

  1061 RSqlColumnReadStream, RSqlStatement::ColumnBinary(L) or RSqlStatement::ColumnText(L) APIs.

  1062 

  1063 Specifically, it is recommended to use this class for blobs over 2Mb in size.

  1064 Indeed, in some circumstances where very large blobs are in use it may be impossible

  1065 to read the blob content using the legacy APIs (due to the server's finite RAM capacity), 

  1066 and this class may provide the only way to access the data.

  1067 

  1068 The following code illustrates typical use cases of this class:

  1069 

  1070 CASE 1 - reading large blob data from the last inserted record.

  1071 

  1072 @code

  1073 RSqlDatabase db;

  1074 CleanupClosePushL(db);

  1075 <open/create "db" object>;

  1076 RSqlBlobReadStream rdStrm;

  1077 CleanupClosePushL(rdStrm);

  1078 rdStrm.OpenL(db, <table_name>, <column_name>);

  1079 HBufC8* buffer = HBufC8::NewLC(KBlockSize);

  1080 TPtr8 bufPtr(buffer->Des());

  1081 TInt size = rdStrm.SizeL();

  1082 while(size)

  1083 	{

  1084 	TInt bytesToRead = (size >= KBlockSize) ? KBlockSize : size ;

  1085 	rdStrm.ReadL(bufPtr, bytesToRead); // read the next block of data		

  1086 	<do something with the block of data>

  1087 	size =- bytesToRead;

  1088 	}

  1089 CleanupStack::PopAndDestroy(3); // buffer, rdStrm, db

  1090 @endcode

  1091 

  1092 CASE 2 - reading large blob data from a selection of records.

  1093 

  1094 @code

  1095 RSqlDatabase db;

  1096 CleanupClosePushL(db);

  1097 <open/create "db" object>;

  1098 RSqlStatement stmt;

  1099 CleanupClosePushL(stmt);

  1100 <prepare "stmt" object to SELECT the ROWIDs of a collection of blob objects>;

  1101 TInt rc = 0;

  1102 while((rc = stmt.Next()) == KSqlAtRow)

  1103 	{

  1104 	TInt64 rowid = stmt.ColumnInt64(0);	

  1105 	RSqlBlobReadStream rdStrm;

  1106 	CleanupClosePushL(rdStrm);

  1107 	rdStrm.OpenL(db, <table_name>, <column_name>, rowid);

  1108 	

  1109 	HBufC8* buffer = HBufC8::NewLC(KBlockSize);

  1110 	TPtr8 bufPtr(buffer->Des());

  1111 	TInt size = rdStrm.SizeL();

  1112 	while(size)

  1113 		{

  1114 		TInt bytesToRead = (size >= KBlockSize) ? KBlockSize : size ;

  1115 		rdStrm.ReadL(bufPtr, bytesToRead); // read the next block of data		

  1116 		<do something with the block of data>

  1117 		size =- bytesToRead;

  1118 		}

  1119 	CleanupStack::PopAndDestroy(2); // buffer, rdStrm

  1120 	}

  1121 CleanupStack::PopAndDestroy(2); // stmt, db

  1122 @endcode

  1123 

  1124 @see RSqlBlobWriteStream

  1125 @see RSqlDatabase::LastInsertedRowId()

  1126 

  1127 @publishedAll

  1128 @released

  1129 */

  1130 class RSqlBlobReadStream : public RReadStream

  1131 	{

  1132 public:						

  1133 	IMPORT_C void OpenL(RSqlDatabase& aDb, const TDesC& aTableName, const TDesC& aColumnName, 

  1134 						TInt64 aRowId = KSqlLastInsertedRowId, const TDesC& aDbName = KNullDesC);