Re: Decouple SQL queries from class in OOP design

"Mikito Harakiri" <mikharakiri_nospaum@xxxxxxxxx> writes:
> boolean isValidEmployee( int empNo ) {
> //wrap around "select count(*) from emp where emp# = :empNo"
> }
>
> OK, boolean more realistically is a record with picture, first and
> last names fields, but I digress. Tell me a single reason why this
> SQL query shouldn't be embedded in this function.

If the data is owned by the application, embedding SQL might be
justified. In most environments of even slightly above trivial levels
of complexity, information such as this is maintained in a database
that is shared across the enterprise. 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.

- 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.

- 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.

- 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.

Note that these issues are independent of the implementation
approach. The value of decoupling the application from the database
schema exists if your implementation uses procedural, OO, functional,
or any other paradigm.

Regards,

Patrick

------------------------------------------------------------------------
S P Engineering, Inc. | The experts in large scale distributed OO
| systems design and implementation.
pjm@xxxxxxx | (C++, Java, Common Lisp, Jini, CORBA, UML)
.

Relevant Pages

  • Split messages
    ... This is based off the northwind database. ... Create document schema: ... Select the SQL entry, make sure that we are pointing to the SQL ... For the item select a receive pipeline and name it EmpSplitPipe.btp. ...
    (microsoft.public.biztalk.general)
  • Re: exporting into a SQL Server schema
    ... inside of the schema. ... The thing that's most confusing is that the dbo schema isn't ... take a look at the way the objects in the AdventureWorks database are ... It turns out that I'm moving some tables to a web hosted solution with sql ...
    (microsoft.public.access.externaldata)
  • Re: Decouple SQL queries from class in OOP design
    ... There is no change to the functionality supported ... > by the database, but the schema changes. ... > Denormalization for performance, as noted above, will definitely ... serioulsly chllenge his SQL tuning abilities. ...
    (comp.object)
  • RE: SQL Adapter
    ... Did you add the SQL schema with the 'Add Generated Items - Add Adapter' wizard. ... Target namespace and Request and Response root element name. ... > have to be inserted in an SQL Database. ...
    (microsoft.public.biztalk.general)
  • RE: from XML to SqlCE
    ... you can load the XML into a data set and then into SQL CE. ... as there is no way for the dataset to create the database schema on the fly. ...
    (microsoft.public.sqlserver.ce)