Re: OOP/OOD Philosophy

frebe wrote:
> >> Anyway, I am developing applications which are vendor independent by
> >> using ANSI SQL. It is possible for me.
> >Sure by avoiding dates, sequences
>
> I don't avoid dates, it is no problem.

Even the most trivial things are non-standard.

1) Show current system time.

ORACLE:
select to_char(SYSDATE, 'DD-Mon-YYYY HH24:MI') from DUAL;

OTHERS:
select NOW() from dummyTable;

What does Informix have?

2. How do you calculate a duration or difference between two dates?

Oracle has INTERVAL and TO_DATE(...) but SQL*Server, Hypersonic and
mySQL don't?

Some have a datediff function others don't ...

One query for one DB and another dialect for others. Ugly duplication
which cannot be resolved by relying on the standardness of SQL.

> I do avoid sequences, but I am
> quite sure it would be possible to do vendor independent applications
> using sequences too.

Sorry, you can't.

mySQL has auto_increment, Oracle has create sequence ... with NEXTVAL,
others have IDENTITY(), standard SQL has something different yet again
.... the sky is the limit here.

> The syntax for createing a sequence may be
> different for different vendors, but the applications does not have to
> care about that. It just put null in that field and the RDBMS creates a
> sequence number.
>

No that isn't correct.

> > and anything else beyond "select *
> >from Blah" and basic joins.
>
> I am working with an human resource application used by the swedish
> police, military and goverment, that currently runs on both Oracle and
> Informix. I can ensure you that we do more than "select * from Blah".
>
> >All business applications I've dealt with require dates and unique
> >keys.
>
> If you really can't use dates and sequences it is not a big deal
> skipping these types. Use a long or string instead of the date, and
> make a helper class that converts the long value to a date.

> Sequences
> are very easy to live without. Just make a "select max(id) from
> mytable" before you do the insert.
>

There is a serious race condition here. After the max(id) and before
the insert you have a critical section.

To resolve this you must have table locking, which not all databases
can do. Or it requires you to prohibit concurrent access (somehow?) or
I suppose you can just hope for the best.

> Fredrik Bertilsson
> http://butler.sourceforge.net

Hmmm, I suppose I can shut my eyes and pretend these things aren't true
....

.

Relevant Pages

  • Re: To LinQ or not LinQ
    ... The assertion that SQL is _strictly_ set-based doesn't make sense to me either, since when you query the database, records always have to be returned in _some_ order. ... Likewise, while it's true that the LINQ syntax is very much centered around the IEnumerableinterface, there's nothing about the interface that requires the sequences to be well-defined. ... It's only practicality that causes that not to happen in practice. ...
    (microsoft.public.dotnet.languages.csharp)
  • functions and sequences
    ... I realize that ms sql doesn't really have sequences like Oracle and some other ... nextval sort of call on a sequence defined for that table and column. ...
    (microsoft.public.sqlserver)
  • Re: PHP/Oracle SQL statements, OciParse
    ... just my sql syntax is ... If you're going to use Oracle and don't have access to a DBA, ... Read up about sequences ... (and probably about triggers - a combination of a sequence and a before- ...
    (comp.lang.php)
  • Re: How to use sequences in DBI/DBD::Oracle with bind
    ... > SQL ... > Does anybody have a clue on how to bind oracle sequences. ... > You might think I should have user bind variables from the start, ...
    (perl.dbi.users)
  • Re: choices regarding where to place code - in the database or middletier
    ... >>DBMS, because it uses only the functionality offered by the DBMS that is ... Packages are good things and should ... >>In Oracle and DB2 the best way to do numering is with a sequence. ... >>Server and Sybase don't have them so throw sequences away. ...
    (comp.lang.java.databases)