+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 data source. 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 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 in your select
+statement (joins), 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 through the \helpref{wxDbTable::Update}{wxdbtableupdate}
+function. 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 / commitdiff
