-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
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?
>
> Consider the difference between these two column definitions
>
> bar1 timestamp not null default 'current_timestamp',
> bar2 timestamp not null default current_timestamp,
>
> or, more generally:
>
> foo1 varchar(20) not null default 'current_user',
> foo2 varchar(20) not null default current_user,
>
> This issue has cropped up in relation to a bug Jos has filed against
> DBIx::Class::Schema::Loader: https://rt.cpan.org/Ticket/Display.html?id=46412
>
> The ODBC 3.0 spec says
> http://web.archive.org/web/20070513203826/http://msdn.microsoft.com/library/en-us/odbc/htm/odbcsqlcolumns.asp
>
> The default value of the column. The value in this column should be
> interpreted as a string *if it is enclosed in quotation marks*.
>
> If NULL was specified as the default value, then this column is the word
> NULL, not enclosed in quotation marks. If the default value cannot be
> represented without truncation, then this column contains TRUNCATED,
> with no enclosing single quotation marks. If no default value was
> specified, then this column is NULL.
>
> *The value of COLUMN_DEF can be used in generating a new column
> definition*, except when it contains the value TRUNCATED.
>
> (The *emphasis* is mine.)
>
> So, people, what does your database driver do for these cases?
> Are COLUMN_DEF values for literal defaults returned by column_info()
> enclosed in quotation marks?
>
> Tim.
>
>
I ran the following code to SQL Server via the Easysoft SQL Server ODBC
driver and DBD::ODBC:
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 char(30) default current_user);
EOT
$h->do($table);
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. 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.
'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', '1', 'char', '30', '30', undef, undef,
'1', undef, '(user_name())', '1', undef, '30', '4', 'YES', undef, undef,
undef, undef, undef, undef, '39'
4 rows
Martin
- --
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFKHVlfhd1KVpsamNgRAqdHAJ42KesVvO3uxy0M20X14PfSYYwVigCfVhko
PUZrbicrua54kN5bc+XpLK4=
=alsQ
-----END PGP SIGNATURE-----
