Home > Articles > Programming > General Programming/Other Languages

Using the TSQLDataset Component in Delphi 6

  • Print
  • + Share This
Learn how to represent data in a database table, a selection query, or the results of a stored procedure by using TSQLDataset.
From the author of

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.

  • + Share This
  • 🔖 Save To Your Account