I wish to produce a text area within the database, without any specific size (it'll store text of length unknown in certain situation) - the specific text are serialized simple object (~ JSON)
What's the most database independent method of doing this : - a varchar without any size specified (don't believe all db support this) - a 'text' area, this appears to become common, however i don't think it is a standard - a blob or any other object of this kind ? - a varchar of the a really large size (that's inefficient and wastes disk space most likely) - Other ?
I am using JDBC, but Let me use something that's supported in many DB (oracle, mysql, postgresql, derby, HSQL, H2 etc...)
Would you really have to support all six of individuals databases? (hint: No.)
I have arrived at the opinion that writing globally portable SQL DDL isn't worthwhile. YAGNI.
You need to offer the databases you're presently using, and be ready to adjust to a database that you simply adopt later on.
Re your comment: The only real standard SQL variable-length data types are
VARCHAR is perfect for string data and it is declaration features a character set and collation.
BLOB is perfect for binary data and doesn't support charset/collation.
Other data types for example
TEXT are vendor extensions:
a varchar of the a really large size (that's inefficient and wastes disk space most likely)
That's destined to be probably the most portable option. Limit you to ultimately 2000 figures and you ought to be acceptable for most databases (oracle being the present 2000 limiter, but be skeptical of old mysql versions too). I would not worry an excessive amount of about disk space, either. Most databases just use disk for that actual data held in the area.
Make use of a BLOB. JDBC2. API supports it and thus any driver that supports JDBC2. (J2SE 5. on) should support it.
The benefits of BLOB are :
1. Size is often as large as 4G-1 (Oracle. other databases not too sure)
2. Can store data you want (even images serialized into some area inside your JSON structure)
3. Completely neutral to move across OS
4. You have left benefit of indexes on secrets that reference the BLOB to ensure that searches on ids etc, dont need to be made by bothering the dwelling.