ExectureNonQuery gets schema?

ExectureNonQuery gets schema?

Postby ianb » Sun 31 Dec 2006 18:19

I am doing an insert using ExecuteNonQuery on a table that has a structure like
id bigint(20) NOT NULL, name varchar(45)

There is an insert trigger that sets id if not supplied. An insert such as INSERT test (name) values(@val) works ok in mysqlbrowser but fails when calling executeNonQuery with an error 'Field 'id' doesnt have a default value'. My question is, is the provider doing something that mysqlbrowser isn't in order to cause this error?

Thanks
Ian
ianb
 
Posts: 4
Joined: Tue 31 May 2005 13:21

Postby Alexey » Tue 02 Jan 2007 12:31

Please provide me with your trigger definition.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby ianb » Tue 02 Jan 2007 21:21

Here's the table definition and sample query:

INSERT INTO testtbl SET name = 'cxcc';


SET NAMES utf8;

SET SQL_MODE='';
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';

/*Table structure for table `testtbl` */

CREATE TABLE `testtbl` (
`id` bigint(20) NOT NULL,
`name` varchar(14) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


/*Trigger structure for table `testtbl` */

DELIMITER $$

DROP TRIGGER `ins`$$

CREATE TRIGGER `ins` BEFORE INSERT ON `testtbl` FOR EACH ROW BEGIN
if NEW.id = 0 or NEW.id IS NULL THEN
SET NEW.id = COALESCE((SELECT MAX(id)+1 FROM testtbl), 1);
END IF;
END$$


DELIMITER ;

SET SQL_MODE=@OLD_SQL_MODE;
ianb
 
Posts: 4
Joined: Tue 31 May 2005 13:21

Postby Alexey » Wed 03 Jan 2007 07:28

What version of MySQLDirect .NET do you use? Try to use the latest one. Should work without any problem.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby ianb » Wed 03 Jan 2007 15:42

Hello,
I updated to version 3.55.18.0. Same problem.
Thanks
Ian
ianb
 
Posts: 4
Joined: Tue 31 May 2005 13:21

Postby Alexey » Fri 05 Jan 2007 16:41

We cannot reproduce such problem.
Please send us small test project to reproduce the problem.
Use e-mail address provided in the Readme file.
Do not use third party components.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for MySQL