Type promotion rules vary across back-ends. For example, in DB2 it appears that the target type of an expression is tied to the operand types; i.e., in order to add two Int32 numbers requires casting both to Int64 so that the result is of sufficient size. Similarly, concatenating two Varchar(25) strings requires first casting each to Varchar(50) so that the concatenation doesn’t truncate.
In order to account for such differences in type promotion, generic clients have to wrap expressions in CONVERT functions. Even though the CONVERT function works in terms of ODBC types, understanding semantics of those types (like maxlength of Varchar versus LongVarchar) requires back-end specific knowledge.
Currently clients call SqlGetTypeInfo to figure out the maximum sizes of the provider types, relying on the types being ordered by how closely they match the corresponding ANSI type in order to make the assumption that the max length of the first SQL_VARCHAR type is the length used when CONVERT is called for SQL_VARCHAR (and without specifying a length parameter).
-should we define an alternate syntax for a convert that has enough information that drivers can omit if the back-end doesn’t require?
-could/should we expose a conversion table from the data source?
-should we expose promotion rules from the driver so the app knows when it has to do converts???
Should it be the driver’s responsibility to convert the ODBC SQL Type into an underlying type of sufficient size, if available, regardless of its default mapping? So, for example, if an application specifies a CONVERT to a SQL_VARCHAR(500), and the data source has a limit of 256 characters for the data-source specific type that it maps to a varchar, the driver converts this to the data-source specific type that maps to a long varchar?
Type promotion rules vary across back-ends. For example, in DB2 it appears that the target type of an expression is tied to the operand types; i.e., in order to add two Int32 numbers requires casting both to Int64 so that the result is of sufficient size. Similarly, concatenating two Varchar(25) strings requires first casting each to Varchar(50) so that the concatenation doesn’t truncate.
In order to account for such differences in type promotion, generic clients have to wrap expressions in CONVERT functions. Even though the CONVERT function works in terms of ODBC types, understanding semantics of those types (like maxlength of Varchar versus LongVarchar) requires back-end specific knowledge.
Currently clients call SqlGetTypeInfo to figure out the maximum sizes of the provider types, relying on the types being ordered by how closely they match the corresponding ANSI type in order to make the assumption that the max length of the first SQL_VARCHAR type is the length used when CONVERT is called for SQL_VARCHAR (and without specifying a length parameter).
-should we define an alternate syntax for a convert that has enough information that drivers can omit if the back-end doesn’t require? -could/should we expose a conversion table from the data source? -should we expose promotion rules from the driver so the app knows when it has to do converts???
Should it be the driver’s responsibility to convert the ODBC SQL Type into an underlying type of sufficient size, if available, regardless of its default mapping? So, for example, if an application specifies a CONVERT to a SQL_VARCHAR(500), and the data source has a limit of 256 characters for the data-source specific type that it maps to a varchar, the driver converts this to the data-source specific type that maps to a long varchar?