Create SQL variable character column> 255 characters supporting multiple databases
I have an application that stores data through a user's ODBC data source. So far, it has worked well on a number of database systems (e.g. JET, Oracle, SQL Server) as the SQL syntax is quite simple.
Now I am facing a problem where I need to store more than 255 characters in my strings. Earlier, I created a table using the VARCHAR (255) column type.
Now if I try to create a table using for example VARCHAR (512) then it crashes on Access databases. I know that I can use the MEMO type for Access, but this is non-standard SQL and therefore might fail on other database systems (like Oracle).
Is there a widely accepted SQL standard for creating text columns over 255 characters wide, or do I need to find another solution? The alternatives seem to me:
1) Profile the database system and customize 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 additional columns of 255 characters (eg LONGSTRING1, LONGSTRING2, ...) and join after reading. I don't like this because it means the number of columns can change between tables and this makes it harder to read / write.
Are there any other alternatives to these two options? Or is it possible to have a SQL compliant CREATE TABLE command supported by most database vendors that supports strings longer than 255 characters?
a source to share
I may be wrong, but I think there is no suitable standard when it comes to fields that store arbitrary sized blobs (which will store a large string field like).
Access - memo
MSSQL - text
ORACLE - text?
However, instead of adding different columns based on the database you are using, you might want to use a separate table with an identification key to the primary key and store the long text in a specific database field. This will give you a lot of flexibility.
PS. Please do not go to option 2.
a source to share
IMO option # 1 is your best option. I doubt that the datatype syntax will be the only database anomaly you run into. For example, many databases have another way to create the equivalent of a SQL Server Identity column (for example, in Access it a AutoNumber), assuming they support the idea at all. As others have pointed out, even between versions of the same product, you won't be able to use the same syntax and work. Also, if Jet is going to be one of the supported databases, I guarantee you will have syntax problems that work in other database products, but not Access without some customization. Unfortunately, ISO standards really only mean that the syntax will be similar to that of a database, not being precise.
a source to share