Using the TSQLDataset Component in Delphi 6
- Introduction
- Executing a Stored Procedure
- Metadata Representation
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Introduction
TSQLDataset is the unidirectional dataset used for retrieving data from a dbExpress-supported server. This dataset can be used to represent data in a database table, a selection query, or the results of a stored procedure. It can also execute a stored procedure.
TSQLDataset's key properties are CommandType and CommandText. The value selected for CommandType determines how the content of CommandText will be used. Possible values for CommandType are listed in Table 1 and in the Delphi help file.
Table 1 CommandType Values (from Delphi Online Help)
|
CommandType |
Corresponding CommandText |
|
ctQuery |
An SQL statement that the dataset executes. |
|
ctStoredProc |
The name of a stored procedure. |
|
ctTable |
The name of a table on the database server. The SQL dataset automatically generates a SELECT statement to fetch all the records of all the fields in the specified table. |
When the CommandType property contains the ctQuery value, CommandText is an SQL statement. This statement might be a SELECT statement that returns a result set such as the following SQL statement: SELECT * FROM CUSTOMER.
If CommandType is ctTable, CommandText refers to a table name on the database server. The CommandText property will change to a drop-down list. If this is an SQL database, any SQL statements needed to retrieve data are automatically generated.
If CommandType has the value ctStoredProc, CommentText will then contain the name of a stored procedure to execute. This would be executed by calling the TSQLDataSet.ExecSQL() method rather than by setting the Active property to True. Note that ExecSQL() should be used if CommandType is ctQuery and the SQL statement doesn't result in a result set.
Retrieving Table Data
To extract table data using the TSQLDataset, you simply set the TSQLDataSet.CommandType property to ctTable. The CommandText property will change to a drop-down list from which you can select the table name.
Displaying Query Results
To extract data from a query select statement, simply set the TSQLDataSet.CommandType property to ctQuery. In the CommandText property, you can enter a query select statement such as Select * from Country. This is demonstrated in the example on the CD under the QueryData directory.
Displaying Stored Procedure Results
Given a stored procedure that returns a result set such as the InterBase procedure that follows, you can extract the result set using a TSQLDataset component:
CREATE PROCEDURE SELECT_COUNTRIES RETURNS ( RCOUNTRY VARCHAR(15), RCURRENCY VARCHAR(10) ) AS BEGIN FOR SELECT COUNTRY, CURRENCY FROM COUNTRY INTO :rCOUNTRY, :rCURRENCY DO SUSPEND; END
To do this, set the TSQLDataset.CommandType property to ctQuery and add the following to its CommandText property:
Select * from SELECT_COUNTRIES
Notice that we use the stored procedure name as though it were a table.