Hi Thomas,
I didn't want to suggest abandoning BLOBs or CLOBs. I just wanted to point
out some problems with these data types we have had in the past, and which
were the reason to not use them in our project. These were not necessarily
Torque problems, the biggest problem we had was with the way the Oracle JDBC
driver worked. We support several database system (currently MySQL,
Firebird/Interbase, MS SQL Server, HSQLD, Oracle and PostgresSQL), so we
have the problem of keeping the code base together.
With regard to DATE, TIME, and TIMESTAMP in SQL92 (I am not an expert on
this):
DATE in format YYYY-MM-DD
TIME in format HH:MM.SS.MMMM (seconds precision is implementation defined)
TIMESTAMP in format YYYY-MM-DD HH:MM.SS.MMMM (seconds precision is
implementation defined)
TIME(p) or TIMESTAMP(p): format with seconds to p digits precision
and then there are
TIME(p) WITH TIME ZONE (not supported by most RDBMS)
TIMESTAMP(p) WITH TIME ZONE (not supported by most RDBMS)
time zone specified as +HH:MM,... or -HH.MM,...
So far for the theory. In most cases if you don't care about the last byte
of storage space TIMESTAMP should cut it for you. But...
don't forget the range! For example MySQL doesn't permit dates in TIMESTAMP
fields to go below 1970 and beyond 2037 (the year 2038 problem is already
preprogrammed). Instead, MySQL has a DATETIME field type. Too bad you can't
find it in the SQL92 specification. DATETIME is also popular in MS SQL
Server.
In the current Torque structure, if you need date and time you can only
specify TIMESTAMP in the schema definition file. This can be mapped to
TIMESTAMP for most database systems, but not for MySQL. In MySQL this should
be mapped to DATETIME.
If we specifiy a DATE or TIME field in the schema definition file, we have a
problem with the current adapter function: it knows of only one method for
date/time related fields. I suggested a little while ago to extend this to
support DATE, TIME and TIMESTAMP.
Unfortunately we do not get a lot of help from the Java side to find out
which method is required: a Date always includes time information.
Here I have to bail out, since I haven't looked into the inner workings of
Village and Torque.
My short term suggestion: always use TIMESTAMP, stick with the current one
adapter function but rigidly using the timestamp format, and change the
MySQL mapping of TIMESTAMP to DATETIME. This should support 99% of all
current applications. This would leave a problem only with TIME fields,
which cannot be handled by TIMESTAMP fields.
Joerg
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>
Hi,
Fabio Insaccanebbia <[EMAIL PROTECTED]> schrieb am 09.05.2005
18:40:47:
> Hi Jörg,
>
> >Particularly the Oracle JDBC drivers used to give a lot of problems
> when trying to use BLOBs.
> >
> yes, that's true.. however the last driver version (10g) seems to have
> solved a couple of problems with BLOBs.. The choice of "avoiding"
> BLOBS in an application is probably a good one: Torque, on the other
> side, should try to "solve" or "reduce" the problems with BLOB using
> so that the application developers can choose to use BLOBs
> without too many issues.
Here, I agree with Fabio. In my opinion, what the user wants is to store a
byte array or a large string in a database, and then looks for a data type
which does this. The user will maybe decide to use a clob and a blob for
this. This may limit the user to certain databases, but maybe this might not
be an issue in his case. Or maybe the user decides to go for maximum
portability, in which case he will use some other data type. But I do not
think that limiting Torque to the subset of features supported by all
databases is a good idea.
>
> >With regard to mapping, there is another problem with date, datetime
> >and timestamp, particularly with newer versions of MySQL which have a
> >very peculiar handling of these types. Even though there is no
> >DATETIME data
type
> >in the JDBC interface we may have to provide the possibility to
> >define
such
> >in the schema.xml file, since some DBMS use it and clearly
> >distinguish between timestamp, date, and datetime.
> >
> The JDBC interface is probably a bit clumsy... the setTimestamp seems
> the only way to set both Date and Time in the same field. I'm afraid
> that some driver creators decided to "overcome" this JDBC limit by
> ignoring the setDate specified behaviour.
>
> I agree with the DATETIME proposal: this could give us the flexibility
> to work around driver's strange behaviours.
Could you please elaborate a bit more on this ? I do not have a SQL
specification ready, so I list what I guess. Please correct me if I'm wrong.
The SQL Date type is supposed to hold dates with day accuracy. The SQL Time
type is supposed to hold time information with second accuracy. It should
not hold any Date information. The SQL Timestamp type is supposed to hold
Date+Time with at least millisecond accuracy.
So I guess that the datetime type should hold date+time information with
second accuracy (like the date type in oracle). Do you want to implement
this for all databases ? This would probably mean that for some databases,
you would have to use the timestamp type and limit its accuracy. But then,
defining a date with day accuracy on oracle also needs limiting the accuracy
to dates.
I am not sure whether this can be implemented with village....
Thomas
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
