Re: OOP/OOD Philosophy

frebe wrote:
> > 1) Show current system time.
>
> In java you do System.currentTimeInMillis(). You don't need the
> database to do that.

Obviously but my examples serve to demonstrate the non-standard nature
of date handling in databases.

Besides I often need to use current time in where clauses of queries.

Anyway I don't want to make a big deal about all this with you. I was
really just hoping to stick it to the database vendors, the current
state sinks.

> If you really want to do it in the database, write
> a own stored procedure. The implementation might differ in different
> databases, but the application is only calling the stored procedure and
> don't know about the implementation.

That's work around but it's not standard-SQL ...

>
> > to_char(SYSDATE, 'DD-Mon-YYYY HH24:MI')
> Why do you convert the date to a string? Your application should
> receive the date as a date object.

Because if you don't Oracle will print something you don't want to see.

TO_DATE and TO_CHAR are very important in Oracle, yet I've never seen
them in any other Standard SQL database.

>
> > 2. How do you calculate a duration or difference between two dates?
>
> I do this in my java code. And if I really needed to do that, create a
> stored procedure.
>
> > Some have a datediff function others don't ...
> Almost all databases gives you the possibility to create your own
> functions.
>

Great but this discussion was about Standand SQL (and lack there of).

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

I gave you some examples, I don't understand why you ask for me. Sure
they were very elementary but they serve the purpose.

> > mySQL has auto_increment, Oracle has create sequence ... with NEXTVAL,
> > others have IDENTITY()
> Now you are talkning about DDL statements. Your application, normally
> only calls DML statements.

No that isn't what I normally do ;-) Having separate DDL statements (in
a script I suppose?) is ugly duplication. The schema and app code get
out of phase.

I go to great lengths to eliminate all DB related duplication. "select
fieldBlah from tableBlah" holds too much duplication, the field names
and table name will be sprayed all over the place ...

>If you really had to create sequences on the
> fly, you could easily create different drivers (strategy pattern maybe)
> for different vendors.
>

"Create sequence" is DDL but identity() and nextval are not.

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

> It works for Oracle, Postgres and MySQL (didn't have time to test more
> vendors). For which vendors does it not work this way?

I can't find two vendors the same.

In Oracle you use yourSequence.NEXTVAL not NULL to get a new ID. I
never thought of trying NULL but I can't see it working.

Others use the function IDENTITY(), which is part of standard SQL92 but
it's only 2005 so I don't expect everyone to have implemented it yet
;-)

>
> > 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.
> Just because not all databases support a standard, or is full-featured
> does not mean that you can't be switchable between vendors that are
> full-featured.

I already agreed you can ... as long as you don't use dates and
sequences ... If you just stick closely to "Select * from Blah" or
provide a truck load of workarounds yes you should be ok.

> Which databases does not support table locking (probably
> MySQL, but I am not sure)?
>

I forgot what mySQL does or doesn't do but I know it's different to
Oracle and Oracle is different to SQL Server and Hypersonic and ...

> > Or it requires you to prohibit concurrent access (somehow?)
> The RDBMS should solve the concurrency issue in this case. That is what
> transactions and locking are supposed to do.

Transactions do not prohibit race conditions across multiple statements
only locking can do that. And I've never seen two DB's with the same
locking.

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

Cheers.

.

Relevant Pages

  • Re: Need your help [arguments for open source databases]
    ... >> I would like to have your opinions about open source databases versus ... >> in the universe of relational databases. ... The main difference between PostgreSQL and Oracle is manageability. ...
    (RedHat)
  • Re: SAHM considering database administration
    ... > I am a 36 year old stay at home mom, with eight years paid work ... Am I too old to start in this career? ... Work for places that use Oracle. ... You better _love_ relational databases. ...
    (comp.databases.oracle.server)
  • US-PA: Newtown - Junior to Mid-Level Oracle DBA
    ... Inc. is seeking an experienced junior to mid-level Oracle DBA to support multiple highly available transactional and data warehouse databases underpinning a 24x7 global web services application. ... Position responsibilities consist of both operational and application support primarily in a Red Hat Linux environment. ...
    (comp.databases.oracle.marketplace)
  • US-PA: Philadelphia Area - Junior to Mid-Level Oracle DBA (FT Permanent)
    ... Inc. is seeking an experienced junior to mid-level Oracle DBA to support multiple highly available transactional and data warehouse databases underpinning a 24x7 global web services application. ... Position responsibilities consist of both operational and application support primarily in a Red Hat Linux environment. ...
    (comp.databases.oracle.marketplace)
  • RE: Server Explorer & Databases
    ... As for the visual studio's database server explorer, ... oracle, visual studio generally use the OLD DB provider which is for Oracle ... and support of most general funcctionalitis in latest version of Oracle. ... #Server Explorer for Oracle Databases ...
    (microsoft.public.vsnet.ide)