This document will give Database developers an overview of the way that ASTA can be used to create thin client Database Applications. The ASTA team has attempted to abstract the complete Database Application process so that you can use VCL TDataSet methods and behavior that you are accustomed to no matter what Database you are using on the server. This abstraction process also can allow you to more readily support different databases by allowing your ASTA client application to remain the same but allowing you to run it against different databases or a different ASTA Server suing other 3rd party Delphi Database Components.

Lay of the Land

ASTA is based on the 3 Tier programming model. These tiers consist of the Database, a Middle Tier or ASTA server and a Client application. Simply put, you must start an ASTA server for an ASTA application to function and an ASTA client must be able to connect to that server via tcp/ip. The AstaBDEServer is used in many ASTA tutorials since the BDE ships with Delphi. If you run the AstaBDEServer and connect to the DBDEMOS data that comes with Delphi you would be running an AstaServer as the middle tier connecting to Paradox Tables. An ASTA client application could be deployed if it can connect to this server. Of course those Paradox tables could be an Oracle Server running on Unix, an Interbase 6 Open Source Server running on Linux or an Access mdb that is on the same machine as the AstaServer. Database-Server-Client.

N-Tier

If you have N-Tier experience, have used MIDAS, or are just curious, we are sure you want to jump in and start checking out the innards of an ASTA server and start coding that famous Business Logic that goes there. We encourage you to stop, take a deep breath and run an ASTA Server unchanged. Compile it if you will or download it as an EXE from the ASTA site but run a server and write some ASTA Client Side SQL. This will give you a "feel" for the way that data can flow back and forth between server and client and client to client through the server. You will see how the sockets connect and get a feel for performance.

Client Side SQL

ASTA supports pure N-Tier programming but also supports Client Side SQL. Client Side SQL allows you to write thin client database applications that can run over the Internet without coding an ASTA server. Think of ASTA Client Side SQL as 2 tier fat client programming but without any installation issues and little or no learning curve if you already use SQL with client/server databases. If you use the BDE it will only exist on the server. If you use ADO, MDACS it need only be installed on the server. If you use Oracle then the Oracle client DLL's will only be needed on the server or Middle Tier. ASTA Client's require no DLL's, setup or registration. They just need to be able to connect to an ASTA Server somewhere.

ASTA was designed so that Delphi Database Developers could use their existing skills. This means that if you know normal VCL TDataSet methods like Append, Edit, FieldbyName, Post you will be able to use ASTA pretty easily. With ASTA you only need to write SQL in your select statements. Our theory was that you do a select, and can then append, edit or delete and when you are ready post your changes to the server. ASTA's suitcase model of course supports all of this even if you disconnect from the server and are offline for a week.

So one of the first concepts you will have to understand is that we don't want to go to the server too often. ASTA provides much better performance than normal client server drivers and components because network communication is kept to a bare minimum. Performance is always tied to how often you go to the server and how large a result set you request from the server.

ASTA uses very fast in memory Datasets on the client. In fact the TAstaDataset is so fast and easy to use that they are used internally by ASTA to store disparate data. There is a TAstaDataset that is a pure in memory dataset and then there is a descendent of the TAstaDataset, the TAstaClientDataSet. So when you do a select the results are a copy of the data that exists on the server and are in memory. There are no open tables on the server; there are no locked rows. When you write some Asta Client Side SQL and then set the TAstaClientDataSet.Active property to true, the SQL statement is sent to the server along with other internal information like if you want to bring back memos and blobs. The server executes the query, packs up the data, and streams the results back to the client and those results sit in an in memory dataset as a result set or copy of the data.

When a normal 2 tier application executes a query it does not iterate through te whole result set. When you do a select from an ASTA server there are 4 pieces that must be executed before the data can be used in a TAstaClientDataSet.

1. The Query executes. If you are going to do any performance testing with ASTA and a 2 tier application when you set some SQL and set Active to true of your TDataSet descendent of choice, this is the piece the 2 tier application does.

