Data Type Mismatchs Between Relational Db And Host Language

[Moved from RelationalHostLanguageImpedanceMismatch.]

Select a widely accepted relational database product and a widely used host language. Regardless of your selections, you can be sure that the set of data types supported by the database, for defining tables, is different from the set of data types available in the host language.

With older languages, like C and COBOL, modern databases often have data types not supported by the host language. With more modern languages, and with most OO languages, the host language has more types than the database.

The two are different. Therefore there is a mismatch.

You could restrict yourself to the intersection of the relational and host language data type sets, but then you'd lose the benefits of the other types not available on "the other" platform. And where's the business requirement that says that all businesses must have a requirement that all data be equally processable in both the relational database's SQL and the host language of choice?

With respect to the host language, you might consider...

Only the data types supported by the compiler.
Data types supported by the compiler and standard libraries. (...typically an issue with OO languages.)
Data types supported by the language -- if you get to buy any and all 3rd party libraries that suit your fancy, and develop whatever additional types you please.

(Because OO languages typically have more usable data types than relational databases, adding libraries and custom code typically makes the mismatch worse, not better.)

Data Type Mismatches:

Relational database data types often don't map one-to-one with host language data types:

Example: Can I reasonably store a NUMBER(5) Oracle column (5 decimal digits) in a 2-byte signed integer variable in my C program? Or how about a Sybase/SQL Server short column (2-byte signed integer) in a COBOL PIC 9999 variable?

No, you cannot. you can't blame Oracle for this, after all Oracle doesn't force you to use NUMBER(5). But on the other hand, depending on your particular language you can have a NumberValue? class

Example: Tell me again how rounding errors won't be an issue when I store fixed-decimal Oracle columns in a Double.

Well, Oracle accepts Double as a type. use Double or used a fixed decimal AND create a class/ or user defined struct together with functions to operate with exactly the same semantics as in Oracle

Example: How well does a CHAR(16) fixed-length database string map to VB or C strings, which are generally variable length?

I really don't know about VB, but in C you can easily define struct bounded_string {int length, char* data} and define some functions that operate on this struct by checking for length and forwarding the calls to string.h functions. Again, you really mean that the host language should be extended a little bit.

Example: More seriously... What about all the pain I experience due to the fixed maximum length of database VARCHAR columns, while I may never have a clear unambiguous definition of the maximum length of a product description, department name, or any of several thousand other text fields in my system. (Since my reports use proportional fonts, it no longer makes sense to define a maximum number of characters that will fit in a given report column.)

Well, you have a TEXT or CLOB data type in most relational databases that deal with unbounded texts. Watch carefully, if you really want to manipulate strings that you cannot predict that will have a reasonable upper bound to their length, you have to rethink your design, even for the host language. Even in this case a database is more capable of dealing with large pieces of data than a programming language. Of course no database can take no matter what abuse.

[New comment as of 7/26 evening:] When I wrote programs in the PICK operating system, I didn't have to impose the technical requirement on my business requirements that all strings have a fixed maximum length: Names, addresses, product descriptions, and all other strings could be as short or as long as needed. Now, because I'm "blessed" with a real relational database, I have to limit my business requirements to the database's limitations? Somehow I fail to see this part of it as an improvement. (TEXT columns aren't the answer either: Can't index. Can't process in SQL with "=" or "like". And the overhead of using them on strings of less than several K bytes in size is really nasty.)

Say I do pick a large "reasonable upper bound" to my varchar columns. Throw a few together in a record, exceed Oracle's 2K limit on record size, and you're stuck again.

The above assessment about Oracle is just not true, and it is not true for any modern RDBMS. In general all relational databases put a reasonable limit (2k or 4k - for newer verison of Oracle it is 4K) on VARCHAR columns. To pretend you need more than that, and you do not to use CLOB columns is deluding yourself. The row itself can be as big as 1GB and bigger, it is not limmited to 2k, however if you grow it too big you'll have to suffer some consequences. Yes the CLOBs columns cannot be used directly in an index, but anything that grows any bigger than a reasonable size, and 2K is way more than reasonable for a simple String, cannot be meaningfully used for an index, based on its intrinsic value. -CostinCozianu

No, in general you don't need mapping if you are happy with only built-in data types offered to you in the host language(like double, float, int, String and the likes). Otherwise you can't blame the impedance mismatch because it doesn't offer you a VARCHAR(15) CHECK // various checks ... here. -- CostinCozianu

Simple solution:

Just use a highly dynamic language with a dynamic database (see MultiParadigmDatabase). It seems that too many DB vendors are obsessed with benchmarks, and put flexibility and adaptability last. It does not have to be this way.

CategoryRelationalDatabase