FireDAC Tip: Write Database Independent SQL Commands With Escape Functions

Escape sequences allow for writing DBMS-independent SQL commands. The sample predefines some escape function sequences. To execute a predefined escape function, right-click the Function edit box and choose a predefined escape function from the popup menu. Moreover, the sample allows you to write your own function. To this end, write text into the Function edit box and press Enter. Then, the sample uses the Prepare, Define, Open and Fetch methods of the IFDPhysCommand interface to process the escape function command text. Note: Go to the following page to see the syntax of the escape function sequences.

Location

You can find the EscapeFunctions sample project at:

• Start | Programs | Embarcadero RAD Studio Sydney | Samples and then navigate to:
  • Object PascalDatabaseFireDACSamplesPhys LayerIFDPhysCommandEscapeFunctions
• Subversion Repository:
  • You can find Delphi code samples in GitHub Repositories. Search by name into the samples repositories according to your RAD Studio version.

How to Use the Sample

1. Navigate to the location given above and open EscapeFunctionsdproj.
2. Press F9 or choose Run > Run.
3. Interact with the sample:
   1. Right-click the Function edit box and select a predefined escape function sequence.
   2. Write your own escape function and press Enter.

Files

File in Delphi	Contains
EscapeFunctions.dproj EscapeFunctions.dpr	The project itself.
fEscapeFunctions.pas fEscapeFunctions.fmx	The main form.

Implementation

To preset the escape function sequences, the sample configures the following components at design time using the Object Inspector:

• A TPopupMenu object named pmnFunc.The sample sets the Items property of this object with a menu of several predefined escape function sequences.
• A TLabeledEdit object labeled as Function.The sample sets the PopupMenu property of the component to pmnFunc at design time. This setup allows you to choose a predefined escape function sequence in run time. To this end, right-click inside the edit box to see the popup menu. Then, choose a predefined escape function from the menu.

Moreover, when you run the application, you can interact with the sample using the following objects:

• A TComboBox object labeled as Use Connection Definition.Click the Use Connection Definition combo box and select an option in order to define a connection to a database. The menu shows all the persistent connections defined on the file C:UsersPublicDocumentsEmbarcaderoStudioFireDACFDConnectionDefs.ini. When the connection is defined, the sample uses the CreateCommand method of IFDPhysConnection to create a command interface. Then, the sample enables the edit boxes labeled as: Function, SQL string and Result.

• A TLabeledEdit object labeled as Function.The sample uses this object to let you choose/write an escape function sequence:
  • To choose a predefined escape function, right-click inside the edit box to see the popup menu. Then, choose a predefined escape function.
  • To write an escape function, type your own command text inside the edit box and press Enter.
  When you chose/write the escape function, the sample uses the Prepare, Define, Open and Fetch methods of the IFDPhysCommand interface to process the scape function command text.
• A TLabeledEdit object labeled as SQL string.The sample uses this edit box to show the SELECT command text that is executed. The Command text is the following: 'select distinct {fn %s} from {id test_free}', where %s corresponds to the escape function selected/written in the Function edit box.
• A TLabeledEdit object labeled as Result.The sample uses this edit box to show the result of the escape function execution.

In the link below you will redirected to the original post of Embarcadero:

http://docwiki.embarcadero.com/CodeExamples/Sydney/en/FireDAC.IFDPhysCommand.EscapeFunctions_Sample

Check out the full source code for the IFDPhysConnection Escape Function sample in Embarcadero’s GitHub.