“Red”), then Delphi posts “Magenta” to the database...

If strings for Values are not provided, the label from a selected radio button (from Items) is returned to the database when a record is posted. Users can modify the value of a data field by clicking the appropriate radio button. When the user scrolls off the current row, Delphi posts the value indicated by the radio button string to the database.
114
D a t a b a s e A p p l i c a t i o n D e v e l o p e r ’ s G u i d e
C h a p t e r
5
Using SQL in applications
Chapter 5
SQL (Structured Query Language) is an industry-standard language for database
operations. Delphi enables your application to use SQL syntax directly through the
TQuery component. Delphi applications can use SQL to access data from:
• Paradox or dBASE tables, using local SQL. The allowable syntax is a sub-set of ANSI-
standard SQL and includes basic SELECT, INSERT, UPDATE, and DELETE
statements. For more information on local SQL syntax, see Appendix C, “Using local
SQL.”
• Databases on the Local InterBase Server. Any statement in InterBase SQL is allowed.
For information on syntax and limitations, see the InterBase Language Reference.
• Databases on remote database servers (Delphi Client/Server only). You must have
installed the appropriate SQL Link. Any standard statement in the server’s SQL is
allowed. For information on SQL syntax and limitations, see your server
documentation.
Delphi also supports heterogeneous queries against more than one server or table type
(for example, data from an Oracle table and a Paradox table). For more information, see
“Creating heterogenous queries” on page 124.
Using TQuery
TQuery is a dataset component, and shares many characteristics with TTable, as described in Chapter 3, “Using data access components and tools.” In addition, TQuery enables Delphi applications to issue SQL statements to a database engine (either the
BDE or a server SQL engine).
The SQL statements can be either static or dynamic, that is, they can be set at design time or include parameters that vary at run time.
C h a p t e r 5 , U s i n g S Q L i n a p p l i c a t i o n s
115
When to use TQuery
For simple database operations, TTable is often sufficient and provides portable database access through the BDE. However, TQuery provides additional capabilities that TTable does not. Use TQuery for:
• Multi-table queries (joins).
• Complex queries that require sub-SELECTs.
• Operations that require explicit SQL syntax.
TTable does not use SQL syntax; TQuery uses SQL, which provides powerful relational capabilities but may increase an application’s overall complexity. Also, use of non-standard (server-specific) SQL syntax may decrease an application’s portability among
servers; for more information, see Chapter 6, “Building a client/server application.”
How to use TQuery
To access a database, set the DatabaseName property to a defined BDE alias, a directory path for desktop database files, or a file name for a server database. If the application has a TDatabase component, DatabaseName can also be set to a local alias that it defines. For more information, see “Using the TDatabase component” in Chapter 6, “Building a
client/server application.”
To issue SQL statements with a TQuery component:
• Assign the TQuery component’s SQL property the text of the SQL statement. You can do this:
• At design time, by editing the TQuery’s SQL property in the Object Inspector,
choosing the SQL property, and entering the SQL statements in the String List
Editor dialog box. With Delphi Client/Server, you can also use the Visual Query
Builder to construct SQL syntax.
• At run time, by closing any current query with Close, clearing the SQL property with Clear, and then specifying the SQL text with the Add method.
• Execute the statement with the TQuery component’s Open or ExecSQL method. Use Open for SELECT statements. Use ExecSQL for all other SQL statements. The differences between Open and ExecSQL are discussed in a subsequent section.
• To use a dynamic SQL statement, use the Prepare method, provide parameters and then call Open or ExecSQL. Prepare is not required, but will improve performance for dynamic queries executed multiple times.
116
D a t a b a s e A p p l i c a t i o n D e v e l o p e r ’ s G u i d e
The following diagram illustrates the lifetime of a TQuery component and the methods used to work with it:
Figure 5.1
TQuery methods and flow
Set SQL text
Prepare
Set Parameters
Unprepare
Close
Open
ExecSQL
Fetch Data
Note