> ## Content Index
> Fetch the complete content index at: https://blogs.remobjects.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# A Highlevel Overview of DA SQL
- URL: https://blogs.remobjects.com/2009/02/18/a-highlevel-overview-of-da-sql/
- Published: 2009-02-18T13:14:56.000Z
- Updated: 2018-08-22T06:17:48.000Z
- Author: marc hoffman
- Tags: #Import 2026-08-17 20:17

*The following is a preview of new content about the DA SQL feature that i’ve been working on as part of a general refresh of the Data Abstract homepage. The goal is not to explain DA SQL technically in depth, but to give a high level *marketing*\-type overview. The topic will be one of many describing different core features of DA. Feedback appreciated.*

— For decades now, SQL (short for Structured Query Language) has established itself as the standard language for expression database requests. Originally devised to be easily understood and written even by non-technical persons, SQL makes it easy to to describe simple and mid-level data queries, but at the same time provides the flexibility and power for experienced SQL developers to write complex and very expressive queries.

In its simplest form, a SQL expression is used to query for all data from a given  
table, or maybe specify a subset of fields or records to retrieve. For example,  
the query

SELECT CustomerID, Name FROM Customers WHERE Name = 'Miller'

would request the two specified fields, for all Customers named *Miller*. However,  
more complex SQL queries can work across multiple tables, aggregate and join data,  
and otherwise express pretty complex data access scenarios.

SQL and Multi-Tier

Traditionally, SQL has been constrained to be used in two-tier client/server applications,  
or on the server (a.k.a. "middle") tier of multi-tier apps.

In the classic client/server scenario, client applications would contain or generate  
SQL statements that were run directly against the database, without any means for  
business logic or fine data access restrictions to be applied in between. Because  
clients had full access to the SQL of the back-end server, they could query any  
data they pleased, and make extensive changes without control. Great flexibility  
on the client-side was achieved by sacrificing control.

