db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Fischer <fisc...@seitenbau.net>
Subject AW: BLOB and CLOB Torque datatypes
Date Wed, 11 May 2005 06:42:46 GMT




Hi Joerg,

Thanks for your informative answer.

I did not forget your suggestion regarding the adapter (I see it every time
I glance at the open issues in scarab), and I think its a very good idea,
but I've decided to work up the issues from the bottom up so I did not
arrive at yours yet (There is also the firebird one, beforeI'm going at
this, I'd like to get firebird running on my test system so I can run the
runtimetest for it.)

I'll add the mysql datetime issue to scarab, too.

I also have to try Fabios information of using the oracle 10g driver
regarding LOBs. I have committed a testcase yesterday which checks reading
and writing long(100kB) byte array and string into BLOBs and CLOBs, see if
that runs with the "normal" village library.

   Thomas


"Joerg Friedrich" <friedj@users.sourceforge.net> schrieb am 10.05.2005
21:20:22:

> 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 <finsaccanebbia@gmail.com> 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: torque-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-dev-help@db.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-dev-help@db.apache.org
>


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org


Mime
View raw message