What is the most "database independent" way of creating a variable length text field in a database

Question

I want to create a text field in the database, with no specific size (it will store text of length unknown in some case) - the particular text are serialized simple object (~ JSON)

What is the most database independent way to do this : - a varchar with no size specified (don't think all db support this) - a 'text' field, this seems to be common, but I don't believe it's a standard - a blob or other object of that kind ? - a varchar of a a very large size (that's inefficient and wastes disk space probably) - Other ?

I'm using JDBC, but I'd like to use something that is supported in most DB (oracle, mysql, postgresql, derby, HSQL, H2 etc...)

Thanks.

Answer 1

Do you really need to support all six of those databases? (hint: No.)

I've come to the opinion that writing universally portable SQL DDL is not worth the trouble. YAGNI.

You should support the databases you are currently using, and be prepared to adapt to a database that you adopt in the future.

---

Re your comment: The only standard SQL variable-length data types are VARCHAR and BLOB. VARCHAR is for string data and its declaration includes a character set and collation. BLOB is for binary data and does not support charset/collation.

Other data types such as VARCHAR(max), CLOB, or TEXT are vendor extensions:

• VARCHAR(max): MS SQL Server
• NVARCHAR(max): MS SQL Server
• LONGVARCHAR: Derby, H2, HSQLDB
• CLOB: Derby, H2, HSQLDB, Oracle, SQLite
• NCLOB: Oracle
• TEXT: MS SQL Server, MySQL, PostgreSQL, SQLite
• NTEXT: MS SQL Server

Answer 2

Use a framework like hibernate, so you won't have the problem to find a universal solution. I don't think that you can use one universal type in every mentioned database. The databases differ to much, I guess.

Answer 3

a varchar of a a very large size (that's inefficient and wastes disk space probably)

That's gonna be the most portable option. Limit yourself to 2000 characters and you should be fine for most databases (oracle being the current 2000 limiter, but be wary of old mysql versions as well). I wouldn't worry too much about disk space, either. Most databases only use disk for the actual data saved in the field.

Answer 4

text is perhaps best but to be removed shortly from SQL Server and there is no DBMS independent option for all you listed.

Saying that, portability is overrated when it comes to SQL. You're more likely to change your client code before you change DBMS. Pick one and go with that....

Answer 5

Use a BLOB. JDBC2.0 API supports it and so any driver that supports JDBC2.0 (J2SE 5.0 on) should support it.
The advantages of BLOB are :
1. Size can be as large as 4G-1 (Oracle. other databases not so sure)
2. Can store any data you wish (even images serialized into some field in your JSON structure)
3. Completely neutral to transport across OS
4. You can still take advantage of indexes on keys that reference the BLOB so that searches on ids etc, don;t have to be done by getting at the structure.