Command and Data Table Parameters

From RemObjects Software

This is a Data Abstract Architecture entry
Feel free to add your notes to this topic below.

Data Abstract provides the concept of parameters to pass data to data tables and commands. Parameters can be used to filter the records retrieved from a data table for certain conditions (for example to only obtain details for a specific master record), to pass new data to insert/update/delete statements, or to simply provide parameters that a certain operation or stored procedure requires.

Regardless of the underlying database system and platform (.NET or Delphi), Data Abstract defines a standardized syntax for representing parameters in SQL statements, and exposes them in a consistent way using the DataParameter/TDAParam classes (.NET/Delphi).

Parameters in SQL statements

To define parameters in your SQL statements, simply precede the identifier with a colon, as such:

SELECT * FROM Orders WHERE Customer = :CustomerID

This will automatically create a parameter "CustomerID" for the data table (or command) that can be filled with a value before executing the query. For each parameter, a data type can be defined in Schema Modeler, from the range of all the data types also supported for fields by Data Abstract. This data type will determine how value assigned to the parameter will be embedded into the query.

Note that some databases or data access layers do not allow the use of the same parameter twice in a query. Instead of writing a SQL statement such as:

SELECT * FROM Orders WHERE Customer = :CustomerID OR ShipToCustomer = :CustomerID

you will need to rewrite the query using a local variable, so that :CustomerID only occurs once inside the query, for example:

DIM @c = :CustomerID
SELECT * FROM Orders WHERE Customer = @c OR ShipToCustomer = @c

Filling Parameters at Runtime

As mentioned before, parameters are represented using a collection of DataParameter/TDAParam classes. On the server side, a IDbCommand/IDASQLCommand will provide a collection of parameters that can be filled before executing the command or opening the data table.

On the client side, parameters can be passed to RemoteDataAdapter.Fill (.NET) or defined on the TDADataTable (Delphi). Also, the RemoteCommand (.NET) component allows specifying parameters to be passed to the server for executing the command.

See Also

• DataParameter, RemoteDataAdapter, RemoteCommand classes (.NET)
• TDAParam and TDADataTable classes (Delphi)
• Lists
  • Namespaces
  • Classes: .NET — Delphi
  • Interfaces: .NET — Delphi
  • Enums: .NET — Delphi
  • Types: Data — File

Product: RemObjects Data Abstract
Current version: Data Abstract 'Vinci' (5.0)

Lists — Glossary — Features — How To — Drivers — Components — Tools — Samples — Articles — Architecture — Issues