|
1 <?xml version="1.0" encoding="utf-8"?> |
|
2 <!-- Copyright (c) 2007-2010 Nokia Corporation and/or its subsidiary(-ies) All rights reserved. --> |
|
3 <!-- This component and the accompanying materials are made available under the terms of the License |
|
4 "Eclipse Public License v1.0" which accompanies this distribution, |
|
5 and is available at the URL "http://www.eclipse.org/legal/epl-v10.html". --> |
|
6 <!-- Initial Contributors: |
|
7 Nokia Corporation - initial contribution. |
|
8 Contributors: |
|
9 --> |
|
10 <!DOCTYPE concept |
|
11 PUBLIC "-//OASIS//DTD DITA Concept//EN" "concept.dtd"> |
|
12 <concept xml:lang="en" id="GUID-22B535FA-6C8A-5B39-9CD7-6E85A86B7C2C"><title>Basic SQL Example: basicsqlexample.cpp </title><prolog><metadata><keywords/></metadata></prolog><conbody><p>A copy of the basicsqlexample.cpp file for the basic SQL example application. </p> <section><title>Introduction</title> <p>The functions demonstrate how to perform several SQL operations. </p> </section> <section><title>Code</title> <p>Defines the functions. </p> <codeblock id="GUID-7DB281F3-2F11-542A-BE71-DDFBDD113A6C" xml:space="preserve">// basicsqlexample.cpp |
|
13 // |
|
14 // Copyright (c) Nokia Ltd 2009. All rights reserved. |
|
15 // |
|
16 /** This example application demonstrates several SQL operations including: |
|
17 <ul> |
|
18 <li>Creating and managing a database</li> |
|
19 <li>Creating and managing tables</li> |
|
20 <li>Querying a database</li> |
|
21 </ul> |
|
22 |
|
23 @file basicsqlquery.cpp |
|
24 */ |
|
25 #include <e32cons.h> |
|
26 #include <SqlDb.h> |
|
27 #include "basicsqlexample.h" |
|
28 /* *************************************************** */ |
|
29 |
|
30 /* SQL Statements: */ |
|
31 _LIT(KTabCreate,"CREATE TABLE Pets( person TEXT, cat SMALLINT, dog SMALLINT, rodent SMALLINT, bird SMALLINT);"); |
|
32 |
|
33 /* An INSERT statement. Long version. */ |
|
34 //Pets of Jon Doe |
|
35 _LIT(KTabInsert1,"INSERT INTO Pets (person, cat, dog, rodent, bird) VALUES ('Jon Doe', 0, 1, 1, 0);"); |
|
36 |
|
37 /* Simplified insert statement text. */ |
|
38 //Pets of Jane Roe |
|
39 _LIT(KTabInsert2,"INSERT INTO Pets VALUES ('Jane Roe',2,1,0,0);"); |
|
40 //Pets of Tom Moe |
|
41 _LIT(KTabInsert3,"INSERT INTO Pets VALUES ('Tom Moe',1,0,0,3);"); |
|
42 |
|
43 /* SQL SELECT Statements. */ |
|
44 _LIT(KSelect1,"SELECT person FROM Pets WHERE cat >= 1;"); |
|
45 _LIT(KSelect2,"SELECT person FROM Pets WHERE cat >= 1 AND dog >= 1;"); |
|
46 _LIT(KSelect3,"SELECT person FROM Pets WHERE rodent >= 1;"); |
|
47 |
|
48 /* Various messages */ |
|
49 _LIT(KTitle, "Basic SQL example"); |
|
50 _LIT(KTextPressAKey, "\n\nPress any key to step through the example\n"); |
|
51 _LIT(KExit,"Press any key to exit the application\n"); |
|
52 _LIT(KPressAKey,"\nPress a key to continue\n"); |
|
53 _LIT(KDatabaseMsg,"\nCreating a database\n"); |
|
54 _LIT(KOpen,"Opening the database\n"); |
|
55 _LIT(KClose,"Closing the database\n"); |
|
56 _LIT(KCreateTable,"\nCreating a table\n"); |
|
57 _LIT(KInsert,"Inserting records into the table\n"); |
|
58 _LIT(KPrepare,"Preparing a query\n"); |
|
59 _LIT(KExecute,"Executing a query\n"); |
|
60 |
|
61 /* Constants used with the SQL operations. */ |
|
62 _LIT(KDatabaseName, "\\Basic_db.db"); |
|
63 _LIT(KPerson,"person"); |
|
64 _LIT(KCat,"cat"); |
|
65 _LIT(KDog,"dog"); |
|
66 _LIT(KRodent,"rodent"); |
|
67 _LIT(KBird,"bird"); |
|
68 |
|
69 /* End of SQL Statements. */ |
|
70 |
|
71 /* *************************************************** */ |
|
72 /** |
|
73 Allocates and constructs a CBasicSqlExample object and |
|
74 leaves it on the cleanup stack. |
|
75 Initialises all member data to their default values. |
|
76 */ |
|
77 CBasicSqlExample* CBasicSqlExample::NewLC() |
|
78 { |
|
79 CBasicSqlExample* rep = new(ELeave) CBasicSqlExample(); |
|
80 CleanupStack::PushL(rep); |
|
81 rep->ConstructL(); |
|
82 return rep; |
|
83 } |
|
84 /* *************************************************** */ |
|
85 |
|
86 /** |
|
87 Constructor |
|
88 */ |
|
89 CBasicSqlExample::CBasicSqlExample() |
|
90 { |
|
91 } |
|
92 |
|
93 void CBasicSqlExample::ConstructL() |
|
94 { |
|
95 iConsole = Console::NewL(KTitle,TSize(KConsFullScreen,KConsFullScreen)); |
|
96 } |
|
97 /* *************************************************** */ |
|
98 |
|
99 /** |
|
100 Destructor |
|
101 */ |
|
102 CBasicSqlExample::~CBasicSqlExample() |
|
103 { |
|
104 iPetDb.Close(); |
|
105 |
|
106 Prompt(KExit); |
|
107 delete iConsole; |
|
108 } |
|
109 /* *************************************************** */ |
|
110 |
|
111 void CBasicSqlExample::Prompt() |
|
112 { |
|
113 iConsole->Printf(KPressAKey); |
|
114 iConsole->Getch(); |
|
115 } |
|
116 void CBasicSqlExample::Prompt(const TDesC& aText) |
|
117 { |
|
118 iConsole->Printf(aText); |
|
119 iConsole->Printf(KPressAKey); |
|
120 iConsole->Getch(); |
|
121 } |
|
122 |
|
123 /** Creates a Database |
|
124 @leave KErrNotFound, KErrAbort, KErrPermissionDenied, |
|
125 KErrArgument, system-wide error codes. |
|
126 */ |
|
127 void CBasicSqlExample::CreateDatabaseL() |
|
128 { |
|
129 RSqlDatabase db; |
|
130 |
|
131 iConsole->Printf(KDatabaseMsg); |
|
132 //create the database |
|
133 User::LeaveIfError(db.Create(KDatabaseName)); |
|
134 |
|
135 iConsole->Printf(KDatabaseName); |
|
136 CleanupClosePushL(db); |
|
137 |
|
138 User::LeaveIfError(db.Exec(KTabCreate)); |
|
139 CleanupStack::PopAndDestroy(1); |
|
140 |
|
141 Prompt(KClose); |
|
142 } |
|
143 /* *************************************************** */ |
|
144 |
|
145 |
|
146 /** Add a few rows to the table. |
|
147 This function shows how to insert data in a table. |
|
148 */ |
|
149 void CBasicSqlExample::PopulateDatabaseL() |
|
150 { |
|
151 RSqlDatabase db; |
|
152 |
|
153 // Open the database that was created earlier. |
|
154 User::LeaveIfError(db.Open(KDatabaseName)); |
|
155 CleanupClosePushL(db); |
|
156 Prompt(KOpen); |
|
157 |
|
158 // Inserts the first row. |
|
159 User::LeaveIfError(db.Exec(KTabInsert1)); |
|
160 Prompt(KTabInsert1); |
|
161 |
|
162 // Inserts the second row. |
|
163 User::LeaveIfError(db.Exec(KTabInsert2)); |
|
164 Prompt(KTabInsert2); |
|
165 |
|
166 // Inserts the third row. |
|
167 User::LeaveIfError(db.Exec(KTabInsert3)); |
|
168 Prompt(KTabInsert3); |
|
169 |
|
170 // Close the database. |
|
171 // cleanup the database. |
|
172 Prompt(KClose); |
|
173 CleanupStack::PopAndDestroy(1); |
|
174 } |
|
175 /* *************************************************** */ |
|
176 |
|
177 /** Querying the database. |
|
178 This function shows how to get information from the database. |
|
179 It provides a basic query. |
|
180 */ |
|
181 void CBasicSqlExample::SqlQueryL(const TDesC& aStatement) |
|
182 { |
|
183 RSqlStatement stmt; |
|
184 iConsole->Printf(_L("Running Query:\n%S\n"), &aStatement); |
|
185 |
|
186 User::LeaveIfError(stmt.Prepare(iPetDb, aStatement)); |
|
187 CleanupClosePushL(stmt); |
|
188 |
|
189 TInt personIndex = stmt.ColumnIndex(KPerson); // index. This doesn't change after query preparation. |
|
190 TInt rc = KErrNone; |
|
191 while ((rc = stmt.Next()) == KSqlAtRow) |
|
192 { |
|
193 // Do something with the results |
|
194 TPtrC myData = stmt.ColumnTextL(personIndex); // read return data |
|
195 iConsole->Printf(_L("Person=%S\n"), &myData); |
|
196 } |
|
197 if (rc != KSqlAtEnd) |
|
198 { |
|
199 _LIT(KErrSQLError, "Error %d returned from RSqlStatement::Next()."); |
|
200 iConsole->Printf(KErrSQLError, rc); |
|
201 } |
|
202 Prompt(); |
|
203 CleanupStack::PopAndDestroy(1); |
|
204 } |
|
205 |
|
206 void CBasicSqlExample::OpenDatabaseL() |
|
207 { |
|
208 User::LeaveIfError(iPetDb.Open(KDatabaseName)); |
|
209 } |
|
210 |
|
211 void CBasicSqlExample::CloseDatabase() |
|
212 { |
|
213 iPetDb.Close(); |
|
214 } |
|
215 |
|
216 void CBasicSqlExample::DeleteDatabase() |
|
217 { |
|
218 // should be safe if db closed, catch if db left open. |
|
219 iPetDb.Close(); |
|
220 iPetDb.Delete(KDatabaseName); |
|
221 } |
|
222 |
|
223 void CBasicSqlExample::ExampleL() |
|
224 { |
|
225 TRAPD(err, DoExampleL()); |
|
226 // Remove database file for next run of example. |
|
227 DeleteDatabase(); |
|
228 User::LeaveIfError(err); |
|
229 } |
|
230 |
|
231 void CBasicSqlExample::DoExampleL() |
|
232 { |
|
233 // Create a database |
|
234 CreateDatabaseL(); |
|
235 |
|
236 // Add table and data |
|
237 PopulateDatabaseL(); |
|
238 |
|
239 // Query the data |
|
240 OpenDatabaseL(); |
|
241 SqlQueryL(KSelect1); |
|
242 SqlQueryL(KSelect2); |
|
243 SqlQueryL(KSelect3); |
|
244 CloseDatabase(); |
|
245 } |
|
246 |
|
247 /* *************************************************** */ |
|
248 |
|
249 template <class T>class TRunExample |
|
250 { |
|
251 public: |
|
252 static void RunExample(){ |
|
253 // Create an Active Scheduler to handle asychronous calls |
|
254 CActiveScheduler* scheduler = new (ELeave) CActiveScheduler; |
|
255 CleanupStack::PushL(scheduler); |
|
256 CActiveScheduler::Install( scheduler ); |
|
257 T* app = T::NewLC(); |
|
258 app->ExampleL(); |
|
259 CleanupStack::PopAndDestroy(2); //app, scheduler |
|
260 }; |
|
261 }; |
|
262 |
|
263 GLDEF_C TInt E32Main() |
|
264 { |
|
265 __UHEAP_MARK; |
|
266 CTrapCleanup* cleanup = CTrapCleanup::New(); |
|
267 if(cleanup == NULL) |
|
268 { |
|
269 return KErrNoMemory; |
|
270 } |
|
271 TRunExample<CBasicSqlExample> example; |
|
272 // TRunExample<CComplexSqlExample> example2; |
|
273 // TRunExample<CEvenMoreComplexSqlExample> example3; |
|
274 |
|
275 TRAPD(err, example.RunExample()); |
|
276 // TRAP(err, example2.RunExample()); |
|
277 // TRAP(err, example3.RunExample()); |
|
278 |
|
279 if(err != KErrNone) |
|
280 { |
|
281 User::Panic(_L("Failed to complete"),err); |
|
282 } |
|
283 delete cleanup; |
|
284 __UHEAP_MARKEND; |
|
285 return KErrNone; |
|
286 }</codeblock> </section> </conbody></concept> |