!\[\](http://www.remobjects.com/isapi/images.dll/getimage?category=images&id={883950C3-6A38-42E8-B6C2-405439BE886A}) When Multi-tier architecture came to replace client/server applications, direct SQL access to the back-end server was banished to the middle-tier. Only the business server could communicate directly with the back-end database through SQL, while clients usually were restricted to retrieving full record sets, or sets of data filtered by criteria specifically exposed thru the business server (maybe the server would expose specific method where clients could ask for data filtered by a given field). This consolidated control over data access and updates to the middle tier – where it belongs – but sacrificed flexibility on the client in how data could be queried and obtained.!\[\](http://www.remobjects.com/isapi/images.dll/getimage?category=images&id={4BDF0B8A-51C4-4BA3-A8E3-36031F6ECB5D}) Enter DA SQL

DA SQL™, a new technology introduced to Data Abstract in 2008, is set to change  
all that, by providing clients the full flexibility of SQL queries to describe their  
data needs, without giving up the control held in the middle-tier. While traditional  
client/server applications would allow clients to write SQL that ran directly against  
the back-end database,as discussed above, DA SQL statements sent from the client  
application are processed and run against the data as published by the business  
tier, allowing that tier to keep full control over data access and updates.

DA SQL statements will use field and table names as they are defined in the middle-tier  
[schemas](http://www.remobjects.com/page.aspx?id=%7B3023D883-C97D-4A1C-B1F8-48B61C037AAC%7D&ref=blogs.remobjects.com) of the  
Data Abstract server, and will automatically be restricted to the data and fields  
that the middle-tier allows access to.

For example, an extensive *Customers* table might be exposed to a client application  
for sales personnel with two restrictions: (a) only a subset of, say, 10 fields  
is available and (b) every sales person may only access customers from his or her  
region. This is data access business logic that would be encoded in the schema or  
in custom code written for the server application, making sure that whenever a client  
application retrieves or updates data, these restrictions are upheld.

DA SQL allows client applications to use SQL to perform data queries, without giving  
up or bypassing this (or any other) business logic in the course. For example, the  
client application might send the following query to the middle-tier server:

SELECT \* FROM Customers WHERE Name = 'Miller'

Whether the application allowed the end-user to type this query or generated this  
SQL code as part of a query builder or some other form of UI, the intention is the  
same: the application is asking for all fields of the Customer table where the customer  
in question is named *Miller*. But as we recall, data access constraints were  
set on the server, so the client will not actually receive all fields of the customer  
table (nor all customers, world-wide, that are named Miller): because DA SQL is  
processed and executed in the middle tier, business logic will be applied constrain  
the access and enforce the business rules we set forth above. As a result, the actual  
database query that will run against the server might look something like this:

SELECT ID, Name, Address, ... FROM Customers WHERE Name = 'Miller' AND Region = 78

DA SQL, which has deep understanding of the back-end database /and/ subset that  
the schema exposes to clients, was able to craft a new query – combining the query  
it received from the client with its own filters, in both SELECT and WHERE clause.

DA SQL Flexibility

![](http://www.remobjects.com/isapi/images.dll/getimage?category=images&id=%7B5462A793-6939-440E-A582-F6E5960C7BCC%7D)

While  
the above is a simple example (and, to boot, a data request that could have easily  
expressed, client side, without the need for SQL), DA SQL allows much more complicated  
and detailed requests to be formulated, which would still benefit from full protection  
of business logic.

For example, a query could JOIN together different tables, employ nested queries  
or have a much more elaborate and complicated WHERE clause. Still, DA SQL would  
enforce that data can be accessed as permitted by the schema  
and other server-side logic.

At the moment, DA SQL supports a subset of the SQL92 standard, with most querying  
options commonly used, including JOINs, nested queries and of course extensive SELECT  
and WHERE clauses to describe subsets of fields or records to retrieve. Over time,  
it will be expanded to support all features of SQL92 that are applicable. More details  
on the current set of supported SQL features can be found [here in our wiki](http://wiki.remobjects.com/wiki/DA%5FSQL?ref=blogs.remobjects.com).

How does it work?

It is important to understand that DA SQL query statements are not, ever, diretcly  
passed through to the back-end database, and thus completely eliminating the risk  
of SQL injection attacks or malicious clients executing unwanted actions as part  
of what looks like an innocent SELECT request.

When a query is received by the DA SQL engine on the middle-tier server, it is parsed  
and processed into an internal structure describing the SELECT, INSERT, UPDATE or  
DELETE statement it represents, much like would be done on a regular back-end database  
engine. This structure is then compared to the tables and fields defined in the  
schema and validated  
against it – for example, a statement touching a table (or fields of a table)  
not exposed in the schema would be rejected with an “Unknown table”  
error – even though the actual table might exist in the back-end database.

Once the query is validated, DA will go ahead and build its own SQL statement (or  
set of statements), in the syntax of the respective back-end databases (“DB  
SQL”), to fetch the data as needed. Here it will apply any and all business  
constraints, to make sure it accesses only data that is permitted (for example,  
as seen above a query with \* asking for all fields would be translated to only retrieve  
the fields actually published via the schema). It will also apply any column mappings  
(link!) or database abstraction defined in the schema.

Depending on the complexity of the original DA SQL query and the type of the data  
sources on the back-end needed to fulfill it, DA might be able to generate a single  
“DB SQL” statement, or it might have to run separate queries (for example  
to fulfill a JOIN spanning separate back-end databases) and combine data internally,  
before sending it back to the client.

All of this is happening transparently on the server – all the client sees  
is the resulting data matching its query.

Find Out More

DA SQL is mainly a server-side technology, and currently available in DA/.NET Servers  
and the upcoming cross-platform standalone DAServer6.  
It can of course be consumed from clients written in all three editions of Data  
Abstract, including .NET, Delphi and the upcoming native OS X library.

For .NET and Silverlight client development, an interesting technology built on  
top of DA SQL is DA LINQ.

You can read more about DA SQL [in our wiki](http://wiki.remobjects.com/wiki/DA%5FSQL?ref=blogs.remobjects.com), or check out the DA SQL samples shipping with the products.