Re: Call for an API standard for SQL statements

Fredrik Bertilsson wrote:

>
> Finally, maybe the most important feature of all. Database vendor
> indepence. Unfortunately the most popular databases have small syntax
> differences in their SQL language. Using object representation of SQL
> statements is a very easy way to build the correct SQL string for the
> target database.
>

This is far easier to achieve by specifying your actions with data and generating commands. I believe the concept of an API is an unnecessary abstraction that may prevent you from getting right to work.

To wit, let's accept for now that any SQL statement can be decomposed into scalar values. If your framework deals in lists of values, then the last stop before passing a request to OLE-DB/ODBC/JDBC would be to build a SQL statement.

Here is an example. You want a view of the customer table and the orders table showing total open orders. If we blow away all of the dross, there are only two pieces of information you are asking for:

customers.customer_id
customers.customer_name (ok I lied, 3 pieces) sum(orders.order_amount)

I would contend that building a framework that builds SQL out of this would be far simpler than trying to define an API. In this example it queries the data dictionary for the join condition between CUSTOMERs and ORDERS and produces:

SELECT c.customer_id,c.customer_name,

       sum(o.Order_amount) as Sum_order_amount   FROM customers c
  JOIN orders o ON c.customer_id = o.customer_id

This starts to really pay off when you get into those weirdnesses like MS SQL Server uses a + sign for string concatenation, while DB/2 requires the double pipe ||. And DB/2 uses "FETCH FIRST 5 ONLY" or some such verbosity that puts even XML to shame, while MS SQL Server uses the "TOP 5" syntax.

-- 
Kenneth Downs
Use first initial plus last name at last name plus literal "fam.net" to
email me