ASTA @ Warp Speed
A Quick Start Tutorial
This document assumes that you have already downloaded and installed ASTA's evaluation components -- the registered components work as well! If you have not downloaded the evaluation components, then get them now!
Requirements
Delphi 3.02
Delphi 4.02 or higher
Delphi 5
BCB 3,4
BDE
TCP/IP
Client/Server Editions are not required.
Starting the ASTA Server
Upon installation of ASTA, you will find in the BDEServer subdirectory an ASTA server program designed for use with the BDE named AstaBDEServer.exe. There may be included with your installation other ASTA servers designed to work with other data access mechanisms, any of which will suit the purposes of this presentation. Upon starting an ASTA server program you will be presented with a dialog box, the ASTA Alias Manager. In order to start the server, leave the User Name and Password fields empty and simply click the OK button.
Client Side Data Access
The client side of ASTA is designed to take advantage of the developers existing skill set and to transparently make the switch from a two-tier model to three-tier. The best demonstration of the power and ease of use of ASTA is made apparent in the following step-by-step tutorial.
The first thing to do is to create a new application in the Delphi/C Builder IDE. When you are presented with the main form of the project select the ASTA tab on the component palette and drop onto your main form an AstaClientSocket, and an AstaClientDataSet.
The next task we need to perform has two simple steps that you will already be familiar with, drop a DataSource component from the Data Access tab on the form, and follow that with a DBGrid control from the Data Controls tab of the IDE. As usual, select the DataSource property of the DBGrid and set it to reference the DataSource component just dropped on the form. Then select the DataSet property of the DataSource and set it to reference the AstaClientDataSet component.
Finally, select the TableName property of the AstaClientDataSet, and choose the Employee table from the drop-down list. Then set AstaClientDataSet Active property to True.
Eureka! The grid has populated with data, at design time no less!
To complete our task, save, compile and run the application. When the application starts and you will be presented with the standard ASTA login prompt. This allows you to specify the server the server that your application will communicate with. For the purposes of this demo, simply select the OK button and our work is done. You have just created a fully functional database application that requires absolutely zero coding in five minutes flat.
ASTA Database Application Development: No Code Baby Steps
NoCodeThinClient - Simple Fetch. Write SQL and Go.
The No Code Thin Client tutorial perhaps bests demonstrates the easy of use and power of ASTA. Upon opening the main form of the project, you will be presented with a simple form. A DataSource, an AstaClientDataSet and AstaClientSocket, a DbGrid for display, and a memo with instructions.
As always, start one of the provided ASTA servers, and select the DbDemos Alias. If the server is running not running locally, then the address of the AstaClientSocket must be adjusted to reflect the IP address of the remote machine where the server is running. Otherwise, the address should be 127.0.0.1, the loop back address for local connections.
ASTA servers, by default listen for and accept TCP/IP connections on Port 9000. Check to make sure that the Port property of the AstaClientSocket reflects the current port setting of the server.
Gotcha number one: The dataset is at this point, Read Only. You cannot edit the data, yet.
Stop the application and return to the Delphi IDE. Set the DbGrid’s ReadOnly property to False. Click on the EditMode property of the AstaClientDataSet. You will be presented with a dialog that will allow you to specify how updates are made to the dataset.
In the Update Method group box, select the Update After Post option. Select the Employee table, from the Update Table page, and finally select the EmpNo field from the Prime Key Fields check list box. You will notice that the status message in red reads, “DataSet is Editable”.
Once again, run the application and you will now be able to edit the data displayed in the grid.
DataAwareControls - Setting Edit Mode. Edits After Post.
The ASTA components fully support the all of native Delphi data-aware controls. The DataAwareControls tutorial demonstrates the use of the DBGrid, DBEdit and the DBNavigator.
Open the tutorial and select the AstaClientDataSet on the main form. Modify either the TableName property to be Customer or insert the following SQL statement into the SQL property, “Select * From Customer”. Once again, select the EditMode property of the AstaClientDataSet to display the property editor. Choose the options, Update After Post, the Prime Key Field of CustNo, and the customer table from the Update Table list. Once done, the red status table should display the message “DataSet is Editable”.
Compile and run the application. You will see that the DBEdit fields track the appropriate row in the dataset as you scroll through the grid. You may even edit and post changed to the data via the DbNavaigator, provided the grid ReadOnly property is set to False. Notice that all updates and inserts to the dataset are instantly reflected, without the need to manually refresh the dataset.
DataAwareMemo - SQLOptions to Fetch and Update Memos and Blobs.
The DataAwareMemo tutorial demonstrates the ease with which memos and blobs can be retrieved, updated, or perhaps most importantly, not fetched. Across a wide area network or the Internet, the transmission of large binary fields may exact a heavy performance penalty. To address this, the AstaClientDataSet provide the SQLOptions property which can be used to control the retrieval of blob and memo fields with the Boolean soFetchBlobs and soFetchMemos sub-properties. Setting these properties to false, blob and memo will not automatically be transmitted to the client from the server when the dataset is requested. Setting them to True will cause all blobs and memos to be retrieved and editable just like any other field type.
FetchMemo/FetchBlob - Fetching Memo/Blob on Demand
In a Wide Area Network environment, BLOB fields must be handled with care -- sending many BLOBs down a slow connection will consume your bandwidth and severely impact the performance of your application. These tutorials show you how to handle a memo as a "detail" field. To see a demonstration with Memo and Blob fields, open the FetchBlobMemo tutorial.
Adjusting the AstaClientDataSet SQLOptions subproperties soFetchMemos and soFetchBlobs to False will prevent the automatic retrieval of memos and blobs. The point being that it may suit your application better to only retrieve these typically larger field types when needed.
In order to manually retrieve a memo field, the AstaClientSocket provides the method FetchBlobString(TableName, FieldName, WhereString: string): string. In the click event of the FetchBlob button on the main form, you will see the line:
Memo1.Lines.Text := AstaClientDataSet1.FetchBlobString(
'BioLife','Notes','Where Biolife."Species No"='+
AstaClientDataSet1.FieldByName('Species No').AsString);
The first parameter being the name of the table to retrieve the memo from, the second, the field name, and the third, the conditional statement which determines which records will be sent. Notice that the third parameter follows the SQL standard for WHERE clauses, ‘Where BioLife.”Species No” = X’, and in this case, the field ‘Species No’ is in quotation marks due to the fact that it contains a space.
The retreival of a blob field is identical, simply use the FetchBlobString method. The difference lies in the interpretation of the string data. In this case, the data in the string will be binary, and will most likely need to be converted to a more commonly accessible medium, such as a TStream object.
procedure TForm1.BtnFetchImageClick(Sender: TObject);
var
St: TStringStream;
begin
// Create a TStringStream to hold the binary data in the returned string
St := TStringStream.Create(
AstaClientDataSet1.FetchBlobString(
'BioLife','Graphic','Where Biolife."Species No"='+
AstaClientDataSet1.FieldByName('Species No').AsString
)
);
try
Image1.Picture.Bitmap.LoadFromStream(St);
finally
St.Free;
end;
end;
Transferring the data from the AnsiString returned by the FetchBlobString method to the TStringStream allows us then to use the stream with any LoadFromStream method.
BDEMasterDetail - Demonstrating Master Detail Relationships
A common question with ASTA components is, “How are Master/Detail relationships implemented via Client Side SQL?” To demonstrate the answer and no-code solution, open the BDENoCodeMD tutorial project. As always, start the AstaBdeServer listening on Port 9000.
To begin, set the AstaClientSocket1’s IP Address and Port to the server’s Address and Port. If you are running the server locally, you may of course set the Address property to “127.0.0.1” and the Port to the default of 9000. All of the AstaDataSet components on the form will of course already reference the AstaClientSocket1 without modification.
You will see two dataset and accompanying datasource components on the main form of the project. The datasets are dtsMasterCustomer, representing the Customer table and the Master side of the relationship, and the dtsDetailCustomerOrder representing the Order table and the detail or the “many” side. Select the dtsMasterCustomer dataset and set the TableName property to the Customer table. Set the dataset property of the dsMasterCustomer datasource to point to the dtsMasterCustomer dataset and set the Active property of the data to True. Notice that the SQL property is automatically generated with the statement, “Select * From CUSTOMER”.
Set the datasource dsDetailCustomerOrder’s dataset property to point to the dtsDetailCustomerOrder dataset and set the SQL property of the dtsDetailCustomerOrder dataset to: “Select * From ORDERS Where CustNo = :CustomerOrder”.
Note that the Params property of the dtsDetailCustomerOrder dataset now displays “1 Param”. To specify the parameter type, click on the Params property to invoke the property editor. Set the CustomerNumber Param to ptInpput and the datatype to ftInteger.
Now set the dataset dtsDetailCustomerOrder Mastersource property to the dsDetailCustomerOrder datasource. Using the dtsDetailCustomerOrder MasterFields property editor, link the CustomerNumber param to the Master Custno, by selecting CustomerNumber from the Detail Fields, CustNo from the Master Fields list, and clicking the Add button to add the join condition to the Joined Fields list.
7. Set the dataset dtsDetailCustomerOrder’s Active property to True.
8. Run the project and test the result. By scrolling through the dbgMasterCustomer grid, you will see the dbgDetailCustomerOrder grid automatically update to reflect the master / detail relationship.
Run Time Edit Changes – Switching and Editing DataSets at Runtime
The DataAwareRunTime tutorial demonstrates the use of editing AstaClientDataSet via data-aware controls and how to programmatically switch datasets at runtime. To experiment with the tutorial, start the BdeAstaServer and open the DataAwareRunTime project in the Delphi IDE.
You will see on the form the usual AstaClientSocket and AstaClientDataSet components, tied to a TDataSource and TDBGrid in turn. To specify the dataset and update status at design-time with no code, modify either the TableName property to be Customer or insert the following SQL statement into the SQL property, “Select * From Customer”. Next, select the EditMode property of the AstaClientDataSet to display the property editor. Choose the options, Update After Post, the Prime Key Field of CustNo, and the customer table from the Update Table list. Once done, the red status table should display the message “DataSet is Editable”.
Compile and run the application. You will notice on the second tab of the TPageFrame entitled “Run Time Edit Setup”, two buttons which can be used to programatically switch the dataset properties at run-time between two different tables. The most minimalistic code required for such a task would be:
with AstaClientDataSet1 do begin Close; TableName := 'SomeTable'; Open; end;
To make the dataset editable in code the following method must be used to duplicate the functionality of the EditMode property editor:
with AstaClientDataSet1 do
begin
Close;
TableName := 'SomeTable';
SetEditMode(['CustNo'], 'Customer', umAfterPost);
Open;
end;
The SetEditMode method has the following declaration:
SetEditMode(const Primes: array of string; TheUpdateTable: string; TheUpdateTable: TAstaUpdateMethod)
A gotcha: The TAstaUpdateMethod is a set containing the following values (umManual, umCached, umAfterPost) and is declared in the AstaDBTools unit, which must be manually added to the form’s uses clause.
To achieve the same effect using the SQL property of the AstaClientDataSet, simply clear the SQL as you would for a standard Delphi TQuery and set the new statement:
with AstaClientDataSet1 do
begin
Close;
SQL.Clear;
SQL.Add('Select * from Customer');
SetEditMode(['CustNo'], 'Customer', umAfterPost);
Open;
end;
A word of warning: If the two methods are combined, it will be necessary to clear both the SQL property, but also the TableName property.
with AstaClientDataSet1 do
begin
Close;
TableName := '';
SQL.Clear;
SQL.Add('Select * from Customer');
SetEditMode(['CustNo'], 'Customer', umAfterPost);
Open;
end;
A word to the wise: As a final touch, you can improve performance of the dataset switching by using the EnabledControls and DisableControls methods inherited from TDataSet:
with AstaClientDataSet1 do
begin
EnableControls;
Close;
TableName := '';
SQL.Clear;
SQL.Add('Select * from Customer');
SetEditMode(['CustNo'], 'Customer', umAfterPost);
Open;
DisableControls;
end;
Transactions – A Demonstration of Cached Updates
The Transactions tutorial demonstrate the use of locally cached database operations and how to post them to the server in a batch move.
As usual, this application is designed to work with the DBDemos alias, however, transactions are not supported in Paradox. In order to address this, set the TDataBase TransIsolation property to tiDirtyReads. This will enable a transaction management behavior. Of course most other normal client server databases of course offer real transaction support. Once the TDatabase is properly modified, start the AstaBDEServer and open the transactions project in the Delphi IDE.
 |
