-
Notifications
You must be signed in to change notification settings - Fork 7
/
myapitut.html
1378 lines (1234 loc) · 58.4 KB
/
myapitut.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html><head><meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"><title>3. Tutorial</title><link rel="stylesheet" href="userman.css" type="text/css"><meta name="generator" content="DocBook XSL Stylesheets V1.58.1"><link rel="home" href="index.html" title="MySQL++ User Manual"><link rel="up" href="index.html" title="MySQL++ User Manual"><link rel="previous" href="overview.html" title="2. Overview"><link rel="next" href="tquery.html" title="4. Template Queries"></head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">3. Tutorial</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="overview.html">Prev</a> </td><th width="60%" align="center"> </th><td width="20%" align="right"> <a accesskey="n" href="tquery.html">Next</a></td></tr></table><hr></div><div class="sect1" lang="en"><div class="titlepage"><div><h2 class="title" style="clear: both"><a name="tutorial"></a>3. Tutorial</h2></div></div><p>This tutorial is meant to give you a jump start in using
MySQL++. While it is a very complicated and powerful library,
it's possible to make quite functional programs without tapping
but a fraction of its power. This section will introduce you to
the most useful fraction.</p><p>This tutorial assumes you know C++ fairly well,
in particuler the Standard Template Library (STL) and
exceptions.</p><div class="sect2" lang="en"><div class="titlepage"><div><h3 class="title"><a name="id2860110"></a>3.1. Running the Examples</h3></div></div><p>All of the examples are complete running
programs. They may or may not be built for you already,
depending on how you installed the library.</p><p>If you installed MySQL++ from the source
tarball on a Unixy system, the examples should
have been built along with the library. If not,
simply go into the examples directory and type
<tt>make</tt>.</p><p>If you installed the library via
RPM, the examples are in the mysql++-devel
RPM. After installing that, the examples are in
<tt>/usr/src/mysql++/examples</tt>.
To build them, go into that directory
and type <tt>make -f
Makefile.simple</tt>. See the file
<tt>/usr/share/doc/mysql++-devel*/README.examples</tt>
for more details.</p><p>If you are on a Windows system, the build process
for the library should have built the examples as
well. Where the programs are depends on which compiler
you're using. There should be a README.* file in the
distribution specific to your compiler with further
instructions.</p><p>Once you have the examples building, you need
to initialize the sample database by running the
<tt>resetdb</tt> example. The
usage of resetdb is as follows:</p><pre class="screen"> resetdb [host [user [password [port]]]]</pre><p>If you leave off host, localhost is assumed. If
you leave off user, your current username is assumed. If
you leave of the password, it is assumed that you don't
need one. And if you leave off the port, it will use
the standard MySQL port number.</p><p>The user you give resetdb needs to be an account
with permission to create databases. Once the database is
created you can use any account that has full permission
to the sample database mysql_cpp_data.</p><p>You may also have to re-run resetdb after
running some of the other examples, as they change the
database.</p></div><div class="sect2" lang="en"><div class="titlepage"><div><h3 class="title"><a name="id2860223"></a>3.2. A Simple Example</h3></div></div><p>The following example demonstrates how to
open a connection, execute a simple query, and
display the results. This is
<tt>examples/simple1.cpp</tt>:</p><pre class="programlisting">
#include "util.h"
#include <mysql++.h>
#include <iostream>
#include <iomanip>
using namespace std;
int
main(int argc, char *argv[])
{
// Connect to the sample database.
mysqlpp::Connection con(false);
if (!connect_to_db(argc, argv, con)) {
return 1;
}
// Retrieve a subset of the sample stock table set up by resetdb
mysqlpp::Query query = con.query();
query << "select item from stock";
mysqlpp::Result res = query.store();
// Display the result set
cout << "We have:" << endl;
if (res) {
char buf[100];
mysqlpp::Row row;
mysqlpp::Row::size_type i;
for (i = 0; row = res.at(i); ++i) {
cout << '\t' << utf8trans(row.at(0), buf, sizeof(buf)) << endl;
}
}
else {
cerr << "Failed to get item list: " << query.error() << endl;
return 1;
}
return 0;
}
</pre><p>This example simply gets the entire "item"
column from the example table, and prints those
values out.</p><p>Notice that MySQL++ lets you store
result sets in STL containers, such as
<tt>std::vector</tt>.
We iterate through the result set just as you would
with any other STL container. The only tricky bit is
the <tt>it->at(0)</tt> idiom.
This dereferences the iterator, which yields a <tt><a href="../../refman/html/classmysqlpp_1_1Row.html">Row</a></tt> object, on which we call
the <tt>at()</tt> method to
retrieve the first field.</p><p>The only thing that isn't explicitly
handled in the code block above is that
we delegate connection establishment to
<tt>connect_to_db()</tt>
in the <tt>util</tt> module.
We do this only because that function also handles
the command line parsing for the examples, so they
have a consistent interface.</p></div><div class="sect2" lang="en"><div class="titlepage"><div><h3 class="title"><a name="id2860327"></a>3.3. Examples' Utility Module</h3></div></div><p>I referred to the
<tt>util</tt> module
above. Following is the source for that module,
which also contains other functions used by other
examples. It isn't important to understand this module
in detail, but understanding its outlines will make
the following examples more clear.</p><pre class="programlisting">
#include "util.h"
#include <iostream>
#include <iomanip>
#include <stdlib.h>
using namespace std;
const char* kpcSampleDatabase = "mysql_cpp_data";
//// utf8trans /////////////////////////////////////////////////////////
// Converts a Unicode string encoded in UTF-8 form (which the MySQL
// database uses) to a form suitable for outputting to the standard C++
// cout stream. Functionality is platform-specific.
char*
utf8trans(const char* utf8_str, char* out_buf, int buf_len)
{
#ifdef MYSQLPP_PLATFORM_WINDOWS
// It's Win32, so assume console output, where output needs to be in
// local ANSI code page by default.
wchar_t ucs2_buf[100];
static const int ub_chars = sizeof(ucs2_buf) / sizeof(ucs2_buf[0]);
// First, convert UTF-8 string to UCS-2
if (MultiByteToWideChar(CP_UTF8, 0, utf8_str, -1,
ucs2_buf, ub_chars) > 0) {
// Next, convert UCS-2 to local code page.
CPINFOEX cpi;
GetCPInfoEx(CP_OEMCP, 0, &cpi);
WideCharToMultiByte(cpi.CodePage, 0, ucs2_buf, -1,
out_buf, buf_len, 0, 0);
return out_buf;
}
else {
int err = GetLastError();
if (err == ERROR_NO_UNICODE_TRANSLATION) {
cerr << "Bad data in UTF-8 string" << endl;
}
else {
cerr << "Unknown error in Unicode translation: " <<
GetLastError() << endl;
}
return 0;
}
#else
// Assume a modern Unixy platform, where the system's terminal I/O
// code handles UTF-8 directly. (e.g. common Linux distributions
// since 2001 or so, recent versions of Mac OS X, etc.)
strncpy(out_buf, utf8_str, buf_len);
return out_buf;
#endif
}
//// print_stock_header ////////////////////////////////////////////////
// Display a header suitable for use with print_stock_rows().
void
print_stock_header(int rows)
{
cout << "Records found: " << rows << endl << endl;
cout.setf(ios::left);
cout << setw(21) << "Item" <<
setw(10) << "Num" <<
setw(10) << "Weight" <<
setw(10) << "Price" <<
"Date" << endl << endl;
}
//// print_stock_row ///////////////////////////////////////////////////
// Print out a row of data from the stock table, in a format compatible
// with the header printed out in the previous function.
void
print_stock_row(const std::string& item, mysqlpp::longlong num,
double weight, double price, const mysqlpp::Date& date)
{
// We do UTF-8 translation on item field because there is Unicode
// data in this field in the sample database, and some systems
// cannot handle UTF-8 sent directly to cout.
char buf[100];
cout << setw(20) << utf8trans(item.c_str(), buf, sizeof(buf)) << ' ' <<
setw(9) << num << ' ' <<
setw(9) << weight << ' ' <<
setw(9) << price << ' ' <<
date << endl;
}
//// print_stock_row ///////////////////////////////////////////////////
// Take a Row from the example 'stock' table, break it up into fields,
// and call the above version of this function.
void
print_stock_row(const mysqlpp::Row& row)
{
// The brief code below illustrates several aspects of the library
// worth noting:
//
// 1. You can subscript a row by integer (position of the field in
// the row) or by string (name of field in the row). The former is
// more efficient, while the latter trades some efficiency for
// robustness in the face of schema changes. (Consider using SSQLS
// if you need a tradeoff in between these two positions.)
//
// 2. You can also get at a row's field's with Row::at(), which is
// much like Row::operator[](int). Besides the syntax difference,
// the only practical difference is that only at() can access field
// 0: this is because '0' can be converted to both int and to const
// char*, so the compiler rightly complains that it can't decide
// which overload to call.
//
// 3. Notice that we make an explicit temporary copy of the first
// field, which is the only string field. We must tolerate the
// inefficiency of this copy, because Row::operator[] returns a
// ColData object, which goes away after it is converted to some
// other form. So, while we could have made print_stock_row()
// take a const char* argument (as past versions mistakenly did!)
// this would result in a dangling pointer, since it points into the
// ColData object, which is dead by the time the pointer is
// evaluated in print_stock_row(). It will probably even work this
// way, but like any memory bug, it can wreak subtle havoc.
std::string item(row.at(0));
print_stock_row(item, row["num"], row[2], row[3], row[4]);
}
//// print_stock_rows //////////////////////////////////////////////////
// Print out a number of rows from the example 'stock' table.
void
print_stock_rows(mysqlpp::Result& res)
{
print_stock_header(res.size());
// Use the Result class's read-only random access iterator to walk
// through the query results.
mysqlpp::Result::iterator i;
for (i = res.begin(); i != res.end(); ++i) {
// Notice that a dereferenced result iterator can be converted
// to a Row object, which makes for easier element access.
print_stock_row(*i);
}
}
//// get_stock_table ///////////////////////////////////////////////////
// Retreive the entire contents of the example 'stock' table.
void
get_stock_table(mysqlpp::Query& query, mysqlpp::Result& res)
{
// Reset the query object, in case we're re-using it.
query.reset();
// You can write to the query object like you would any ostream.
query << "select * from stock";
// Show the query string. If you call preview(), it must be before
// you call execute() or store() or use().
cout << "Query: " << query.preview() << endl;
// Execute the query, storing the results in memory.
res = query.store();
}
//// connect_to_db /////////////////////////////////////////////////////
// Establishes a connection to a MySQL database server, optionally
// attaching to database kdb. This is basically a command-line parser
// for the examples, since the example programs' arguments give us the
// information we need to establish the server connection.
bool
connect_to_db(int argc, char *argv[], mysqlpp::Connection& con,
const char *kdb)
{
if (argc < 1) {
cerr << "Bad argument count: " << argc << '!' << endl;
return false;
}
if (!kdb) {
kdb = kpcSampleDatabase;
}
if ((argc > 1) && (argv[1][0] == '-')) {
cout << "usage: " << argv[0] <<
" [host] [user] [password] [port]" << endl;
cout << endl << "\tConnects to database ";
if (strlen(kdb) > 0) {
cout << '"' << kdb << '"';
}
else {
cout << "server";
}
cout << " on localhost using your user" << endl;
cout << "\tname and no password by default." << endl << endl;
return false;
}
if (argc == 1) {
con.connect(kdb);
}
else if (argc == 2) {
con.connect(kdb, argv[1]);
}
else if (argc == 3) {
con.connect(kdb, argv[1], argv[2]);
}
else if (argc == 4) {
con.connect(kdb, argv[1], argv[2], argv[3]);
}
else if (argc >= 5) {
con.connect(kdb, argv[1], argv[2], argv[3], atoi(argv[4]));
}
if (con) {
return true;
}
else {
cerr << "Database connection failed: " << con.error() << endl;
return false;
}
}
</pre><p>This is actually an abridged version of util.cpp,
with the Unicode stuff removed. The interaction
between MySQL, MySQL++ and Unicode is covered in a
later chapter, <a href="unicode.html">Using Unicode with MySQL++</a>.</p></div><div class="sect2" lang="en"><div class="titlepage"><div><h3 class="title"><a name="id2860383"></a>3.4. A More Complicated Example</h3></div></div><p>The <tt>simple1</tt>
example above was pretty trivial. Let's
get a little deeper. Here is
<tt>examples/simple2.cpp</tt>:</p><pre class="programlisting">
#include "util.h"
#include <mysql++.h>
#include <iostream>
#include <iomanip>
using namespace std;
int
main(int argc, char *argv[])
{
// Connect to the sample database.
mysqlpp::Connection con(false);
if (!connect_to_db(argc, argv, con)) {
return 1;
}
// Retrieve the sample stock table set up by resetdb
mysqlpp::Query query = con.query();
query << "select * from stock";
mysqlpp::Result res = query.store();
// Display results
if (res) {
// Display header
cout.setf(ios::left);
cout << setw(21) << "Item" <<
setw(10) << "Num" <<
setw(10) << "Weight" <<
setw(10) << "Price" <<
"Date" << endl << endl;
// Get each row in result set, and print its contents
char buf[100];
mysqlpp::Row row;
mysqlpp::Row::size_type i;
for (i = 0; row = res.at(i); ++i) {
cout << setw(20) <<
utf8trans(row["item"], buf, sizeof(buf)) << ' ' <<
setw(9) << row["num"] << ' ' <<
setw(9) << row["weight"] << ' ' <<
setw(9) << row["price"] << ' ' <<
setw(9) << row["sdate"] <<
endl;
}
}
else {
cerr << "Failed to get stock table: " << query.error() << endl;
return 1;
}
return 0;
}
</pre><p>This example illustrates several new concepts.</p><p>First, notice that we store the result set in
a <tt><a href="../../refman/html/classmysqlpp_1_1Result.html">Result</a></tt> object. Like
the a <tt>std::vector</tt> we
used in the <tt>simple1</tt>
example, <tt>Result</tt>
is a container type, so iterating through it
is straightforward. The main difference is that
<tt>Result</tt> is less
distanced from the way the underlying MySQL C API
works, so it is somewhat more efficient.</p><p>Second, we access each row's data indirectly
through a <tt><a href="../../refman/html/classmysqlpp_1_1Row.html">Row</a></tt>
object. This affords several nice features, such
as the ability to access a field by name. You can
also access fields by position, as we did in the
<tt>simple1</tt> example,
which is more efficient, but makes your code less
flexible.</p></div><div class="sect2" lang="en"><div class="titlepage"><div><h3 class="title"><a name="exceptions"></a>3.5. Exceptions</h3></div></div><p>By default, MySQL++ uses exceptions to signal
errors. Most of the examples have a full set of
exception handlers. This is worthy of emulation.</p><p>All of MySQL++'s custom exceptions derive
from a common base class, <tt><a href="../../refman/html/classmysqlpp_1_1Exception.html">Exception</a></tt>. That in turn derives from the
Standard C++ exception base class,
<tt>std::exception</tt>.
Since the library can indirectly cause exceptions
to come from the Standard C++ Library, it's possible
to catch all exceptions from MySQL++ by just catching
<tt>std::exception</tt> by
reference. However, it's usually better to catch
the all of the concret eexception types that you
expect, and add a handler for
<tt>Exception</tt> or
<tt>std::exception</tt> to
act as a "catch-all" for unexpected exceptions.</p><p>Some of these exceptions are optional.
When disabled, the object signals errors in
some other way, typically by returning an
error code or setting an error flag. Classes
that support this feature derive from
<tt><a href="../../refman/html/classmysqlpp_1_1OptionalExceptions.html">OptionalExceptions</a></tt>.
Moreover, when such an object creates another object
that also derives from this interface, it passes on its
exception flag. Since everything flows from the
<tt><a href="../../refman/html/classmysqlpp_1_1Connection.html">Connection</a></tt>
object, disabling exceptions on it at the start of
the program disables all optional exceptions. You can
see this technique at work in the "simple" examples,
which keeps them, well, simple.</p><p>Real-world code typically can't afford to
lose out on the additional information and control
offered by exceptions. But at the same time, it is
still sometimes useful to disable exceptions
temporarily. To do this, put the section of code
that you want to not throw exceptions inside a
block, and create a <tt><a href="../../refman/html/classmysqlpp_1_1NoExceptions.html">NoExceptions</a></tt> object at the top of that
block. When created, it saves the exception flag
of the <tt>OptionalExceptions</tt>
derivative you pass to it, and then disables
exceptions on it. When the
<tt>NoExceptions</tt> object
goes out of scope at the end of the block, it
restores the exceptions flag to its previous state.
See <tt>examples/resetdb.cpp</tt> to
see this technique at work.</p><p>When one
<tt>OptionalExceptions</tt>
derivative creates another such object and passes
on its exception flag, it passes a copy of the
flag. Therefore, the two objects' flags operate
independently after the new one is created. There's
no way to globally enable or disable this flag on
existing objects in a single call.</p><p>There are a few classes of exceptions MySQL++
can throw that are not optional:</p><div class="itemizedlist"><ul type="disc"><li><p>The largest set of non-optional
exceptions are those from the Standard C++
Library. For instance, if your code said
"<tt>row[21]</tt>"
on a row containing only 5 fields, the
<tt>std::vector</tt>
underlying the row object will throw an
exception. (It will, that is, if it conforms to
the standard.) You might consider wrapping your
program's main loop in a try block catching
<tt>std::exception</tt>s,
just in case you trigger one of these
exceptions.</p></li><li><p><tt><a href="../../refman/html/classmysqlpp_1_1ColData__Tmpl.html">ColData</a></tt>
will always throw <tt><a href="../../refman/html/classmysqlpp_1_1BadConversion.html">BadConversion</a></tt>
when you ask it to do an improper type
conversion. For example, you'll get an
exception if you try to convert "1.25"
to <tt>int</tt>,
but not when you convert "1.00" to
<tt>int</tt>. In
the latter case, MySQL++ knows that it
can safely throw away the fractional
part.</p></li><li><p>If you use template
queries and don't pass enough parameters
when instantiating the template,
<tt>Query</tt>
will throw a <tt><a href="../../refman/html/classmysqlpp_1_1BadParamCount.html">BadParamCount</a></tt>
exception.</p></li></ul></div><p>It's educational to modify the examples to
force exceptions. For instance, misspell a field
name, use an out-of-range index, or change a type
to force a <tt>ColData</tt>
conversion error.</p></div><div class="sect2" lang="en"><div class="titlepage"><div><h3 class="title"><a name="id2806700"></a>3.6. Quoting and Escaping</h3></div></div><p>SQL syntax often requires certain data to be
quoted. Consider this query:</p><pre class="programlisting">
SELECT * FROM stock WHERE item = 'Hotdog Buns' </pre><p>Because the string "Hotdog Buns" contains a space,
it must be quoted. With MySQL++, you don't have to add
these quote marks manually:</p><pre class="programlisting">
string s = "Hotdog Buns";
Query q = conn.query();
q << "SELECT * FROM stock WHERE item = " << quote_only << s; </pre><p>That code produces the same query string
as in the previous example. We used the MySQL++
<tt>quote_only</tt>
manipulator, which causes single quotes to be
added around the next item inserted into the
stream. This works for various string types, for any
type of data that can be converted to MySQL++'s
<tt><a href="../../refman/html/classmysqlpp_1_1ColData__Tmpl.html">ColData</a></tt>
type, and for <a href="ssqls.html">Specialized SQL Structures</a>. (The next section
introduces the SSQLS feature.)</p><p>Quoting is pretty simple, but SQL syntax also often
requires that certain characters be "escaped". Imagine
if the string in the previous example was "Frank's Brand Hotdog
Buns" instead. The resulting query would be:</p><pre class="programlisting">
SELECT * FROM stock WHERE item = 'Frank's Brand Hotdog Buns' </pre><p>That's not valid SQL syntax. The correct syntax is:</p><pre class="programlisting">
SELECT * FROM stock WHERE item = 'Frank''s Brand Hotdog Buns' </pre><p>As you might expect, MySQL++
provides that feature, too, through its
<tt>escape</tt>
manipulator. But here, we want both quoting and
escaping. That brings us to the most widely useful
manipulator:</p><pre class="programlisting">
string s = "Frank's Brand Hotdog Buns";
Query q = conn.query();
q << "SELECT * FROM stock WHERE item = " << quote << s; </pre><p>The <tt>quote</tt>
manipulator both quotes strings, and escapes any
characters that are special in SQL.</p></div><div class="sect2" lang="en"><div class="titlepage"><div><h3 class="title"><a name="id2806840"></a>3.7. Specialized SQL Structures</h3></div></div><div class="sect3" lang="en"><div class="titlepage"><div><h4 class="title"><a name="id2806847"></a>Retrieving data</h4></div></div><p>The next example introduces one of
the most powerful features of MySQL++: <a href="ssqls.html">Specialized SQL Structures</a> (SSQLS). This is
<tt>examples/custom1.cpp</tt>:</p><pre class="programlisting">
#include "util.h"
#include <mysql++.h>
#include <custom.h>
#include <iostream>
#include <iomanip>
#include <vector>
using namespace std;
using namespace mysqlpp;
// The following is calling a very complex macro which will create
// "struct stock", which has the member variables:
//
// string item
// ...
// Date sdate
//
// plus methods to help populate the class from a MySQL row
// among other things that I'll get to in a later example.
sql_create_5(stock,
1, 5, // explained in the user manual
string, item,
longlong, num,
double, weight,
double, price,
Date, sdate)
int
main(int argc, char *argv[])
{
// Wrap all MySQL++ interactions in one big try block, so any
// errors are handled gracefully.
try {
// Establish the connection to the database server.
Connection con(use_exceptions);
if (!connect_to_db(argc, argv, con)) {
return 1;
}
// Retrieve the entire contents of the stock table, and store
// the data in a vector of 'stock' SSQLS structures.
Query query = con.query();
query << "select * from stock";
vector<stock> res;
query.storein(res);
// Display the result set
print_stock_header(res.size());
vector<stock>::iterator it;
for (it = res.begin(); it != res.end(); ++it) {
print_stock_row(it->item, it->num, it->weight, it->price,
it->sdate);
}
}
catch (const BadQuery& er) {
// Handle any query errors
cerr << "Query error: " << er.what() << endl;
return -1;
}
catch (const BadConversion& er) {
// Handle bad conversions; e.g. type mismatch populating 'stock'
cerr << "Conversion error: " << er.what() << endl <<
"\tretrieved data size: " << er.retrieved <<
", actual size: " << er.actual_size << endl;
return -1;
}
catch (const Exception& er) {
// Catch-all for any other MySQL++ exceptions
cerr << "Error: " << er.what() << endl;
return -1;
}
return 0;
}
</pre><p>As you can see, SSQLS is very powerful.</p></div><div class="sect3" lang="en"><div class="titlepage"><div><h4 class="title"><a name="id2806897"></a>Adding data</h4></div></div><p>SSQLS can also be used to add data to a table. This
is <tt>examples/custom2.cpp</tt>:</p><pre class="programlisting">
#include "util.h"
#include <mysql++.h>
#include <custom.h>
#include <iostream>
#include <string>
#include <vector>
using namespace std;
using namespace mysqlpp;
sql_create_5(stock,
1, 5,
string, item,
longlong, num,
double, weight,
double, price,
Date, sdate)
int
main(int argc, char *argv[])
{
try {
// Establish the connection to the database server.
Connection con(use_exceptions);
if (!connect_to_db(argc, argv, con)) {
return 1;
}
// Create and populate a stock object. We could also have used
// the set() member, which takes the same parameters as this
// constructor.
stock row("Hot Dogs", 100, 1.5, 1.75, "1998-09-25");
// Form the query to insert the row into the stock table.
Query query = con.query();
query.insert(row);
// Show the query about to be executed.
cout << "Query: " << query.preview() << endl;
// Execute the query. We use execute() because INSERT doesn't
// return a result set.
query.execute();
// Print the new table.
Result res;
get_stock_table(query, res);
print_stock_rows(res);
}
catch (const BadQuery& er) {
// Handle any query errors
cerr << "Query error: " << er.what() << endl;
return -1;
}
catch (const BadConversion& er) {
// Handle bad conversions
cerr << "Conversion error: " << er.what() << endl <<
"\tretrieved data size: " << er.retrieved <<
", actual size: " << er.actual_size << endl;
return -1;
}
catch (const Exception& er) {
// Catch-all for any other MySQL++ exceptions
cerr << "Error: " << er.what() << endl;
return -1;
}
return 0;
}
</pre><p>That's all there is to it!</p><p>There is one subtlety: MySQL++ automatically
quotes and escapes the data when building SQL
queries using SSQLS structures. It's efficient,
too: MySQL++ is smart enough to apply quoting
and escaping only for those data types that
actually require it.</p><p>Because this example modifies the sample
database, you may want to run resetdb after
running this program.</p></div><div class="sect3" lang="en"><div class="titlepage"><div><h4 class="title"><a name="id2806952"></a>Modifying data</h4></div></div><p>It almost as easy to modify data with
SSQLS. This is
<tt>examples/custom3.cpp</tt>:</p><pre class="programlisting">
#include "util.h"
#include <mysql++.h>
#include <custom.h>
#include <iostream>
#include <string>
#include <vector>
using namespace std;
using namespace mysqlpp;
sql_create_5(stock,
1, 5,
string, item,
longlong, num,
double, weight,
double, price,
Date, sdate)
int
main(int argc, char *argv[])
{
try {
// Establish the connection to the database server.
Connection con(use_exceptions);
if (!connect_to_db(argc, argv, con)) {
return 1;
}
// Build a query to retrieve the stock item that has Unicode
// characters encoded in UTF-8 form.
Query query = con.query();
query << "select * from stock where item = \"Nürnberger Brats\"";
// Retrieve the row, throwing an exception if it fails.
Result res = query.store();
if (res.empty()) {
throw BadQuery("UTF-8 bratwurst item not found in "
"table, run resetdb");
}
// Because there should only be one row in the result set,
// there's no point in storing the result in an STL container.
// We can store the first row directly into a stock structure
// because one of an SSQLS's constructors takes a Row object.
stock row = res.at(0);
// Create a copy so that the replace query knows what the
// original values are.
stock orig_row = row;
// Change the stock object's item to use only 7-bit ASCII, and
// to deliberately be wider than normal column widths printed
// by print_stock_table().
row.item = "Nuerenberger Bratwurst";
// Form the query to replace the row in the stock table.
query.update(orig_row, row);
// Show the query about to be executed.
cout << "Query: " << query.preview() << endl;
// Run the query with execute(), since UPDATE doesn't return a
// result set.
query.execute();
// Print the new table contents.
get_stock_table(query, res);
print_stock_rows(res);
}
catch (const BadQuery& er) {
// Handle any query errors
cerr << "Query error: " << er.what() << endl;
return -1;
}
catch (const BadConversion& er) {
// Handle bad conversions
cerr << "Conversion error: " << er.what() << endl <<
"\tretrieved data size: " << er.retrieved <<
", actual size: " << er.actual_size << endl;
return -1;
}
catch (const Exception& er) {
// Catch-all for any other MySQL++ exceptions
cerr << "Error: " << er.what() << endl;
return -1;
}
return 0;
}
</pre><p>When you run the example you will notice
that in the WHERE clause only the 'item' field
is checked for. This is because SSQLS also also
less-than-comparable.</p><p>Don't forget to run resetdb after running
the example.</p></div><div class="sect3" lang="en"><div class="titlepage"><div><h4 class="title"><a name="id2807000"></a>Less-than-comparable</h4></div></div><p>SSQLS structures can be sorted and
stored in STL associative containers as
demonstrated in the next example. This is
<tt>examples/custom4.cpp</tt>:</p><pre class="programlisting">
#include "util.h"
#include <mysql++.h>
#include <custom.h>
#include <iostream>
#include <iomanip>
#include <string>
#include <vector>
using namespace std;
using namespace mysqlpp;
sql_create_5(stock,
1, // This number is used to make a SSQLS less-than-comparable.
// When comparing two SSQLS structures, the first N elements are
// compared. In this instance, we are saying that we only want
// the first element ('item') to be used when comparing two
// stock structures.
5, // Each SSQLS structure includes a number of constructors. Some
// of these are fixed in nature, but one of these will have this
// number of arguments, one for each of the first N elements in
// the structure; it is an initialization ctor. Since N is the
// same as the number of structure elements in this instance,
// that ctor will be able to fully initialize the structure. This
// behavior is not always wanted, however, so the macro allows
// you make the constructor take fewer parameters, leaving the
// remaining elements uninitialized. An example of when this is
// necessary is when you have a structure containing only two
// integer elements: one of the other ctors defined for SSQLS
// structures takes two ints, so the compiler barfs if you pass
// 2 for this argument. You would need to pass 0 here to get
// that SSQLS structure to compile.
string, item,
longlong, num,
double, weight,
double, price,
Date, sdate)
int
main(int argc, char *argv[])
{
try {
// Establish the connection to the database server.
Connection con(use_exceptions);
if (!connect_to_db(argc, argv, con)) {
return 1;
}
// Retrieve all rows from the stock table and put them in an
// STL set. Notice that this works just as well as storing them
// in a vector, which we did in custom1.cpp. It works because
// SSQLS objects are less-than comparable.
Query query = con.query();
query << "select * from stock";
set<stock> res;
query.storein(res);
// Display the result set. Since it is an STL set and we set up
// the SSQLS to compare based on the item column, the rows will
// be sorted by item.
print_stock_header(res.size());
set<stock>::iterator it;
cout.precision(3);
for (it = res.begin(); it != res.end(); ++it) {
print_stock_row(it->item.c_str(), it->num, it->weight,
it->price, it->sdate);
}
// Use set's find method to look up a stock item by item name.
// This also uses the SSQLS comparison setup.
it = res.find(stock("Hotdog Buns"));
if (it != res.end()) {
cout << endl << "Currently " << it->num <<
" hotdog buns in stock." << endl;
}
else {
cout << endl << "Sorry, no hotdog buns in stock." << endl;
}
}
catch (const BadQuery& er) {
// Handle any query errors
cerr << "Query error: " << er.what() << endl;
return -1;
}
catch (const BadConversion& er) {
// Handle bad conversions
cerr << "Conversion error: " << er.what() << endl <<
"\tretrieved data size: " << er.retrieved <<
", actual size: " << er.actual_size << endl;
return -1;
}
catch (const Exception& er) {
// Catch-all for any other MySQL++ exceptions
cerr << "Error: " << er.what() << endl;
return -1;
}
return 0;
}
</pre><p>For more details on the SSQLS feature, see the <a href="ssqls.html">Specialized SQL Structures</a> chapter.</p></div></div><div class="sect2" lang="en"><div class="titlepage"><div><h3 class="title"><a name="id2807050"></a>3.8. Handling SQL Nulls</h3></div></div><p>There is no equivalent of SQL's null in the standard
C++ type system.</p><p>The primary distinction is one of type: in
SQL, null is a column attribute, which affects
whether that column can hold a SQL null. Just
like the 'const' keyword in the C++ type system,
this effectively doubles the number of SQL data
types. To emulate this, MySQL++ provides the <tt><a href="../../refman/html/classmysqlpp_1_1null.html">Null</a></tt> template
to allow the creation of distinct "nullable"
versions of existing C++ types. So
for example, if you have a <tt>TINY
INT UNSIGNED</tt> column that can have
nulls, the proper declaration for MySQL++ would
be:</p><pre class="programlisting">
mysqlpp::Null<unsigned char> myfield;</pre><p>Template instantiations are first-class types
in the C++ language, on par with any other type. You
can use <tt>Null</tt>
template instantiations anywhere you'd use the
plain version of that type. (You can see a complete
list of <tt>Null</tt>
template instantiations for all column
types that MySQL understands at the top of
<tt>lib/type_info.cpp</tt>.)</p><p>There's a secondary distinction between SQL null
and anything available in the standard C++ type system:
SQL null is a distinct value, equal to nothing else. We
can't use C++'s <tt>NULL</tt>
for this because it is ambiguous, being equal to
0 in integer context. MySQL++ provides the global
<tt>null</tt> object, which you
can assign to a <tt>Null</tt>
template instance to make it equal to SQL null:</p><pre class="programlisting">
myfield = mysqlpp::null;</pre><p>The final aspect of MySQL++'s null handling
is that, by default, it will enforce the uniqueness
of the SQL null value. If you try to convert a SQL
null to a plain C++ data type, MySQL++ will throw
a <tt><a href="../../refman/html/classmysqlpp_1_1BadNullConversion.html">BadNullConversion</a></tt>
exception. If you insert a SQL null into a C++
stream, you get "(NULL)". If you don't like
this behavior, you can change it, by passing
a different value for the second parameter to
template <tt>Null</tt>. By
default, this parameter is <tt><a href="../../refman/html/structmysqlpp_1_1NullisNull.html">NullisNull</a></tt>, meaning that we should
enforce the uniqueness of the null type. To
relax this distinction, you can instantiate the
<tt>Null</tt> template with
a different behavior type: <tt><a href="../../refman/html/structmysqlpp_1_1NullisZero.html">NullisZero</a></tt> or <tt><a href="../../refman/html/structmysqlpp_1_1NullisBlank.html">NullisBlank</a></tt>. Consider this code:</p><pre class="programlisting">
mysqlpp::Null<unsigned char, mysqlpp::NullisZero> myfield;
myfield = mysqlpp::null;
cout << myfield << endl;
int x = myfield;
cout << x << endl;</pre><p>This will print "0" twice. If
you had used the default for the second
<tt>Null</tt> template
parameter, the first output statement would have
printed "(NULL)", and the second would have thrown
a <tt>BadNullConversion</tt>
exception.</p></div><div class="sect2" lang="en"><div class="titlepage"><div><h3 class="title"><a name="id2807266"></a>3.9. Which Query Type to Use?</h3></div></div><p>There are three major ways to execute a query in MySQL++:
<tt>Query::execute()</tt>,
<tt>Query::store()</tt>, and
<tt>Query::use()</tt>. Which should you
use, and why?</p><p><tt>execute()</tt>
is for queries that do not return data <span class="emphasis"><em>per
se</em></span>. For instance, <tt>CREATE
INDEX</tt>. You do get back some
information from the MySQL server, which
<tt>execute()</tt>
returns to its caller in a
<tt><a href="../../refman/html/classmysqlpp_1_1ResNSel.html">ResNSel</a></tt>
object. In addition to the obvious — a flag stating
whether the query succeeded or not — this object also
contains things like the number of rows that the query
affected. If you only need the success status, there's
<tt>Query::exec()</tt>, which
just returns bool.</p><p>If your query does pull data from
the database, the simplest option is
<tt>store()</tt>.
This returns a <tt><a href="../../refman/html/classmysqlpp_1_1Result.html">Result</a></tt>
object, which contains an in-memory copy of
the result set. The nice thing about this is
that <tt>Result</tt>
is a sequential container, like
<tt>std::vector</tt>, so you
can iterate through it forwards and backwards, access
elements with subscript notation, etc. There are also
the <tt>storein()</tt> methods,
which actually put the result set into an STL container
of your choice. The downside of these methods is that
a sufficiently large result set will give your program
memory problems.</p><p>For these large result sets, the superior
option is a <tt>use()</tt>
query. This returns a <tt><a href="../../refman/html/classmysqlpp_1_1ResUse.html">ResUse</a></tt> object, which is similar to Result,
but without all of the random-access features. This
is because a "use" query tells the database server
to send the results back one row at a time, to be
processed linearly. It's analogous to a C++ stream's
input iterator, as opposed to a random-access
iterator that a container like vector offers. By
accepting this limitation, you can process arbitrarily
large result sets. This technique is demonstrated
in <tt>examples/simple3.cpp</tt>:</p><pre class="programlisting">
#include "util.h"
#include <mysql++.h>
#include <iostream>
#include <iomanip>