How to insert the default database value via Parameters?

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
Guest

How to insert the default database value via Parameters?

Post by Guest » Thu 03 Aug 2006 20:52

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

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Fri 04 Aug 2006 06:14

It might be something like this:

Code: Select all

      command.CommandText = "INSERT INTO myTable (field1) VALUES(:field1); 
      command.Parameters.Add("field1", "text");
      command.ExecuteNonQuery();

Guest

Post by Guest » Fri 04 Aug 2006 10:36

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

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Fri 04 Aug 2006 12:09

Parameter knows nothing about default value in some column, so it is not possible to do what you need.

Guest

Post by Guest » Fri 04 Aug 2006 12:46

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

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Fri 04 Aug 2006 15:16

We will discuss the possibility of implementing this feature. Look forward to hearing from us soon.

Serious

Post by Serious » Fri 11 Aug 2006 13:29

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.

Post Reply