Re: how to set a DEFAULT value !!
I wrote: Whoa. Be careful what you say about Oracle. Oracle does have default values for table columns, defined by the DEFAULT clause in CREATE/ALTER TABLE. However, the _only_ way to get a column set to the default value is to leave the column out of the INSERT statement altogether. There is nothing you can put in a VALUES(..) list which will do the trick, nor is there anything that can be bound to a placeholder which will result in the default being set. and Greg Sabino Mullane replied: You sure about that? You might want to check your docs, or update to a newer version of Oracle. If I recall correctly, this ability was added in 9i. Doh. You are right. Oracle 9i does support the DEFAULT keywords in VALUES(...) lists. I apologise for my mistake. My feeble excuse is that this new use of the word DEFAULT is not indexed in either the 9i or the 10g SQL Reference Manual. However, this does not help as much as you might think. DBD::Oracle does not implement placeholder binding by re-writing statements itself. Instead it uses Oracle's internal implementation of placeholders. The second half of my statement above still appears to be true. If a statement like the following has been prepared INSERT ... VALUES ( ? ) there is no way of binding anything to the placeholder which make the statement act like INSERT ... VALUES ( DEFAULT ) The only possibilities are to bind a value or to bind a NULL. I would be very happy to be proved wrong on this point, but I have checked the 9i and 10g OCI manuals, and I am depressingly sure that I am right. So - my point remains. DBD::Oracle, as designed, cannot implement the suggested feature. -- Charles Jardine - Computing Service, University of Cambridge [EMAIL PROTECTED]Tel: +44 1223 334506, Fax: +44 1223 334679
Re: how to set a DEFAULT value !!
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 DBD::Oracle does not implement placeholder binding by re-writing statements itself. Instead it uses Oracle's internal implementation of placeholders. Yes, unfortunately PostgreSQL does not support the use of DEFAULT inside of its server-side prepared statements either. However, someday it will, and DBD::Pg will be ready! :) Currently, if any of the values (e.g. the xeecute() array) is a DEFAULT value, DBD::Pg switches transparently back to the old style of prepared statements by doing the placeholder substituting itself, and then sending the computed string to the backend to be executed. So, we potentially lose a tiny bit of speed but allow people to not have to create a separate statement handle (which they can still do of course, if performance becomes that much of an issue). So - my point remains. DBD::Oracle, as designed, cannot implement the suggested feature. Well, I don't think it will actually involve any shared DBI logic code, but I would like to see a common syntax used, e.g. something like $DBI_DEFAULT. Drivers would be free to have their dbdimp.c take advantage of it or not. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200604281323 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFEUk/NvJuQZxSWSsgRAj24AKD0R8hOCKQd4wb8vV0XUr3Wr4+xjQCcCdUj smeCkeJKYWHdDOoVncib6mU= =wIpH -END PGP SIGNATURE-
Re: how to set a DEFAULT value !!
On 4/26/06, Greg Sabino Mullane [EMAIL PROTECTED] wrote: DBI is complex enough, and AIUI the DBI philosophy opposes adding features to the core that will cause implementation headaches for driver authors. The standard perl idiom for default values is You misunderstand. The DEFAULT is on the database side, not the client, and is represented by sending the literal string 'DEFAULT' to the backend, similar to the way that null values are sent by the literal string 'NULL'. The database then populates the column with whatever the default has been set as, which may be a constant, or may be (in PostgreSQL's case) an arbitrarily complex expression or call to a stored procedure. Is it a string that's sent, or the identifier? For NULL, it is either an identifier (not quoted) or Perl undef that denotes NULL in the DBMS. I'm not sure how you'd represent DEFAULT in Perl, or as a string rather than an identifier. -- Jonathan Leffler [EMAIL PROTECTED] #include disclaimer.h Guardian of DBD::Informix - v2005.02 - http://dbi.perl.org I don't suffer from insanity - I enjoy every minute of it.
Re: how to set a DEFAULT value !!
Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 NotDashEscaped: You need GnuPG to verify this message Which database server is this? This is definitely the behavior of MySQL, PostgreSQL, and Oracle, and I'm pretty sure most others follow it as well. Whoa. Be careful what you say about Oracle. Oracle does have default values for table columns, defined by the DEFAULT clause in CREATE/ALTER TABLE. However, the _only_ way to get a column set to the default value is to leave the column out of the INSERT statement altogether. There is nothing you can put in a VALUES(..) list which will do the trick, nor is there anything that can be bound to a placeholder which will result in the default being set. DBD::Oracle cannot be enhanced to provide a facility to get a placeholder replaced by the default value for a column, since the OCI API does not support it. This seems to me to be a sufficient reason not to attempt to extend the DBI to provide this facility. Perhaps it would be better if the authors of DBDs which can support it could be persuaded to do it using a special bind value, as DBD:Pg apparently does does. -- Charles Jardine - Computing Service, University of Cambridge [EMAIL PROTECTED]Tel: +44 1223 334506, Fax: +44 1223 334679
Re: how to set a DEFAULT value !!
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Is it a string that's sent, or the identifier? For NULL, it is either an
identifier (not quoted) or Perl undef that denotes NULL in the DBMS. I'm
not sure how you'd represent DEFAULT in Perl, or as a string rather than an
identifier.
DBI (or DBD) currently maps undef to the literal string NULL before sending it
to the backend. To achieve other values, we have to use something besides a
simple scalar. In DBD::Pg's case, we're using a blessed ref, so the backend
does something like this:
if (! defined $value) {
$value = NULL;
}
elsif (ref $value eq 'DBD::Pg::DefaultValue') {
$value = DEFAULT;
}
else {
$value = quote($value);
}
The user would do something like this:
$sth-execute(12,undef,'chocolate',$DBDPG_DEFAULT,99);
Ideally once it's added to DBI the code becomes a little more portable:
$sth-execute(12,undef,'chocolate',$DBI_DEFAULT,99);
I'm working on expanding this into a more general framework, as there are
some other magic variables that could also be usefully sent, such as
CURRENT_TIMESTAMP.
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200604271801
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFEUUAZvJuQZxSWSsgRAku7AJ4oios4B4DeHNFry+VwFnd5z6NGMgCfTPsS
NEqjDqwKEyyWubisf4PEwKQ=
=FLUs
-END PGP SIGNATURE-
Re: how to set a DEFAULT value !!
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 This is definitely the behavior of MySQL, PostgreSQL, and Oracle, and I'm pretty sure most others follow it as well. Whoa. Be careful what you say about Oracle. Oracle does have default values for table columns, defined by the DEFAULT clause in CREATE/ALTER TABLE. However, the _only_ way to get a column set to the default value is to leave the column out of the INSERT statement altogether. There is nothing you can put in a VALUES(..) list which will do the trick, nor is there anything that can be bound to a placeholder which will result in the default being set. You sure about that? You might want to check your docs, or update to a newer version of Oracle. If I recall correctly, this ability was added in 9i. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200604271837 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFEUUfrvJuQZxSWSsgRAt/YAKDvcFCDz41zVERWPb3OuI5Bmg3k1QCgyYmt Z6Id4DtXS519enpBJWN214U= =/wPB -END PGP SIGNATURE-
Re: how to set a DEFAULT value !!
On 4/24/06, Greg Sabino Mullane [EMAIL PROTECTED] wrote: I've proposed adding something simlilar to DBI itself, but I don't recall getting any feedback on it. Presumably once in place DBIx::Class will someday support it. DBI is complex enough, and AIUI the DBI philosophy opposes adding features to the core that will cause implementation headaches for driver authors. The standard perl idiom for default values is %hash = (key1 = 'defaultvalue1', key2 = 'defaultval2', @_ ); Combining that with something like the insert_hash example from perldoc DBI should give you a tidy function that inserts default values. -- David L Nicol Document what you do, then do what you documented
Re: how to set a DEFAULT value !!
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 DBI is complex enough, and AIUI the DBI philosophy opposes adding features to the core that will cause implementation headaches for driver authors. The standard perl idiom for default values is You misunderstand. The DEFAULT is on the database side, not the client, and is represented by sending the literal string 'DEFAULT' to the backend, similar to the way that null values are sent by the literal string 'NULL'. The database then populates the column with whatever the default has been set as, which may be a constant, or may be (in PostgreSQL's case) an arbitrarily complex expression or call to a stored procedure. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200604262344 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFEUD4jvJuQZxSWSsgRAjUPAJ9X8mTMNT2cPZYCPHVFVuBr2ydccQCdFFma BbE1KNnv1ofBwOkd8UhOFM8= =j+L7 -END PGP SIGNATURE-
Re: how to set a DEFAULT value !!
On Thu, 27 Apr 2006 03:45:22 -, Greg Sabino Mullane wrote: Hi Greg You misunderstand. The DEFAULT is on the database side, not the Just curious. Which database server is this? And, can you omit the name of the column you want defaulted, and does this server then insert the default value? -- Ron Savage [EMAIL PROTECTED] http://savage.net.au/index.html
Re: how to set a DEFAULT value !!
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
NotDashEscaped: You need GnuPG to verify this message
Which database server is this?
This is definitely the behavior of MySQL, PostgreSQL, and Oracle,
and I'm pretty sure most others follow it as well.
And, can you omit the name of the column you want defaulted, and does this
server then insert the default value?
Yes. One way to think about this is to realize that *all* columns have
an automatic default of NULL, we are just changing it to something a
little more useful:
CREATE TABLE foo (
a int,
b int DEFAULT 22,
c int DEFAULT stockprice('RHAT')
);
is completely identical to:
CREATE TABLE foo (
a int DEFAULT NULL,
b int DEFAULT 22,
c int DEFAULT stockprice('RHAT')
);
-- A PostgreSQL example.
-- stockprice() is a pl/perl function that returns the real-time value
-- (in cents) of RedHat stock via a web service.
INSERT INTO foo(b) VALUES (14);
SELECT * FROM foo;
a | b | c
---++--
| 14 | 3025
-- We triggered the DEFAULT values of both a and c because we did
-- not specify them
INSERT INTO foo(a,b,c) VALUES (7,DEFAULT,47);
SELECT * FROM foo;
a | b | c
---++--
| 14 | 3025
7 | 22 | 47
-- We told b to use its default value explicitly
INSERT INTO foo(a,b,c) VALUES (DEFAULT,DEFAULT,NULL);
SELECT * FROM foo;
a | b | c
---++--
| 14 | 3025
7 | 22 | 47
| 22 |
-- We told a and b to use their default values, and set c explicitly
It's late here, so hope that made sense. :)
--
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200604270030
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFEUEo0vJuQZxSWSsgRAmAYAKDcMYGCUfTkpsVBGvTDr+rD1sjf/gCdGvYI
lpjCTQT14ynvtN2LOV++rLs=
=ww4D
-END PGP SIGNATURE-
Re: how to set a DEFAULT value !!
On Thu, 27 Apr 2006 04:37:20 -, Greg Sabino Mullane wrote: Hi Greg It's late here, so hope that made sense. :) Yep. It means just that much more I don't know... -- Ron Savage [EMAIL PROTECTED] http://savage.net.au/index.html
Re: how to set a DEFAULT value !!
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I would like to do in SQL
INSERT table VALUES (DEFAULT, NOW());
...
is there any simple way I can write like this?
I'm not exactly sure how DBI::Class does things, but currently
in plain old DBI your only real option is to create a separate statement
handle like this:
my %sth;
$sth{nodefault} = $dbh-prepare(INSERT INTO mytable(foo,bar) VALUES (?, ?));
$sth{nodefault}-execute(11,12);
$sth{default} = $dbh-prepare(INSERT INTO mytable(foo,bar) VALUES (DEFAULT,
?));
$sth{default}-execute(12);
In recent versions of DBD::Pg, you can also pass in a special variable to the
execute method which allows using only one statement handle:
$sth{nodefault}-execute($DBDPG_DEFAULT, 12);
I've proposed adding something simlilar to DBI itself, but I don't recall
getting
any feedback on it. Presumably once in place DBIx::Class will someday support
it.
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200604241124
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFETO4zvJuQZxSWSsgRApL3AKDLdmt0B+G0d5eziZYMnEW3LyULsQCgzj5n
gJWNshh94iV5vrdHDLnuA7k=
=DgPG
-END PGP SIGNATURE-
how to set a DEFAULT value !!
for example, a table, 2 column
Table
| lang_code | CHAR(2)| default: 'en' |
| time | DATETIME | |
I would like to do in SQL
INSERT table VALUES (DEFAULT, NOW());
I use DBIx::Class and it seems not so easy
When I do pupulate, I write like this..
$schema-populate(
'Table',
[ [ qw/lang_code
time/
],
(
[
$schema-source('Table')-column_info('lang_code')-{default_value},
'2006-04-24 12:13:23',
],
)
],
)
Question!
is there any simple way I can write like this?
$schema-populate(
'Table',
...
[ DEFAUT,
NOW(),
]
..
)
thanks a lot!