Select the AstaClientDataSet1 on the main form. Modify either the TableName property to be BioLife or insert the following SQL statement into the SQL property, “Select * From BIOLIFE”. Once again, select the EditMode property of the AstaClientDataSet to display the property editor. This time choose the option, Cached Updates. The Prime Key Field of Species No, and the BioLife table from the Update Table list. Once done, the red status table should display the message “DataSet is Editable”.
All To Common Gotcha: Be sure that the SQLOptions property of the AstaClientSocket1 is set to include ssUseQuotesInFieldNames to compensate for the “Species No” field.
Compile and run the application. You will quickly notice that you may edit as many records as you wish, including the memo fields, without updating the remote database. When you wish to post all of the changes, click the button labeled Apply Cached Updates.
Client Side Sorting
Sorting a dataset is an operation that is in most implementations, done at the server side. Often this requires re-fetching the dataset, which can be a costly operation. The AstaClientDataSet provides an easy and powerful way to sort datasets on the client, with no need to make a call to the server. To see this illustrated, open the SortDataSet tutorial in the Delphi IDE. Unlike most of the other tutorials, this demo can be used with any AstaServer running against any database. But for demonstration purposes, we will reference the ASTABDEServer here.
The traditional means of sorting a dataset is via the ORDER BY clause of a SQL statement which is sent to the database server. Of course ASTA supports this feature, but suppose that you want to give the end user the option of scrolling through records in another order. Instead of dynamically generating a new SQL statement with the appropriate ORDER BY clause, you can simply call the SortDataSetByFieldName method, whose declaration look like this:
TAstaClientData.SortDataSetByFieldName(FieldName: string; Descending: Boolean);
For example, to specify an ascending sort on the Company Field of the Customer Table, call:
AstaClientData1.SortDataSetByFieldName(‘Company’, False);
The demo application is coded so that when a field title in the TDBGrid is selected, the dataset will be sorted according to the sort order selected in the Sort Order radio group.
procedure TForm1.GridTitleClick(Column: TColumn);
var
FieldToSortBy: string;
SortStyle: Boolean;
begin
{ get the name of the field we wish to sort by }
FieldToSortBy := Column.Field.FieldName;
{ false if ascending; true if descending }
SortStyle := Boolean(RdgSortOrder.ItemIndex);
{ TAstaClientData.SortDataSetByFieldName(FieldName: string; Descending: Boolean); }
AstaClientDataSet1.SortDataSetByFieldName(FieldToSortBy, SortStyle);
end;
Gotcha: The SortDataSetByFieldName method cannot sort blob fields.
Future: Multiple field sorting and blob support is scheduled for
a future release.
In Code Edits - Editing Data In Code
Every so often the question is asked: “Can I modify AstaClientDataSet in code just like I modify TTable?” The answer is always, if you can use TTable, you can use ASTA. The code is actually identical. In order to see a demonstration of modifying an AstaClientDataSet in code, open the InCodeEdit tutorial and start the AstaBDEServer.
The AstaClientDataSet on the form of the project is set to retrieve data from the Customer table of the DBDemos alias. Notice that the form has on it a field labeled Addr1 Field. This field, instead of being o TDBEdit, is a TEdit. Which means that in order to display a value from the AstaClientDataSet, it must be put there programatically. This is achieved by simplying using the method inherited from TDataSet, FieldByName(‘SomeFieldName’).AsString.
EdtAddr1.Text := AstaClientDataSet1.FieldByName('Addr1').AsString;
In order to actually modify the data in the AstaClientDataSet, we simply call the standard TDataSet methods, Edit and Post.
with AstaClientDataSet1 do
begin
Edit;
FieldbyName('Addr1').AsString := EdtAddr1.Text;
Post;
end;
Clicking the button labeled edit and post will call the aforementioned method and update the table on the remote server.
Hereto now, the tutorials have all utilized the ASTA server running in a stateless mode. For this tutorial, where we will demonstrate the use of server side cursors, persistence and Just-In-Time fetching we will need to start the ASTA server in Persistent Mode. To do so launch the server with the command line parameter: PersistentSessions. Ex: AstaBdeServer PersistentSessions.
The ASTA server should display in its form caption the addendum, “Persistent Threaded Sessions”. This will confirm that the server was started in the appropriate mode.
Once the server is up and running in Persistent Mode, open the PacketTutorial project in the IDE. You will notice the standard AstaClientSocket and AstaClientDataSet present on the form as usual. The difference on the client side from the previous examples is the RowsToReturn property of the AstaClientDataSet. When the dataset is opened on the server, only the RowsToReturn value will be streamed back to the client. As you scroll through the dataset, more rows will be returned, as they are required.
Run the demo program and notice that the grid at the top of the form is displaying only two rows. Now, begin to scroll down, and notice that as the cursor advances through the dataset, more rows are automatically fetched. To handle fetching manually, the AstaClientDataSet provides the property AutoFetchPackets, setting this to false will require you to call for the packets in code. Ex:
Procedure TForm1.GetSomePackets;
var
Packets: Integer;
begin
// GetNextPacket returns the number of packets fetched
Packets := AstaClientDatASet1.GetNextPacket;
ShowMessage( ‘Retrieved : ‘ + IntToStr( Packets ) + ‘ Packets.’
end;
Typically, the cursor on the server will remain open until the AstaClientDataSet is closed, however, you may close the cursor explicitly at any time with the method TAstaClientDataSet.CloseQueryOnServer.
CloneCursor - Clone Cursor Feature
The TAstaDataSet allows itself to be "cloned" by other TAstaDataSets. Cloned datasets all share exactly the same data all of the time. Changes to one dataset are reflected in all clones. A common question is Why clone a cursor? The point of cloning cursors is that multiple cursor can refer to exactly the same data, yet maintain separate cursor positions.
Upon opening the CloseCursor tutorial you will see a form with two TAstaDataSets. The first dataset will have its SQL defined and executed, and the second dataset will clone the first. By scrolling through the separate grids, you will see that the two cursors can maintain distinct positions, yet changes to the data are reflected in both datasets.
If at any time you wish to remove the replication of data between the “clone links”, you may simply use the RemoveCloneLinks method of the TAstaDataSet. Calling the RemoveCloneLinks method of any cloned dataset will “unlink” all associated clones.
ExecParams - Old time SQL with a New Twist
Using Parameterized SQL statements is a common practice when writing Client/Server applications. However, the switch to an N-Tier environment is not always so obvious. ASTA provides a mechanism by which almost anything can be sent to an ASTAServer as a parameter in a SQL statement.
The TAstaDataSet introduces the powerful ExecSqlWithParams method. It allows developers to combine the ease of use of a table with the power of a SQL statement to update data sets at the server. For example, suppose we have a SQL statement which retrieves a selection of records, “Select * From EVENTS”. Then we want to programmatically update the data set without having to worry about data types. We can use the TAstaParamList’s FastAdd method, which allow us to access data of any type including blobs and memos:
with MyDataSet do
begin
Params.Clear;
Params.FastAdd( SomeString );
Params.FastAdd( SomeDateTime );
ExecSqlWithParams(‘Update EVENTS Set Event_Name = :pEvent_Name, Event_Date = :pEventDate’);
end;
In the ExecParamSql Tutorial you will see the standard TAstaDataSet with a simple SQL statement, “Select * From EVENTS”, which is used to populate the controls on the form. Clicking on the button labeled “Update the current row with values including a Memo” will update the Events table with the following statement:
procedure TForm1.BtnUpdateTableClick(Sender: TObject);
begin
with EventDataSet do
begin
Params.Clear;
// Fast add adds a Param and sets the Datatype according to the input
// first three come in as text,integer,datetime
Params.FastAdd(EdtEventName.Text);
Params.FastAdd(2);
Params.FastAdd(Now);
// memo needs to be create explicitly
Params.CreateParam(ftMemo, 'Description', ptInput);
ParamByName('Description').AsMemo := Memo1.Text;
Params.FastAdd(Time);
Params.FastAdd(FieldByName('EventNo').AsInteger);
//this shows how to send a paramerized update statement
ExecSQLWithParams(
' Update EVENTS Set ' +
' Event_Name = :A, ' +
' VenueNo = :B, ' +
' Event_date = :C, ' +
' Event_Description = :D, ' +
' Event_Time = :E ' +
' Where ' +
' EventNo = :F');
// open and close the dataset to refresh the displayed data
Close;
Open;
end;
end;
The one notable exception is that memo fields cannot use the FastAdd method. Instead, a parameter must be explicitly created to handle it with the CreateParam method where you specify a data type, a name and whether the parameter is for input or output.
ConflictManager - ASTA DataSet Deltas
In a client server environment there is often a need to resolve conflicts when two or more clients attempt to update the same data. ASTA provides a mechanism known as Conflict Management to allow the developer to handle such conflicts to the remote database.
