How to insert the default database value via Parameters?

How to insert the default database value via Parameters?

Postby 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
Guest
 

Postby 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();
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby 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
Guest
 

Postby 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.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby 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
Guest
 

Postby Alexey » Fri 04 Aug 2006 15:16

We will discuss the possibility of implementing this feature. Look forward to hearing from us soon.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby 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.
Serious
 


Return to dotConnect for MySQL