2. After the SQL executes ASTA servers must iterate through all the rows and pack up the data. In client server databases just getting the number of rows can be an expensive proposition. For instance with MS SQL Server and the BDE or ODBCExpress if you call recordcount a second forward only cursor is opened and a select (*) from Mytable sql statement is executed.

3. After the dataset is packed up it must be transported via tcp/ip. Tcp/ip transports buffers in 4K (4096 bytes) pieces. So if you want to go to a server any size select smaller than 4096 will cost the same as a 4K fetch.

4. When the data packet arrives on the client, it must be unpacked into the TAstaClientDataSet.

ASTA in memory Datasets are very fast but they contain no indexes. Instead of using FindKey as you would in a TTable with an index, use Locate. Locate is extremely fast since all the data is in memory. If you want to re-order your result set, don't go to the server to do it, use some of the sort methods of AstaDataSets to sort by one or more fields.

We recommend that you use ASTA Cached Edit Mode. This means that after you have executed your Select statement you can edit, insert, delete as long as you want and then post the changes to the server with a call to ApplyUpdates(usmServerTransaction). You can even disconnect from the server, save the dataset to file, wait a week, do more edits, inserts and deletes, connect back to the server and apply the updates. ASTA generates pure SQL that is posted to the server in transactions so it is very efficient. If you want to use AfterPost, after each edit, insert or delete, when the Post method of the TastaClientDataSet is called, SQL is generated and sent to the server to be executed. Performance will be very fast but in the same trip to the server you could have also sent many rows to populate the 4K packet that tcp/ip uses. That is why we recommend using Cached EditMode.

Transactions

In normal client/server programming you are probably used to starting transactions, executing some SQL and then either committing or rolling back the transaction depending on whether the executed SQL succeeded or not. To facilitate efficient communication over latent connections ASTA was designed to efficiently communicate with remote servers by executing transactions on the server for you. Client/Server drivers are slow or "chatty" because they make too many round trips between client and server. With ASTA you only need to call ApplyUpdates and internally ASTA will transmit all the information needed to be used by the remote server for you. On the server, ASTA will provide a Database Connection to be used, start a transaction with that connection, attempt to post all the SQL and then appropriately commit or rollback the transaction. If the transaction was a success, a message will be returned to the client application so that the AstaClientDatasets can flush their pending changes. If the transaction was not a success, a message will be sent from the server and raise an Exception on the client. You can decide then whether to CancelUpdates, RevertRecord or prompt the user to make any changes needed to resolve the problem that triggered the exception. One message to the server and one message back from the server.

To recap, with ASTA you only write SQL for select statements, set your edit mode, and then use normal VCL TdataSet methods to append , edit and delete. Then post your changes and you have a fully enabled thin client Database application that can run efficiently over the internet with only having to write one line of code if you choose Cached EditMode : AstaClientDataSet1.ApplyUpdates(usmServerTransaction). If you use After Post EditMode then no code is required.

To faciliate this client process the AstaServer really only needs to do a few tasks

• Perform a Select
• Perform a Parameterized ExecSQL statement
• Perform Transaction Start
• Perform a Transaction End

ASTA was designed so that AstaServers would receive pure SQL statements so that Delphi 3rd Party Database components could be easily used on ASTA servers by needing only a minimum of functionality.

Auto Increment Fields and Default Values on Inserts

When you insert a value into a table there may be some "Auto Increment" fields or other triggers or default values that are created by your database. Oftentimes dealing with these values created on the server are problematic. An AstaClientDataSet is an In Memory dataset so calling refresh after an insert does nothing but refreshes the internal TDataSet buffers. It is normal client server practice to close and then re-open a Query to "refresh" the database but of course there is a performance hit if the complete result set is to be refetched from the server.

AstaClientDataSets have the ability to Refetch Fields on Inserts and Updates to make this process very easy. Refetches are only supported in Cached Edit Mode has a transaction is required on the server. After writing some select SQL and setting active to true, just use the AutoIncrementField to identify any AutoInc Field. This does the same as marking the field as Read Only in that no sql will be generated for Read Only fields[7]. Then use the RefetchOnInsert property to tag the AutoInc Field, and any other fields that will have values updated on the server on inserts, to be refetched when an Insert is fired.

