Creating an SQL variable character column > 255 characters supporting multiple databases

Question

I have an application that stores data through an ODBC data source of the user's choosing. So far it has worked well on a range of database systems (e.g. JET, Oracle, SQL Server), as the SQL syntax is fairly simple.

Now I am running into a problem where I need to store more than 255 characters in my strings. Previously I created the table using column type VARCHAR (255).

Now if I try to create a table using, e.g. VARCHAR (512) then it falls over on Access databases. I know that I can use the MEMO type for Access, but this is non-standard SQL and will thus likely fail on other database systems (e.g. Oracle).

Is there any widely supported SQL standard for creating text columns wider than 255 characters, or do I need to find another solution? The alternatives seem to me to be:

1) Profile the database system and customise the SQL CREATE TABLE command based on the database system. I don't like this as it defeats the purpose of using ODBC.

2) Add extra columns of 255 chars as required (e.g. LONGSTRING1, LONGSTRING2, ...) and concatenate after reading. I don't like this because it means the number of columns can vary between tables and it complicates read/write.

Are there any other viable alternatives to these two options? Or is it possible to have an SQL compliant CREATE TABLE command supported by the majority of database vendors, that supports strings longer than 255 chars?

Answer 1

No. As can be seen in Hibernate, for example, they have a config for every database which customizes how unbound character types are created (besides a lot of other database specific customizations).

Answer 2

I may be proved wrong, but I think there isn't a proper standard when it comes to fields that store arbitrary sized binary objects (which is what a large string field will be stored as).

Access - memo 
MSSQL - text 
ORACLE - text?

However, rather than adding different columns based on the database you are using it might be neater to use a separate table which has an ID Key to your Primary Key and stores the long text in a database specific field. This will give you greater flexibility.

PS. Please don't go for option 2.

Answer 3

IMO, Option #1 is your best option. I doubt that the data type syntax will be the only database specific anomaly that you will encounter. For example, many databases have a different way of creating the equivalent of SQL Server's Identity column (e.g. in Access it's an AutoNumber) assuming they support the idea at all. As others have pointed out, even between versions of the same product, you may not be able to use the same syntax and have it work. In addition, if Jet is going to be one of the supported databases, I guarantee you will run into plenty of syntax problems that work in other database products but not in Access without some tweaking. Unfortunately, the ISO standards really only mean that the syntax will be similar between databases not exact.