1 // Copyright (c) 2007-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 //

    15 

    16 #include <e32test.h>

    17 #include <e32math.h>

    18 #include <bautils.h>

    19 #include <s32buf.h>				//MStreamBuf

    20 #include <sqldb.h>

    21 #include "SqlResourceProfiler.h"

    22 

    23 ///////////////////////////////////////////////////////////////////////////////////////

    24 

    25 RTest TheTest(_L("t_sqlapi2 test"));

    26 RSqlDatabase TheDb;

    27 RSqlStatement TheStmt;

    28 

    29 _LIT(KTestDir, "c:\\test\\");

    30 _LIT(KTestDbName1, "c:\\test\\t_sqlapi2_1.db");

    31 _LIT(KTestDbName2, "c:\\private\\1111C1EF\\t_sqlapi2_2.db");//t_sqlapi2 app - private database

    32 

    33 _LIT(KDbInjectedName1, "DELETE FROM symbian_settings;c:\\test\\A.db");

    34 _LIT(KDbInjectedName2, "c:\\test\\A.db;DELETE FROM symbian_settings;");

    35 

    36 const TInt KBufLen = 8192;

    37 TBuf<KBufLen> TheBuf;

    38 

    39 ///////////////////////////////////////////////////////////////////////////////////////

    40 

    41 void DeleteTestFiles()

    42 	{

    43 	TheStmt.Close();

    44 	TheDb.Close();

    45 	(void)RSqlDatabase::Delete(KDbInjectedName2);

    46 	(void)RSqlDatabase::Delete(KDbInjectedName1);

    47 	(void)RSqlDatabase::Delete(KTestDbName2);

    48 	(void)RSqlDatabase::Delete(KTestDbName1);

    49 	}

    50 

    51 ///////////////////////////////////////////////////////////////////////////////////////

    52 ///////////////////////////////////////////////////////////////////////////////////////

    53 //Test macros and functions

    54 void Check(TInt aValue, TInt aLine)

    55 	{

    56 	if(!aValue)

    57 		{

    58 		DeleteTestFiles();

    59 		TheTest(EFalse, aLine);

    60 		}

    61 	}

    62 void Check(TInt aValue, TInt aExpected, TInt aLine)

    63 	{

    64 	if(aValue != aExpected)

    65 		{

    66 		DeleteTestFiles();

    67 		RDebug::Print(_L("*** Expected error: %d, got: %d\r\n"), aExpected, aValue);

    68 		TheTest(EFalse, aLine);

    69 		}

    70 	}

    71 #define TEST(arg) ::Check((arg), __LINE__)

    72 #define TEST2(aValue, aExpected) ::Check(aValue, aExpected, __LINE__)

    73 

    74 ///////////////////////////////////////////////////////////////////////////////////////

    75 

    76 void CreateTestEnv()

    77     {

    78     RFs fs;

    79 	TInt err = fs.Connect();

    80 	TEST2(err, KErrNone);

    81 

    82 	err = fs.MkDir(KTestDir);

    83 	TEST(err == KErrNone || err == KErrAlreadyExists);

    84 

    85 	err = fs.CreatePrivatePath(EDriveC);

    86 	TEST(err == KErrNone || err == KErrAlreadyExists);

    87 

    88 	fs.Close();

    89 	}

    90 

    91 ///////////////////////////////////////////////////////////////////////////////////////

    92 

    93 /**

    94 @SYMTestCaseID			SYSLIB-SQL-UT-3512

    95 @SYMTestCaseDesc		RSqlStatement::ColumnCount() - SELECT statements test

    96 						The test creates a database with a table and then checks the ColumnCount()

    97 						return result for the following statements:

    98 						- select all columns;

    99 						- select a subset;

   100 						- select an expression;

   101 						- select a constant;

   102 						- multi-table select;

   103 						- select a function;

   104 						- select plus sub-query;

   105 @SYMTestPriority		High

   106 @SYMTestActions			RSqlStatement::ColumnCount() test

   107 @SYMTestExpectedResults Test must not fail

   108 @SYMREQ					REQ8035

   109 */

   110 void ColumnCountTest()

   111 	{

   112 

   113 	TInt err = TheDb.Create(KTestDbName1);

   114 	TEST2(err, KErrNone);

   115 	//Create table 1

   116 	err = TheDb.Exec(_L("CREATE TABLE A(Id INTEGER,Name TEXT,Id2 INTEGER,Data BLOB)"));

   117 	TEST(err >= 0);

   118 	

   119 	err = TheDb.Exec(_L("INSERT INTO A VALUES(1,'AAA',6234567890,x'11AAFD0C771188')"));

   120 	TEST2(err, 1);

   121 		

   122 	//Select all columns (SELECT *)

   123 	err = TheStmt.Prepare(TheDb, _L("SELECT * FROM A"));

   124 	TEST2(err, KErrNone);

   125 	TInt cnt = TheStmt.ColumnCount();

   126 	TEST2(cnt, 4);

   127 	TheStmt.Close();

   128 	//Select all columns (SELECT a,b,c...)

   129 	err = TheStmt.Prepare(TheDb, _L("SELECT Id,Name,Id2,Data FROM A"));

   130 	TEST2(err, KErrNone);

   131 	cnt = TheStmt.ColumnCount();

   132 	TEST2(cnt, 4);

   133 	TheStmt.Close();

   134 	//Select column subset

   135 	err = TheStmt.Prepare(TheDb, _L("SELECT Id,Name,Data FROM A"));

   136 	TEST2(err, KErrNone);

   137 	cnt = TheStmt.ColumnCount();

   138 	TEST2(cnt, 3);

   139 	TheStmt.Close();

   140 	//Select column subset + expression

   141 	err = TheStmt.Prepare(TheDb, _L("SELECT Id,Id+Id2 FROM A"));

   142 	TEST2(err, KErrNone);

   143 	cnt = TheStmt.ColumnCount();

   144 	TEST2(cnt, 2);

   145 	TheStmt.Close();

   146 	//Select column subset + constant

   147 	err = TheStmt.Prepare(TheDb, _L("SELECT Id,Id2,345.78 FROM A"));

   148 	TEST2(err, KErrNone);

   149 	cnt = TheStmt.ColumnCount();

   150 	TEST2(cnt, 3);

   151 	TheStmt.Close();

   152 	//Select SQL function

   153 	err = TheStmt.Prepare(TheDb, _L("SELECT COUNT(*) FROM A"));

   154 	TEST2(err, KErrNone);

   155 	cnt = TheStmt.ColumnCount();

   156 	TEST2(cnt, 1);

   157 	TheStmt.Close();

   158 	//Create table 2

   159 	err = TheDb.Exec(_L("CREATE TABLE B(Id INTEGER, S INTEGER)"));

   160 	TEST(err >= 0);

   161 	err = TheDb.Exec(_L("INSERT INTO B VALUES(1,25)"));

   162 	TEST2(err, 1);

   163 	//Multitable select

   164 	err = TheStmt.Prepare(TheDb, _L("SELECT A.Id,B.S FROM A,B WHERE A.Id = B.Id"));

   165 	TEST2(err, KErrNone);

   166 	cnt = TheStmt.ColumnCount();

   167 	TEST2(cnt, 2);

   168 	TheStmt.Close();

   169 	//Select + Subquery

   170 	err = TheStmt.Prepare(TheDb, _L("SELECT Id FROM A WHERE (SELECT S FROM B WHERE A.Id = B.Id) > 10"));

   171 	TEST2(err, KErrNone);

   172 	cnt = TheStmt.ColumnCount();

   173 	TEST2(cnt, 1);

   174 	TheStmt.Close();

   175 	//Cleanup

   176 	TheDb.Close();

   177 	(void)RSqlDatabase::Delete(KTestDbName1);

   178 	}

   179 

   180 /**

   181 @SYMTestCaseID			SYSLIB-SQL-UT-3513

   182 @SYMTestCaseDesc		RSqlStatement::ColumnCount() - DDL and DML statements test

   183 						The test creates a database with a table and then checks the ColumnCount() return result for

   184 						DML statements (INSERT/UPDATE/DELETE) and DDL statements (CREATE TABLE/INDEX, DROP TABLE?INDEX).

   185 						The column count for DML and DDL statements should be 0.

   186 @SYMTestPriority		High

   187 @SYMTestActions			RSqlStatement::ColumnCount() test

   188 @SYMTestExpectedResults Test must not fail

   189 @SYMREQ					REQ8035

   190 */

   191 void ColumnCountTest2()

   192 	{

   193 	TInt err = TheDb.Create(KTestDbName1);

   194 	TEST2(err, KErrNone);

   195 	//Create table

   196 	err = TheDb.Exec(_L("CREATE TABLE A(Id INTEGER,Name TEXT,Id2 INTEGER,Data BLOB)"));

   197 	TEST(err >= 0);

   198 	err = TheDb.Exec(_L("INSERT INTO A VALUES(1,'AAA',6234567890,x'11AAFD0C771188')"));

   199 	TEST2(err, 1);

   200 	//INSERT statement

   201 	err = TheStmt.Prepare(TheDb, _L("INSERT INTO A(Id,Id2) VALUES(:P1,:P2)"));

   202 	TEST2(err, KErrNone);

   203 	TInt cnt = TheStmt.ColumnCount();

   204 	TEST2(cnt, 0);

   205 	TheStmt.Close();

   206 	//UPDATE statement

   207 	err = TheStmt.Prepare(TheDb, _L("UPDATE A SET Id2=100 WHERE Id=:P1"));

   208 	TEST2(err, KErrNone);

   209 	cnt = TheStmt.ColumnCount();

   210 	TEST2(cnt, 0);

   211 	TheStmt.Close();

   212 	//DELETE statement

   213 	err = TheStmt.Prepare(TheDb, _L("DELETE FROM A WHERE Id=:P1"));

   214 	TEST2(err, KErrNone);

   215 	cnt = TheStmt.ColumnCount();

   216 	TEST2(cnt, 0);

   217 	TheStmt.Close();

   218 	//CREATE TABLE statement

   219 	err = TheStmt.Prepare(TheDb, _L("CREATE TABLE B AS SELECT * FROM A"));

   220 	TEST2(err, KErrNone);

   221 	cnt = TheStmt.ColumnCount();

   222 	TEST2(cnt, 0);

   223 	TheStmt.Close();

   224 	//DROP TABLE statement

   225 	err = TheStmt.Prepare(TheDb, _L("DROP TABLE A"));

   226 	TEST2(err, KErrNone);

   227 	cnt = TheStmt.ColumnCount();

   228 	TEST2(cnt, 0);

   229 	TheStmt.Close();

   230 	//CREATE INDEX statement

   231 	err = TheStmt.Prepare(TheDb, _L("CREATE INDEX I ON A(Id)"));

   232 	TEST2(err, KErrNone);

   233 	cnt = TheStmt.ColumnCount();

   234 	TEST2(cnt, 0);

   235 	err = TheStmt.Exec();

   236 	TEST(err >= 0);

   237 	TheStmt.Close();

   238 	//DROP INDEX statement

   239 	err = TheStmt.Prepare(TheDb, _L("DROP INDEX I"));

   240 	TEST2(err, KErrNone);

   241 	cnt = TheStmt.ColumnCount();

   242 	TEST2(cnt, 0);

   243 	TheStmt.Close();

   244 	//CREATE TRIGGER statement

   245 	err = TheStmt.Prepare(TheDb,

   246 			_L("CREATE TRIGGER Trg BEFORE DELETE ON A \

   247 	             BEGIN \

   248 	                SELECT CASE WHEN ((SELECT Id2 FROM A WHERE A.Id = old.Id) > 0) \

   249 	                            THEN RAISE (ABORT, 'Id2 > 0') \

   250 	                END;\

   251 	             END;"));

   252 	TEST2(err, KErrNone);

   253 	cnt = TheStmt.ColumnCount();

   254 	TEST2(cnt, 0);

   255 	TheStmt.Close();

   256 	//CREATE VIEW statement

   257 	err = TheStmt.Prepare(TheDb, _L("CREATE VIEW V AS SELECT * FROM A"));

   258 	TEST2(err, KErrNone);

   259 	cnt = TheStmt.ColumnCount();

   260 	TEST2(cnt, 0);

   261 	err = TheStmt.Exec();

   262 	TEST(err >= 0);

   263 	TheStmt.Close();

   264 	//DROP VIEW statement

   265 	err = TheStmt.Prepare(TheDb, _L("DROP VIEW V"));

   266 	TEST2(err, KErrNone);

   267 	cnt = TheStmt.ColumnCount();

   268 	TEST2(cnt, 0);

   269 	TheStmt.Close();

   270 	//Cleanup

   271 	TheDb.Close();

   272 	(void)RSqlDatabase::Delete(KTestDbName1);

   273 	}

   274 

   275 /**

   276 @SYMTestCaseID			SYSLIB-SQL-UT-3514

   277 @SYMTestCaseDesc		RSqlStatement::DeclaredColumnType() test

   278 						The test creates a database with a table and then checks the DeclaredColumnType() return result for:

   279 						- select all column from the table and check their types;

   280 						- multi-table select plus column type checks;

   281 						- select expression - the expected column type is ESqlInt;

   282 						- select constant - the expected column type is ESqlInt;

   283 @SYMTestPriority		High

   284 @SYMTestActions			RSqlStatement::ColumnCount() test

   285 @SYMTestExpectedResults Test must not fail

   286 @SYMREQ					REQ8035

   287 */

   288 void DeclaredColumnTypeTest()

   289 	{

   290 	TInt err = TheDb.Create(KTestDbName1);

   291 	TEST2(err, KErrNone);

   292 	const char* KColTypeNames[] =

   293 		{"INTEGER", "LONG INTEGER", "INT", "SHORT INT", "SMALL INT", "TINY INT", "SHORT", "INT64",

   294 		"TEXT", "LONGTEXT", "CLOB", "CHAR", "CHARACTER(20)", "LONG TEXT",

   295 		"BINARY", "LONG BINARY", "LONGBINARY", "BLOB", "LONGBLOB", "LONG BLOB",

   296 		"REAL", "FLOAT", "DOUBLE", "LONG DOUBLE",

   297 		"LONG LONG", "BOO HOO"};

   298 	const TSqlColumnType KColTypes[] =

   299 		{ESqlInt,ESqlInt,ESqlInt,ESqlInt,ESqlInt,ESqlInt,ESqlInt,ESqlInt,

   300 		 ESqlText,ESqlText,ESqlText,ESqlText,ESqlText,ESqlText,

   301 		 ESqlBinary,ESqlBinary,ESqlBinary,ESqlBinary,ESqlBinary,ESqlBinary,

   302 		 ESqlReal,ESqlReal,ESqlReal,ESqlReal,

   303 		 ESqlInt,ESqlInt};

   304 	const TInt KColTypeCnt = sizeof(KColTypes) / sizeof(KColTypes[0]);

   305 	TEST2(sizeof(KColTypeNames) / sizeof(KColTypeNames[0]), KColTypeCnt);

   306 	//Create table 1

   307 	TBuf8<512> sql;

   308 	sql.Copy(_L8("CREATE TABLE T("));

   309 	for(TInt i=0;i<KColTypeCnt;++i)

   310 		{

   311 		sql.Append(TChar('A'));

   312 		sql.AppendNum(i + 1);

   313 		sql.Append(TChar(' '));

   314 		sql.Append((TUint8*)KColTypeNames[i], User::StringLength((TUint8*)KColTypeNames[i]));

   315 		sql.Append(TChar(','));

   316 		}

   317 	sql.Replace(sql.Length() - 1, 1, _L8(")"));

   318 	err = TheDb.Exec(sql);

   319 	TEST(err >= 0);

   320 	//Select all columns (SELECT *)

   321 	err = TheStmt.Prepare(TheDb, _L("SELECT * FROM T"));

   322 	TEST2(err, KErrNone);

   323 	TInt cnt = TheStmt.ColumnCount();

   324 	TEST2(cnt, KColTypeCnt);

   325 	TSqlColumnType colType;

   326 	for(TInt i=0;i<KColTypeCnt;++i)

   327 		{

   328 		TInt err = TheStmt.DeclaredColumnType(i, colType);

   329 		TEST2(err, KErrNone);

   330 		TEST2(colType, KColTypes[i]);

   331 		}

   332 	TheStmt.Close();

   333 	//Create table 2

   334 	err = TheDb.Exec(_L8("CREATE TABLE T2(Id INTEGER, DATA BLOB)"));

   335 	TEST(err >= 0);

   336 	//Multi-table select

   337 	err = TheStmt.Prepare(TheDb, _L("SELECT T.A1,T2.Id,T.A9,T2.Data FROM T,T2"));

   338 	TEST2(err, KErrNone);

   339 	err = TheStmt.DeclaredColumnType(0, colType);

   340 	TEST2(err, KErrNone);

   341 	TEST2(colType, ESqlInt);

   342 	err = TheStmt.DeclaredColumnType(1, colType);

   343 	TEST2(err, KErrNone);

   344 	TEST2(colType, ESqlInt);

   345 	err = TheStmt.DeclaredColumnType(2, colType);

   346 	TEST2(err, KErrNone);

   347 	TEST2(colType, ESqlText);

   348 	err = TheStmt.DeclaredColumnType(3, colType);

   349 	TEST2(err, KErrNone);

   350 	TEST2(colType, ESqlBinary);

   351 	TheStmt.Close();

   352 	//Select expression

   353 	err = TheStmt.Prepare(TheDb, _L("SELECT (Id + Data) AS RES FROM t2"));

   354 	TEST2(err, KErrNone);

   355 	err = TheStmt.DeclaredColumnType(0, colType);

   356 	TEST2(err, KErrNone);

   357 	TEST2(colType, ESqlInt);

   358 	TheStmt.Close();

   359 	//Select constant

   360 	err = TheStmt.Prepare(TheDb, _L("SELECT (Id + Data) AS RES, 55.89 FROM t2"));

   361 	TEST2(err, KErrNone);

   362 	err = TheStmt.DeclaredColumnType(1, colType);

   363 	TEST2(err, KErrNone);

   364 	TEST2(colType, ESqlInt);

   365 	TheStmt.Close();

   366 	//Cleanup

   367 	TheDb.Close();

   368 	(void)RSqlDatabase::Delete(KTestDbName1);

   369 	}

   370 

   371 /**

   372 @SYMTestCaseID			SYSLIB-SQL-UT-4017

   373 @SYMTestCaseDesc		RSqlStatement::ColumnName(TInt, TPtrC&) test

   374 						The test creates a database with a table and then checks the ColumnName() return result for:

   375 						- select all column from the table and check their names;

   376 						- multi-table select plus column name checks;

   377 						- select expression - the expected column name is RES

   378 						- select constant - the expected column type is 55.89 

   379 @SYMTestPriority		High

   380 @SYMTestActions			RSqlStatement::ColumnName() test

   381 @SYMTestExpectedResults Test must not fail

   382 @SYMCR				    RMAD-7B7EV5

   383                         Add SQL Server APIs to retrieve column and parameter names

   384 */	

   385 void ColumnNameTest()

   386 	{

   387 	TInt err = TheDb.Create(KTestDbName1);

   388 	TEST2(err, KErrNone);

   389 	const char* KColTypeNames[] = 

   390 		{"INTEGER", "LONG INTEGER", "INT", "SHORT INT", "SMALL INT", "TINY INT", "SHORT", "INT64",

   391 		"TEXT", "LONGTEXT", "CLOB", "CHAR", "CHARACTER(20)", "LONG TEXT",

   392 		"BINARY", "LONG BINARY", "LONGBINARY", "BLOB", "LONGBLOB", "LONG BLOB",

   393 		"REAL", "FLOAT", "DOUBLE", "LONG DOUBLE",

   394 		"LONG LONG", "BOO HOO"};

   395 	const TSqlColumnType KColTypes[] = 

   396 		{ESqlInt,ESqlInt,ESqlInt,ESqlInt,ESqlInt,ESqlInt,ESqlInt,ESqlInt,

   397 		 ESqlText,ESqlText,ESqlText,ESqlText,ESqlText,ESqlText,

   398 		 ESqlBinary,ESqlBinary,ESqlBinary,ESqlBinary,ESqlBinary,ESqlBinary,

   399 		 ESqlReal,ESqlReal,ESqlReal,ESqlReal,

   400 		 ESqlInt,ESqlInt};

   401 	const TInt KColTypeCnt = sizeof(KColTypes) / sizeof(KColTypes[0]);

   402 	TEST2(sizeof(KColTypeNames) / sizeof(KColTypeNames[0]), KColTypeCnt);

   403 	//Create table 1

   404 	TBuf8<512> sql;

   405 	sql.Copy(_L8("CREATE TABLE T("));

   406 	for(TInt i=0;i<KColTypeCnt;++i)

   407 		{

   408 		sql.Append(TChar('A'));

   409 		sql.AppendNum(i + 1);

   410 		sql.Append(TChar(' '));

   411 		sql.Append((TUint8*)KColTypeNames[i], User::StringLength((TUint8*)KColTypeNames[i]));

   412 		sql.Append(TChar(','));

   413 		}

   414 	sql.Replace(sql.Length() - 1, 1, _L8(")"));

   415 	err = TheDb.Exec(sql);

   416 	TEST(err >= 0);

   417 	//Select all columns (SELECT *)

   418 	err = TheStmt.Prepare(TheDb, _L("SELECT * FROM T"));

   419 	TEST2(err, KErrNone);

   420 	TInt cnt = TheStmt.ColumnCount();

   421 	TEST2(cnt, KColTypeCnt);

   422 	TPtrC colName;

   423 	TBuf<128> expectedColName;

   424 	for(TInt i=0;i<KColTypeCnt;++i)

   425 		{

   426 		expectedColName.Zero();

   427 		expectedColName.Append(TChar('A'));

   428 		expectedColName.AppendNum(i + 1);

   429 		TInt err = TheStmt.ColumnName(i, colName);

   430 		TEST2(err, KErrNone);

   431 		TEST2(colName.Compare(expectedColName), 0);

   432 		TSqlColumnType type;

   433 		err = TheStmt.DeclaredColumnType(i, type);

   434 		TEST2(err, KErrNone);

   435 		TEST2(type, KColTypes[i]);

   436 		}

   437 	TheStmt.Close();

   438 	//Create table 2

   439 	err = TheDb.Exec(_L8("CREATE TABLE T2(Id INTEGER, DATA BLOB)"));

   440 	TEST(err >= 0);

   441 	//Multi-table select

   442 	err = TheStmt.Prepare(TheDb, _L("SELECT T.A1,T2.Id,T.A9,T2.DATA FROM T,T2"));

   443 	TEST2(err, KErrNone);

   444 	err = TheStmt.ColumnName(0, colName);

   445 	TEST2(err, KErrNone);

   446 	TEST2(colName.Compare(_L("A1")), 0);

   447 	err = TheStmt.ColumnName(1, colName);

   448 	TEST2(err, KErrNone);

   449 	TEST2(colName.Compare(_L("Id")), 0);

   450 	err = TheStmt.ColumnName(2, colName);

   451 	TEST2(err, KErrNone);

   452 	TEST2(colName.Compare(_L("A9")), 0);

   453 	err = TheStmt.ColumnName(3, colName);

   454 	TEST2(err, KErrNone);

   455 	TEST2(colName.Compare(_L("DATA")), 0);

   456 	TheStmt.Close();

   457 	//Select expression

   458 	err = TheStmt.Prepare(TheDb, _L("SELECT (Id + Data) AS RES FROM t2"));

   459 	TEST2(err, KErrNone);

   460 	err = TheStmt.ColumnName(0, colName);

   461 	TEST2(err, KErrNone);

   462 	TEST2(colName.Compare(_L("RES")), 0);

   463 	TheStmt.Close();

   464 	//Select constant

   465 	err = TheStmt.Prepare(TheDb, _L("SELECT (Id + Data) AS RES, 55.89 FROM t2"));

   466 	TEST2(err, KErrNone);

   467 	err = TheStmt.ColumnName(1, colName);

   468 	TEST2(err, KErrNone);

   469 	TEST2(colName.Compare(_L("55.89")), 0);

   470 	TheStmt.Close();

   471 	//Cleanup

   472 	TheDb.Close();

   473 	(void)RSqlDatabase::Delete(KTestDbName1);

   474 	}

   475 

   476 /**

   477 @SYMTestCaseID			SYSLIB-SQL-UT-4018

   478 @SYMTestCaseDesc		RSqlStatement::ParameterName(TInt, TPtrC&) and RSqlStatement::ParamName(TInt, TPtrC&) test

   479 						DML test:

   480 						The test creates a database with a table and prepares an insert query.

   481 						The test then checks the ParameterName() and ParamName() return result for:

   482 						- Named parameters - return the named param

   483 						- Unnamed parameters - return ?<param-index>

   484 @SYMTestPriority		High

   485 @SYMTestActions			RSqlStatement::ParameterName() and RSqlStatement::ParamName() test

   486 @SYMTestExpectedResults Test must not fail

   487 @SYMCR					RMAD-7B7EV5

   488                         Add SQL Server APIs to retrieve column and parameter names

   489 */	

   490 void ParamNameTest()

   491 	{

   492 	TInt err = TheDb.Create(KTestDbName1);

   493 	TEST2(err, KErrNone);

   494 	const char* KColTypeNames[] = 

   495 		{"INTEGER", "TEXT"};

   496 	const TInt KColTypeCnt = sizeof(KColTypeNames) / sizeof(KColTypeNames[0]);

   497 	//Create table 1

   498 	TBuf8<256> sql;

   499 	sql.Copy(_L8("CREATE TABLE T("));

   500 	for(TInt i=0;i<KColTypeCnt;++i)

   501 		{

   502 		sql.Append(TChar('A'));

   503 		sql.AppendNum(i + 1);

   504 		sql.Append(TChar(' '));

   505 		sql.Append((TUint8*)KColTypeNames[i], User::StringLength((TUint8*)KColTypeNames[i]));

   506 		sql.Append(TChar(','));

   507 		}

   508 	sql.Replace(sql.Length() - 1, 1, _L8(")"));

   509 	err = TheDb.Exec(sql);

   510 	TEST(err >= 0);

   511 	TheStmt.Close();

   512 	

   513 	// Create insert statement, then check param names

   514 	err = TheStmt.Prepare(TheDb, _L("INSERT INTO T (A1, A2) VALUES (:prm1, :prm2)"));

   515 	TEST2(err, KErrNone);

   516 	TPtrC paramName;

   517 	TBuf<128> expectedParamName;

   518 	for(TInt i=0;i<KColTypeCnt;++i)

   519 		{

   520 		expectedParamName.Zero();

   521 		expectedParamName.Append(_L(":prm"));

   522 		expectedParamName.AppendNum(i + 1);

   523 		TInt paramIndex = TheStmt.ParameterIndex(expectedParamName);

   524 		TEST2(paramIndex, i);

   525 		TInt err = TheStmt.ParameterName(i, paramName);

   526 		TEST2(err, KErrNone);

   527 		TEST2(paramName.Compare(expectedParamName), 0);

   528 		err = TheStmt.ParamName(i, paramName);

   529 		TEST2(err, KErrNone);

   530 		TEST2(paramName.Compare(expectedParamName), 0);

   531 		}

   532 	TheStmt.Close();

   533 

   534 	// Create insert statement, then check param names

   535 	err = TheStmt.Prepare(TheDb, _L("INSERT INTO T (A1, A2) VALUES (:prm1, ?)"));

   536 	TEST2(err, KErrNone);

   537 	

   538 	expectedParamName.Zero();

   539 	expectedParamName.Append(_L(":prm1"));

   540 	TInt paramIndex = TheStmt.ParameterIndex(expectedParamName);

   541 	TEST2(paramIndex, 0);

   542 	err = TheStmt.ParameterName(0, paramName);

   543 	TEST2(err, KErrNone);

   544 	TEST2(paramName.Compare(expectedParamName), 0);

   545 	err = TheStmt.ParamName(0, paramName);

   546 	TEST2(err, KErrNone);

   547 	TEST2(paramName.Compare(expectedParamName), 0);

   548 

   549 	expectedParamName.Zero();

   550 	expectedParamName.Append(_L("?1"));

   551 	err = TheStmt.ParameterName(1, paramName);

   552 	TEST2(err, KErrNone);

   553 	paramIndex = TheStmt.ParameterIndex(expectedParamName);

   554 	TEST2(paramIndex, 1);

   555 	TEST2(paramName.Compare(expectedParamName), 0);

   556 	

   557 	err = TheStmt.ParamName(1, paramName);

   558 	TEST2(err, KErrNone);

   559 	TEST2(paramName.Compare(expectedParamName), 0);

   560 

   561 	TheStmt.Close();

   562 	

   563 	//Cleanup

   564 	TheDb.Close();

   565 	(void)RSqlDatabase::Delete(KTestDbName1);

   566 	}

   567 

   568 

   569 /**

   570 @SYMTestCaseID			SYSLIB-SQL-UT-4006

   571 @SYMTestCaseDesc		Test for DEF115300 - SqlSrv.EXE::!SQL Server when preparing invalid LEFT JOIN sql query.

   572 						The test does the following steps:

   573 						1) Creates a 16-bit database and using 16-bit queries proves that the "GROUP BY GROUP BY" syntax error

   574 						   does not cause an assert inside the SQLITE code.

   575 						2) Creates a 8-bit database and using 8-bit queries proves that the "GROUP BY GROUP BY" syntax error

   576 						   does not cause an assert inside the SQLITE code.

   577 @SYMTestPriority		Medium

   578 @SYMTestActions			Test for DEF115300 - SqlSrv.EXE::!SQL Server when preparing invalid LEFT JOIN sql query.

   579 @SYMTestExpectedResults Test must not fail

   580 @SYMDEF					DEF115300

   581 */

   582 void DEF115300()

   583 	{

   584 

   585 	//Step 1: 16-bit statements

   586 	(void)RSqlDatabase::Delete(KTestDbName1);

   587 	TInt err = TheDb.Create(KTestDbName1);

   588 	TEST2(err, KErrNone);

   589 

   590 	err = TheDb.Exec(_L("CREATE TABLE MAIN(Id INTEGER, Id1 INTEGER, F2 BLOB)"));

   591 	TEST2(err, 1);

   592 	err = TheDb.Exec(_L("INSERT INTO MAIN(Id,Id1) VALUES(2,3)"));

   593 	TEST2(err, 1);

   594 	err = TheDb.Exec(_L("INSERT INTO MAIN(Id,Id1) VALUES(3,4)"));

   595 	TEST2(err, 1);

   596 

   597 	err = TheDb.Exec(_L("CREATE TABLE A(Id INTEGER, Id1 INTEGER, F2 BLOB)"));

   598 	TEST2(err, 1);

   599 	err = TheDb.Exec(_L("INSERT INTO A(Id, Id1) VALUES(2,3)"));

   600 	TEST2(err, 1);

   601 	err = TheDb.Exec(_L("INSERT INTO A(Id, Id1) VALUES(4,4)"));

   602 	TEST2(err, 1);

   603 

   604 	err = TheDb.Exec(_L("CREATE TABLE B(Id INTEGER, Id1 INTEGER, F2 BLOB)"));

   605 	TEST2(err, 1);

   606 	err = TheDb.Exec(_L("INSERT INTO B(Id, Id1) VALUES(2,3)"));

   607 	TEST2(err, 1);

   608 	err = TheDb.Exec(_L("INSERT INTO B(Id, Id1) VALUES(5,4)"));

   609 	TEST2(err, 1);

   610 	err = TheDb.Exec(_L("INSERT INTO B(Id, Id1) VALUES(5,4)"));

   611 	TEST2(err, 1);

   612 

   613 	err = TheDb.Exec(_L("CREATE VIEW v2 AS SELECT Id,Id1,F2 FROM B"));

   614 	TEST2(err, 1);

   615 	err = TheDb.Exec(_L("CREATE VIEW v1 AS SELECT Id,Id1,F2 FROM A"));

   616 	TEST2(err, 1);

   617 	err = TheDb.Exec(_L("CREATE VIEW v3 AS SELECT Id,Id1,F2 FROM B"));

   618 	TEST2(err, 1);

   619 

   620 	RSqlStatement stmt;

   621 	err = stmt.Prepare(TheDb, _L("SELECT * FROM v2 LEFT JOIN MAIN ON v2.Id = MAIN.Id LEFT JOIN A ON MAIN.Id = A.Id GROUP BY GROUP BY MAIN.Id"));

   622 	stmt.Close();

   623 	TEST(err != KErrNone && err != KErrServerTerminated);

   624 

   625 	TheDb.Close();

   626 	err = RSqlDatabase::Delete(KTestDbName1);

   627 	TEST2(err, KErrNone);

   628 

   629 	//Step 2: 8-bit statements

   630 	_LIT8(KServerConfigString1, "encoding=\"UTF-8\"");

   631 	err = TheDb.Create(KTestDbName1, &KServerConfigString1);

   632 	TEST2(err, KErrNone);

   633 

   634 	err = TheDb.Exec(_L8("CREATE TABLE MAIN(Id INTEGER, Id1 INTEGER, F2 BLOB)"));

   635 	TEST2(err, 1);

   636 

   637 	err = stmt.Prepare(TheDb, _L8("SELECT * FROM main GROUP BY GROUP BY main.Id"));

   638 	stmt.Close();

   639 	TEST(err != KErrNone && err != KErrServerTerminated);

   640 

   641 	TheDb.Close();

   642 	err = RSqlDatabase::Delete(KTestDbName1);

   643 	TEST2(err, KErrNone);

   644 	}

   645 

   646 /**

   647 @SYMTestCaseID			SYSLIB-SQL-UT-4007

   648 @SYMTestCaseDesc		Test for DEF115331 - SQL, bad code coverage for db reindexing if default collation has changed.

   649 						The test does the following steps, using public shared and private secure database:

   650 						1) Creates a test database with a table and one index using one of the collations.

   651 						2) Updates the symbian_settings table, setting the collation dll name column value 

   652 						   to be a "bbbababz" string (Simulates that there is no valid collation dll name).

   653 						3) Reopens the database. This operation should cause a database reindexing, since the index uses

   654 						   one of the user-defined collation methods.

   655 						   The default system collation dll name should be stored in the symbian_settings table.

   656 						4) Verifies that symbian_settings table contains only one record and that the collation dll name

   657 						   column value has been updated.

   658 @SYMTestPriority		Medium

   659 @SYMTestActions			Test for DEF115331 - SQL, bad code coverage for db reindexing if default collation has changed.

   660 @SYMTestExpectedResults Test must not fail

   661 @SYMDEF					DEF115331

   662 */

   663 void DEF115331L()

   664 	{

   665 	const TPtrC KDbNames[]		=	{KTestDbName1(),	KTestDbName2()};

   666 

   667 	for(TInt i=0;i<(sizeof(KDbNames)/sizeof(KDbNames[0]));++i)

   668 		{

   669 		//Step 1: Create a test database with a table and one index using one of the collations.

   670 		(void)RSqlDatabase::Delete(KDbNames[i]);

   671 		TInt err = TheDb.Create(KDbNames[i]);

   672 		TEST2(err, KErrNone);

   673 

   674 		err = TheDb.Exec(_L("CREATE TABLE A(C TEXT)"));

   675 		TEST2(err, 1);

   676 

   677 		err = TheDb.Exec(_L("CREATE INDEX I ON A(C COLLATE CompareC1)"));

   678 		TEST2(err, 1);

   679 

   680 		//Step 2: Make sure that the collation dll name is set and unique (not the default collation).

   681 		err = TheDb.Exec(_L("UPDATE symbian_settings SET CollationDllName='bbbababz'"));

   682 		TEST2(err, 1);

   683 

   684 		TheDb.Close();

   685 

   686 		//Step 3: Reopen the database. That step should cause a database reindexing, because the default collation dll 

   687 		//        name is not the one stored in the table.

   688 		err = TheDb.Open(KDbNames[i]);

   689 		TEST2(err, KErrNone);

   690 

   691 		TSqlScalarFullSelectQuery query(TheDb);

   692 

   693 		//Step 4: Check that the settigns table has only one record.

   694 		TInt cnt = query.SelectIntL(_L("SELECT COUNT(*) FROM symbian_settings"));

   695 		TEST2(cnt, 1);

   696 

   697 		//Step 5: Check that the collation dll name in the settings table has been updated.

   698 		TFileName collationDllName;

   699 		err = query.SelectTextL(_L("SELECT CollationDllName FROM symbian_settings"), collationDllName);

   700 		TEST2(err, KErrNone);

   701 		_LIT(KTestCollationDllName, "bbbababz");//The same as the used in step 2 - above.

   702 		TEST(collationDllName != KTestCollationDllName);

   703 

   704 		TheDb.Close();

   705 		err = RSqlDatabase::Delete(KDbNames[i]);

   706 		TEST2(err, KErrNone);

   707 		}

   708 	}

   709 

   710 /**

   711 @SYMTestCaseID			SYSLIB-SQL-UT-4079

   712 @SYMTestCaseDesc		RSqlDatabase::Create() and RSqlDatabase::Open() - injection test

   713 						The test attempts to create or open a database which name contains

   714 						"DELETE FROM symbian_settings" statement.If it is possible to open or

   715 						create a database with that name, the "symbian_settings" table content

   716 						should stay unchanged.

   717 @SYMTestPriority		Medium

   718 @SYMTestActions			RSqlDatabase::Create() and RSqlDatabase::Open() - injection test

   719 @SYMTestExpectedResults Test must not fail

   720 @SYMREQ					REQ5792

   721 */

   722 void InjectionTest()

   723 	{

   724 	TInt err = TheDb.Create(KDbInjectedName1);

   725 	TEST(err != KErrNone);

   726 

   727 	err = TheDb.Create(KDbInjectedName2);

   728 	TEST2(err, KErrNone);

   729 	

   730 	TSqlScalarFullSelectQuery query(TheDb);

   731 	TInt recCount = 0;

   732 	TRAP(err, recCount = query.SelectIntL(_L("SELECT COUNT(*) FROM symbian_settings")));

   733 	TEST2(err, KErrNone);

   734 	TEST2(recCount, 1);

   735 	TheDb.Close();

   736 	

   737 	err = TheDb.Open(KDbInjectedName2);

   738 	TEST2(err, KErrNone);

   739 	recCount = 0;

   740 	query.SetDatabase(TheDb);

   741 	TRAP(err, recCount = query.SelectIntL(_L("SELECT COUNT(*) FROM symbian_settings")));

   742 	TEST2(err, KErrNone);

   743 	TEST2(recCount, 1);

   744 	TheDb.Close();

   745 		

   746 	(void)RSqlDatabase::Delete(KDbInjectedName2);

   747 	(void)RSqlDatabase::Delete(KDbInjectedName1);

   748 	}

   749 

   750 /**

   751 @SYMTestCaseID			SYSLIB-SQL-UT-4038

   752 @SYMTestCaseDesc		Background compaction - two connections usability test.

   753 						The test creates a database connection with a background compaction mode. The the test 

   754 						locks the database in a transaction. Then the test creates a second connection

   755 						to the same database while the first connection is in a transaction.

   756 @SYMTestPriority		Medium

   757 @SYMTestActions			Background compaction - two connections usability test.

   758 @SYMTestExpectedResults Test must not fail

   759 @SYMREQ					REQ10271

   760 */

   761 void TwoConnectionsTest()

   762 	{

   763 	(void)RSqlDatabase::Delete(KTestDbName1);

   764 	RSqlDatabase db1, db2;

   765 	TInt err = db1.Create(KTestDbName1);

   766 	TEST2(err, KErrNone);

   767 	err = db1.Exec(_L("CREATE TABLE A(I INTEGER); INSERT INTO A VALUES(1)"));

   768 	TEST2(err, 1);

   769 	err = db1.Exec(_L("BEGIN TRANSACTION"));

   770 	TEST(err >= 0);

   771 	err = db1.Exec(_L("INSERT INTO A VALUES(2)"));

   772 	TEST2(err, 1);

   773 	err = db2.Open(KTestDbName1);

   774 	TEST2(err, KErrNone);

   775 	err = db1.Exec(_L("COMMIT TRANSACTION"));

   776 	TEST(err >= 0);

   777 	db2.Close();		

   778 	db1.Close();		

   779 	(void)RSqlDatabase::Delete(KTestDbName1);

   780 	}

   781 

   782 TInt StackOverflowThreadFunc(void* aData)

   783 	{

   784 	CTrapCleanup* tc = CTrapCleanup::New();

   785 	TEST(tc != NULL);

   786 	

   787 	User::SetJustInTime(EFalse);	// disable debugger panic handling

   788 	

   789 	TInt* cntptr = reinterpret_cast<TInt*> (aData);

   790 	TEST(cntptr != NULL);

   791 	TInt cnt = *cntptr;

   792 

   793 	HBufC* buf = HBufC::New(cnt * 12 + 32);//enough for the "SELECT Id FROM A WHERE Id=v1 OR Id=v2 OR ..." string

   794 	if(!buf)

   795 		{

   796 		delete tc;

   797 		return KErrNoMemory;	

   798 		}

   799 	TPtr sql = buf->Des();

   800 	

   801 	TInt err = TheDb.Open(KTestDbName1);

   802 	if(err != KErrNone)

   803 		{

   804 		delete buf;

   805 		delete tc;

   806 		return err;	

   807 		}

   808 

   809 	TTime now;

   810 	now.UniversalTime();

   811 	TInt64 seed = now.Int64();

   812 

   813 	sql.Copy(_L("SELECT Id FROM A WHERE "));

   814 	for(TInt i=0;i<cnt;++i)

   815 		{

   816 		sql.Append(_L("Id="));

   817 		sql.AppendNum(Math::Rand(seed) % cnt);

   818 		sql.Append(_L(" OR "));

   819 		}

   820 	sql.SetLength(sql.Length() - 4);//Remove the last " OR "

   821 

   822 	RSqlStatement stmt;

   823 	err = stmt.Prepare(TheDb, sql);

   824 	stmt.Close();

   825 	

   826 	TheDb.Close();

   827 	delete buf;

   828 	delete tc;

   829 	

   830 	return err;

   831 	}

   832 

   833 /**

   834 @SYMTestCaseID			SYSLIB-SQL-UT-4080

   835 @SYMTestCaseDesc		SQL server stack overflow test

   836 						The test creates a database and runs a thread. The thread opens the database

   837 						and attempts to execute a SELECT statement, which format is:

   838 						"SELECT Id FROM A WHERE Id=1 OR Id=2 OR...OR Id=N",

   839 						where N is a number passed as an argument from the main thread, starts from 100000

   840 						and is increased or decreased on each test iteration, depending on the reported result from the thread.

   841 						Finally, the main thread will report the max number of the OR subexpressions that can be included

   842 						in the SELECT statement, without an error to be reported.

   843 						The test should not crash the SQL server, if the server stack size and parsing tree depth has

   844 						been properly configured.

   845 @SYMTestPriority		Medium

   846 @SYMTestActions			SQL server stack overflow test

   847 @SYMTestExpectedResults Test must not fail

   848 @SYMREQ					REQ5792

   849 */

   850 void SqlServerStackOverflowTest()

   851 	{

   852 	(void)RSqlDatabase::Delete(KTestDbName1);

   853 	TInt err = TheDb.Create(KTestDbName1);

   854 	TEST2(err, KErrNone);

   855 	err = TheDb.Exec(_L("CREATE TABLE A(Id INTEGER PRIMARY KEY AUTOINCREMENT)"));

   856 	TEST2(err, 1);

   857 	TheDb.Close();

   858 	

   859 	TInt prev = 0, next = 100000;

   860 	while(Abs(next - prev) > 0)

   861 		{

   862 		TInt count = next;

   863 		TheTest.Printf(_L("'OR' expr. count: %d\r\n"), count);

   864 		RThread thread;

   865 		_LIT(KThreadName,"ORThread");						//stack	minheap		maxheap

   866 		err = thread.Create(KThreadName, &StackOverflowThreadFunc, 0x2000, 0x00100000, 0x02000000, &count);

   867 		TEST2(err, KErrNone);

   868 		

   869 		TRequestStatus status;

   870 		thread.Logon(status);

   871 		TEST2(status.Int(), KRequestPending);

   872 		thread.Resume();

   873 		User::WaitForRequest(status);

   874 		User::SetJustInTime(ETrue);	// enable debugger panic handling

   875 

   876 		TInt exitType = thread.ExitType();

   877 		const TDesC& exitCategory = thread.ExitCategory();

   878 		TInt exitReason = thread.ExitReason();

   879 		TheTest.Printf(_L("Exit type: %d, exit reason: %d, exit category: %S\r\n"), exitType, exitReason, &exitCategory);

   880 		thread.Close();

   881 		TEST(exitReason != KErrServerTerminated);

   882 		TEST(exitType != EExitPanic);

   883 

   884 		TInt tmp = next;		

   885 		if(status.Int() != KErrNone)

   886 			{//The number of the OR subexpressions is too big and cannot be parsed. Decrease the number, repeat the test.

   887 			next -= Abs(next - prev) / 2;

   888 			}

   889 		else

   890 			{//KErrNone: The number of the OR subexpressions has been successfully parsed. Increase the number, repeat the test.

   891 			next += Abs(next - prev) / 2;

   892 			}

   893 		prev = tmp;

   894 		}

   895 	TheTest.Printf(_L("The test has succeeded with an expression with %d ORs\r\n"), prev);

   896 	}

   897 

   898 void AssertSettingsTable()

   899 	{

   900 	TSqlScalarFullSelectQuery query(TheDb);

   901 	TInt recCount = 0;

   902 	TRAPD(err, recCount = query.SelectIntL(_L("SELECT COUNT(*) FROM symbian_settings")));

   903 	TEST2(err, KErrNone);

   904 	TEST2(recCount, 1);

   905 	}

   906 

   907 /**

   908 @SYMTestCaseID			SYSLIB-SQL-UT-4086

   909 @SYMTestCaseDesc		RSqlBlobWriteStream::OpenL() and RSqlBlobReadStream::OpenL() - injection test

   910 						The test attempts to open a blob stream with an attached database name containing

   911 						"DELETE FROM symbian_settings" statement. The test should not delete the content of the

   912 						"symbian_settings" table.

   913 						The test also attempts to open a blob stream with a set of very long database/table/column names.

   914 @SYMTestPriority		Medium

   915 @SYMTestActions			RSqlBlobWriteStream::OpenL() and RSqlBlobReadStream::OpenL() - injection test

   916 @SYMTestExpectedResults Test must not fail

   917 @SYMREQ					REQ5792

   918                         REQ10410

   919 						REQ10411

   920 						REQ10418

   921 */

   922 void BlobStreamInjectionTest()

   923 	{

   924 	(void)RSqlDatabase::Delete(KTestDbName1);

   925 	TInt err = TheDb.Create(KTestDbName1);

   926 	TEST2(err, KErrNone);

   927 	err = TheDb.Exec(_L("CREATE TABLE A(Id INTEGER,Data BLOB)"));

   928 	TEST2(err, 1);

   929 	err = TheDb.Exec(_L("INSERT INTO A VALUES(1, x'11223344556677889900')"));

   930 	TEST2(err, 1);

   931 	_LIT(KAttachDb, "AttachDb");

   932 	err = TheDb.Attach(KTestDbName1, KAttachDb);

   933 	TEST2(err, KErrNone);

   934 	//RSqlBlobWriteStream::OpenL() - attached database  name injected

   935 	RSqlBlobWriteStream strm1;

   936 	TRAP(err, strm1.OpenL(TheDb, _L("A"), _L("Data"), 1, _L("Id;DELETE FROM symbian_settings;")));

   937 	TEST(err != KErrNone);

   938 	AssertSettingsTable();

   939 	//RSqlBlobReadStream::OpenL() - attached database  name injected

   940 	RSqlBlobReadStream strm2;

   941 	TRAP(err, strm2.OpenL(TheDb, _L("A"), _L("Data"), 1, _L("Id;DELETE FROM symbian_settings;")));

   942 	TEST(err != KErrNone);

   943 	AssertSettingsTable();

   944 	//Attempt to open a write blob stream with a set of very long database/table/column names.

   945 	TBuf<KMaxFileName + 10> longName;

   946 	longName.SetLength(longName.MaxLength());

   947 	RSqlBlobWriteStream strm3;

   948 	TRAP(err, strm3.OpenL(TheDb, longName, longName, 1, longName));

   949 	TEST(err != KErrNone);

   950 	//Attempt to open a read blob stream with a set of very long database/table/column names.

   951 	RSqlBlobReadStream strm4;

   952 	TRAP(err, strm4.OpenL(TheDb, longName, longName, 1, longName));

   953 	TEST(err != KErrNone);

   954 	//Attempt to open a write blob stream with a set of KNullDesC database/table/column names.

   955 	RSqlBlobWriteStream strm5;

   956 	TRAP(err, strm5.OpenL(TheDb, KNullDesC, KNullDesC, 1, KNullDesC));

   957 	TEST(err != KErrNone);

   958 	//Attempt to open a read blob stream with a set of KNullDesC database/table/column names.

   959 	RSqlBlobReadStream strm6;

   960 	TRAP(err, strm6.OpenL(TheDb, KNullDesC, KNullDesC, 1, KNullDesC));

   961 	TEST(err != KErrNone);

   962 	//

   963 	err = TheDb.Detach(KAttachDb);

   964 	TEST2(err, KErrNone);

   965 	TheDb.Close();

   966 	(void)RSqlDatabase::Delete(KTestDbName1);

   967 	}

   968 	

   969 /**

   970 @SYMTestCaseID			SYSLIB-SQL-UT-4087

   971 @SYMTestCaseDesc		Bound parameter values test.

   972 						The test verifies that bound parameters with big text/binary values retain their values after

   973 						the RSqlStatement::Reset() call. The old bound paramegter values can be used for the next 

   974 						RSqlStatement::Exec() call.

   975 @SYMTestActions			Bound parameter values test.

   976 @SYMTestExpectedResults Test must not fail

   977 @SYMTestPriority		High

   978 @SYMREQ					REQ5792

   979 */

   980 void BoundParameterValuesTest()

   981 	{

   982 	(void)RSqlDatabase::Delete(KTestDbName1);

   983 	TInt err = TheDb.Create(KTestDbName1);

   984 	TEST2(err, KErrNone);

   985 	err = TheDb.Exec(_L("CREATE TABLE A1(T1 TEXT, T2 TEXT)"));

   986 	TEST2(err, 1);

   987 	err = TheDb.Exec(_L("CREATE TABLE A2(T1 TEXT, T2 TEXT)"));

   988 	TEST2(err, 1);

   989 	

   990 	RSqlStatement stmt1, stmt2;

   991 	err = stmt1.Prepare(TheDb, _L("INSERT INTO A1 VALUES(:Prm1, :Prm2)"));

   992 	TEST2(err, KErrNone);

   993 	err = stmt2.Prepare(TheDb, _L("INSERT INTO A2 VALUES(:Prm1, :Prm2)"));

   994 	TEST2(err, KErrNone);

   995 	//Insert 1 record into table "A1". T2 = "ZZZZ.....".

   996 	TheBuf.SetLength(KBufLen - 100);

   997 	TheBuf.Fill(TChar('Z'));

   998 	err = stmt1.BindText(0, TheBuf);

   999 	TEST2(err, KErrNone);

  1000 	err = stmt1.BindText(1, TheBuf);

  1001 	TEST2(err, KErrNone);

  1002 	err = stmt1.Exec();

  1003 	TEST2(err, 1);

  1004 	err = stmt1.Reset();

  1005 	TEST2(err, KErrNone);

  1006 	//Insert 1 record into table "A2". T2 = "AAAAAAA.....".

  1007 	TheBuf.SetLength(KBufLen);

  1008 	TheBuf.Fill(TChar('A'));

  1009 	err = stmt2.BindText(0, TheBuf);

  1010 	TEST2(err, KErrNone);

  1011 	err = stmt2.BindText(1, TheBuf);

  1012 	TEST2(err, KErrNone);

  1013 	err = stmt2.Exec();

  1014 	TEST2(err, 1);

  1015 	err = stmt2.Reset();

  1016 	TEST2(err, KErrNone);

  1017 	//Insert 1 record into table "A1". T2 not set. T2 should be initialized with the previous bound value - "ZZZZZZ....".

  1018 	//If the problem is not fixed, the SQLITE will attempt to access an already deleted region of memory.

  1019 	TheBuf.SetLength(KBufLen - 100);

  1020 	TheBuf.Fill(TChar('B'));

  1021 	err = stmt1.BindText(0, TheBuf);

  1022 	TEST2(err, KErrNone);

  1023 	err = stmt1.Exec();

  1024 	TEST2(err, 1);

  1025 	err = stmt1.Reset();

  1026 	TEST2(err, KErrNone);

  1027 	

  1028 	stmt2.Close();

  1029 	stmt1.Close();

  1030 	

  1031 	//Check the inserted records.

  1032 	TSqlScalarFullSelectQuery q(TheDb);

  1033 	TRAP(err, q.SelectTextL(_L("SELECT T2 FROM A1 WHERE ROWID=1"), TheBuf));

  1034 	TEST2(err, KErrNone);

  1035 	TEST2(TheBuf.Length(), (KBufLen - 100));

  1036 	for(TInt i1=0;i1<(KBufLen - 100);++i1)

  1037 		{

  1038 		TEST2(TheBuf[i1], TChar('Z'));	

  1039 		}

  1040 	TRAP(err, q.SelectTextL(_L("SELECT T2 FROM A1 WHERE ROWID=2"), TheBuf));

  1041 	TEST2(err, KErrNone);

  1042 	TEST2(TheBuf.Length(), (KBufLen - 100));

  1043 	for(TInt i2=0;i2<(KBufLen - 100);++i2)

  1044 		{

  1045 		TEST2(TheBuf[i2], TChar('Z'));	

  1046 		}

  1047 	

  1048 	TheDb.Close();

  1049 	(void)RSqlDatabase::Delete(KTestDbName1);

  1050 	}

  1051 

  1052 /**

  1053 @SYMTestCaseID			SYSLIB-SQL-UT-4076

  1054 @SYMTestCaseDesc		Bound parameter values test.

  1055 						The test verifies that when a RSqlParamWriteStream object is used for binding parameter values,

  1056 						it is safe to erverse the order of RSqlParamWriteStream::Close() and RSqlStatement::Close() calls.

  1057 @SYMTestActions			Bound parameter values test.

  1058 @SYMTestExpectedResults Test must not fail

  1059 @SYMTestPriority		High

  1060 @SYMREQ					REQ5792

  1061 */

  1062 void BoundParameterValuesTest2()

  1063 	{

  1064 	(void)RSqlDatabase::Delete(KTestDbName1);

  1065 	TInt err = TheDb.Create(KTestDbName1);

  1066 	TEST2(err, KErrNone);

  1067 	err = TheDb.Exec(_L("CREATE TABLE A1(T1 TEXT, T2 TEXT)"));

  1068 	TEST2(err, 1);

  1069 	

  1070 	RSqlStatement stmt;

  1071 	err = stmt.Prepare(TheDb, _L("INSERT INTO A1 VALUES(:Prm1, :Prm2)"));

  1072 	TEST2(err, KErrNone);

  1073 	RSqlParamWriteStream strm;

  1074 	err = strm.BindText(stmt, 0);

  1075 	TEST2(err, KErrNone);

  1076 	err = stmt.Exec();

  1077 	TEST2(err, 1);

  1078 	stmt.Close();

  1079 	strm.Close();

  1080 

  1081 	TheDb.Close();

  1082 	(void)RSqlDatabase::Delete(KTestDbName1);

  1083 	}

  1084 

  1085 /**

  1086 @SYMTestCaseID			SYSLIB-SQL-UT-4077

  1087 @SYMTestCaseDesc		Bound parameter values test.

  1088 						The test verifies that when a RSqlParamWriteStream object is used for binding parameter values,

  1089 						it is possible to write the parameter value, then call RSqlParamWriteStream::Close() and finally -

  1090 						RSqlStatement::Exec() to execute the operation (an INSERT statement). The test verifies that the record

  1091 						has really been inserted and the column value is equal to the bound parameter value

  1092 @SYMTestActions			Bound parameter values test.

  1093 @SYMTestExpectedResults Test must not fail

  1094 @SYMTestPriority		High

  1095 @SYMREQ					REQ5792

  1096 */

  1097 void BoundParameterValuesTest3()

  1098 	{

  1099 	(void)RSqlDatabase::Delete(KTestDbName1);

  1100 	TInt err = TheDb.Create(KTestDbName1);

  1101 	TEST2(err, KErrNone);

  1102 	err = TheDb.Exec(_L("CREATE TABLE A1(T1 TEXT, T2 TEXT)"));

  1103 	TEST2(err, 1);

  1104 	

  1105 	RSqlStatement stmt;

  1106 	err = stmt.Prepare(TheDb, _L("INSERT INTO A1 VALUES(:Prm1, :Prm2)"));

  1107 	TEST2(err, KErrNone);

  1108 	RSqlParamWriteStream strm;

  1109 	err = strm.BindText(stmt, 0);

  1110 	TEST2(err, KErrNone);

  1111 	_LIT(KText, "AAAA");

  1112 	TRAP(err, strm.WriteL(KText));

  1113 	TEST2(err, KErrNone);

  1114 	TRAP(err, strm.CommitL());

  1115 	TEST2(err, KErrNone);

  1116 	strm.Close();

  1117 	err = stmt.Exec();

  1118 	TEST2(err, 1);

  1119 	stmt.Close();

  1120 

  1121 	TSqlScalarFullSelectQuery q(TheDb);

  1122 	TRAP(err, q.SelectTextL(_L("SELECT T1 FROM A1"), TheBuf));

  1123 	TEST2(err, KErrNone);

  1124 	TEST(KText() == TheBuf);	

  1125 

  1126 	TheDb.Close();

  1127 	(void)RSqlDatabase::Delete(KTestDbName1);

  1128 	}

  1129 

  1130 /**

  1131 @SYMTestCaseID			SYSLIB-SQL-UT-4083

  1132 @SYMTestCaseDesc		Bound parameter values test.

  1133 						The test prepares an INSERT sql statement and inserts two records using streams to bind the parameter values.

  1134 						For the second INSERT no parameter value is bound to the first parameter. The expectation is that the value