Re: OOP/OOD Philosophy

> 1) Show current system time.

In java you do System.currentTimeInMillis(). You don't need the
database to do that. 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.

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

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

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

> 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. If you really had to create sequences on the
fly, you could easily create different drivers (strategy pattern maybe)
for different vendors.

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

> 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. Which databases does not support table locking (probably
MySQL, but I am not sure)?

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

Fredrik Bertilsson
http://butler.sourceforge.net

.

Relevant Pages

  • E*Trades database systems?
    ... Does anyone know what database vendor and middleware vendors are used ... address information in their multiple databases. ... Account verification forms that popup when the user logs in. ... is unable to remove these forms after a customer has completed it. ...
    (comp.databases)
  • Re: OOP/OOD Philosophy
    ... of date handling in databases. ... Because if you don't Oracle will print something you don't want to see. ... them in any other Standard SQL database. ... > for different vendors. ...
    (comp.object)
  • links to other databases in MS SQL
    ... databases created by different vendors into one database. ... all databases into MS SQL format I have to find the way to do linking-like ...
    (microsoft.public.sqlserver.server)
  • Re: simple web programming options
    ... And that I have seen from CGI/FCGI, Java ... PHP seems to provide the best API of ready-to-use ... doing CGIs specifically and scripted access to databases. ... I recommend PHP highest for language. ...
    (comp.programming)
  • Re: CLOS and databases
    ... > socks off the Java people, so I really want to do this the Right Way. ... Current O/R mapping products include more or ... support for caching; ... support for all major databases (with the possibility to let you write ...
    (comp.lang.lisp)