Re: JDBC submission?
Posted by: Todd Farmer
Date: May 03, 2010 09:22AM

Hi Ray,

Ray Madigan Wrote:
-------------------------------------------------------
> I have a table that has a date column that in
> other databases I would fill at row insert with a
> default like;
>
> origin DATE NOT NULL DEFAULT
> CURRENT_DATE,

You might consider using TIMESTAMP, here:

http://dev.mysql.com/doc/refman/5.0/en/timestamp.html


> if I type in an insert into the mysql client the
> origin column is filled by the trigger, if I
> submit the same request with and and jdbc I get
> the following
>
> /lemur/resource/sommelier/build.xml:90:
> org.springframework.jdbc.UncategorizedSQLException
> : PreparedStatementCallback; uncategorized
> SQLException for SQL ; SQL state ; error code
> [1364]; Field 'origin' doesn't have a default
> value; nested exception is java.sql.SQLException:
> Field 'origin' doesn't have a default value

The difference in behavior observed between Connector/J and the mysql command-line client is the sql_mode. Connector/J includes "STRICT_TRANS_TABLES" by default, which prevents INSERT statements which do not explicitly define values for all columns with no DEFAULT VALUE clause. Without the "STRICT" modes, such statements are only warnings. You can observe this behavior in the following mysql command-line statements:

mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (a int, b int, c int not null);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 (a, b) values (1, 2);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SET sql_mode = 'STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 (a, b) values (1, 2);
ERROR 1364 (HY000): Field 'c' doesn't have a default value
mysql> create trigger t1_bi BEFORE INSERT ON t1
-> FOR EACH ROW SET new.c = 3;
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t1 (a, b) values (1, 2);
ERROR 1364 (HY000): Field 'c' doesn't have a default value
mysql> truncate t1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
Empty set (0.00 sec)

mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 (a, b) values (1, 2);
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1364 | Field 'c' doesn't have a default value |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+------+---+
| a | b | c |
+------+------+---+
| 1 | 2 | 3 |
+------+------+---+
1 row in set (0.00 sec)

mysql>

The sql_mode checks happen before the trigger is invoked.

The sql_mode is documented here, for further reference:

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

Options: ReplyQuote


Subject
Written By
Posted
May 01, 2010 09:09AM
Re: JDBC submission?
May 03, 2010 09:22AM
July 07, 2010 08:23AM
July 07, 2010 08:29AM
July 08, 2010 08:23AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.