Page 1 of 1
How to insert the default database value via Parameters?
Posted: Thu 03 Aug 2006 20:52
by Guest
Hello!
I use a prepared and parameterized INSERT-Script to insert data into a
MySQL database. Sometimes I want to insert the default value into a
column like its done with SQL:
Code: Select all
INSERT INTO myTable (field1, field2) VALUES ("text", "anotherText");
INSERT INTO myTable (field1, field2) VALUES ("text", DEFAULT); // <- Insert Default
How can I insert the SQL-DEFAULT-Command into a parameter?
Code: Select all
command.Parameters[1] = "DEFAULT";
doesn't work...
Regards,
Florian Heinemann
Software developer for dbcopy.com
Posted: Fri 04 Aug 2006 06:14
by Alexey
It might be something like this:
Code: Select all
command.CommandText = "INSERT INTO myTable (field1) VALUES(:field1);
command.Parameters.Add("field1", "text");
command.ExecuteNonQuery();
Posted: Fri 04 Aug 2006 10:36
by Guest
For sure - that will work.
But I've a prepared SQL statement so that I cannot change the number
of parameters while inserting data. Additionaly i don't know (before
inserting) when I need the default value. Our applications copies data
from one database to another. When our applications is not able to
convert the data, we want to insert the default value.
It would be great if you could offer a method like
command.parameters[myParam].SetDefault();
to support the DEFAULT-Command in INSERT-Scripts.
Regards,
Florian Heinemann
Posted: Fri 04 Aug 2006 12:09
by Alexey
Parameter knows nothing about default value in some column, so it is not possible to do what you need.
Posted: Fri 04 Aug 2006 12:46
by Guest
Thats right, but I think not important. The DEFAULT-Command is a
command that you can use directly in the INSERT-Script. It's essential
when you are working with strict tables, where you cannot leave fields
unassigned. You can use it directly as shown above:
Code: Select all
INSERT INTO myTable (field1, field2) VALUES ("text", DEFAULT);
More information about this:
http://mysql.com/doc/refman/4.1/en/insert.html
The INSERT-Syntax:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr |
DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
So writing "DEFAULT" instead of a parameter is the only thing that have
to be done. The Parameter class needs no information about the real
Defaults.
It would be really great, if you you could implement this feature.
Regards,
Florian Heinemann
Posted: Fri 04 Aug 2006 15:16
by Alexey
We will discuss the possibility of implementing this feature. Look forward to hearing from us soon.
Posted: Fri 11 Aug 2006 13:29
by Serious
It is impossible to insert default column values in prepared statements using DEFAULT keyword.
To insert default values with prepared statements you can use following SQL:
Code: Select all
-- table definition: create table def_test(id int primary key auto_increment, f_varchar varchar(20) default 'default');
insert into def_test (f_varchar) values (if(:useDefaultValue, default(f_varchar), :value))
Then you can decide whether to insert default values or not by setting "useDefaultValue" parameter to 1 or 0.