Re: Decouple SQL queries from class in OOP design

Patrick May wrote:
> The reasons why the application
> should not embed SQL in this case include:
>
> - It couples the application to the database schema. Changes to
> the schema (due, for example, to requirements of completely
> different applications or database administrator decisions)
> could break the application.

Wrong. The database schema outlives the application. I just went to DMV
where the clerk was still using character based entry screen. Now tell
me, which of the following is likely to happen on the next step

1. DMV would throw away the database of all vehiles/drivers/ etc in
california and write a completely new application with new persistence
store. (Persistence is just implementation detail, you know).

2. They write GUI based application that will work with existing
database. Both applications would coexist at some point.

> - The "validate employee ID" functionality is likely to be used
> in more than one application. Encapsulating it as a reusable
> component or service reduces the complexity of each application
> and provides more consistency across the enterprise.

Aha. In case #2 of my new example tell me what components of the old
application do you think would be reusable for the new application.

> - Embedded SQL requires more than a simple SQL statement. The
> application must deal with database connections, transactions,
> cursors, etc. Encapsulating this overhead and allowing it to
> be reused across multiple applications reduces the complexity
> of each application and the opportunity for bugs to be
> introduced.

If you have any difficulty opening database connection, or iterating a
cursor, you may think refreshing your programming skills. Those are not
the kind things that are worth abstracting.

> - Encapsulating SQL in components or services that expose
> required functionality instead of the underlying database
> schema makes impact analysis much more straightforward and
> reliable. When SQL is embedded in applications across the
> enterprise, it becomes virtually impossible to determine the
> effect of any proposed change to the database schema.

If the schema change is nontrivial (because just adding a column
wouldn't break anything), then you make backward compatible views. Is
this kind of obvious?

.

Relevant Pages

  • Re: Data-aware GUI controls
    ... >SQL is just a language that is based upon the RDM, ... already well taken care of by just the database and its facilities. ... >diminishing the power of the database in providing persistence. ... >while I am hiding it in a DB access subsystem. ...
    (comp.object)
  • Re: dbdebunk Quote of Week comment
    ... > a lot of really bad SQL programmers. ... But SQL does not have a pointer data type or the ... > being told to design a database. ... But why is little Cindy Lou Who employee ...
    (comp.databases.theory)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: dbdebunk Quote of Week comment
    ... > a lot of really bad SQL programmers. ... a surrogate key should support the primary key. ... But SQL does not have a pointer data type or the ... > being told to design a database. ...
    (comp.databases.theory)
  • Re: dbdebunk Quote of Week comment
    ... But SQL does not have a pointer data type or the ... More and more programmers who have absolutely no database training are ... But why is little Cindy Lou Who employee ...
    (comp.databases.theory)