/usr/share/qt3/doc/html/sql.html is in qt3-doc 3:3.3.8-b-8ubuntu3.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
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 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 1541 1542 1543 1544 1545 1546 1547 1548 1549 1550 1551 1552 1553 1554 1555 1556 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 1603 1604 1605 1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 | <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<!-- /home/espenr/tmp/qt-3.3.8-espenr-2499/qt-x11-free-3.3.8/doc/sql.doc:69 -->
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>SQL Module</title>
<style type="text/css"><!--
fn { margin-left: 1cm; text-indent: -1cm; }
a:link { color: #004faf; text-decoration: none }
a:visited { color: #672967; text-decoration: none }
body { background: #ffffff; color: black; }
--></style>
</head>
<body>
<table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr bgcolor="#E5E5E5">
<td valign=center>
<a href="index.html">
<font color="#004faf">Home</font></a>
| <a href="classes.html">
<font color="#004faf">All Classes</font></a>
| <a href="mainclasses.html">
<font color="#004faf">Main Classes</font></a>
| <a href="annotated.html">
<font color="#004faf">Annotated</font></a>
| <a href="groups.html">
<font color="#004faf">Grouped Classes</font></a>
| <a href="functions.html">
<font color="#004faf">Functions</font></a>
</td>
<td align="right" valign="center"><img src="logo32.png" align="right" width="64" height="32" border="0"></td></tr></table><h1 align=center>SQL Module</h1>
<p>
<p> <center><table cellpadding="4" cellspacing="2" border="0">
<tr bgcolor="#f0f0f0">
<td valign="top"><a href="qsql.html">QSql</a>
<td valign="top"><a href="qsqlcursor.html">QSqlCursor</a>
<td valign="top"><a href="qsqldatabase.html">QSqlDatabase</a>
<td valign="top"><a href="qsqldriver.html">QSqlDriver</a>
<td valign="top"><a href="qsqldriverplugin.html">QSqlDriverPlugin</a>
<tr bgcolor="#d0d0d0">
<td valign="top"><a href="qsqleditorfactory.html">QSqlEditorFactory</a>
<td valign="top"><a href="qsqlerror.html">QSqlError</a>
<td valign="top"><a href="qsqlfield.html">QSqlField</a>
<td valign="top"><a href="qsqlfieldinfo.html">QSqlFieldInfo</a>
<td valign="top"><a href="qsqlform.html">QSqlForm</a>
<tr bgcolor="#f0f0f0">
<td valign="top"><a href="qsqlindex.html">QSqlIndex</a>
<td valign="top"><a href="qsqlpropertymap.html">QSqlPropertyMap</a>
<td valign="top"><a href="qsqlquery.html">QSqlQuery</a>
<td valign="top"><a href="qsqlrecord.html">QSqlRecord</a>
<td valign="top"><a href="qsqlrecordinfo.html">QSqlRecordInfo</a>
<tr bgcolor="#d0d0d0">
<td valign="top"><a href="qsqlresult.html">QSqlResult</a>
<td valign="top"><a href="qsqlselectcursor.html">QSqlSelectCursor</a>
<td valign="top" colspan="3" rowspan="1"> See also: <a href="sql-driver.html">Supported Drivers</a>
</table></center>
<p> <!-- toc -->
<ul>
<li><a href="#1"> Introduction
</a>
<li><a href="#2"> SQL Module Architecture
</a>
<li><a href="#3"> SQL Driver Plugins
</a>
<li><a href="#4"> Connecting to Databases
</a>
<li><a href="#5"> Connecting to a Single Database
</a>
<ul>
<li><a href="#5-1"> Connecting to Multiple Databases
</a>
</ul>
<li><a href="#6"> Executing SQL Commands Using QSqlQuery
</a>
<ul>
<li><a href="#6-1"> Transactions
</a>
<li><a href="#6-2"> Basic Browsing
</a>
<li><a href="#6-3"> Basic Data Manipulation
</a>
<li><a href="#6-4"> Navigating Result Sets
</a>
</ul>
<li><a href="#7"> Using QSqlCursor
</a>
<ul>
<li><a href="#7-1"> Retrieving Records
</a>
<ul>
<li><a href="#7-1-1"> Sorting and Filtering Records
</a>
<li><a href="#7-1-2"> Extracting Data
</a>
</ul>
<li><a href="#7-2"> Manipulating Records
</a>
<ul>
<li><a href="#7-2-1"> Inserting Records
</a>
<li><a href="#7-2-2"> Updating Records
</a>
<li><a href="#7-2-3"> Deleting Records
</a>
</ul>
</ul>
<li><a href="#8"> Data-Aware Widgets
</a>
<ul>
<li><a href="#8-1"> Data-Aware Tables
</a>
<li><a href="#8-2"> Creating Data-Aware Forms
</a>
<ul>
<li><a href="#8-2-1"> Displaying a Record
</a>
<li><a href="#8-2-2"> Displaying a Record in a Data Form
</a>
<li><a href="#8-2-3"> Editing a Record
</a>
</ul>
<li><a href="#8-3"> Custom Editor Widgets
</a>
<ul>
<li><a href="#8-3-1"> Custom Editor Widgets for Tables
</a>
</ul>
</ul>
<li><a href="#9"> Subclassing QSqlCursor
</a>
<li><a href="#10"> The Example Tables
</a>
</ul>
<!-- endtoc -->
<p> <a name="Introduction"></a>
<h2> Introduction
</h2>
<a name="1"></a><p> Qt's SQL classes help you provide seamless database integration to
your Qt applications.
<p> <blockquote>
This overview assumes that you have at least a basic knowledge of SQL.
You should be able to understand simple <tt>SELECT</tt>, <tt>INSERT</tt>, <tt>UPDATE</tt>
and <tt>DELETE</tt> commands. Although the <a href="qsqlcursor.html">QSqlCursor</a> class provides an
interface to database browsing and editing that does not <em>require</em> a
knowledge of SQL, a basic understanding of SQL is highly recommended. A
standard text covering SQL databases is <em>An Introduction to Database Systems (7th ed.)</em> by C. J. Date, ISBN 0201385902.
</blockquote>
<p> Whilst this module overview presents the classes from a purely
programmatic point of view the <a href="designer-manual.html">Qt
Designer</a> manual's "Creating Database Applications" chapter
takes a higher-level approach demonstrating how to set up
master-detail relationships between widgets, perform drilldown and
handle foreign key lookups.
<p> This document is divided into six sections:
<p> <a href="#Architecture">SQL Module Architecture</a>. This describes
how the classes fit together.
<p> <a href="#Connecting_to_Databases">Connecting to Databases</a>.
This section explains how to set up database connections using the <a href="qsqldatabase.html">QSqlDatabase</a> class.
<p> <a href="#Executing_SQL_commands">Executing SQL Commands</a>. This
section demonstrates how to issue the standard data manipulation
commands, <tt>SELECT</tt>, <tt>INSERT</tt>, <tt>UPDATE</tt> and <tt>DELETE</tt> on tables in
the database (although any valid SQL statement can be sent to the
database). The focus is purely on database interaction using <a href="qsqlquery.html">QSqlQuery</a>.
<p> <a href="#Using_QSqlCursor">Using Cursors</a>. This section explains
how to use the <a href="qsqlcursor.html">QSqlCursor</a> class which provides a simpler API than the
raw SQL used with <a href="qsqlquery.html">QSqlQuery</a>.
<p> <a href="#Data-Aware_Widgets">Data-Aware Widgets</a>. This section shows
how to programmatically link your database to the user interface. In
this section we introduce the <a href="qdatatable.html">QDataTable</a>, <a href="qsqlform.html">QSqlForm</a>, <a href="qsqlpropertymap.html">QSqlPropertyMap</a> and <a href="qsqleditorfactory.html">QSqlEditorFactory</a> classes and demonstrate how to
use custom data-aware widgets. <a href="designer-manual.html">Qt
Designer</a> provides an easy visual way of achieving the same
thing. See the <a href="designer-manual.html">Qt Designer</a> manual,
<a href="qdatabrowser.html">QDataBrowser</a> and <a href="qdataview.html">QDataView</a> for more information.
<p> <a href="#Subclassing_QSqlCursor">Subclassing QSqlCursor</a>. This
section gives examples of subclassing QSqlCursor. Subclassing can be
used to provide default and calculated values for fields (such as
auto-numbered primary index fields), and to display calculated data,
e.g. showing names rather than ids of foreign keys.
<p> All the examples in this document use the tables defined in the
<a href="#Example_Tables">Example Tables</a> section.
<p> <a name="Architecture"></a>
<h2> SQL Module Architecture
</h2>
<a name="2"></a><p> The SQL classes are divided into three layers:
<p> <em>User Interface Layer.</em> These classes provide data-aware widgets
that can be connected to tables or views in the database (by using a
<a href="qsqlcursor.html">QSqlCursor</a> as a data source). End users can interact directly with
these widgets to browse or edit data. <a href="designer-manual.html">Qt
Designer</a> is fully integrated with the SQL classes and can be
used to create data-aware forms. The data-aware widgets can also be
programmed directly with your own C++ code. The classes that support
this layer include <a href="qsqleditorfactory.html">QSqlEditorFactory</a>, <a href="qsqlform.html">QSqlForm</a>, <a href="qsqlpropertymap.html">QSqlPropertyMap</a>, <a href="qdatatable.html">QDataTable</a>, <a href="qdatabrowser.html">QDataBrowser</a> and <a href="qdataview.html">QDataView</a>.
<p> <em>SQL API Layer.</em> These classes provide access to databases.
Connections are made using the <a href="qsqldatabase.html">QSqlDatabase</a> class. Database
interaction is achieved either by using the <a href="qsqlquery.html">QSqlQuery</a> class and
executing SQL commands directly or by using the higher level <a href="qsqlcursor.html">QSqlCursor</a> class which composes SQL commands automatically. In
addition to <a href="qsqldatabase.html">QSqlDatabase</a>, <a href="qsqlcursor.html">QSqlCursor</a> and <a href="qsqlquery.html">QSqlQuery</a>, the SQL
API layer is supported by <a href="qsqlerror.html">QSqlError</a>, <a href="qsqlfield.html">QSqlField</a>, <a href="qsqlfieldinfo.html">QSqlFieldInfo</a>,
<a href="qsqlindex.html">QSqlIndex</a>, <a href="qsqlrecord.html">QSqlRecord</a> and <a href="qsqlrecordinfo.html">QSqlRecordInfo</a>.
<p> <em>Driver Layer.</em> This comprises three classes, <a href="qsqlresult.html">QSqlResult</a>, <a href="qsqldriver.html">QSqlDriver</a> and QSqlDriverFactoryInterface. This layer provides the
low level bridge between the database and the SQL classes. This layer
is <a href="sql-driver.html">documented separately</a> since it is
only relevant to driver writers, and is rarely used in standard
database application programming. See <a href="sql-driver.html">here</a> for more information on implementing a Qt SQL driver plugin.
<p> <a name="Plugins"></a>
<h2> SQL Driver Plugins
</h2>
<a name="3"></a><p> The Qt SQL module can dynamically load new drivers at runtime using
the <a href="plugins-howto.html">Plugins</a>.
<p> The <a href="sql-driver.html">SQL driver documentation</a> describes
how to build plugins for specific database management systems.
<p> Once a plugin is built, Qt will automatically load it, and the driver
will be available for use by <a href="qsqldatabase.html">QSqlDatabase</a> (see <a href="qsqldatabase.html#drivers">QSqlDatabase::drivers</a>()
for more information).
<p> <a name="Connecting_to_Databases"></a>
<h2> Connecting to Databases
</h2>
<a name="4"></a><p> At least one database connection must be created and opened before the
<a href="qsqlquery.html">QSqlQuery</a> or <a href="qsqlcursor.html">QSqlCursor</a> classes can be used.
<p> If the application only needs a single database connection, the <a href="qsqldatabase.html">QSqlDatabase</a> class can create a connection which is used by default
for all SQL operations. If multiple database connections are required
these can easily be set up.
<p> <a href="qsqldatabase.html">QSqlDatabase</a> requires the <a href="qsqldatabase-h.html">qsqldatabase.h</a> header file.
<p> <a name="Connecting_to_a_Single_Database"></a>
<h2> Connecting to a Single Database
</h2>
<a name="5"></a><p> Making a database connection is a simple three step process: activate
the driver, set up the connection information, and open the
connection.
<p>
<pre> #include <<a href="qapplication-h.html">qapplication.h</a>>
#include <<a href="qsqldatabase-h.html">qsqldatabase.h</a>>
#include "../connection.h"
int main( int argc, char *argv[] )
{
<a href="qapplication.html">QApplication</a> app( argc, argv, FALSE );
<a name="x2141"></a> <a href="qsqldatabase.html">QSqlDatabase</a> *defaultDB = QSqlDatabase::<a href="qsqldatabase.html#addDatabase">addDatabase</a>( DB_SALES_DRIVER );
<a name="x2143"></a> defaultDB-><a href="qsqldatabase.html#setDatabaseName">setDatabaseName</a>( DB_SALES_DBNAME );
<a name="x2146"></a> defaultDB-><a href="qsqldatabase.html#setUserName">setUserName</a>( DB_SALES_USER );
<a name="x2145"></a> defaultDB-><a href="qsqldatabase.html#setPassword">setPassword</a>( DB_SALES_PASSWD );
<a name="x2144"></a> defaultDB-><a href="qsqldatabase.html#setHostName">setHostName</a>( DB_SALES_HOST );
<a name="x2142"></a> if ( defaultDB-><a href="qsqldatabase.html#open">open</a>() ) {
// Database successfully opened; we can now issue SQL commands.
}
return 0;
}
</pre><blockquote><p align="center"><em> From <a href="sql-overview-connect1-main-cpp.html">sql/overview/connect1/main.cpp</a>
</em></p>
</blockquote><p> First we activate the driver by calling <a href="qsqldatabase.html#addDatabase">QSqlDatabase::addDatabase</a>(),
passing the name of the driver we wish to use for this connection. At
the time of writing the available drivers are: <a href="sql-driver.html#QODBC3">QODBC3</a> (Open Database
Connectivity, includes Microsoft SQL Server support), <a href="sql-driver.html#QOCI8">QOCI8</a> (Oracle 8 and 9),
<a href="sql-driver.html#QTDS7">QTDS7</a> (Sybase Adaptive Server), <a href="sql-driver.html#QPSQL7">QPSQL7</a> (PostgreSQL 6 and 7),
<a href="sql-driver.html#QMYSQL3">QMYSQL3</a> (MySQL), <a href="sql-driver.html#QDB2">QDB2</a> (IBM DB2), <a href="sql-driver.html#QSQLITE">QSQLITE</a> (SQLite) and <a href="sql-driver.html#QIBASE">QIBASE</a> (Interbase).
Note that some of these drivers aren't included in the Qt Open Source Edition; see
the <tt>README</tt> files for details.
<p> The connection which is created becomes the application's default
database connection and will be used by the Qt SQL classes if no
other database is specified.
<p> Second we call setDatabaseName(), setUserName(), setPassword() and
setHostName() to initialize the connection information. Note that for
the QOCI8 (Oracle 8 and 9) driver the TNS Service Name must be passed
to setDatbaseName(). When connecting to ODBC data sources the Data
Source Name (DSN) should be used in the setDatabaseName() call.
<p> Third we call open() to open the database and give us access to the
data. If this call fails it will return FALSE; error information can
be obtained from <a href="qsqldatabase.html#lastError">QSqlDatabase::lastError</a>().
<p> <a name="Connecting_to_Multiple_Databases"></a>
<h3> Connecting to Multiple Databases
</h3>
<a name="5-1"></a><p> Connecting to multiple databases is achieved using the two argument form
of <a href="qsqldatabase.html#addDatabase">QSqlDatabase::addDatabase</a>() where the second argument is a unique
identifier distinguishing the connection.
<p> In the example below we have moved the connections into their own
function, <tt>createConnections()</tt>, and added some basic error handling.
<p> <pre>
#define DB_SALES_DRIVER "QPSQL7"
#define DB_SALES_DBNAME "sales"
#define DB_SALES_USER "salesperson"
#define DB_SALES_PASSWD "salesperson"
#define DB_SALES_HOST "database.domain.no"
#define DB_ORDERS_DRIVER "QOCI8"
#define DB_ORDERS_DBNAME "orders"
#define DB_ORDERS_USER "orderperson"
#define DB_ORDERS_PASSWD "orderperson"
#define DB_ORDERS_HOST "database.domain.no"
bool createConnections();
</pre>
<p> We set up some constants and also declare the <tt>createConnections()</tt>
function in <tt>connection.h</tt>.
<p>
<pre> #include <<a href="qsqldatabase-h.html">qsqldatabase.h</a>>
#include "connection.h"
bool createConnections()
{
<a name="x2147"></a> <a href="qsqldatabase.html">QSqlDatabase</a> *defaultDB = QSqlDatabase::<a href="qsqldatabase.html#addDatabase">addDatabase</a>( DB_SALES_DRIVER );
<a name="x2150"></a> defaultDB-><a href="qsqldatabase.html#setDatabaseName">setDatabaseName</a>( DB_SALES_DBNAME );
<a name="x2153"></a> defaultDB-><a href="qsqldatabase.html#setUserName">setUserName</a>( DB_SALES_USER );
<a name="x2152"></a> defaultDB-><a href="qsqldatabase.html#setPassword">setPassword</a>( DB_SALES_PASSWD );
<a name="x2151"></a> defaultDB-><a href="qsqldatabase.html#setHostName">setHostName</a>( DB_SALES_HOST );
<a name="x2149"></a> if ( ! defaultDB-><a href="qsqldatabase.html#open">open</a>() ) {
<a name="x2148"></a> <a href="qapplication.html#qWarning">qWarning</a>( "Failed to open sales database: " + defaultDB-><a href="qsqldatabase.html#lastError">lastError</a>().text() );
return FALSE;
}
<a href="qsqldatabase.html">QSqlDatabase</a> *oracle = QSqlDatabase::<a href="qsqldatabase.html#addDatabase">addDatabase</a>( DB_ORDERS_DRIVER, "ORACLE" );
oracle-><a href="qsqldatabase.html#setDatabaseName">setDatabaseName</a>( DB_ORDERS_DBNAME );
oracle-><a href="qsqldatabase.html#setUserName">setUserName</a>( DB_ORDERS_USER );
oracle-><a href="qsqldatabase.html#setPassword">setPassword</a>( DB_ORDERS_PASSWD );
oracle-><a href="qsqldatabase.html#setHostName">setHostName</a>( DB_ORDERS_HOST );
if ( ! oracle-><a href="qsqldatabase.html#open">open</a>() ) {
<a href="qapplication.html#qWarning">qWarning</a>( "Failed to open orders database: " + oracle-><a href="qsqldatabase.html#lastError">lastError</a>().text() );
return FALSE;
}
<a href="qsqlquery.html">QSqlQuery</a> q(QString::null, defaultDB);
<a name="x2154"></a> q.<a href="qsqlquery.html#exec">exec</a>("create table people (id integer primary key, name char(40))");
q.<a href="qsqlquery.html#exec">exec</a>("create table staff (id integer primary key, forename char(40), "
"surname char(40), salary float, statusid integer)");
q.<a href="qsqlquery.html#exec">exec</a>("create table status (id integer primary key, name char(30))");
q.<a href="qsqlquery.html#exec">exec</a>("create table creditors (id integer primary key, forename char(40), "
"surname char(40), city char(30))");
q.<a href="qsqlquery.html#exec">exec</a>("create table prices (id integer primary key, name char(40), price float)");
q.<a href="qsqlquery.html#exec">exec</a>("create table invoiceitem (id integer primary key, "
"pricesid integer, quantity integer, paiddate date)");
<a href="qsqlquery.html">QSqlQuery</a> q2(QString::null, oracle);
q2.<a href="qsqlquery.html#exec">exec</a>("create table people (id integer primary key, name char(40))");
return TRUE;
}
</pre><blockquote><p align="center"><em> From <a href="sql.html">sql/overview/connection.cpp</a>
</em></p>
</blockquote><p> We've chosen to isolate database connection in our <tt>createConnections()</tt> function.cpp.
<p> <a name="create_connections"></a>
<pre> #include <<a href="qapplication-h.html">qapplication.h</a>>
#include <<a href="qsqldatabase-h.html">qsqldatabase.h</a>>
#include "../connection.h"
int main( int argc, char *argv[] )
{
<a href="qapplication.html">QApplication</a> app( argc, argv, FALSE );
if ( createConnections() ) {
// Databases successfully opened; get pointers to them:
<a name="x2155"></a> <a href="qsqldatabase.html">QSqlDatabase</a> *oracledb = QSqlDatabase::<a href="qsqldatabase.html#database">database</a>( "ORACLE" );
// Now we can now issue SQL commands to the oracle connection
// or to the default connection
}
return 0;
}
</pre><blockquote><p align="center"><em> From <a href="sql-overview-create_connections-main-cpp.html">sql/overview/create_connections/main.cpp</a>
</em></p>
</blockquote><p> The static function <a href="qsqldatabase.html#database">QSqlDatabase::database</a>() can be called from
anywhere to provide a pointer to a database connection. If we call it
without a parameter it will return the default connection. If called
with the identifier we've used for a connection, e.g. "ORACLE", in the
above example, it will return a pointer to the specified connection.
<p> If you create a <tt>main.cpp</tt> using <a href="designer-manual.html">Qt
Designer</a>, it will <em>not</em> include our example
createConnections() function. This means that applications that
preview correctly in <a href="designer-manual.html">Qt Designer</a>
will not run unless you implement your own database connections
function.
<p> Note that in the code above the ODBC connection was not named and is
therefore used as the default connection. <a href="qsqldatabase.html">QSqlDatabase</a> maintains
ownership of the pointers returned by the addDatabase() static
function. To remove a database from the list of maintained
connections, first close the database with <a href="qsqldatabase.html#close">QSqlDatabase::close</a>(), and
then remove it using the static function
<a href="qsqldatabase.html#removeDatabase">QSqlDatabase::removeDatabase</a>().
<p> <a name="Executing_SQL_commands"></a>
<h2> Executing SQL Commands Using <a href="qsqlquery.html">QSqlQuery</a>
</h2>
<a name="6"></a><p> The <a href="qsqlquery.html">QSqlQuery</a> class provides an interface for executing SQL commands.
It also has functions for navigating through the result sets of <tt>SELECT</tt>
queries and for retrieving individual records and field values.
<p> The <a href="qsqlcursor.html">QSqlCursor</a> class described in the next section inherits from <a href="qsqlquery.html">QSqlQuery</a> and provides a higher level interface that composes SQL
commands for us. <a href="qsqlcursor.html">QSqlCursor</a> is particularly easy to integrate with
on-screen widgets. Programmers unfamiliar with SQL can safely skip this
section and use the <a href="qsqlcursor.html">QSqlCursor</a> class covered in
<a href="#Using_QSqlCursor">"Using QSqlCursor"</a>.
<p> <a name="Transactions"></a>
<h3> Transactions
</h3>
<a name="6-1"></a><p> If the underlying database engine supports transactions
<a href="qsqldriver.html#hasFeature">QSqlDriver::hasFeature</a>( QSqlDriver::Transactions ) will return TRUE.
You can use <a href="qsqldatabase.html#transaction">QSqlDatabase::transaction</a>() to initiate a transaction,
followed by the SQL commands you want to execute within the context of
the transaction, and then either <a href="qsqldatabase.html#commit">QSqlDatabase::commit</a>() or
<a href="qsqldatabase.html#rollback">QSqlDatabase::rollback</a>().
<p> <a name="Basic_Browsing"></a>
<h3> Basic Browsing
</h3>
<a name="6-2"></a><p>
<pre> #include <<a href="qapplication-h.html">qapplication.h</a>>
#include <<a href="qsqldatabase-h.html">qsqldatabase.h</a>>
#include <<a href="qsqlquery-h.html">qsqlquery.h</a>>
#include "../connection.h"
int main( int argc, char *argv[] )
{
<a href="qapplication.html">QApplication</a> app( argc, argv, FALSE );
if ( createConnections() ) {
<a name="x2156"></a> <a href="qsqldatabase.html">QSqlDatabase</a> *oracledb = QSqlDatabase::<a href="qsqldatabase.html#database">database</a>( "ORACLE" );
// Copy data from the oracle database to the ODBC (default)
// database
<a href="qsqlquery.html">QSqlQuery</a> target;
<a href="qsqlquery.html">QSqlQuery</a> query( "SELECT id, name FROM people", oracledb );
<a name="x2158"></a> if ( query.<a href="qsqlquery.html#isActive">isActive</a>() ) {
<a name="x2159"></a> while ( query.<a href="qsqlquery.html#next">next</a>() ) {
<a name="x2157"></a> target.<a href="qsqlquery.html#exec">exec</a>( "INSERT INTO people ( id, name ) VALUES ( " +
<a name="x2160"></a> query.<a href="qsqlquery.html#value">value</a>(0).toString() +
", '" + query.<a href="qsqlquery.html#value">value</a>(1).toString() + "' )" );
}
}
}
return 0;
}
</pre><blockquote><p align="center"><em> From <a href="sql-overview-basicbrowsing-main-cpp.html">sql/overview/basicbrowsing/main.cpp</a>
</em></p>
</blockquote><p> In the example above we've added an additional header file,
<a href="qsqlquery-h.html">qsqlquery.h</a>. The first query we create, <tt>target</tt>, uses the default
database and is initially empty. For the second query, <tt>q</tt>, we specify
the "ORACLE" database that we want to retrieve records from. Both the
database connections were set up in the createConnections() function we
wrote earlier.
<p> After creating the initial <tt>SELECT</tt> statement, isActive() is checked
to see if the query executed successfully. The next() function is
used to iterate through the query results. The value() function
returns the contents of fields as QVariants. The insertions are
achieved by creating and executing queries against the default
database using the <tt>target</tt> <a href="qsqlquery.html">QSqlQuery</a>.
<p> Note that this example and all the other examples in this document use
the tables defined in the <a href="#Example_Tables">Example Tables</a>
section.
<p>
<pre> int count = 0;
<a name="x2162"></a> if ( query.<a href="qsqlquery.html#isActive">isActive</a>() ) {
<a name="x2163"></a> while ( query.<a href="qsqlquery.html#next">next</a>() ) {
<a name="x2161"></a> target.<a href="qsqlquery.html#exec">exec</a>( "INSERT INTO people ( id, name ) VALUES ( " +
<a name="x2165"></a> query.<a href="qsqlquery.html#value">value</a>(0).toString() +
", '" + query.<a href="qsqlquery.html#value">value</a>(1).toString() + "' )" );
if ( target.<a href="qsqlquery.html#isActive">isActive</a>() )
<a name="x2164"></a> count += target.<a href="qsqlquery.html#numRowsAffected">numRowsAffected</a>();
}
}
</pre><blockquote><p align="center"><em> From <a href="sql-overview-basicbrowsing2-main-cpp.html">sql/overview/basicbrowsing2/main.cpp</a>
</em></p>
</blockquote><p> The above code introduces a count of how many records are successfully
inserted. Note that isActive() returns FALSE if the query, e.g. the
insertion, fails. numRowsAffected() returns -1 if the number of rows
cannot be determined, e.g. if the query fails.
<p> <a name="Basic_Data_Manipulation"></a>
<h3> Basic Data Manipulation
</h3>
<a name="6-3"></a><p>
<pre> ** $Id: qt/main.cpp 3.3.8 edited Jan 11 14:37 $
**
** Copyright (C) 1992-2007 Trolltech ASA. All rights reserved.
**
** This file is part of an example program for Qt. This example
** program may be used, distributed and modified without limitation.
**
*****************************************************************************/
#include <<a href="qapplication-h.html">qapplication.h</a>>
#include <<a href="qsqldatabase-h.html">qsqldatabase.h</a>>
#include <<a href="qsqlquery-h.html">qsqlquery.h</a>>
#include "../connection.h"
bool createConnections();
int main( int argc, char *argv[] )
{
<a href="qapplication.html">QApplication</a> app( argc, argv, FALSE );
int rows = 0;
if ( createConnections() ) {
<a href="qsqlquery.html">QSqlQuery</a> query( "INSERT INTO staff ( id, forename, surname, salary ) "
"VALUES ( 1155, 'Ginger', 'Davis', 50000 )" );
<a name="x2168"></a><a name="x2167"></a> if ( query.<a href="qsqlquery.html#isActive">isActive</a>() ) rows += query.<a href="qsqlquery.html#numRowsAffected">numRowsAffected</a>() ;
<a name="x2166"></a> query.<a href="qsqlquery.html#exec">exec</a>( "UPDATE staff SET salary=60000 WHERE id=1155" );
if ( query.<a href="qsqlquery.html#isActive">isActive</a>() ) rows += query.<a href="qsqlquery.html#numRowsAffected">numRowsAffected</a>() ;
query.<a href="qsqlquery.html#exec">exec</a>( "DELETE FROM staff WHERE id=1155" );
if ( query.<a href="qsqlquery.html#isActive">isActive</a>() ) rows += query.<a href="qsqlquery.html#numRowsAffected">numRowsAffected</a>() ;
}
return ( rows == 3 ) ? 0 : 1;
}
</pre><blockquote><p align="center"><em> From <a href="sql-overview-basicdatamanip-main-cpp.html">sql/overview/basicdatamanip/main.cpp</a>
</em></p>
</blockquote><p> This example demonstrates straightforward SQL DML (data manipulation
language) commands. Since we did not specify a database in the <a href="qsqlquery.html">QSqlQuery</a> constructor the default database is used. <a href="qsqlquery.html">QSqlQuery</a> objects
can also be used to execute SQL DDL (data definition language) commands
such as <tt>CREATE TABLE</tt> and <tt>CREATE INDEX</tt>.
<p> <a name="Navigating_Result_Sets"></a>
<h3> Navigating Result Sets
</h3>
<a name="6-4"></a><p> Once a <tt>SELECT</tt> query has been executed successfully we have access
to the result set of records that matched the query criteria. We have
already used one of the navigation functions, next(), which can be
used alone to step sequentially through the records. <a href="qsqlquery.html">QSqlQuery</a> also
provides first(), last() and prev(). After any of these commands we
can check that we are on a valid record by calling isValid().
<p> We can also navigate to any arbitrary record using seek(). The
first record in the dataset is zero. The number of the last record is
size() - 1. Note that not all databases provide the size of a
<tt>SELECT</tt> query and in such cases size() returns -1.
<p>
<pre> if ( createConnections() ) {
<a href="qsqlquery.html">QSqlQuery</a> query( "SELECT id, name FROM people ORDER BY name" );
<a name="x2171"></a> if ( ! query.<a href="qsqlquery.html#isActive">isActive</a>() ) return 1; // Query failed
int i;
<a name="x2174"></a> i = query.<a href="qsqlquery.html#size">size</a>(); // In this example we have 9 records; i == 9.
<a name="x2170"></a> query.<a href="qsqlquery.html#first">first</a>(); // Moves to the first record.
<a name="x2169"></a> i = query.<a href="qsqlquery.html#at">at</a>(); // i == 0
<a name="x2172"></a> query.<a href="qsqlquery.html#last">last</a>(); // Moves to the last record.
i = query.<a href="qsqlquery.html#at">at</a>(); // i == 8
<a name="x2173"></a> query.<a href="qsqlquery.html#seek">seek</a>( query.<a href="qsqlquery.html#size">size</a>() / 2 ); // Moves to the middle record.
i = query.<a href="qsqlquery.html#at">at</a>(); // i == 4
}
</pre><blockquote><p align="center"><em> From <a href="sql-overview-navigating-main-cpp.html">sql/overview/navigating/main.cpp</a>
</em></p>
</blockquote><p> The example above shows some of the navigation functions in use.
<p> Not all drivers support size(), but we can interrogate the driver to
find out:
<p> <pre>
<a href="qsqldatabase.html">QSqlDatabase</a>* defaultDB = QSqlDatabase::<a href="qsqldatabase.html#database">database</a>();
if ( defaultDB-><a href="qsqldatabase.html#driver">driver</a>()->hasFeature( QSqlDriver::QuerySize ) ) {
// QSqlQuery::size() supported
}
else {
// QSqlQuery::size() cannot be relied upon
}
</pre>
<p> Once we have located the record we are interested in we may wish to
retrieve data from it.
<p>
<pre> if ( createConnections() ) {
<a href="qsqlquery.html">QSqlQuery</a> query( "SELECT id, surname FROM staff" );
<a name="x2175"></a> if ( query.<a href="qsqlquery.html#isActive">isActive</a>() ) {
<a name="x2176"></a> while ( query.<a href="qsqlquery.html#next">next</a>() ) {
<a href="qapplication.html#qDebug">qDebug</a>( query.<a href="qsqlquery.html#value">value</a>(0).toString() + ": " +
<a name="x2177"></a> query.<a href="qsqlquery.html#value">value</a>(1).toString() );
}
}
}
</pre><blockquote><p align="center"><em> From <a href="sql-overview-retrieve1-main-cpp.html">sql/overview/retrieve1/main.cpp</a>
</em></p>
</blockquote><p> Note that if you wish to iterate through the record set in order the
only navigation function you need is next().
<p> Tip: The lastQuery() function returns the text of the last query
executed. This can be useful to check that the query you think is being
executed is the one actually being executed.
<p> <a name="Using_QSqlCursor"></a>
<h2> Using <a href="qsqlcursor.html">QSqlCursor</a>
</h2>
<a name="7"></a><p> The <a href="qsqlcursor.html">QSqlCursor</a> class provides a high level interface to browsing and
editing records in SQL database tables or views without the need to
write your own SQL.
<p> QSqlCursor can do almost everything that <a href="qsqlquery.html">QSqlQuery</a> can, with two
exceptions. Since cursors represent tables or views within the
database, by default, <a href="qsqlcursor.html">QSqlCursor</a> objects retrieve all the fields of
each record in the table or view whenever navigating to a new
record. If only some fields are relevant simply confine your
processing to those and ignore the others. Or, manually disable the
generation of certain fields using <a href="qsqlrecord.html#setGenerated">QSqlRecord::setGenerated</a>(). Another
approach is to create a <tt>VIEW</tt> which only presents the fields you're
interested in; but note that some databases do not support editable
views. So if you really don't want to retrieve all the fields in the
cursor, then you should use a <a href="qsqlquery.html">QSqlQuery</a> instead, and customize the
query to suit your needs. You can edit records using a <a href="qsqlcursor.html">QSqlCursor</a>
providing that the table or view has a primary index that uniquely
distinguishes each record. If this condition is not met then you'll
need to use a <a href="qsqlquery.html">QSqlQuery</a> for edits.
<p> <a href="qsqlcursor.html">QSqlCursor</a> operates on a single record at a time. Whenever performing
an insert, update or delete using QSqlCursor, only a single record in
the database is affected. When navigating through records in the
cursor, only one record at a time is available in application code.
In addition, QSqlCursor maintains a separate 'edit buffer' which is
used to make changes to a single record in the database. The edit
buffer is maintained in a separate memory area, and is unnaffected by
the 'navigation buffer' which changes as the cursor moves from record
to record.
<p> Before we can use <a href="qsqlcursor.html">QSqlCursor</a> objects we must first create and open
a database connection. Connecting is described in the <a href="#Connecting_to_Databases">Connecting to Databases</a> section
above. For the examples that follow we will assume that the
connections have been created using the createConnections() function
defined in the <a href="#create_connections">QSqlDatabase example</a>
presented earlier.
<p> In the <a href="#Data-Aware_Widgets">data-aware widgets</a> section that
follows this one we show how to link widgets to database cursors. Once
we have a knowledge of both cursors and data-aware widgets we can
discuss <a href="#Subclassing_QSqlCursor">subclassing QSqlCursor</a>.
<p> The <a href="qsqlcursor.html">QSqlCursor</a> class requires the <a href="qsqlcursor-h.html">qsqlcursor.h</a> header file.
<p> <a name="Retrieving_Records"></a>
<h3> Retrieving Records
</h3>
<a name="7-1"></a><p>
<pre> #include <<a href="qapplication-h.html">qapplication.h</a>>
#include <<a href="qsqldatabase-h.html">qsqldatabase.h</a>>
#include <<a href="qsqlcursor-h.html">qsqlcursor.h</a>>
#include "../connection.h"
int main( int argc, char *argv[] )
{
<a href="qapplication.html">QApplication</a> app( argc, argv );
if ( createConnections() ) {
<a href="qsqlcursor.html">QSqlCursor</a> cur( "staff" ); // Specify the table/view name
<a name="x2178"></a> cur.<a href="qsqlcursor.html#select">select</a>(); // We'll retrieve every record
while ( cur.<a href="qsqlquery.html#next">next</a>() ) {
<a name="x2179"></a> <a href="qapplication.html#qDebug">qDebug</a>( cur.<a href="qsqlquery.html#value">value</a>( "id" ).toString() + ": " +
cur.<a href="qsqlquery.html#value">value</a>( "surname" ).toString() + " " +
cur.<a href="qsqlquery.html#value">value</a>( "salary" ).toString() );
}
}
return 0;
}
</pre><blockquote><p align="center"><em> From <a href="sql-overview-retrieve2-main-cpp.html">sql/overview/retrieve2/main.cpp</a>
</em></p>
</blockquote><p> We create the <a href="qsqlcursor.html">QSqlCursor</a> object, specifying the table or view to use.
If we need to use a database other than the default we can specify it
in the <a href="qsqlcursor.html">QSqlCursor</a> constructor.
<p> The SQL executed by the cur.select() call is
<p> <pre>
SELECT staff.id, staff.forename, staff.surname, staff.salary, staff.statusid FROM staff
</pre>
<p> Next, we iterate through the records returned by this select statement
using cur.next(). Field values are retrieved in in a similar way to
<a href="qsqlquery.html">QSqlQuery</a>, except that we pass field names rather than numeric indexes
to value() and setValue().
<p> <a name="Sorting_Data"></a>
<h4> Sorting and Filtering Records
</h4>
<a name="7-1-1"></a><p> To specify a subset of records to retrieve we can pass filtering
criteria to the select() function. Each record that is returned will
meet the criteria of the filter (the filter corresponds to the SQL
statement's <tt>WHERE</tt> clause).
<p> <pre>
cur.select( "id > 100" );
</pre>
<p> This select() call will execute the SQL
<pre>
SELECT staff.id, staff.forename, staff.surname, staff.salary, staff.statusid
FROM staff WHERE staff.id > 100
</pre>
<p> This will retrieve only those staff whose <tt>id</tt> is greater than 100.
<p> In addition to retrieving selected records we often want to specify a
sort order for the returned records. This is achieved by creating a <a href="qsqlindex.html">QSqlIndex</a> object which contains the names of the field(s) we wish to
sort by and pass this object to the select() call.
<p> <pre>
<a href="qsqlcursor.html">QSqlCursor</a> cur( "staff" );
<a href="qsqlindex.html">QSqlIndex</a> nameIndex = cur.<a href="qsqlcursor.html#index">index</a>( "surname" );
cur.<a href="qsqlcursor.html#select">select</a>( nameIndex );
</pre>
<p> Here we create a <a href="qsqlindex.html">QSqlIndex</a> object with one field, "surname". When
we call the select() function we pass the index object, which
specifies that the records should be returned sorted by
staff.surname. Each field in the index object is used in the ORDER BY
clause of the select statement. The SQL executed here is
<pre>
SELECT staff.id, staff.forename, staff.surname, staff.salary, staff.statusid
FROM staff ORDER BY staff.surname ASC
</pre>
<p> Combining the retrieval of a subset of records and ordering the results
is straightforward.
<p> <pre>
cur.select( "staff.surname LIKE 'A%'", nameIndex );
</pre>
<p> We pass in a filter string (the <tt>WHERE</tt> clause), and the <a href="qsqlindex.html">QSqlIndex</a>
object to sort by (the <tt>ORDER BY</tt> clause). This produces
<p> <pre>
SELECT staff.id, staff.forename, staff.surname, staff.salary, staff.statusid
FROM staff WHERE staff.surname LIKE 'A%' ORDER BY staff.surname ASC
</pre>
<p> To sort by more than one field, an index can be created which contains
multiple fields. Ascending and descending order can be set using
<a href="qsqlindex.html#setDescending">QSqlIndex::setDescending</a>(); the default is ascending.
<p>
<pre> <a href="qsqlcursor.html">QSqlCursor</a> cur( "staff" );
<a href="qstringlist.html">QStringList</a> fields = QStringList() << "surname" << "forename";
<a name="x2181"></a> <a href="qsqlindex.html">QSqlIndex</a> order = cur.<a href="qsqlcursor.html#index">index</a>( fields );
<a name="x2182"></a> cur.<a href="qsqlcursor.html#select">select</a>( order );
<a name="x2183"></a> while ( cur.<a href="qsqlquery.html#next">next</a>() ) {
</pre><blockquote><p align="center"><em> From <a href="sql-overview-order1-main-cpp.html">sql/overview/order1/main.cpp</a>
</em></p>
</blockquote><p> Here we create a string list containing the fields we wish to sort by,
in the order they are to be used. Then we create a <a href="qsqlindex.html">QSqlIndex</a> object
based on these fields, finally executing the select() call using this
index. This executes
<pre>
SELECT staff.id, staff.forename, staff.surname, staff.salary, staff.statusid
FROM staff ORDER BY staff.surname ASC, staff.forename ASC
</pre>
<p> If we need to retrieve records with fields that match specific criteria we
can create a filter based on an index.
<p>
<pre> <a href="qsqlcursor.html">QSqlCursor</a> cur( "staff" );
<a href="qstringlist.html">QStringList</a> fields = QStringList() << "id" << "forename";
<a name="x2184"></a> <a href="qsqlindex.html">QSqlIndex</a> order = cur.<a href="qsqlcursor.html#index">index</a>( fields );
<a href="qsqlindex.html">QSqlIndex</a> filter = cur.<a href="qsqlcursor.html#index">index</a>( "surname" );
<a name="x2186"></a> cur.<a href="qsqlrecord.html#setValue">setValue</a>( "surname", "Bloggs" );
<a name="x2185"></a> cur.<a href="qsqlcursor.html#select">select</a>( filter, order );
while ( cur.<a href="qsqlquery.html#next">next</a>() ) {
</pre><blockquote><p align="center"><em> From <a href="sql-overview-order2-main-cpp.html">sql/overview/order2/main.cpp</a>
</em></p>
</blockquote><p> This executes
<pre>
SELECT staff.id, staff.forename, staff.surname, staff.salary, staff.statusid
FROM staff WHERE staff.surname='Bloggs' ORDER BY staff.id ASC, staff.forename ASC
</pre>
<p> The "order" <a href="qsqlindex.html">QSqlIndex</a> contains two fields, "id" and "forename"
which are used to order the results. The "filter" <a href="qsqlindex.html">QSqlIndex</a>
contains a single field, "surname". When an index is passed as a
filter to the select() function, for each field in the filter, a
<em>fieldname=value</em> subclause is created where the value
is taken from the current cursor's value for that field. We use
setValue() to ensure that the value used is the one we want.
<p> <a name="Extracting_Data"></a>
<h4> Extracting Data
</h4>
<a name="7-1-2"></a><p>
<pre> <a href="qsqlcursor.html">QSqlCursor</a> cur( "creditors" );
<a href="qstringlist.html">QStringList</a> orderFields = QStringList() << "surname" << "forename";
<a name="x2188"></a> <a href="qsqlindex.html">QSqlIndex</a> order = cur.<a href="qsqlcursor.html#index">index</a>( orderFields );
<a href="qstringlist.html">QStringList</a> filterFields = QStringList() << "surname" << "city";
<a href="qsqlindex.html">QSqlIndex</a> filter = cur.<a href="qsqlcursor.html#index">index</a>( filterFields );
<a name="x2190"></a> cur.<a href="qsqlrecord.html#setValue">setValue</a>( "surname", "Chirac" );
cur.<a href="qsqlrecord.html#setValue">setValue</a>( "city", "Paris" );
<a name="x2189"></a> cur.<a href="qsqlcursor.html#select">select</a>( filter, order );
while ( cur.<a href="qsqlquery.html#next">next</a>() ) {
<a name="x2191"></a> int id = cur.<a href="qsqlquery.html#value">value</a>( "id" ).toInt();
<a href="qstring.html">QString</a> name = cur.<a href="qsqlquery.html#value">value</a>( "forename" ).toString() + " " +
cur.<a href="qsqlquery.html#value">value</a>( "surname" ).toString();
<a name="x2193"></a> <a href="qapplication.html#qDebug">qDebug</a>( QString::<a href="qstring.html#number">number</a>( id ) + ": " + name );
}
</pre><blockquote><p align="center"><em> From <a href="sql-overview-extract-main-cpp.html">sql/overview/extract/main.cpp</a>
</em></p>
</blockquote><p> In this example we begin by creating a cursor on the creditors table.
We create two <a href="qsqlindex.html">QSqlIndex</a> objects. The first, "order", is created
from the "orderFields" string list. The second, "filter", is created
from the "filterFields" string list. We set the values of the two
fields used in the filter, "surname" and "city", to the values we're
interested in. Now we call select() which generates and executes the
following SQL:
<pre>
SELECT creditors.city, creditors.surname, creditors.forename, creditors.id
FROM creditors
WHERE creditors.surname = 'Chirac' AND creditors.city = 'Paris'
ORDER BY creditors.surname ASC, creditors.forename ASC
</pre>
The filter fields are used in the <tt>WHERE</tt> clause. Their values are
taken from the cursor's current values for those fields; we set these
values ourselves with the setValue() calls. The order fields are used
in the <tt>ORDER BY</tt> clause.
<p> Now we iterate through each matching record (if any). We retrieve the
contents of the id, forename and surname fields and pass them on to
some processing function, in this example a simple <a href="qapplication.html#qDebug">qDebug</a>() call.
<p> <a name="Manipulating_Records"></a>
<h3> Manipulating Records
</h3>
<a name="7-2"></a><p> Records can be inserted, updated or deleted in a table or view using a
<a href="qsqlcursor.html">QSqlCursor</a> providing that the table or view has a primary index
that uniquely distinguishes each record. If this is not the case a <a href="qsqlquery.html">QSqlQuery</a> must be used instead. (Note that not all databases support
editable views.)
<p> Each cursor has an internal 'edit buffer' which is used by all the
edit operations (insert, update and delete). The editing process is
the same for each operation: acquire a pointer to the relevant buffer;
call setValue() to <a href="primes.html#prime">prime</a> the buffer with the values you want; call
insert() or update() or del() to perform the desired operation. For
example, when inserting a record using a cursor, you call
primeInsert() to get a pointer to the edit buffer and then call
setValue() on this buffer to set each field's value. Then you call
QSQlCursor::insert() to insert the contents of the edit buffer into
the database. Similarly, when updating (or deleting) a record, the
values of the fields in the edit buffer are used to update (or delete)
the record in the database. The 'edit buffer' is unaffected by any
<a href="#Navigating_Result_Sets">cursor navigation</a> functions.
Note that if you pass a string value to setValue() any single quotes
will be escaped (turned into a pair of single quotes) since a single
quote is a special character in SQL.
<p> The primeInsert(), primeUpdate() and primeDelete() methods all return
a pointer to the internal edit buffer. Each method can potentially
perform different operations on the edit buffer before returning it.
By default, <a href="qsqlcursor.html#primeInsert">QSqlCursor::primeInsert</a>() clears all the field values in
the edit buffer (see <a href="qsqlrecord.html#clearValues">QSqlRecord::clearValues</a>()). Both <a href="qsqlcursor.html#primeUpdate">QSqlCursor::primeUpdate</a>() and <a href="qsqlcursor.html#primeDelete">QSqlCursor::primeDelete</a>() initialize the
edit buffer with the current contents of the cursor before returning
it. All three of these functions are virtual, so you can redefine the
behavior (for example, reimplementing primeInsert() to auto-number
fields in the edit buffer). Data-aware user-interface controls emit
signals, e.g. primeInsert(), that you can connect to; these pass a
pointer to the appropriate buffer so subclassing may not be necessary.
See <a href="#Subclassing_QSqlCursor">subclassing QSqlCursor</a> for
more information on subclassing; see the <a href="designer-manual.html">Qt
Designer</a> manual for more on connecting to the primeInsert()
signal.
<p> When insert(), update() or del() is called on a cursor, it will be
invalidated and will no longer be positioned on a valid record. If you
need to move to another record after performing an insert(), update()
or del() you must make a fresh select() call. This ensures that
changes to the database are accurately reflected in the cursor.
<p> <a name="Inserting_Records"></a>
<h4> Inserting Records
</h4>
<a name="7-2-1"></a><p>
<pre> <a href="qsqlcursor.html">QSqlCursor</a> cur( "prices" );
<a href="qstringlist.html">QStringList</a> names = QStringList() <<
"Screwdriver" << "Hammer" << "Wrench" << "Saw";
int id = 20;
<a name="x2197"></a> for ( QStringList::Iterator name = names.<a href="qvaluelist.html#begin">begin</a>();
<a name="x2198"></a> name != names.<a href="qvaluelist.html#end">end</a>(); ++name ) {
<a name="x2195"></a> <a href="qsqlrecord.html">QSqlRecord</a> *buffer = cur.<a href="qsqlcursor.html#primeInsert">primeInsert</a>();
<a name="x2196"></a> buffer-><a href="qsqlrecord.html#setValue">setValue</a>( "id", id );
buffer-><a href="qsqlrecord.html#setValue">setValue</a>( "name", *name );
buffer-><a href="qsqlrecord.html#setValue">setValue</a>( "price", 100.0 + (double)id );
<a name="x2194"></a> count += cur.<a href="qsqlcursor.html#insert">insert</a>();
id++;
}
</pre><blockquote><p align="center"><em> From <a href="sql-overview-insert-main-cpp.html">sql/overview/insert/main.cpp</a>
</em></p>
</blockquote><p> In this example we create a cursor on the "prices" table. Next we
create a list of product names which we iterate over. For each
iteration we call the cursor's primeInsert() method. This method
returns a pointer to a <a href="qsqlrecord.html">QSqlRecord</a> buffer in which all the fields
are set to <tt>NULL</tt>. (Note that <a href="qsqlcursor.html#primeInsert">QSqlCursor::primeInsert</a>() is virtual,
and can be customized by derived classes. See <a href="qsqlcursor.html">QSqlCursor</a>). Next we
call setValue() for each field that requires a value. Finally we call
insert() to insert the record. The insert() call returns the number of
rows inserted.
<p> We obtained a pointer to a <a href="qsqlrecord.html">QSqlRecord</a> object from the primeInsert()
call. <a href="qsqlrecord.html">QSqlRecord</a> objects can hold the data for a single record plus some
meta-data about the record. In practice most interaction with a
QSqlRecord consists of simple value() and setValue() calls as shown in
this and the following example.
<p> <a name="Updating_Records"></a>
<h4> Updating Records
</h4>
<a name="7-2-2"></a><p>
<pre> <a href="qsqlcursor.html">QSqlCursor</a> cur( "prices" );
<a name="x2200"></a> cur.<a href="qsqlcursor.html#select">select</a>( "id=202" );
if ( cur.<a href="qsqlquery.html#next">next</a>() ) {
<a name="x2199"></a> <a href="qsqlrecord.html">QSqlRecord</a> *buffer = cur.<a href="qsqlcursor.html#primeUpdate">primeUpdate</a>();
<a name="x2204"></a> double price = buffer-><a href="qsqlrecord.html#value">value</a>( "price" ).toDouble();
double newprice = price * 1.05;
<a name="x2203"></a> buffer-><a href="qsqlrecord.html#setValue">setValue</a>( "price", newprice );
<a name="x2201"></a> cur.<a href="qsqlcursor.html#update">update</a>();
}
</pre><blockquote><p align="center"><em> From <a href="sql-overview-update-main-cpp.html">sql/overview/update/main.cpp</a>
</em></p>
</blockquote><p> This example begins with the creation of a cursor over the prices table.
We select the record we wish to update with the select() call and
move to it with the next() call. We call primeUpdate() to get a <a href="qsqlrecord.html">QSqlRecord</a> pointer to a buffer which is populated with the contents of
the current record. We retrieve the value of the price field, calculate
a new price, and set the the price field to the newly calculated value.
Finally we call update() to update the record. The update() call returns
the number of rows updated.
<p> If many identical updates need to be performed, for example increasing
the price of every item in the price list, using a single SQL statement
with <a href="qsqlquery.html">QSqlQuery</a> is more efficient, e.g.
<p> <pre>
<a href="qsqlquery.html">QSqlQuery</a> query( "UPDATE prices SET price = price * 1.05" );
</pre>
<p> <a name="Deleting_Records"></a>
<h4> Deleting Records
</h4>
<a name="7-2-3"></a><p>
<pre> <a href="qsqlcursor.html">QSqlCursor</a> cur( "prices" );
<a name="x2207"></a> cur.<a href="qsqlcursor.html#select">select</a>( "id=999" );
<a name="x2208"></a> if ( cur.<a href="qsqlquery.html#next">next</a>() ) {
<a name="x2206"></a> cur.<a href="qsqlcursor.html#primeDelete">primeDelete</a>();
<a name="x2205"></a> cur.<a href="qsqlcursor.html#del">del</a>();
</pre><blockquote><p align="center"><em> From <a href="sql-overview-delete-main-cpp.html">sql/overview/delete/main.cpp</a>
</em></p>
</blockquote><p> To delete records, select the record to be deleted and navigate to it.
Then call primeDelete() to populate the cursor with the primary key
of the selected record, (in this example, the <tt>prices.id</tt> field), and
then call <a href="qsqlcursor.html#del">QSqlCursor::del</a>() to delete it.
<p> As with update(), if multiple deletions need to be made with some common
criteria it is more efficient to do so using a single SQL statement,
e.g.
<p> <pre>
<a href="qsqlquery.html">QSqlQuery</a> query( "DELETE FROM prices WHERE id >= 2450 AND id <= 2500" );
</pre>
<p> <a name="Data-Aware_Widgets"></a>
<h2> Data-Aware Widgets
</h2>
<a name="8"></a><p> Data-Aware Widgets provide a simple yet powerful means of connecting
databases to Qt user interfaces. The easiest way of creating and
manipulating data-aware widgets is with <a href="designer-manual.html">Qt
Designer</a>. For those who prefer a purely programmatic approach
the following examples and explanations provide an introduction. Note
that the "Creating Database Applications" chapter of the <a href="designer-manual.html">Qt Designer</a> manual and its accompanying
examples provides additional information.
<p> <a name="Data-Aware_Tables"></a>
<h3> Data-Aware Tables
</h3>
<a name="8-1"></a><p>
<pre> #include <<a href="qapplication-h.html">qapplication.h</a>>
#include <<a href="qsqldatabase-h.html">qsqldatabase.h</a>>
#include <<a href="qsqlcursor-h.html">qsqlcursor.h</a>>
#include <<a href="qdatatable-h.html">qdatatable.h</a>>
#include "../connection.h"
int main( int argc, char *argv[] )
{
<a href="qapplication.html">QApplication</a> app( argc, argv );
if ( createConnections() ) {
<a href="qsqlcursor.html">QSqlCursor</a> staffCursor( "staff" );
<a href="qdatatable.html">QDataTable</a> *staffTable = new <a href="qdatatable.html">QDataTable</a>( &staffCursor, TRUE );
app.<a href="qapplication.html#setMainWidget">setMainWidget</a>( staffTable );
<a name="x2211"></a> staffTable-><a href="qdatatable.html#refresh">refresh</a>();
staffTable-><a href="qwidget.html#show">show</a>();
return app.<a href="qapplication.html#exec">exec</a>();
}
return 0;
}
</pre><blockquote><p align="center"><em> From <a href="sql-overview-table1-main-cpp.html">sql/overview/table1/main.cpp</a>
</em></p>
</blockquote><p> Data-Aware tables require the <a href="qdatatable-h.html">qdatatable.h</a> and <a href="qsqlcursor-h.html">qsqlcursor.h</a> header
files. We create our application object, call createConnections() and
create the cursor. We create the <a href="qdatatable.html">QDataTable</a> passing it a pointer to
the cursor, and set the autoPopulate flag to TRUE. Next we make our <a href="qdatatable.html">QDataTable</a> the main widget and call refresh() to populate it with data
and call show() to make it visible.
<p> The autoPopulate flag tells the <a href="qdatatable.html">QDataTable</a> whether or nor it should
create columns based on the cursor. autoPopulate does not affect the
loading of data into the table; that is achieved by the refresh()
function.
<p>
<pre> <a href="qsqlcursor.html">QSqlCursor</a> staffCursor( "staff" );
<a href="qdatatable.html">QDataTable</a> *staffTable = new <a href="qdatatable.html">QDataTable</a>( &staffCursor );
app.<a href="qapplication.html#setMainWidget">setMainWidget</a>( staffTable );
<a name="x2214"></a> staffTable-><a href="qdatatable.html#addColumn">addColumn</a>( "forename", "Forename" );
staffTable-><a href="qdatatable.html#addColumn">addColumn</a>( "surname", "Surname" );
staffTable-><a href="qdatatable.html#addColumn">addColumn</a>( "salary", "Annual Salary" );
<a href="qstringlist.html">QStringList</a> order = QStringList() << "surname" << "forename";
<a name="x2216"></a> staffTable-><a href="qdatatable.html#setSort">setSort</a>( order );
<a name="x2215"></a> staffTable-><a href="qdatatable.html#refresh">refresh</a>();
staffTable-><a href="qwidget.html#show">show</a>();
</pre><blockquote><p align="center"><em> From <a href="sql-overview-table2-main-cpp.html">sql/overview/table2/main.cpp</a>
</em></p>
</blockquote><p> We create an empty <a href="qdatatable.html">QDataTable</a> which we make into our main widget and
then we manually add the columns we want in the order we wish them to
appear. For each column we specify the field name and optionally a
display label.
<p> We have also opted to sort the rows in the table; this could also have
been achieved by applying the sort to the cursor itself.
<p> Once everything is set up we call refresh() to load the data from the
database and show() to make the widget visible.
<p> QDataTables only retrieve visible rows which (depending on the driver)
allows even large tables to be displayed very quickly with minimal
memory cost.
<p> <a name="Creating_Forms"></a>
<h3> Creating Data-Aware Forms
</h3>
<a name="8-2"></a><p> Creating data-aware forms is more involved than using data-aware
tables because we must take care of each field individually. Most of
the code below can be automatically generated by <a href="designer-manual.html">Qt Designer</a>. See the <a href="designer-manual.html">Qt Designer</a> manual for more details.
<p> <a name="Displaying_a_Record"></a>
<h4> Displaying a Record
</h4>
<a name="8-2-1"></a><p>
<pre> #include <<a href="qapplication-h.html">qapplication.h</a>>
#include <<a href="qdialog-h.html">qdialog.h</a>>
#include <<a href="qlabel-h.html">qlabel.h</a>>
#include <<a href="qlayout-h.html">qlayout.h</a>>
#include <<a href="qlineedit-h.html">qlineedit.h</a>>
#include <<a href="qsqldatabase-h.html">qsqldatabase.h</a>>
#include <<a href="qsqlcursor-h.html">qsqlcursor.h</a>>
#include <<a href="qsqlform-h.html">qsqlform.h</a>>
#include "../connection.h"
class FormDialog : public <a href="qdialog.html">QDialog</a>
{
public:
FormDialog();
};
FormDialog::FormDialog()
{
<a href="qlabel.html">QLabel</a> *forenameLabel = new <a href="qlabel.html">QLabel</a>( "Forename:", this );
<a href="qlabel.html">QLabel</a> *forenameDisplay = new <a href="qlabel.html">QLabel</a>( this );
<a href="qlabel.html">QLabel</a> *surnameLabel = new <a href="qlabel.html">QLabel</a>( "Surname:", this );
<a href="qlabel.html">QLabel</a> *surnameDisplay = new <a href="qlabel.html">QLabel</a>( this );
<a href="qlabel.html">QLabel</a> *salaryLabel = new <a href="qlabel.html">QLabel</a>( "Salary:", this );
<a href="qlineedit.html">QLineEdit</a> *salaryEdit = new <a href="qlineedit.html">QLineEdit</a>( this );
<a href="qgridlayout.html">QGridLayout</a> *grid = new <a href="qgridlayout.html">QGridLayout</a>( this );
<a name="x2221"></a> grid-><a href="qgridlayout.html#addWidget">addWidget</a>( forenameLabel, 0, 0 );
grid-><a href="qgridlayout.html#addWidget">addWidget</a>( forenameDisplay, 0, 1 );
grid-><a href="qgridlayout.html#addWidget">addWidget</a>( surnameLabel, 1, 0 );
grid-><a href="qgridlayout.html#addWidget">addWidget</a>( surnameDisplay, 1, 1 );
grid-><a href="qgridlayout.html#addWidget">addWidget</a>( salaryLabel, 2, 0 );
grid-><a href="qgridlayout.html#addWidget">addWidget</a>( salaryEdit, 2, 1 );
<a name="x2222"></a> grid-><a href="qlayout.html#activate">activate</a>();
<a href="qsqlcursor.html">QSqlCursor</a> staffCursor( "staff" );
staffCursor.<a href="qsqlcursor.html#select">select</a>();
staffCursor.<a href="qsqlquery.html#next">next</a>();
<a href="qsqlform.html">QSqlForm</a> sqlForm( this );
<a name="x2227"></a><a name="x2223"></a> sqlForm.<a href="qsqlform.html#setRecord">setRecord</a>( staffCursor.<a href="qsqlcursor.html#primeUpdate">primeUpdate</a>() );
<a name="x2225"></a> sqlForm.<a href="qsqlform.html#insert">insert</a>( forenameDisplay, "forename" );
sqlForm.<a href="qsqlform.html#insert">insert</a>( surnameDisplay, "surname" );
sqlForm.<a href="qsqlform.html#insert">insert</a>( salaryEdit, "salary" );
<a name="x2226"></a> sqlForm.<a href="qsqlform.html#readFields">readFields</a>();
}
int main( int argc, char *argv[] )
{
<a href="qapplication.html">QApplication</a> app( argc, argv );
if ( ! createConnections() ) return 1;
FormDialog *formDialog = new FormDialog();
<a name="x2220"></a> formDialog-><a href="qdialog.html#show">show</a>();
app.<a href="qapplication.html#setMainWidget">setMainWidget</a>( formDialog );
return app.<a href="qapplication.html#exec">exec</a>();
}
</pre><blockquote><p align="center"><em> From <a href="sql-overview-form1-main-cpp.html">sql/overview/form1/main.cpp</a>
</em></p>
</blockquote><p> We include the header files for the widgets that we need. We also
include <a href="qsqldatabase-h.html">qsqldatabase.h</a> and <a href="qsqlcursor-h.html">qsqlcursor.h</a> as usual, but we now add
<a href="qsqlform-h.html">qsqlform.h</a>.
<p> The form will be presented as a dialog so we subclass <a href="qdialog.html">QDialog</a> with
our own FormDialog class. We use a <a href="qlineedit.html">QLineEdit</a> for the salary so that
the user can change it. All the widgets are laid out using a grid.
<p> We create a cursor on the staff table, select all records and move to
the first record.
<p> Now we create a <a href="qsqlform.html">QSqlForm</a> object and set the <a href="qsqlform.html">QSqlForm</a>'s record buffer
to the cursor's update buffer. For each widget that we wish to make
data-aware we insert a pointer to the widget and the associated field
name into the <a href="qsqlform.html">QSqlForm</a>. Finally we call readFields() to populate the
widgets with data from the database via the cursor's buffer.
<p> <a name="Displaying_a_Record_in_a_DataForm"></a>
<h4> Displaying a Record in a Data Form
</h4>
<a name="8-2-2"></a><p> <a href="qdataview.html">QDataView</a> is a Widget that can hold a read-only <a href="qsqlform.html">QSqlForm</a>. In
addition to <a href="qsqlform.html">QSqlForm</a> it offers the slot refresh( <a href="qsqlrecord.html">QSqlRecord</a> * ) so it
can easily be linked together with a <a href="qdatatable.html">QDataTable</a> to display a detailed
view of a record:
<p> <pre>
connect( myDataTable, SIGNAL( currentChanged( <a href="qsqlrecord.html">QSqlRecord</a>* ) ),
myDataView, SLOT( refresh( <a href="qsqlrecord.html">QSqlRecord</a>* ) ) );
</pre>
<p> <a name="Editing_a_Record"></a>
<h4> Editing a Record
</h4>
<a name="8-2-3"></a><p> This example is similar to the previous one so we will focus on the
differences.
<p>
<pre> class FormDialog : public <a href="qdialog.html">QDialog</a>
{
<a href="metaobjects.html#Q_OBJECT">Q_OBJECT</a>
public:
FormDialog();
~FormDialog();
public slots:
void save();
private:
<a href="qsqlcursor.html">QSqlCursor</a> staffCursor;
<a href="qsqlform.html">QSqlForm</a> *sqlForm;
<a href="qsqlindex.html">QSqlIndex</a> idIndex;
};
</pre><blockquote><p align="center"><em> From <a href="sql-overview-form2-main-h.html">sql/overview/form2/main.h</a>
</em></p>
</blockquote><p> The save slot will be used for a button that the user can press to
confirm their update. We also hold pointers to the <a href="qsqlcursor.html">QSqlCursor</a> and the
<a href="qsqlform.html">QSqlForm</a> since they will need to be accessed outside the constructor.
<p>
<pre> staffCursor.setTrimmed( "forename", TRUE );
staffCursor.setTrimmed( "surname", TRUE );
</pre>
<p> We call setTrimmed() on the text fields so that any spaces used to
right pad the fields are removed when the fields are retrieved.
<p> Properties that we might wish to apply to fields, such as alignment
and validation are achieved in the conventional way, for example, by
calling <a href="qlineedit.html#setAlignment">QLineEdit::setAlignment</a>() and <a href="qlineedit.html#setValidator">QLineEdit::setValidator</a>().
<p> <pre> <a href="qlineedit.html">QLineEdit</a> *forenameEdit = new <a href="qlineedit.html">QLineEdit</a>( this );
</pre>
<p> <pre> <a href="qpushbutton.html">QPushButton</a> *saveButton = new <a href="qpushbutton.html">QPushButton</a>( "&Save", this );
<a href="qobject.html#connect">connect</a>( saveButton, SIGNAL(<a href="qbutton.html#clicked">clicked</a>()), this, SLOT(save()) );
</pre>
<p> The FormDialog constructor is similar to the one in the previous
example. We have changed the forename and surname widgets to
<a href="qlineedit.html">QLineEdit</a>s to make them editable and have added a <a href="qpushbutton.html">QPushButton</a>
the user can click to save their updates.
<p> <pre> <a name="x2230"></a> grid-><a href="qgridlayout.html#addWidget">addWidget</a>( saveButton, 3, 0 );
</pre>
<p> We add an extra row to the grid containing the save button.
<p> <pre> idIndex = staffCursor.index( "id" );
staffCursor.select( idIndex );
staffCursor.first();
</pre>
<p> We create a <a href="qsqlindex.html">QSqlIndex</a> object and then execute a select() using the
index. We then move to the first record in the result set.
<p> <pre> sqlForm = new <a href="qsqlform.html">QSqlForm</a>( this );
<a name="x2233"></a> sqlForm-><a href="qsqlform.html#setRecord">setRecord</a>( staffCursor.primeUpdate() );
</pre>
<p> We create a new <a href="qsqlform.html">QSqlForm</a> object and set it's record buffer to the
cursor's update buffer.
<p> <pre> <a name="x2231"></a> sqlForm-><a href="qsqlform.html#insert">insert</a>( forenameEdit, "forename" );
sqlForm-><a href="qsqlform.html#insert">insert</a>( surnameEdit, "surname" );
sqlForm-><a href="qsqlform.html#insert">insert</a>( salaryEdit, "salary" );
<a name="x2232"></a> sqlForm-><a href="qsqlform.html#readFields">readFields</a>();
</pre>
<p> Now we link the buffer's fields to the <a href="qlineedit.html">QLineEdit</a> controls. (In the
previous example we linked the cursor's fields.) The edit controls are
populated by the readFields() call as before.
<p> <pre> FormDialog::~FormDialog()
{
}
</pre>
<p> In the destructor we don't have to worry about the widgets or QSqlForm
since they are children of the form and will be deleted by Qt at the
right time.
<p> <pre> void FormDialog::save()
{
<a name="x2234"></a> sqlForm-><a href="qsqlform.html#writeFields">writeFields</a>();
staffCursor.update();
staffCursor.select( idIndex );
staffCursor.first();
}
</pre>
<p> Finally we add the save functionality for when the user presses the
save button. We write back the data from the widgets to the <a href="qsqlrecord.html">QSqlRecord</a> buffer with the writeFields() call. Then we update the
database with the updated version of the record with the cursor's
update() function. At this point the cursor is no longer positioned at
a valid record so we reissue the select() call using our <a href="qsqlindex.html">QSqlIndex</a>
and move to the first record.
<p> <a href="qdatabrowser.html">QDataBrowser</a> and <a href="qdataview.html">QDataView</a> are widgets which provide a great deal of
the above functionality. <a href="qdatabrowser.html">QDataBrowser</a> provides a data form which
allows editing of and navigation through a cursor's records. <a href="qdataview.html">QDataView</a> provides a read only form for data in a cursor or database
record. See the class documentation or the <a href="designer-manual.html">Qt Designer</a> manual for more information on using these
widgets.
<p> Link to <a href="sql.html">sql/overview/form2/main.cpp</a>
<p> <a name="Custom_Editor_Widgets"></a>
<h3> Custom Editor Widgets
</h3>
<a name="8-3"></a><p> <a href="qsqlform.html">QSqlForm</a> uses <a href="qsqlpropertymap.html">QSqlPropertyMap</a> to handle the transfer of data between
widgets and database fields. Custom widgets can also be used in a form
by installing a property map that contains information about the
properties of the custom widget which should be used to transfer the
data.
<p> This example is based on the form2 example in the previous section so
we will only cover the differences here. The full source is in <a href="sql-overview-custom1-main-h.html">sql/overview/custom1/main.h</a> and <a href="sql-overview-custom1-main-cpp.html">sql/overview/custom1/main.cpp</a>
<p>
<pre> class CustomEdit : public <a href="qlineedit.html">QLineEdit</a>
{
Q_OBJECT
Q_PROPERTY( QString upperLine READ upperLine WRITE setUpperLine )
public:
CustomEdit( <a href="qwidget.html">QWidget</a> *parent=0, const char *name=0 );
<a href="qstring.html">QString</a> upperLine() const;
void setUpperLine( const <a href="qstring.html">QString</a> &line );
public slots:
void changed( const <a href="qstring.html">QString</a> &line );
private:
<a href="qstring.html">QString</a> upperLineText;
};
</pre>
<p> We've created a simple subclass of <a href="qlineedit.html">QLineEdit</a> and added a property,
upperLineText, which will hold an uppercase version of the text. We
also created a slot, changed().
<p> <pre> <a href="qsqlpropertymap.html">QSqlPropertyMap</a> *propMap;
</pre>
<p> We will be using a property map so we add a pointer to a property map
to our FormDialog's private data.
<p>
<pre> CustomEdit::CustomEdit( <a href="qwidget.html">QWidget</a> *parent, const char *name ) :
<a href="qlineedit.html">QLineEdit</a>( parent, name )
{
<a href="qobject.html#connect">connect</a>( this, SIGNAL(<a href="qlineedit.html#textChanged">textChanged</a>(const <a href="qstring.html">QString</a> &)),
this, SLOT(changed(const <a href="qstring.html">QString</a> &)) );
}
</pre>
<p> In the CustomEdit constructor we use the QLineEdit constructor and add
a connection between the textChanged signal and our own changed slot.
<p> <pre> void CustomEdit::changed( const <a href="qstring.html">QString</a> &line )
{
setUpperLine( line );
}
</pre>
<p> The changed() slot calls our setUpperLine() function.
<p> <pre> void CustomEdit::setUpperLine( const <a href="qstring.html">QString</a> &line )
{
<a name="x2240"></a> upperLineText = line.<a href="qstring.html#upper">upper</a>();
setText( upperLineText );
}
</pre>
<p> The setUpperLine() function places an uppercase copy of the text in the
upperLineText buffer and then sets the text of the widget to this text.
<p> Our CustomEdit class ensures that the text entered is always uppercase
and provides a property that can be used with a property map to link
CustomEdit instances directly to database fields.
<p> <pre> CustomEdit *forenameEdit = new CustomEdit( this );
</pre>
<p> <pre> CustomEdit *surnameEdit = new CustomEdit( this );
</pre>
<p> We use the same FormDialog as we did before, but this time replace two
of the <a href="qlineedit.html">QLineEdit</a> widgets with our own CustomEdit widgets.
<p> Laying out the grid and setting up the cursor is the same as before.
<p> <pre> propMap = new <a href="qsqlpropertymap.html">QSqlPropertyMap</a>;
<a name="x2239"></a><a name="x2235"></a> propMap-><a href="qsqlpropertymap.html#insert">insert</a>( forenameEdit-><a href="qobject.html#className">className</a>(), "upperLine" );
</pre>
<p> We create a new property map on the heap and register our CustomEdit
class and its upperLine property with the property map.
<p> <pre> sqlForm = new <a href="qsqlform.html">QSqlForm</a>( this );
<a name="x2238"></a><a name="x2236"></a> sqlForm-><a href="qsqlform.html#setRecord">setRecord</a>( staffCursor-><a href="qsqlcursor.html#primeUpdate">primeUpdate</a>() );
<a name="x2237"></a> sqlForm-><a href="qsqlform.html#installPropertyMap">installPropertyMap</a>( propMap );
</pre>
<p> The final change is to install the property map into the <a href="qsqlform.html">QSqlForm</a> once
the QSqlForm has been created. This passes responsibility for the
property map's memory to QSqlForm which itself is owned by the
FormDialog, so Qt will delete them at the right time.
<p> The behaviour of this example is identical to the previous one except
that the forename and surname fields will be uppercase since they use
our CustomEdit widget.
<p> <a name="Custom_Editor_Widgets_for_Tables"></a>
<h4> Custom Editor Widgets for Tables
</h4>
<a name="8-3-1"></a><p> We must reimpliment <a href="qsqleditorfactory.html">QSqlEditorFactory</a> to use custom editor widgets in
tables. In the following example we will create a custom editor based
on <a href="qcombobox.html">QComboBox</a> and a QSqlEditorFactory subclass to show how a <a href="qdatatable.html">QDataTable</a>
can use a custom editor.
<p>
<pre> class StatusPicker : public <a href="qcombobox.html">QComboBox</a>
{
Q_OBJECT
Q_PROPERTY( int statusid READ statusId WRITE setStatusId )
public:
StatusPicker( <a href="qwidget.html">QWidget</a> *parent=0, const char *name=0 );
int statusId() const;
void setStatusId( int id );
private:
<a href="qmap.html">QMap</a>< int, int > index2id;
};
</pre><blockquote><p align="center"><em> From <a href="sql-overview-table3-main-h.html">sql/overview/table3/main.h</a>
</em></p>
</blockquote><p> We create a property, statusid, and define our READ and WRITE methods
for it. The statusid's in the status table will probably be different
from the combobox's indexes so we create a <a href="qmap.html">QMap</a> to map combobox indexes
to/from the statusids that we will list in the combobox.
<p> <pre> class CustomSqlEditorFactory : public <a href="qsqleditorfactory.html">QSqlEditorFactory</a>
{
Q_OBJECT
public:
<a href="qwidget.html">QWidget</a> *createEditor( <a href="qwidget.html">QWidget</a> *parent, const <a href="qsqlfield.html">QSqlField</a> *field );
};
</pre>
<p> We also need to subclass <a href="qsqleditorfactory.html">QSqlEditorFactory</a> declaring a createEditor()
function since that is the only function we need to reimplement.
<p>
<pre> StatusPicker::StatusPicker( <a href="qwidget.html">QWidget</a> *parent, const char *name )
: <a href="qcombobox.html">QComboBox</a>( parent, name )
{
<a href="qsqlcursor.html">QSqlCursor</a> cur( "status" );
<a name="x2242"></a><a name="x2241"></a> cur.<a href="qsqlcursor.html#select">select</a>( cur.<a href="qsqlcursor.html#index">index</a>( "name" ) );
int i = 0;
while ( cur.<a href="qsqlquery.html#next">next</a>() ) {
<a name="x2243"></a> <a href="qcombobox.html#insertItem">insertItem</a>( cur.<a href="qsqlquery.html#value">value</a>( "name" ).toString(), i );
index2id[i] = cur.<a href="qsqlquery.html#value">value</a>( "id" ).toInt();
i++;
}
</pre><blockquote><p align="center"><em> From <a href="sql-overview-table3-main-cpp.html">sql/overview/table3/main.cpp</a>
</em></p>
</blockquote><p> In the StatusPicker's constructor we create a cursor over the status
table indexed by the name field. We then iterate over each record in the
status table inserting each name into the combobox. We store the
statusid for each name in the index2id <a href="qmap.html">QMap</a> using the same QMap index as
the combobox index.
<p> <pre> int StatusPicker::statusId() const
{
return index2id[ currentItem() ];
}
</pre>
<p> The statusid property READ function simply involves looking up the
combobox's index for the currently selected item in the index2id QMap
which maps combobox indexes to statusids.
<p> <pre> void StatusPicker::setStatusId( int statusid )
{
QMap<int,int>::Iterator it;
for ( it = index2id.begin(); it != index2id.end(); ++it ) {
if ( it.data() == statusid ) {
setCurrentItem( it.key() );
break;
}
}
}
</pre>
<p> The statusId() function implements the statusid property's WRITE
function. We create an iterator over a QMap and iterate over the
index2id QMap. We compare each index2id element's data (statusid) to
the id parameter's value. If we have a match we set the combobox's
current item to the index2id element's key (the combobox index), and
leave the loop.
<p> When the user edits the status field in the <a href="qdatatable.html">QDataTable</a> they will be
presented with a combobox of valid status names taken from the status
table. However the status displayed is still the raw statusid. To
display the status name when the field isn't being edited requires us
to subclass QDataTable and reimplement the paintField() function.
<p>
<pre> class CustomTable : public <a href="qdatatable.html">QDataTable</a>
{
Q_OBJECT
public:
CustomTable(
<a href="qsqlcursor.html">QSqlCursor</a> *cursor, bool autoPopulate = FALSE,
<a href="qwidget.html">QWidget</a> * parent = 0, const char * name = 0 ) :
<a href="qdatatable.html">QDataTable</a>( cursor, autoPopulate, parent, name ) {}
void paintField(
<a href="qpainter.html">QPainter</a> * p, const <a href="qsqlfield.html">QSqlField</a>* field, const <a href="qrect.html">QRect</a> & cr, bool );
};
</pre><blockquote><p align="center"><em> From <a href="sql-overview-table4-main-h.html">sql/overview/table4/main.h</a>
</em></p>
</blockquote><p> We simply call the original <a href="qdatatable.html">QDataTable</a> constructor without changing
anything. We also declare the paintField function.
<p>
<pre> <a name="x2245"></a>void CustomTable::<a href="qdatatable.html#paintField">paintField</a>( <a href="qpainter.html">QPainter</a> * p, const <a href="qsqlfield.html">QSqlField</a>* field,
const <a href="qrect.html">QRect</a> & cr, bool b)
{
if ( !field )
return;
<a name="x2249"></a> if ( field-><a href="qsqlfield.html#name">name</a>() == "statusid" ) {
<a href="qsqlquery.html">QSqlQuery</a> query( "SELECT name FROM status WHERE id=" +
<a name="x2250"></a> field-><a href="qsqlfield.html#value">value</a>().toString() );
<a href="qstring.html">QString</a> text;
if ( query.<a href="qsqlquery.html#next">next</a>() ) {
<a name="x2252"></a> text = query.<a href="qsqlquery.html#value">value</a>( 0 ).toString();
}
p-><a href="qpainter.html#drawText">drawText</a>( 2,2, cr.<a href="qrect.html#width">width</a>()-4, cr.<a href="qrect.html#height">height</a>()-4, fieldAlignment( field ), text );
}
else {
QDataTable::<a href="qdatatable.html#paintField">paintField</a>( p, field, cr, b) ;
}
</pre><blockquote><p align="center"><em> From <a href="sql-overview-table4-main-cpp.html">sql/overview/table4/main.cpp</a>
</em></p>
</blockquote><p> The paintField code is based on <a href="qdatatable.html">QDataTable</a>'s source code. We need to
make three changes. Firstly add an if clause <tt>field->name() == "statusid"</tt> and look up the textual value for the id with a
straighforward <a href="qsqlquery.html">QSqlQuery</a>. Secondly call the superclass to handle other
fields. The last change is in our main function where we change
staffTable from being a QDataTable to being a CustomTable.
<p> <a name="Subclassing_QSqlCursor"></a>
<h2> Subclassing <a href="qsqlcursor.html">QSqlCursor</a>
</h2>
<a name="9"></a><p>
<pre> #include <<a href="qapplication-h.html">qapplication.h</a>>
#include <<a href="qsqldatabase-h.html">qsqldatabase.h</a>>
#include <<a href="qsqlcursor-h.html">qsqlcursor.h</a>>
#include <<a href="qdatatable-h.html">qdatatable.h</a>>
#include "../connection.h"
int main( int argc, char *argv[] )
{
<a href="qapplication.html">QApplication</a> app( argc, argv );
if ( createConnections() ) {
<a href="qsqlcursor.html">QSqlCursor</a> invoiceItemCursor( "invoiceitem" );
<a href="qdatatable.html">QDataTable</a> *invoiceItemTable = new <a href="qdatatable.html">QDataTable</a>( &invoiceItemCursor );
app.<a href="qapplication.html#setMainWidget">setMainWidget</a>( invoiceItemTable );
<a name="x2255"></a> invoiceItemTable-><a href="qdatatable.html#addColumn">addColumn</a>( "pricesid", "PriceID" );
invoiceItemTable-><a href="qdatatable.html#addColumn">addColumn</a>( "quantity", "Quantity" );
invoiceItemTable-><a href="qdatatable.html#addColumn">addColumn</a>( "paiddate", "Paid" );
<a name="x2256"></a> invoiceItemTable-><a href="qdatatable.html#refresh">refresh</a>();
invoiceItemTable-><a href="qwidget.html#show">show</a>();
return app.<a href="qapplication.html#exec">exec</a>();
}
return 1;
}
</pre><blockquote><p align="center"><em> From <a href="sql-overview-subclass1-main-cpp.html">sql/overview/subclass1/main.cpp</a>
</em></p>
</blockquote><p> This example is very similar to the table1 example presented earlier. We
create a cursor, add the fields and their display labels to a <a href="qdatatable.html">QDataTable</a>,
call refresh() to load the data and call show() to show the widget.
<p> Unfortunately this example is unsatisfactory. It is tedious to set the
table name and any custom characteristics for the fields every time we
need a cursor over this table. And it would be far better if we
displayed the name of the product rather than its pricesid. Since we
know the price of the product and the quantity we could also show the
product cost and the cost of each invoiceitem. Finally it would be
useful (or even essential for primary keys) if we could default some of
the values when the user adds a new record.
<p>
<pre> class InvoiceItemCursor : public <a href="qsqlcursor.html">QSqlCursor</a>
{
public:
InvoiceItemCursor();
};
</pre><blockquote><p align="center"><em> From <a href="sql-overview-subclass2-main-h.html">sql/overview/subclass2/main.h</a>
</em></p>
</blockquote><p> We have created a separate header file and subclassed <a href="qsqlcursor.html">QSqlCursor</a>.
<p>
<pre> InvoiceItemCursor::InvoiceItemCursor() :
<a href="qsqlcursor.html">QSqlCursor</a>( "invoiceitem" )
{
// NOOP
}
</pre><blockquote><p align="center"><em> From <a href="sql-overview-subclass2-main-cpp.html">sql/overview/subclass2/main.cpp</a>
</em></p>
</blockquote><p> In our class's constructor we call the QSqlCursor constructor with the
name of the table. We don't have any other characteristics to add at
this stage.
<p> <pre> InvoiceItemCursor invoiceItemCursor;
</pre>
<p> Whenever we require a cursor over the invoiceitem table we can create
an InvoiceItemCursor instead of a generic QSqlCursor.
<p> We still need to show the product name rather than the pricesid.
<p>
<pre> protected:
<a href="qvariant.html">QVariant</a> calculateField( const <a href="qstring.html">QString</a> & name );
</pre><blockquote><p align="center"><em> From <a href="sql-overview-subclass3-main-h.html">sql/overview/subclass3/main.h</a>
</em></p>
</blockquote><p> The change in the header file is minimal: we simply add the signature
of the calculateField() function since we will be reimplementing it.
<p>
<pre> InvoiceItemCursor::InvoiceItemCursor() :
<a href="qsqlcursor.html">QSqlCursor</a>( "invoiceitem" )
{
<a href="qsqlfieldinfo.html">QSqlFieldInfo</a> productName( "productname", QVariant::String );
<a href="qsqlcursor.html#append">append</a>( productName );
<a name="x2260"></a> <a href="qsqlcursor.html#setCalculated">setCalculated</a>( productName.<a href="qsqlfieldinfo.html#name">name</a>(), TRUE );
}
<a name="x2259"></a>QVariant InvoiceItemCursor::<a href="qsqlcursor.html#calculateField">calculateField</a>( const <a href="qstring.html">QString</a> & name )
{
if ( name == "productname" ) {
<a href="qsqlquery.html">QSqlQuery</a> query( "SELECT name FROM prices WHERE id=" +
<a href="qsqlrecord.html#field">field</a>( "pricesid" )->value().toString() );
if ( query.<a href="qsqlquery.html#next">next</a>() )
<a name="x2262"></a> return query.<a href="qsqlquery.html#value">value</a>( 0 );
}
return QVariant( <a href="qstring.html#QString-null">QString::null</a> );
}
</pre><blockquote><p align="center"><em> From <a href="sql-overview-subclass3-main-cpp.html">sql/overview/subclass3/main.cpp</a>
</em></p>
</blockquote><p> We have changed the InvoiceItemCursor constructor. We now create a new
<a href="qsqlfield.html">QSqlField</a> called productname and append this to the
InvoiceItemCursor's set of fields. We call setCalculated() on
productname to identify it as a calculated field. The first argument
to setCalculated() is the field name, the second a bool which if TRUE
signifies that calculateField() must be called to get the field's
value.
<p> <pre> <a name="x2258"></a> invoiceItemTable-><a href="qdatatable.html#addColumn">addColumn</a>( "productname", "Product" );
</pre>
<p> We add our new fields with addColumn() which adds them to the form and
sets their display names.
<p> We have to define our own calculateField() function. In our example
database the pricesid in the invoiceitem table is a foreign key into
the prices table. We find the name of the product by executing a query
on the prices table using the pricesid. This returns the product's
name.
<p> We are now able to extend the example to include calculated fields
which perform real calculations.
<p> The header file, <a href="sql-overview-subclass4-main-h.html">sql/overview/subclass4/main.h</a>, remains unchanged
from the previous example, but the constructor and calculateField()
function require some simple expansion. We'll look at each in turn.
<p>
<pre> InvoiceItemCursor::InvoiceItemCursor() :
<a href="qsqlcursor.html">QSqlCursor</a>( "invoiceitem" )
{
<a href="qsqlfieldinfo.html">QSqlFieldInfo</a> productName( "productname", QVariant::String );
<a href="qsqlcursor.html#append">append</a>( productName );
<a name="x2264"></a> <a href="qsqlcursor.html#setCalculated">setCalculated</a>( productName.<a href="qsqlfieldinfo.html#name">name</a>(), TRUE );
<a href="qsqlfieldinfo.html">QSqlFieldInfo</a> productPrice( "price", QVariant::Double );
<a href="qsqlcursor.html#append">append</a>( productPrice );
<a href="qsqlcursor.html#setCalculated">setCalculated</a>( productPrice.<a href="qsqlfieldinfo.html#name">name</a>(), TRUE );
<a href="qsqlfieldinfo.html">QSqlFieldInfo</a> productCost( "cost", QVariant::Double );
<a href="qsqlcursor.html#append">append</a>( productCost );
<a href="qsqlcursor.html#setCalculated">setCalculated</a>( productCost.<a href="qsqlfieldinfo.html#name">name</a>(), TRUE );
}
</pre><blockquote><p align="center"><em> From <a href="sql-overview-subclass4-main-cpp.html">sql/overview/subclass4/main.cpp</a>
</em></p>
</blockquote><p> We create two extra fields, price and cost, and append them to the
cursor's set of fields. Both are registered as calculated fields with
calls to setCalculated().
<p> <pre> <a name="x2263"></a>QVariant InvoiceItemCursor::<a href="qsqlcursor.html#calculateField">calculateField</a>( const <a href="qstring.html">QString</a> & name )
{
if ( name == "productname" ) {
<a href="qsqlquery.html">QSqlQuery</a> query( "SELECT name FROM prices WHERE id=" +
<a href="qsqlrecord.html#field">field</a>( "pricesid" )->value().toString() );
<a name="x2265"></a> if ( query.<a href="qsqlquery.html#next">next</a>() )
<a name="x2266"></a> return query.<a href="qsqlquery.html#value">value</a>( 0 );
}
else if ( name == "price" ) {
<a href="qsqlquery.html">QSqlQuery</a> query( "SELECT price FROM prices WHERE id=" +
<a href="qsqlrecord.html#field">field</a>( "pricesid" )->value().toString() );
if ( query.<a href="qsqlquery.html#next">next</a>() )
return query.<a href="qsqlquery.html#value">value</a>( 0 );
}
else if ( name == "cost" ) {
<a href="qsqlquery.html">QSqlQuery</a> query( "SELECT price FROM prices WHERE id=" +
<a href="qsqlrecord.html#field">field</a>( "pricesid" )->value().toString() );
if ( query.<a href="qsqlquery.html#next">next</a>() )
return QVariant( query.<a href="qsqlquery.html#value">value</a>( 0 ).toDouble() *
<a href="qsqlquery.html#value">value</a>( "quantity").toDouble() );
}
return QVariant( QString::null );
}
</pre><blockquote><p align="center"><em> From <a href="sql-overview-subclass4-main-cpp.html">sql/overview/subclass4/main.cpp</a>
</em></p>
</blockquote><p> The calculateField() function has expanded slightly because now we
must calculate the value of three different fields. The productname
and price fields are produced by looking up the corresponding values
in the prices table keyed by pricesid. The cost field is calculated
simply by multiplying the price by the quantity. Note that we cast the
cost to a <a href="qvariant.html">QVariant</a> since that is the type that calculateField() must
return.
<p> We've written three separate queries rather than one to make the
example more like a real application where it is more likely that each
calculated field would be a lookup against a different table or view.
<p> The last feature that we need to add is defaulting values when the
user attempts to insert a new record.
<p>
<pre> <a href="qsqlrecord.html">QSqlRecord</a> *primeInsert();
</pre><blockquote><p align="center"><em> From <a href="sql-overview-subclass5-main-h.html">sql/overview/subclass5/main.h</a>
</em></p>
</blockquote><p> We declare our own primeInsert() function since we will need to
reimplement this.
<p> The constructor and the calculateField() function remain unchanged.
<p>
<pre> <a name="x2267"></a>QSqlRecord *InvoiceItemCursor::<a href="qsqlcursor.html#primeInsert">primeInsert</a>()
{
<a href="qsqlrecord.html">QSqlRecord</a> *buffer = <a href="qsqlcursor.html#editBuffer">editBuffer</a>();
<a href="qsqlquery.html">QSqlQuery</a> query( "SELECT NEXTVAL( 'invoiceitem_seq' )" );
<a name="x2268"></a> if ( query.<a href="qsqlquery.html#next">next</a>() )
<a name="x2270"></a><a name="x2269"></a> buffer-><a href="qsqlrecord.html#setValue">setValue</a>( "id", query.<a href="qsqlquery.html#value">value</a>( 0 ) );
buffer-><a href="qsqlrecord.html#setValue">setValue</a>( "paiddate", QDate::currentDate() );
buffer-><a href="qsqlrecord.html#setValue">setValue</a>( "quantity", 1 );
return buffer;
}
</pre><blockquote><p align="center"><em> From <a href="sql-overview-subclass5-main-cpp.html">sql/overview/subclass5/main.cpp</a>
</em></p>
</blockquote><p> We get a pointer to the internal edit buffer that the cursor uses for
inserts and updates. The id field is a unique integer that we generate
using the invoiceitem_seq. We default the value of the paiddate field
to today's date and default the quantity to 1. Finally we return a
pointer to the buffer. The rest of the code is unchanged from the
previous version.
<p> <a name="Example_Tables"></a>
<h2> The Example Tables
</h2>
<a name="10"></a><p> The example tables used can be recreated with the following standard
SQL. You may need to modify the SQL to match that used by your
particular database.
<p> <pre>
create table people (id integer primary key, name char(40))
create table staff (id integer primary key, forename char(40),
surname char(40), salary float, statusid integer)
create table status (id integer primary key, name char(30))
create table creditors (id integer primary key, forename char(40),
surname char(40), city char(30))
create table prices (id integer primary key, name char(40), price float)
create table invoiceitem (id integer primary key,
pricesid integer, quantity integer,
paiddate date)
</pre>
<p> A sequence was used in the calculateField() example above. Note that
sequences are not supported in all databases.
<p> <pre>
create sequence invoiceitem_seq
</pre>
<p>
<!-- eof -->
<p><address><hr><div align=center>
<table width=100% cellspacing=0 border=0><tr>
<td>Copyright © 2007
<a href="troll.html">Trolltech</a><td align=center><a href="trademarks.html">Trademarks</a>
<td align=right><div align=right>Qt 3.3.8</div>
</table></div></address></body>
</html>
|