On Wed, May 27, 2009 at 04:16:48PM +0100, Martin Evans wrote:
> Tim Bunce wrote:
> > How does your DBI driver represent a default column value in the results
> > returned by the column_info() method?
> > Specifically, does it distinguish between default literal strings and
> > default functions/expressions?
>
> I ran the following code to SQL Server via the Easysoft SQL Server ODBC
> driver and DBD::ODBC:
>
> create table martin (a int default NULL,
> b int default 1,
> c char(20) default 'fred',
> d char(30) default current_user);
> DBI::dump_results($h->column_info(undef, undef, 'martin', undef));
>
> The results are below. If you would like more types of defaults let me know.
> 'master', 'dbo', 'martin', 'a', '4', 'int', '10', '4', '0', '10', '1', undef,
> '(NULL)',
> 'master', 'dbo', 'martin', 'b', '4', 'int', '10', '4', '0', '10', '1', >
> undef, '((1))',
> 'master', 'dbo', 'martin', 'c', '1', 'char', '20', '20', undef, undef, > '1',
> undef, '('fred')',
> 'master', 'dbo', 'martin', 'd', '1', 'char', '30', '30', undef, undef, > '1',
> undef, '(user_name())',
So ODBC matches the spec and, like Postgres, is reporting an expression
rather than the original literal text. (And wrapping it in parens,
presumably to avoid precedence issues if used in an expression.)
For now I've a draft patch to the DBI docs that looks like:
-B<COLUMN_DEF>: The default value of the column.
+B<COLUMN_DEF>: The default value of the column, in a format that can be used
+directly in an SQL statement.
+
+Note that this may be an expression and not simply the text used for the
+default value in the original CREATE TABLE statement. For example, given:
+
+ col1 char(30) default current_user
+ col2 char(30) default 'string'
+
+where "current_user" is the name of a function, the corresponding C<COLUMN_DEF>
+values would be:
+
+ Database col1 col2
+ Postgres: "current_user"() 'string'::text
+ MS SQL: (user_name()) ('string')
+
> I could in theory run this to around 10 databases via 4 or 5 DBDs
> but I'd really need ALOT or persuasion that I was helping out big time
> to to that.
I'd be interested in the COLUMN_DEF values for other databases and DBDs
but it's not urgent. Hopefully others can fill in the gaps.
(Oracle and mysql are two big missing databases at the moment.).
Tim.
