Tim Bunce wrote:
> 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.)
Of course, this is one particular ODBC driver. DBD::ODBC has the
"luxury" in this one case of not having to do anything but leave it up
to the ODBC driver to produce the result-set. We wrote this ODBC driver
so obviously it conforms with the spec to the best we can make it.
You should also be aware that although the Microsoft SQL Server driver
(or at least one of the many many versions of their driver) returns the
same as above it for those columns it returns more columns than above -
another 6 IIRC.
>
> 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.
>
>
DBD::Oracle to one of our databases with:
use DBI;
use strict;
use warnings;
my $h = DBI->connect;
eval {$h->do(q{drop table martin})};
my $table = << 'EOT';
create table martin (a int default NULL,
b int default 1,
c char(20) default 'fred',
d varchar2(30) default user,
e int)
EOT
$h->do($table);
DBI::dump_results($h->column_info(undef, 'XXX', 'MARTIN', undef));
shows:
undef, "XXX", "MARTIN", "A", "3", "NUMBER", "38", "40", "0", "10", "1",
undef, "NULL", "3", undef, undef, "1", "YES"
undef, "XXX", "MARTIN", "B", "3", "NUMBER", "38", "40", "0", "10", "1",
undef, "1", "3", undef, undef, "2", "YES"
undef, "XXX", "MARTIN", "C", "1", "CHAR", "20", "20", undef, undef, "1",
undef, "'fred'", "1", undef, undef, "3", "YES"
undef, "XXX", "MARTIN", "D", "12", "VARCHAR2", "30", "30", undef, undef,
"1", undef, "user", "12", undef, undef, "4", "YES"
undef, "XXX", "MARTIN", "E", "3", "NUMBER", "38", "40", "0", "10", "1",
undef, undef, "3", undef, undef, "5", "YES"
but from what I remember (October 2001 when Steffen came up with it), it
is just a massive SQL statement and probably could be altered without
too many problems (for someone else ;-) - I've got way too many problems
with DBD::Oracle and JSON::XS and bits of them not working with unicode).
Notice I added a column with no default which I missed off the ODBC one
to SQL Server so here it is again (different fn and not varchar2 obviously):
'master', 'dbo', 'martin', 'a', '4', 'int', '10', '4', '0', '10', '1',
undef, '(NULL)', '4', undef, undef, '1', 'YES', undef, undef, undef,
undef, undef, undef, '38'
'master', 'dbo', 'martin', 'b', '4', 'int', '10', '4', '0', '10', '1',
undef, '((1))', '4', undef, undef, '2', 'YES', undef, undef, undef,
undef, undef, undef, '38'
'master', 'dbo', 'martin', 'c', '1', 'char', '20', '20', undef, undef,
'1', undef, '('fred')', '1', undef, '20', '3', 'YES', undef, undef,
undef, undef, undef, undef, '39'
'master', 'dbo', 'martin', 'd', '12', 'varchar', '30', '30', undef,
undef, '1', undef, '(user_name())', '12', undef, '30', '4', 'YES',
undef, undef, undef, undef, undef, undef, '39'
'master', 'dbo', 'martin', 'e', '4', 'int', '10', '4', '0', '10', '1',
undef, undef, '4', undef, undef, '5', 'YES', undef, undef, undef, undef,
undef, undef, '38'
I don't think mysql accepts functions in default clause.
So missing out the d column above I get:
undef, undef, 'martin', 'e', 4, 'INT', '11', undef, undef, 10, 1, undef,
undef, 4, undef, undef, 4, 'YES', undef, undef, undef, undef, undef,
undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
undef, undef, undef, '', 'int(11)', undef
undef, undef, 'martin', 'c', 1, 'CHAR', '20', undef, undef, undef, 1,
undef, 'fred', 1, undef, undef, 3, 'YES', undef, undef, undef, undef,
undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
undef, undef, undef, undef, '', 'char(20)', undef
undef, undef, 'martin', 'a', 4, 'INT', '11', undef, undef, 10, 1, undef,
undef, 4, undef, undef, 1, 'YES', undef, undef, undef, undef, undef,
undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
undef, undef, undef, '', 'int(11)', undef
undef, undef, 'martin', 'b', 4, 'INT', '11', undef, undef, 10, 1, undef,
'1', 4, undef, undef, 2, 'YES', undef, undef, undef, undef, undef,
undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
undef, undef, undef, '', 'int(11)', undef
However, this is a rather old (now) mysql and DBD::mysql now whereas the
other 2 I provided are up to date modules:
perl -MDBD::mysql -le 'print $DBD::mysql::VERSION'
3.0006
You would probably be better off getting something for mysql from a
newer one although the last column_info change I see in the changes was
early 2007.
DBD::ODBC 1.21
DBD::Oracle 1.23
DBI: various but all 1.60*
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