Here is what the Asta Server logs show when an ASTA Client is setup to Refetch Fields on Inserts.

1. A Transaction is started.
2. For any SQL that is an insert, the AstaServerSocket.OnInsertAutoIncrementFetch event is fired to get an autoincrement value
3. Another select is fired with the value retrieved in #2 to get the refetched data.
4. If all the Transactions succeed and a Commit is Issued the Refecthed Field Values will be streamed back to the client application.

On the client you will know when the Refetched Fields are back by using the AstaclientDataSet.AfterRefetchOnInsert Event

Here is an example log from an ASTA Server:

Start Transaction 127.0.0.1 Transaction
Exec ParamList Call: Insert Into AstaCustomer (Company) Values ('test')
Auto Increment Value Fetched for AstaCustomer field CustNo 9917 ->127.0.0.1:1753
AstaDbisamDataModule.AstaDataBase1->SELECT CustNo FROM AstaCustomer Where CustNo=9917
Transaction Committed

That Famous Middle Tier Business Logic

When discussing "N Tier" technology the phrase "allows you to put the business Logic on the Middle Tier" is often heard. Normal 2 tier applications use SQL on the client, and this technique has been described above as used with ASTA to create thin client Internet enabled client server applications. But there are occasions where for security, maintainability, code reuse or performance you may want to move that SQL to the server. ASTA fully supports this with TAstaProviders, TAstaBusinessObjectsManagers and ASTA Messaging. These topics are covered more fully in other documents but we will attempt here to provide an overview of what is possible using ASTA and server side N Tier coding.

Server Side Processing

There is a report that needs to be run weekly that uses a lot of queries. Instead of writing sql on the client, use a TAstaBusinessObjectsManager to write a server side Method that will trigger code you can write on the server. The AstaClientDataSet sees a list of these server methods at design time by pulling down it's ServerMethod property. Instead of opening up 4 DataSets on the client, a very small message will be sent to the server that triggers code that is tied to the OnActionitem event of the server method. 4 queries are executed on the server and they results are combined in a TastaDataSet (in memory dataset) and streamed down to the client dataset. All the processing and fetching took place on the server so the resultant network traffic was kept to a minimum.

Server Side SQL

If you want to keep your SQL on the server and not have any SQL on the client, you can use TAstaProviders. TAstaProviders allow any TDataSet descendent to be hooked to it's DataSet:TDataSet property so that you can write SQL on the server using any TDataSet you desire. On the client, instead of SQL, the ProviderName property is used to see all of the TAstaProviders available on the ASTA Server. When you choose a TAstaProvider and set active to true a small message is sent to the server to trigger the dataset connected to the provider to execute. Any result sets are streamed back to TAstaclentDatASets. If you want to make your result set updateable, then you must set the PrimeKey and Update Tablename of the TastaProvider. On the client, you set the SQLGenerateLocation to gsServer and the AstaclientDataSet is but in Cached Edit Model. Now you can append, edit and delete and when you call ApplyUpdates(usmServerTransaction) your updates will be posted to the server. But ASTA doesn't do this by generating SQL on the client.

Update SQL on the Server

ASTA maintains the original values of any TastaclientDataSet row for any delete or edit activity as the FoldValuesDataSet. When ApplyUpdates is called using Client side sql, the FoldValuesDataSet is compared to the Current Rows and SQL is generated on the client to be posted to the server in a transaction. When a TastaProvider is used, the server is updated differently.

• The FoldValuesDataSet is packed up to be sent to the server
• A TastaDataSet is created containing any row that has been edited or appended and this is packed up also
• On the server, there are Before/After events on the TastaProvider. Here you have an opportunity to change the CurrentValuesDataSet for each row and you have the OrignalValues, and Optionally current Server side values to compare. You can update any field, or tell ASTA not to generate any SQL for a given row.
• By using providers you can also use ASTA's Provider Broadcast ability