M
MattStickney
Guest
Currently, the ODBC throws an error if an application calls SQLBindParameter for a string parameter and specifies a column size of 0. This is common behavior for ODBC wrapper libraries, particularly in dynamic languages; there is a workaround option for this (WorkArounds=1048576), but that will truncate any parameter data longer than 256 characters with only a warning. It's very easy to find out about this after truncated data has already been inserted into the db. The ODBC driver should support string parameters with a column size of 0; I argue that doing so would be technically valid, useful, and would match the way ODBC clients and drivers operate in the wild. A little background first: the docs from Microsoft are a little vague on the subject, but the ColumnSize parameter to SQLBindParameter appears to be meant to match the target column size, not the size of the data being sent. Also, while OpenEdge does not accept columns of type varchar(0), varchar(0) is a legitimate type -- it can hold the empty string or NULL. Validity The driver currently accepts string parameters with a column size that doesn't exactly match the target column, as long as the parameter type is a subtype of the column type (i.e. the column is of type varchar(m) and the parameter is of type varchar(n) where n <= m), with the sole exception of varchar(0). Since the driver will accept any other subtype of the target column's type, and since varchar(0) can represent the type of valid values, it is technically valid for the driver to accept parameters of that type as well. Usefulness If the column size parameter must match the target column exactly, the application has to determine which column the parameter refers to, and retrieve its size with SQLDescribeColumn; Microsoft's recommendation[1] boils down to hardcoding column data in applications or parsing SQL, which is frankly not realistic. ODBC wrapper libraries typically punt on this problem and use the length of the input data as the column size, expecting the database to signal a type error if the data is too large. Binding an empty string will result in a varchar(0) type, which the OpenEdge ODBC driver rejects. The client libraries could be altered to use a minimum column size of 1 for string parameters, but modifying all of these libraries to suit the OpenEdge driver is probably not feasible. Conventional Usage Existing database systems already allow parameters with a 0 column size to support exactly this pattern of usage. PostgreSQL, like OpenEdge, does not allow columns of type varchar(0), but the official psqlodbc ODBC driver accepts string parameters with a column size of 0. The practical reality is that the ODBC API is excessively difficult to use as specified, and databases, drivers, and client libraries support more realistic uses by being flexible with their input types. This is a relative minor change that would eliminate a whole set of ODBC client bugs. It's technically valid, useful for applications using existing wrapper libraries for ODBC, and it matches what other databases are doing. [1] Describing Parameters
Continue reading...
Continue reading...