Advantage Developer Zone

 
 
 

The SQL Advantage

Wednesday, May 14, 2003

How would you like the ability to use an SQL result set just as if it were a standard table? Well, with the powerful combination of SQL and the TAdsQuery component you can. Advantage SQL and the Advantage TAdsQuery component provide a unique and extremely powerful approach to SQL development.

How it works

Advantage SQL is built into the Advantage Database Server and Advantage Local Server and provides the ability to execute SQL statements on the server, thereby adding powerful capabilities to a Delphi application. SQL is a subset of SQL-92 with extensions. When using the Advantage Database Server, the server performs all the processing of the SQL statements. If you are using Advantage Local Server, the processing of SQL statements occurs at the client workstation, but the ability to manipulate your data with SQL statements is still very useful and powerful. If you use Advantage Database Server, SQL can provide even more power because the SQL statement is executed closer to the data. For example, an UPDATE statement can update multiple records in a table with a single network request.

When you use SELECT statements, the rowset will be one of two types of cursors: live (keyset-driven) or static. Advantage produces a live cursor when it can filter and order the base table in the query without using temporary relations. If it is not possible to do this, Advantage produces a static cursor. The primary difference between these two cursor types is that live cursors can be edited, while static cursors are read-only. In general, a static cursor is produced any time a SELECT statement has more than one table or does any summarization of the data.

Now, on to the good stuff

The TAdsQuery component is intended to supplement the Advantage TAdsTable component rather than replace it. The two components can be used together very effectively in applications. As you develop your application, you should evaluate each component and choose the best one (or both) on a case-by-case basis.

The TAdsQuery component also contains nearly all of the TAdsTable functionality as well as many of the Advantage Extended Methods. Operations such as indexing, filtering, seeking, scoping, and other table operations can all be done on the result set of an SQL query. This is functionality that no other TQuery-type component can offer.

Delphi sample code

//add the SQL statement to be executed
AdsQuery1.SQL.Clear()
AdsQuery1.SQL.Add('SELECT firstname, lastname, address, city, state, zipcode FROM customers WHERE zipcode = 83713');

//Open the query
AdsQuery1.Open;

//Perform the magic... create an index on lastname
AdsTable1.AdsCreateIndex( '', 'LastName', 'lastname', '', '', [] );

//Set the current index
AdsTable1.IndexName := 'LastName';
AdsTable1.First;

//do a FindKey on an SQL result set!!!
//seek on lastname = 'Wood'
AdsTable1.FindKey(['Wood']);

//set a range on an SQL result set!!!
//set a range on all records with lastname = 'Wood'
AdsTable1.SetRange(['Wood'],['Wood']);
AdsTable1.CancelRange;

//set a lightning fast Advantage Optimized Filter (AOF) on
//lastname = 'Wood' or lastname = 'Smith'
AdsTable1.Filter := 'lastname = '+QuotedStr('Wood')+' or lastname = '+QuotedStr('Smith');
AdsTable1.Filtered := True;
AdsTable1.First;

This is just one example of the power and flexibility of the Advantage TAdsQuery component. As you can see, Advantage SQL has capabilities far beyond other SQL solutions. Give it a try — you will be glad you did!