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 _v=
alue},
'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!

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 greg@turnstep.com
PGP Key: 0x14964AC8 200604241124
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B90 6714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFETO4zvJuQZxSWSsgRApL3AKDLdmt0B+G0d5eziZYMnEW3LyULsQCg zj5n
gJWNshh94iV5vrdHDLnuA7k=
=DgPG
-----END PGP SIGNATURE-----

Re: how to set a DEFAULT value !!

On 4/24/06, Greg Sabino Mullane 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 sup=
port 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 =3D ( key1 =3D> 'defaultvalue1', key2 =3D> '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 greg@turnstep.com
PGP Key: 0x14964AC8 200604262344
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B90 6714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFEUD4jvJuQZxSWSsgRAjUPAJ9X8mTMNT2cPZYCPHVFVuBr2ydccQCd FFma
BbE1KNnv1ofBwOkd8UhOFM8=
=j+L7
-----END PGP SIGNATURE-----

Re: how to set a DEFAULT value !!

On Thu, 27 Apr 2006 03:45:22 -0000, 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
ron@savage.net.au
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 greg@turnstep.com
PGP Key: 0x14964AC8 200604270030
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B90 6714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFEUEo0vJuQZxSWSsgRAmAYAKDcMYGCUfTkpsVBGvTDr+rD1sjf/gCd GvYI
lpjCTQT14ynvtN2LOV++rLs=
=ww4D
-----END PGP SIGNATURE-----

Re: how to set a DEFAULT value !!

On Thu, 27 Apr 2006 04:37:20 -0000, 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
ron@savage.net.au
http://savage.net.au/index.html

Re: how to set a DEFAULT value !!

------=_Part_50009_27914494.1146122808328
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

On 4/26/06, Greg Sabino Mullane 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 #include
Guardian of DBD::Informix - v2005.02 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."

------=_Part_50009_27914494.1146122808328--

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
cj10@cam.ac.uk 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 greg@turnstep.com
PGP Key: 0x14964AC8 200604271801
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B90 6714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFEUUAZvJuQZxSWSsgRAku7AJ4oios4B4DeHNFry+VwFnd5z6NGMgCf TPsS
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 greg@turnstep.com
PGP Key: 0x14964AC8 200604271837
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B90 6714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFEUUfrvJuQZxSWSsgRAt/YAKDvcFCDz41zVERWPb3OuI5Bmg3k1QCg yYmt
Z6Id4DtXS519enpBJWN214U=
=/wPB
-----END PGP SIGNATURE-----

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
cj10@cam.ac.uk 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 greg@turnstep.com
PGP Key: 0x14964AC8 200604281323
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B90 6714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFEUk/NvJuQZxSWSsgRAj24AKD0R8hOCKQd4wb8vV0XUr3Wr4+xjQCc CdUj
smeCkeJKYWHdDOoVncib6mU=
=wIpH
-----END PGP SIGNATURE-----