+This is the most powerful form of the query functions available. This member
+function allows a programmer to write their own custom SQL SELECT statement
+for requesting data from the datasource. This gives the programmer access
+to the full power of SQL for performing operations such as scalar functions,
+aggregate functions, table joins, and sub-queries, as well as datasource
+specific function calls.
+
+The requirements of the SELECT statement are the following:
+
+\begin{verbatim}
+ 1. Must return the correct number of columns. In the derived
+ wxDbTable constructor, it is specified how many columns are in
+ the wxDbTable object. The SELECT statement must return exactly
+ that many columns.
+
+ 2. The columns must be returned in the same sequence as specified
+ when defining the bounds columns using wxDbTable::SetColDefs(),
+ and the columns returned must be of the proper data type. For
+ example, if column 3 is defined in the wxDbTable bound column
+ definitions to be a float, the SELECT statement must return a
+ float for column 3 (e.g. PRICE * 1.10 to increase the price by
+ 10%).
+
+ 3. The ROWID can be included in your SELECT statement as the {\bf last}
+ column selected, if the datasource supports it. Use
+ wxDbTable::CanUpdByROWID() to determine if the ROWID can be
+ selected from the datasource. If it can, much better
+ performance can be achieved on updates and deletes by including
+ the ROWID in the SELECT statement.
+\end{verbatim}
+
+Even though data can be selected from multiple tables (joins) in your select
+statement, only the base table associated with this wxDbTable object
+is automatically updated through the ODBC class library. Data from multiple
+tables can be selected for display purposes however. Include columns in
+the wxDbTable object and mark them as non-updateable (See
+\helpref{wxDbColDef}{wxdbcoldef} for details). This way columns can be
+selected and displayed from other tables, but only the base table will be
+updated automatically when performed through the
+\helpref{wxDbTable::Update}{wxdbtableupdate} function after using this type of
+query. To update tables other than the base table, use the
+\helpref{wxDbTable::Update}{wxdbtableupdate} function passing a SQL statement.
+
+After this function has been called, the cursor is positioned before the
+first record in the record set. To retrieve the first record, call
+either \helpref{wxDbTable::GetFirst}{wxdbtablegetfirst} or
+\helpref{wxDbTable::GetNext}{wxdbtablegetnext}.
+
+\wxheading{Example}
+
+\begin{verbatim}
+ // Incomplete code samples
+ strcpy(sqlStmt, "SELECT * FROM PARTS WHERE STORAGE_DEVICE = 'SD98' \
+ AND CONTAINER = 12");
+ // Query the records using the SQL SELECT statement above
+ parts->QueryBySqlStmt(sqlStmt);
+ // Display all records queried
+ while(parts->GetNext())
+ dispPart(&parts);
+
+ Example SQL statements
+ ----------------------
+
+ // Table Join returning 3 columns
+ SELECT part_no, part_desc, sd_name
+ from parts, storage_devices
+ where parts.storage_device_id =
+ storage_devices.storage_device_id
+
+ // Aggregate function returning total number of
+ // parts in container 99
+ SELECT count(*) from PARTS where container = 99
+
+ // Order by clause; ROWID, scalar function
+ SELECT part_no, substring(part_desc, 1, 10), qty_on_hand + 1, ROWID
+ from parts
+ where warehouse = 10
+ order by part_no desc // descending order
+
+ // Subquery
+ SELECT * from parts
+ where container in (select container
+ from storage_devices
+ where device_id = 12)
+\end{verbatim}
+
+
projects / wxWidgets.git / blobdiff