TB9.2 Example Applications: examples/SysLibs/SqlExample/sqlexample.cpp Source File

00001 // Copyright (c) 2006-2009 Nokia Corporation and/or its subsidiary(-ies).
+00002 // All rights reserved.
+00003 // This component and the accompanying materials are made available
+00004 // under the terms of "Eclipse Public License v1.0"
+00005 // which accompanies this distribution, and is available
+00006 // at the URL "http://www.eclipse.org/legal/epl-v10.html".
+00007 //
+00008 // Initial Contributors:
+00009 // Nokia Corporation - initial contribution.
+00010 //
+00011 // Contributors:
+00012 //
+00013 // Description:
+00014 // This example program demonstrates the use of SQL APIs. 
+00015 // The code demonstrates how to create non secure and secure databases on 
+00016 // the writable drive and perform basic operations on the databases.
+00017 //
+00018 
+00019 
+00020 
+00024 #include "sqlexample.h"
+00025 #include <e32cons.h>
+00026 #include <sqldb.h>
+00027 
+00028 _LIT(KTitle, "SQL example");
+00029 _LIT(KTextPressAKey, "\n\nPress any key to step through the example\n");
+00030 _LIT(KExit,"Press any key to exit the application ");
+00031 _LIT(KPressAKey,"Press any key to continue \n");
+00032 _LIT(KNonSecure,"\nCreating a non secure database \n");
+00033 _LIT(KSecure,"\nCreating a secure database \n");
+00034 _LIT(KOpen,"Opening  the secure database \n");
+00035 _LIT(KDelete,"Deleting the database(s)\n");
+00036 _LIT(KClose,"Closing the database(s)\n");
+00037 _LIT(KCopyNonSec,"\nCopying a non secure database to another non secure one \n");
+00038 _LIT(KCopySecure,"\nCopying a secure database to another secure database \n");                                    
+00039 _LIT(KAttach,"\nOpen a secure database and attach another secure database\n");
+00040 _LIT(KCreateTable,"\nCreating a table\n");
+00041 _LIT(KInsert,"Inserting records into the table\n");
+00042 _LIT(KPrepare,"Preparing a query\n");
+00043 _LIT(KExecute,"Executing a query\n");
+00044 
+00045 // Names of the databases created, operated upon and later deleted.
+00046 _LIT(KDbName, "\\Example_db.db");
+00047 _LIT(KAnotherDbName, "\\Example_Anotherdb.db");
+00048 _LIT(KSecureDb1, "[E80000AF]db1.db");
+00049 _LIT(KSecureDb2, "[E80000AF]db2.db");
+00050 _LIT(KDatabase, "\\Sqlscalarfullselect.db");
+00051 
+00052 // Security policies used
+00053 const TSecurityPolicy KPolicy1(ECapabilityReadUserData, ECapabilityNetworkControl,  ECapabilityWriteUserData);
+00054 const TSecurityPolicy KPolicy2(ECapabilityReadUserData);
+00055 
+00061 CSqlExample* CSqlExample::NewLC()
+00062         {
+00063         CSqlExample* rep = new(ELeave) CSqlExample();
+00064         CleanupStack::PushL(rep);
+00065         rep->ConstructL();
+00066         return rep;
+00067         }
+00068         
+00072 CSqlExample::CSqlExample()
+00073         {
+00074         }       
+00075 
+00076 void CSqlExample::ConstructL()
+00077         {
+00078         iConsole = Console::NewL(KTitle,TSize(KConsFullScreen,KConsFullScreen));
+00079         iConsole->Printf ( KTextPressAKey );
+00080         iConsole->Getch ();
+00081         }
+00082 
+00086 CSqlExample::~CSqlExample()
+00087         {
+00088         iConsole->Printf(KExit);
+00089         iConsole->Getch();
+00090         
+00091         delete iConsole;
+00092         }
+00093         
+00100 void CSqlExample::CreateNonSecureDBL()
+00101         {
+00102         TBuf<200> buffer;
+00103         RSqlDatabase db;
+00104                 
+00105         // Create non-secure database
+00106         iConsole->Printf(KNonSecure);
+00107         TInt error;
+00108         TRAP(error,db.Create(KDbName););
+00109 
+00110         _LIT(KTable,"CREATE TABLE A1(F1 INTEGER, F2 INTEGER)");
+00111         User::LeaveIfError(error = db.Exec(KTable));
+00112 
+00113         db.Close();
+00114         iConsole->Printf(KClose);
+00115         iConsole->Printf(KPressAKey);
+00116         iConsole->Getch();
+00117         }
+00118         
+00127 void CSqlExample::CreateAndOpenSecureDBL()
+00128         {
+00129         RSqlDatabase db;
+00130         RSqlSecurityPolicy securityPolicy;
+00131         
+00132         User::LeaveIfError(securityPolicy.Create(TSecurityPolicy(TSecurityPolicy::EAlwaysPass)));
+00133         User::LeaveIfError(securityPolicy.SetDbPolicy(RSqlSecurityPolicy::EWritePolicy, KPolicy1));
+00134                 
+00135         iConsole->Printf(KSecure);
+00136         User::LeaveIfError(db.Create(KSecureDb1, securityPolicy));
+00137                 
+00138         securityPolicy.Close(); 
+00139         
+00140         // Check that the database security policy matches the policy used 
+00141         //when the database was created.
+00142         User::LeaveIfError(db.GetSecurityPolicy(securityPolicy));
+00143         
+00144         _LIT(KSecureCreate,"CREATE TABLE secure(int_fld integer, null_int_fld integer default null)");  
+00145         User::LeaveIfError(db.Exec(KSecureCreate));
+00146 
+00147         // Attempt to write to the secure database
+00148         _LIT(KSecureInsert,"INSERT INTO secure(int_fld) values(200)");
+00149         User::LeaveIfError(db.Exec(KSecureInsert));
+00150         db.Close();
+00151         
+00152         iConsole->Printf(KOpen);
+00153         // Open the secure database     
+00154         User::LeaveIfError(db.Open(KSecureDb1));
+00155         
+00156         db.Close();
+00157         iConsole->Printf(KClose);
+00158         iConsole->Printf(KPressAKey);
+00159         iConsole->Getch();
+00160         securityPolicy.Close();
+00161 
+00162         }
+00163         
+00171 void CSqlExample::CopyDatabaseL()
+00172         {
+00173         RSqlDatabase db;        
+00174         
+00175         // Copy non-secure to non-secure database
+00176         iConsole->Printf(KCopyNonSec);  
+00177         User::LeaveIfError(RSqlDatabase::Copy(KDbName, KAnotherDbName));
+00178         
+00179         // Delete the source database
+00180         iConsole->Printf(KDelete);
+00181         User::LeaveIfError(RSqlDatabase::Delete(KAnotherDbName));
+00182 
+00183         // Create another secure database with a different security policy,
+00184         // KSecureDb1 is already created with KPolicy1.
+00185         RSqlSecurityPolicy securityPolicy;
+00186         User::LeaveIfError(securityPolicy.Create(TSecurityPolicy(TSecurityPolicy::EAlwaysPass)));
+00187         
+00188         User::LeaveIfError(securityPolicy.SetDbPolicy(RSqlSecurityPolicy::EWritePolicy, KPolicy2));
+00189                 
+00190         User::LeaveIfError(db.Create(KSecureDb2, securityPolicy));
+00191         
+00192         User::LeaveIfError(db.GetSecurityPolicy(securityPolicy));
+00193         db.Close();
+00194         
+00195         // Copy secure to secure database. The application is the database owner.
+00196         iConsole->Printf(KCopySecure);
+00197         User::LeaveIfError(RSqlDatabase::Copy(KSecureDb1, KSecureDb2));
+00198         
+00199         // Delete the source database
+00200         iConsole->Printf(KDelete);
+00201         User::LeaveIfError(RSqlDatabase::Delete(KSecureDb1));
+00202 
+00203         iConsole->Printf(KPressAKey);
+00204         iConsole->Getch();
+00205         securityPolicy.Close(); 
+00206         db.Close();
+00207 
+00208         }
+00209         
+00217 void CSqlExample::AttachDatabasesL()
+00218         {
+00219         RSqlDatabase db;
+00220         RSqlSecurityPolicy securityPolicy;
+00221 
+00222         _LIT(KAttachDb2, "Db2");
+00223         
+00224         User::LeaveIfError(db.Open(KSecureDb2));
+00225         iConsole->Printf(KAttach);
+00226         
+00227         User::LeaveIfError(db.Attach(KDbName, KAttachDb2));
+00228         
+00229         // Attempt to write to the attached non secure database
+00230         _LIT(KTabInsert,"INSERT INTO db2.a1(f1) valUES(10)");
+00231         User::LeaveIfError(db.Exec(KTabInsert));
+00232                         
+00233         // Attempt to read from the attached non secure database
+00234         _LIT(KSelect,"SELECT * FROM db2.a1");
+00235         User::LeaveIfError(db.Exec(KSelect));
+00236                 
+00237         // Attempt to write to the main secure database
+00238         _LIT(KAttachInsert,"INSERT INTO a1(f1) valUES(10)");
+00239         User::LeaveIfError(db.Exec(KAttachInsert));
+00240                 
+00241         db.Close();
+00242         iConsole->Printf(KDelete);
+00243         User::LeaveIfError(RSqlDatabase::Delete(KDbName));
+00244 
+00245         User::LeaveIfError(RSqlDatabase::Delete(KSecureDb2));
+00246 
+00247         securityPolicy.Close();
+00248         
+00249         }
+00250                 
+00257 void CSqlExample:: DataTypesQueryL()
+00258         {
+00259         RSqlDatabase db;
+00260         iConsole->Printf(KSecure);
+00261         
+00262         User::LeaveIfError(db.Create(KDbName));
+00263 
+00264         // Create a table with different numeric field types
+00265         iConsole->Printf(KCreateTable);
+00266         iConsole->Printf(KExecute);
+00267         
+00268         _LIT(KSql1, "CREATE TABLE Tbl(A INTEGER, B SMALLINT, C REAL, D DOUBLE PRECISION, E FLOAT, \
+00269                                                             F DECIMAL)");                                                   
+00270         User::LeaveIfError(db.Exec(KSql1));
+00271         
+00272         // Insert one record in to the created table
+00273         iConsole->Printf(KInsert);                                 
+00274         iConsole->Printf(KExecute);
+00275         
+00276         _LIT(KSql2, "INSERT INTO Tbl(A,B,C,D,E,F) VALUES(2000000000, 30000, 123.45, 0.912E+55,\
+00277                                             1.34E-14, 1234.5678)");
+00278         User::LeaveIfError(db.Exec(KSql2));
+00279         
+00280         // Get the inserted record data
+00281         RSqlStatement stmt;
+00282         iConsole->Printf(KPrepare);
+00283         
+00284         _LIT(KPrepQuery,"SELECT * FROM Tbl");
+00285         User::LeaveIfError(stmt.Prepare(db, KPrepQuery));
+00286         
+00287         User::LeaveIfError(stmt.Next());
+00288         stmt.Close();
+00289         
+00290         // The statement object has to be closed before db.Exec() call, 
+00291         // otherwise the reported error is "database table is locked"
+00292         // Insert second record in to the created table but inverse the column types.
+00293         iConsole->Printf(KExecute);
+00294         _LIT(KSql3, "INSERT INTO Tbl(A,   B,  C, D, E, F) VALUES(\
+00295                                                                                 -2.5,1.1,12,23,45,111)");                                                                       
+00296         User::LeaveIfError(db.Exec(KSql3)); 
+00297         
+00298         // Get the inserted record data
+00299         _LIT(KPrepQuery2,"SELECT * FROM Tbl");
+00300         User::LeaveIfError(stmt.Prepare(db, KPrepQuery2));
+00301 
+00302         User::LeaveIfError(stmt.Next());
+00303         
+00304         stmt.Close();
+00305         
+00306         // Insert third record in to the created table
+00307         _LIT(KSql4, "INSERT INTO Tbl(A,B,C,D,E,F) VALUES(\
+00308                                             2,3,123.45,1.5,2.5,1.56)");
+00309         User::LeaveIfError(db.Exec(KSql4));
+00310         
+00311         stmt.Close();
+00312         db.Close();
+00313         User::LeaveIfError(RSqlDatabase::Delete(KDbName));
+00314         }
+00315         
+00316 
+00328 void CSqlExample::ScalarFullSelectL()
+00329         {
+00330         RSqlDatabase db;
+00331         //Create  database.
+00332         User::LeaveIfError(db.Create(KDatabase));
+00333         
+00334         _LIT(KTabCreateA,"CREATE TABLE A(F1 INTEGER, F2 INTEGER, F3 FLOAT, F4 TEXT, F5 BLOB)");
+00335         User::LeaveIfError(db.Exec(KTabCreateA));
+00336         
+00337         _LIT(KTabInsert1A,"INSERT INTO A(F1, F2, F3, F4, F5) VALUES(1, 10000000000, 2.54, 'NAME1234567890', NULL)");
+00338         User::LeaveIfError(db.Exec(KTabInsert1A));
+00339         
+00340         _LIT(KTabInsert2A,"INSERT INTO A(F1, F2, F3, F4) VALUES(2, 200, -1.11, 'ADDRESS')");    
+00341         User::LeaveIfError(db.Exec(KTabInsert2A));
+00342         
+00343         RSqlStatement stmt;
+00344         CleanupClosePushL(stmt);
+00345         
+00346         _LIT(KUpdate,"UPDATE A SET F5=:P WHERE F1 = 2");
+00347         User::LeaveIfError(stmt.Prepare(db,KUpdate));
+00348 
+00349         // Open the parameter stream
+00350         RSqlParamWriteStream strm;
+00351         CleanupClosePushL(strm);
+00352         
+00353         // Prepare and set the parameter value (non-NULL value)
+00354         User::LeaveIfError(strm.BindBinary(stmt, 0));
+00355 
+00356         for(TInt i=0;i<100;++i)
+00357                 {
+00358                 strm << static_cast <TUint8> (i);       
+00359                 }
+00360         // Write the buffered data into stream
+00361         strm.CommitL();
+00362         // Execute the prepared SQL statement   
+00363         User::LeaveIfError(stmt.Exec());        
+00364                 
+00365         CleanupStack::PopAndDestroy(&strm);
+00366         CleanupStack::PopAndDestroy(&stmt);
+00367         
+00368         TSqlScalarFullSelectQuery fullSelectQuery(db);
+00369 
+00370         TBuf<100> sql;
+00371         
+00372         // Query with F2 column (64-bit integer column)
+00373         _LIT(KAnotherSql, "SELECT F2 FROM A WHERE F1 = 1");
+00374         sql.Copy(KAnotherSql);
+00375         // Read F2 as integer.
+00376         TInt valInt = fullSelectQuery.SelectIntL(sql);
+00377                 
+00378         // Read F2 as 64-bit integer. Expected value: 10000000000
+00379         TInt64  valInt64 = fullSelectQuery.SelectInt64L(sql);
+00380         
+00381         // Read F2 as real. Expected value: 10000000000.0
+00382         TReal valReal = fullSelectQuery.SelectRealL(sql);
+00383         
+00384         // Read F2 as text. Expected value: zero-length 16-bit descriptor.
+00385         TBuf<10> valText;
+00386         TInt err = fullSelectQuery.SelectTextL(sql, valText);
+00387 
+00388         // Read F2 as binary. Expected value: zero-length 8-bit descriptor.
+00389         TBuf8<10> valBinary;
+00390         err = fullSelectQuery.SelectBinaryL(sql, valBinary);
+00391         
+00392         // Query with F4 column (text column) 
+00393         _LIT(KSql4, "SELECT F4 FROM A WHERE F1 = 1");
+00394         sql.Copy(KSql4);
+00395         // Read F4 as integer. Expected value: 0.
+00396         valInt = fullSelectQuery.SelectIntL(sql);
+00397         
+00398         //Read F4 as 64-bit integer. Expected value: 0.
+00399         valInt64 = fullSelectQuery.SelectInt64L(sql);
+00400         
+00401         // Read F4 as real. Expected value: 0.0.
+00402         valReal = fullSelectQuery.SelectRealL(sql);
+00403         
+00404         // Read F4 as text. Small buffer. Expected return code: positive value, which is the column length in characters.
+00405         err = fullSelectQuery.SelectTextL(sql, valText);
+00406         
+00407         // Read F4 as text. Big enough buffer. 
+00408         TBuf<32> valText2;
+00409         err = fullSelectQuery.SelectTextL(sql, valText2);
+00410         
+00411         // Read F4 as binary. Expected error: KErrNone. Zero-length 8-bit descriptor.
+00412         err = fullSelectQuery.SelectBinaryL(sql, valBinary);
+00413 
+00414         // Read F5 as binary. Big enough buffer.
+00415         TBuf8<100> valBinary2;
+00416         err = fullSelectQuery.SelectBinaryL(sql, valBinary2);
+00417         
+00418         // Text column value, small buffer, reallocation 
+00419         HBufC* hbuf = HBufC::NewLC(10);
+00420         TPtr name = hbuf->Des();
+00421         sql.Copy(KSql4);
+00422         err = fullSelectQuery.SelectTextL(sql, name);
+00423         
+00424         if(err > 0)
+00425                 {
+00426                 hbuf = hbuf->ReAllocL(err);
+00427                 CleanupStack::Pop();    
+00428                 CleanupStack::PushL(hbuf);
+00429                 name.Set(hbuf->Des());
+00430                 err = fullSelectQuery.SelectTextL(sql, name);
+00431         
+00432                 }
+00433         CleanupStack::PopAndDestroy(); // hbuf, can't be put as parameter, because may be reallocated
+00434         //Close database, delete database file.
+00435         db.Close();
+00436         User::LeaveIfError(RSqlDatabase::Delete(KDatabase));
+00437         
+00438         }
+00439         
+00447 void CSqlExample::ColumnBinaryStreamL()
+00448         {
+00449         RSqlDatabase db;
+00450         TInt error;
+00451         User::LeaveIfError(db.Create(KDbName));
+00452 
+00453         // Create a table
+00454         _LIT(KSqlStmt1, "CREATE TABLE A(Fld1 INTEGER, Fld2 BLOB);");
+00455         
+00456         User::LeaveIfError(error = db.Exec(KSqlStmt1));
+00457         const TInt KDataLen = 100;
+00458 
+00459         _LIT(KSqlStmt2, "INSERT INTO A(Fld1, Fld2) VALUES(");
+00460         
+00461         // Allocate a buffer for the SQL statement.
+00462         HBufC8* buf = HBufC8::New(KSqlStmt2().Length() + KDataLen * 2 + 10);
+00463         
+00464         TPtr8 sql = buf->Des();
+00465         
+00466         // Insert row 1
+00467 
+00468         _LIT(KHexValStr1, "7E");
+00469         sql.Copy(KSqlStmt2);
+00470         
+00471         _LIT(KString1, "1, x'");
+00472         sql.Append(KString1);
+00473         TInt i;
+00474         for(i=0;i<KDataLen;++i)
+00475                 {
+00476                 sql.Append(KHexValStr1);
+00477                 }
+00478         _LIT(KString2, "')");   
+00479         sql.Append(KString2);
+00480 
+00481         User::LeaveIfError(db.Exec(sql));
+00482 
+00483         // Insert row 2
+00484         
+00485         _LIT(KHexValStr2, "A3");
+00486         sql.Copy(KSqlStmt2);
+00487         _LIT(KString3, "2, x'");
+00488         sql.Append(KString3);
+00489         for(i=0;i<KDataLen;++i)
+00490                 {
+00491                 sql.Append(KHexValStr2);
+00492                 }
+00493         _LIT(KString4, "')");
+00494         sql.Append(KString4);
+00495 
+00496         User::LeaveIfError(db.Exec(sql));
+00497         
+00498         // Prepare SELECT SQL statement
+00499         _LIT(KSqlStmt3, "SELECT * FROM A");
+00500         
+00501         RSqlStatement stmt;
+00502         User::LeaveIfError(error = stmt.Prepare(db, KSqlStmt3));
+00503         
+00504         // Move on row 1
+00505         User::LeaveIfError(error = stmt.Next());
+00506 
+00507         // Read the long binary column using a stream
+00508         RSqlColumnReadStream columnStream;
+00509         error = columnStream.ColumnBinary(stmt, 1);
+00510         
+00511         
+00512         TInt size = stmt.ColumnSize(1);
+00513         TPtr8 colData = buf->Des();
+00514         TRAP(error, columnStream.ReadL(colData, size));
+00515         columnStream.Close();
+00516         
+00517         User::LeaveIfError(stmt.Next());
+00518                 
+00519         //Read row 2 using ColumnBinary(TInt aColumnIndex, TDes8& aDest).
+00520         error = stmt.ColumnBinary(1, colData);
+00521 
+00522         //Read row 2 using a stream
+00523         colData.Zero();
+00524         error = columnStream.ColumnBinary(stmt, 1);
+00525         
+00526         size = stmt.ColumnSize(1);
+00527         TRAP(error, columnStream.ReadL(colData, size));
+00528         columnStream.Close();
+00529         
+00530         stmt.Close();
+00531                 
+00532         delete buf; 
+00533         buf = NULL;
+00534         
+00535         db.Close();
+00536 
+00537         error = RSqlDatabase::Delete(KDbName);
+00538         
+00539         }
+00540 
+00541                                 
+00542 LOCAL_C void MainL()
+00543         {
+00544         // Create an Active Scheduler to handle asychronous calls
+00545         CActiveScheduler* scheduler = new (ELeave) CActiveScheduler;
+00546         CleanupStack::PushL(scheduler);
+00547         CActiveScheduler::Install( scheduler );
+00548         CSqlExample* app = CSqlExample::NewLC();
+00549         
+00550         // Create a non secure database
+00551         app->CreateNonSecureDBL();
+00552         
+00553         // Create and open a secure database
+00554         app->CreateAndOpenSecureDBL();
+00555         
+00556         // Copy two databases
+00557         app->CopyDatabaseL();
+00558         
+00559         // Attach two databases
+00560         app->AttachDatabasesL();
+00561         
+00562         // Simple query and query with paramaters
+00563         app->DataTypesQueryL();
+00564         
+00565         //Prepares and executes a query with a large parameter
+00566         // writing that parameter using streaming (RParamWriteStream)
+00567         app->ScalarFullSelectL();
+00568         
+00569         // Prepare and execute a query which returns data,
+00570         // and read that data using streaming (RColumnReadStream)
+00571         app->ColumnBinaryStreamL();
+00572                 
+00573         CleanupStack::PopAndDestroy(2); //app, scheduler
+00574         
+00575         }
+00576 
+00577 GLDEF_C TInt E32Main()
+00578         {
+00579     __UHEAP_MARK;
+00580     CTrapCleanup* cleanup = CTrapCleanup::New();
+00581     if(cleanup == NULL)
+00582         {
+00583         return KErrNoMemory;
+00584         }
+00585     TRAPD(err, MainL());
+00586         if(err != KErrNone)
+00587                 {
+00588                 User::Panic(_L("Failed to complete"),err);
+00589                 }
+00590 
+00591     delete cleanup;
+00592     __UHEAP_MARKEND;
+00593     return KErrNone;
+00594         }
+