Hi

i would have a trigger to check the data before inserting/updating a
table and if the data is not valid it would raise an error ...

like if new.total < 0 then raise_error(10001, ' you cannot use the
resources anymore') end if ;

in oracle i used raise_application_error(errcode, 'explaination');

how to do the same thing on mysql ?

I also got an 'weird' data handling in mysql, suppose the field was
"not null" but when i tried to insert/update the data .. it still able
to insert the data ...

like
tableX
{
id : int, auto increment, primary key
name : varchar(20) ; not null ;
desc : varchar(20): not null ;
}

when i did a script like this
insert into tableX (name) values ('ABCDEF');

it would insert into database without generating error ... it should
raise an error because 'desc' should be filled in (not null).


how to solve this problems without my program doing a checking one by
one ?

Thanks
adwin

Adwin Wijaya wrote:
> Hi
>
> i would have a trigger to check the data before inserting/updating a
> table and if the data is not valid it would raise an error ...
>
> like if new.total < 0 then raise_error(10001, ' you cannot use the
> resources anymore') end if ;
>
> in oracle i used raise_application_error(errcode, 'explaination');
>
> how to do the same thing on mysql ?
>
> I also got an 'weird' data handling in mysql, suppose the field was
> "not null" but when i tried to insert/update the data .. it still able
> to insert the data ...
>
> like
> tableX
> {
> id : int, auto increment, primary key
> name : varchar(20) ; not null ;
> desc : varchar(20): not null ;
> }
>
> when i did a script like this
> insert into tableX (name) values ('ABCDEF');
>
> it would insert into database without generating error ... it should
> raise an error because 'desc' should be filled in (not null).
>
>
> how to solve this problems without my program doing a checking one by
> one ?
>
> Thanks
> adwin


To answer your question - MYSQL is not yet a real database engine and
has a long way to go to there. It will do the insert and does return
with a WARNING status. In most other REAL DB engines this would fail
altogether as NOT NULL should mean "HEY STUPID YOU CANT DO THAT", but
the MySQL developers haven't got that far yet "YEA, IT SAYS NOT NULL
BUT, HEY, WHATEVER"... It does return a warning and you should
check to ensure it is SUCCESS not WARNING.

In my "test" version rollbacks seem to be broke. (autocommit=0)

Michael Austin wrote:
> Adwin Wijaya wrote:
>> Hi
>>
>> i would have a trigger to check the data before inserting/updating a
>> table and if the data is not valid it would raise an error ...
>>
>> like if new.total < 0 then raise_error(10001, ' you cannot use the
>> resources anymore') end if ;
>>
>> in oracle i used raise_application_error(errcode, 'explaination');
>>
>> how to do the same thing on mysql ?
>>
>> I also got an 'weird' data handling in mysql, suppose the field was
>> "not null" but when i tried to insert/update the data .. it still able
>> to insert the data ...
>>
>> like
>> tableX
>> {
>> id : int, auto increment, primary key
>> name : varchar(20) ; not null ;
>> desc : varchar(20): not null ;
>> }
>>
>> when i did a script like this
>> insert into tableX (name) values ('ABCDEF');
>>
>> it would insert into database without generating error ... it should
>> raise an error because 'desc' should be filled in (not null).
>>
>>
>> how to solve this problems without my program doing a checking one by
>> one ?
>>
>> Thanks
>> adwin
>
>
> To answer your question - MYSQL is not yet a real database engine and
> has a long way to go to there. It will do the insert and does return
> with a WARNING status. In most other REAL DB engines this would fail
> altogether as NOT NULL should mean "HEY STUPID YOU CANT DO THAT", but
> the MySQL developers haven't got that far yet "YEA, IT SAYS NOT NULL
> BUT, HEY, WHATEVER"... It does return a warning and you should
> check to ensure it is SUCCESS not WARNING.
>
> In my "test" version rollbacks seem to be broke. (autocommit=0)
>

Horse Hockey. MySQL is quite a competent database. Just because it's a
little looser than some other databases doesn't mean it's not a "real"
database.

Try strict mode - it is much more ANSI compliant (although I'm not
positive it will catch this particular problem).

And I don't know what your problem with rollbacks is. They work fine on
my system when using INNODB.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Mon, 07 Apr 2008 09:18:40 -0400, Jerry Stuckle wrote:
> Michael Austin wrote:
>> Adwin Wijaya wrote:
>>> Hi
>>>
>>> i would have a trigger to check the data before inserting/updating a
>>> table and if the data is not valid it would raise an error ...
>>>
>>> like if new.total < 0 then raise_error(10001, ' you cannot use the
>>> resources anymore') end if ;
>>>
>>> in oracle i used raise_application_error(errcode, 'explaination');
>>>
>>> how to do the same thing on mysql ?
>>>
>>> I also got an 'weird' data handling in mysql, suppose the field was
>>> "not null" but when i tried to insert/update the data .. it still able
>>> to insert the data ...
>>>
>>> like
>>> tableX
>>> {
>>> id : int, auto increment, primary key
>>> name : varchar(20) ; not null ;
>>> desc : varchar(20): not null ;
>>> }
>>>
>>> when i did a script like this
>>> insert into tableX (name) values ('ABCDEF');
>>>
>>> it would insert into database without generating error ... it should
>>> raise an error because 'desc' should be filled in (not null).
>>>
>>>
>>> how to solve this problems without my program doing a checking one by
>>> one ?
>>>
>>> Thanks
>>> adwin
>>
>>
>> To answer your question - MYSQL is not yet a real database engine and
>> has a long way to go to there. It will do the insert and does return
>> with a WARNING status. In most other REAL DB engines this would fail
>> altogether as NOT NULL should mean "HEY STUPID YOU CANT DO THAT", but
>> the MySQL developers haven't got that far yet "YEA, IT SAYS NOT NULL
>> BUT, HEY, WHATEVER"... It does return a warning and you should
>> check to ensure it is SUCCESS not WARNING.
>>
>> In my "test" version rollbacks seem to be broke. (autocommit=0)
>>
>
> Horse Hockey. MySQL is quite a competent database. Just because it's a
> little looser than some other databases doesn't mean it's not a "real"
> database.
>
> Try strict mode - it is much more ANSI compliant (although I'm not
> positive it will catch this particular problem).

It does, at least on 5.0.31-log

mysql> show create table tablex;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| tablex | CREATE TABLE `tablex` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select @@session.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode
|
+-------------------------------------------------------------------------------------------------------------------------------+
|
STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_D ATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADIT IONAL,NO_AUTO_CREATE_USER
|
+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> insert into tablex (id) values (5);
ERROR 1364 (HY000): Field 'name' doesn't have a default value
mysql> insert into tablex (id,name) values (5,'my name is larry');
Query OK, 1 row affected (0.00 sec)

mysql>

The key part of the SQL_MODE for making this do what the OP expects is
TRADITIONAL, which the fine manual summarizes as "A simple description
of this mode is 'give an error instead of a warning' when inserting an
incorrect value into a column."

--
47. If I learn that a callow youth has begun a quest to destroy me, I will
slay him while he is still a callow youth instead of waiting for him to
mature.
--Peter Anspach's list of things to do as an Evil Overlord