This section describes some of the more common tasks that you can perform with Delphi. These include connecting to a data dictionary, opening a table, executing a query, using a parameterized query, and executing a stored procedure.
You connect to a data dictionary or a directory in which free tables are located using an AdsConnection component. (AdsConnection is very similar to the VCL’s Database component, located on the BDE page of the Component Palette.) At a minimum, you must set a property of this component to describe where the data dictionary or free tables are located, after which you set the IsConnected property to True. If you are connecting to a data dictionary that requires a login, and you do not want the user to have to enter their user name, you must also set the Username and Password properties of this component.
There are two ways to indicate the location of your data. The easiest way, if you have an alias defined for your data dictionary or data directory, is to set the Alias name property of the AdsConnection to a defined alias. Aliases are defined in the ADS.INI file, which can be stored in the same directory as your client application (under Windows), or anywhere on your search path.
Alternatively, you can set the ConnectPath of the AdsConnection to a path, preferably a UNC path. The benefit of using an ADS.INI file is that you can change the directory in which ADS looks for your data by changing an entry in the ADS.INI file. By comparison, if your data directory is hard-coded in your application, changing your data directory involves recompiling and redistributing your client application.
Some developers who do not want to use the ADS.INI file assign the ConnectPath property of the AdsConnection object at runtime, assigning to it a directory relative to the application’s directory. You can obtain the application’s directory path by calling the ExtractFilePath function, passing Application.ExeName to it.
If you set the IsConnected property of the AdsConnection to True at design time, and a user name and password are supplied (if necessary), you will have an active connection within the Delphi IDE, and the connection will be reestablished at runtime once the AdsConnection is created and initialized. If IsConnected is set to False at design time, the AdsConnection component will establish a connection at runtime once either the IsConnected property is explicitly set to True or any TDataSets that use the AdsConnection attempt to open or execute.
If you set the Username and Password properties prior to connecting an AdsConnection, set the LoginPrompt property to False. If LoginPrompt is True, the user will be prompted for their user name and password.
If LoginPrompt is set to True, but no login dialog box is displayed, you must add the DBLogDlg unit to your unit’s uses clause.
In the Delphi_TDataSet project, the Alias property of the AdsConnection is set to DemoDD (the alias you defined for the DemoDictionary data dictionary in Chapter 4), the user name is set to adsuser, the password is set to password, and the LoginPrompt is set to False. With these settings in place, you will not be prompted for a user name or password when connecting to the data dictionary.
If you have difficulty connecting, it might be because you have other client applications, such as the Advantage Data Architect, connected using a local connection. Ensure that all clients on the same machine use the same type of connection. You control the type of connections attempted by an AdsConnection using the AdsServerTypes property.
The easiest way to access data is with an AdsTable component. At a minimum, you must set the AdsTable’s DatabaseName property to an AdsConnection (this is often done at design time), its TableName property to the name of a table or view you want to access, and the Active property to True. Calling the AdsTable’s Open method sets the AdsTable’s Active property to True.
This is demonstrated in the following event handler, which is associated with the Show Invoice Table button (shown in Figure 12-1):
procedure TForm1.ShowInvoiceBtnClick(Sender: TObject); begin if AdsTable1.Active then AdsTable1.Close; AdsTable1.TableName := 'INVOICE'; AdsTable1.Open; DataSource1.DataSet := AdsTable1; end;
You access individual records in a TDataSet using its Fields property or its FieldByName method. Fields is a collection property, and you must pass it an index that identifies which field you want to read from or write to, based on its zero-based ordinal position in the table’s structure. When you invoke FieldByName, you pass a string containing the name of the field you want to work with. This approach works for any AdsTable, AdsQuery, or AdsStoredProc component that returns a result set.
The following event handler, associated with the Get Address button (shown in Figure 12-1), demonstrates how to read a field:
procedureC TForm1.GetAddressBtnClick(Sender: TObject); begin if AdsTable1.Active then AdsTable1.Close; AdsTable1.TableName := 'CUSTOMER'; AdsTable1.IndexName := 'Customer ID'; AdsTable1.Open; if AdsTable1.FindKey([CustNoText.Text]) then OldAddressText.Text := AdsTable1.FieldByName('Address').AsString else ShowMessage('Customer ID ' + CustNoText.Text + ' not found'); DataSource1.DataSet := AdsTable1; end;
The following event handler, associated with the Set New Address button (shown in Figure 12-1), demonstrates writing to a field:
procedure TForm1.SetAddressBtnClick(Sender: TObject); begin if AdsTable1.Active then AdsTable1.Close; AdsTable1.TableName := 'CUSTOMER'; AdsTable1.IndexName := 'Customer ID'; AdsTable1.Open; if AdsTable1.FindKey([CustNoText.Text]) then begin AdsTable1.Edit; AdsTable1.FieldByName('Address').AsString := NewAddressText.Text; AdsTable1.Post; end else ShowMessage('Customer ID ' + CustNoText.Text + ' not found'); DataSource1.DataSet := AdsTable1; end;
You define a query by assigning the SQL statement you want to execute to the SQL StringList property of an AdsQuery. If the query returns a result set, you execute it by calling its Open method or by setting its Active property to True. If the query does not return a result set, execute it by calling its ExecSQL method.
By default, you cannot edit the result set returned by an AdsQuery. If your query produces a live cursor, setting the AdsQuery’s RequestLive property to True permits you to edit the data in the result set.
The following code demonstrates the execution of a query entered by the user. It is associated with the Execute SELECT button (shown in Figure 12-1):
procedure TForm1.DoSelectClick(Sender: TObject); begin if AdsQuery1.Active then AdsQuery1.Close; AdsQuery1.SQL.Text := SELECTText.Text; AdsQuery1.Open; DataSource1.DataSet := AdsQuery1; end;
The AdsQuery component supports both named and positional parameters. You must bind data to every parameter of a parameterized query prior to executing it. You can do this either using the Params property, which is a collection property indexed by parameter position, or the ParamByName method, which takes a parameter name as an argument. Both of these approaches return a TParam, which you use to assign a value to the parameter.
The AdsQuery2 component on this project’s main form has the following SQL statement assigned to its SQL property:
SELECT * FROM INVOICE WHERE [Customer ID] = :cust
The parameter, named cust, is bound and the query executed from the event handler shown in the following code segment. This event handler is associated with the Show Invoices button (shown in Figure 12-1):
procedure TForm1.ShowInvoicesBtnClick(Sender: TObject); begin if AdsQuery2.Active then AdsQuery2.Close; if ParamText.Text = '' then begin ShowMessage('Please enter a customer number'); Exit; end; AdsQuery2.Params.AsInteger := StrToInt(ParamText.Text); AdsQuery2.Open; DataSource1.DataSet := AdsQuery2; end;
Stored procedures are invoked using SQL EXECUTE PROCEDURE statements in most of the Advantage data access mechanisms, and you can use an AdsQuery in Delphi to do the same. But Delphi developers have an alternative solution, being able to invoke a stored procedure using the AdsStoredProc component.
There are several advantages to invoking a stored procedure using an AdsStoredProc. The first is that you can use the Params property of the stored procedure to configure and assign the stored procedure’s input parameters. After configuring a stored procedure in the Delphi IDE, you can select the Params property editor of the stored procedure to view the names and data types of each parameter. You can even assign a default value for each of your stored procedure’s input parameters using this property editor.
Another advantage of AdsStoredProc is realized when the stored procedure returns a result set. Specifically, stored procedures that return a result set can be treated exactly the same as AdsTables and AdsQueries that return a result set. They can be assigned to the DataSet property of a DataSource so that the returned data can be associated with data-aware controls. The AdsStoredProc also populates any output parameters, which can be read individually using the Params property or ParamByName method.
At a minimum, you must set the DatabaseName and the StoredProcName properties of the stored procedure. Also, you must assign values to all input parameters, if present, prior to executing the stored procedure. Set the AdsStoredProc component’s ParamBindMode property to pbByName or pbByNumber based on whether you want to bind parameters by name or position, respectively.
If your stored procedure returns a result set, you execute it by calling its Open method, or by setting its Active property to True. If your stored procedure does not return a result set, you execute it by calling its ExecProc method.
If you want to execute a given stored procedure more than once, and with different values passed to its input parameters, you must first close the stored procedure before changing any input parameters. This is not necessary for a stored procedure that does not return a result set.
The use of a stored procedure is demonstrated by the following code associated with the OnClick event handler for the Show 10% of Invoices button (shown in Figure 12-1): The stored procedure referenced in this code is the Delphi AEP (Advantage Extended Procedure) created in Chapter 7. If you did not create this AEP, but created one of the other AEPs described in that chapter, substitute the name of the stored procedure object in your data dictionary in the EXECUTE PROCEDURE string or StoredProcName property, like this:
procedure TForm1.CallStoredProcBtnClick(Sender: TObject); begin if AdsStoredProc1.Active then AdsStoredProc1.Close; if ParamText.Text = '' then begin ShowMessage('Please enter a customer number'); Exit; end; AdsStoredProc1.Params.Value := ParamText.Text; try AdsStoredProc1.Open; except on e: Exception do ShowMessage(e.Message); end; DataSource1.DataSet := AdsStoredProc1; end;
To view the stored procedure’s parameters, select AdsStoredProc1 on the main form, and then using the Object Inspector, select the Params property and click the ellipsis button that appears. Delphi displays the parameters in the Params collection editor:
If you select one of the available parameters in the Params collection editor, you can view and edit the parameters properties using the Object Inspector shown here: