- SQL parse time: Much information is available early, when a SQL statement is parsed for the first time. This will lead to most optimal execution plans. Most RDBMS do this to some extent.
- Bind time: When you bind variables, well, the JDBC driver doesn’t have to be too smart to assess that a
java.lang.Integeris best represented by
NUMBER(10), or a
VARCHAR2. If bind-variable peeking is activated, then the execution plan can still be quite good, even if the bind variable type isn’t necessarily easy to infer anymore.
- Execution time: If all else fails, then while iterating over the cursor, you can still run checks on encountered data. It’s too late to affect the execution plan, but not to keep data integrity.
But some of you will know the depths of DB2 and Derby well enough to be aware of the ultra-strong type systems, those RDBMS chose to implement. In DB2, for instance, these two values are very different:
-- Easy to infer bind types or optimal default cast, at SQL parse time INSERT INTO table (field1, field2) VALUES (?, ?) INSERT INTO table (field1, field2) SELECT ?, ? FROM DUAL UPDATE table SET field1 = ?, field2 = ? SELECT * FROM table WHERE field1 = ? AND field2 = ? SELECT field1 * ? / ? FROM table SELECT field1 || ? || field2 FROM table -- Bind types can still be inferred, at bind time SELECT ?, ? FROM DUAL -- etc...
In many RDBMS, for instance, this won’t work
cast(null as char(3)) cast(null as integer)
For a generic database abstraction framework, this can be quite a challenge to hide from the client code, the way jOOQ aims to do it. So here’s an overview over this casting madness, and how which RDBMS can be categorised:
-- Won't work INSERT INTO table (field1, field2) SELECT ?, ? FROM DUAL -- Has to be written like this INSERT INTO table (field1, field2) SELECT cast(? as char(3)), cast(? as integer) FROM DUAL
Friendly RDBMSThese RDBMS are my friends. They are powerful, provide lots of functionality, and infer all sorts of data-types, so I don’t have to cast any bind variables. Give a hand to these guys:
- MySQL (*)
- SQL Server
- SQLite (**)
function differs from the type defined in the table’s DDL statement. See ()some documentation on this. (**) SQLite is very special. Well, they just don’t care. You wanna put a
NUMERICcolumn? No problem. A
DATEcolumn? Even better! They call that “type affinity“.
Friendly RDBMS, with the odd JDBC bugVery unfortunately, these JDBC drivers seem to suffer from 1-2 bugs concerning uncast variable binding in rather complex SQL (e.g. nested SELECTs in JOIN clauses). So to be on the safe side, casting is better.
- Sybase SQL Anywhere
The difficult onesThey do a lot of inferring. But also a lot of intentional omitting inferring. You can never be sure whether you can omit the cast for your next query or not with these guys:
The lost onesThese RDBMS hardly infer any type. In fact, a bind variable without an associated type is utterly meaningless in many situations. Up until DB2 v9.7, even the literal
nullhad no meaning without an associated type. That’s very hard to handle in a database abstraction layer, as
nullreally isn’t a value from the Java perspective. So the strict ones are:
VARCHARwithout casting to
CHARfirst. See the conversion table from hell.